You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Piotr Bojko (JIRA)" <ji...@apache.org> on 2018/01/05 14:19:00 UTC
[jira] [Comment Edited] (CALCITE-2009) Possible bug in interpreting
( IN ) OR ( IN ) logic
[ https://issues.apache.org/jira/browse/CALCITE-2009?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16313176#comment-16313176 ]
Piotr Bojko edited comment on CALCITE-2009 at 1/5/18 2:18 PM:
--------------------------------------------------------------
Update.
The problem is somewhere at the SqlToRelConverter.convertQuery level. The query
{code}
select * from ISSUES
{code}
is resolved to
{code}
SELECT `JI`.`ID`, `JI`.`SUMMARY`, `JI`.`PROJECT`, `JI`.`ISSUENUM` AS `NUM`, `JI`.`CREATOR`, `JI`.`ASSIGNEE`, `JI`.`REPORTER`, `JI`.`CREATED`, `JI`.`UPDATED`, `JI`.`DUEDATE` AS `DUE`, `JI`.`RESOLUTIONDATE` AS `RESOLVED`, `JI`.`DESCRIPTION`, `JI`.`ENVIRONMENT`, `JI`.`ISSUETYPE` AS `TYPE`, `JI`.`PRIORITY`, `JI`.`RESOLUTION`, `JI`.`ISSUESTATUS` AS `STATUS`, `JI`.`SECURITY` AS `SECURITYLEVEL`, `JI`.`VOTES`, `JI`.`WATCHES`, `JI`.`TIMEORIGINALESTIMATE` AS `ORIGINALESTIMATE`, `JI`.`TIMEESTIMATE` AS `ESTIMATE`, `JI`.`TIMESPENT`
FROM `INTERNAL`.`JIRAISSUE` AS `JI`
WHERE `JI`.`ID` IN (SELECT `EAI`.`ID`
FROM `EXPLICIT_ALLOWED_ISSUES` AS `EAI`) OR `JI`.`PROJECT` IN (SELECT `AP`.`ID`
FROM `EXPLICIT_ALLOWED_PROJECTS` AS `AP`)
{code}
which is resolved to
{code}
LogicalProject(ID=[$0], SUMMARY=[$8], PROJECT=[$3], NUM=[$2], CREATOR=[$6], ASSIGNEE=[$5], REPORTER=[$4], CREATED=[$14], UPDATED=[$15], DUE=[$16], RESOLVED=[$17], DESCRIPTION=[$9], ENVIRONMENT=[$10], TYPE=[$7], PRIORITY=[$11], RESOLUTION=[$12], STATUS=[$13], SECURITYLEVEL=[$24], VOTES=[$18], WATCHES=[$19], ORIGINALESTIMATE=[$20], ESTIMATE=[$21], TIMESPENT=[$22])
LogicalFilter(condition=[OR(true, true)])
LogicalJoin(condition=[=($3, $28)], joinType=[inner])
LogicalJoin(condition=[=($0, $27)], joinType=[inner])
JdbcTableScan(table=[[INTERNAL, JIRAISSUE]])
LogicalAggregate(group=[{0}])
LogicalProject(ID=[$0])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_ISSUES]])
LogicalAggregate(group=[{0}])
LogicalProject(ID=[$0])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_PROJECTS]])
{code}
which is invalid.
but when passing resolved query (not using the declared view ISSUES) explicitly, plan resolves to:
{code}
LogicalProject(ID=[$0], SUMMARY=[$8], PROJECT=[$3], NUM=[$2], CREATOR=[$6], ASSIGNEE=[$5], REPORTER=[$4], CREATED=[$14], UPDATED=[$15], DUE=[$16], RESOLVED=[$17], DESCRIPTION=[$9], ENVIRONMENT=[$10], TYPE=[$7], PRIORITY=[$11], RESOLUTION=[$12], STATUS=[$13], SECURITYLEVEL=[$24], VOTES=[$18], WATCHES=[$19], ORIGINALESTIMATE=[$20], ESTIMATE=[$21], TIMESPENT=[$22])
LogicalFilter(condition=[OR(IN($0, {LogicalProject(ID=[$0]) EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_ISSUES]]) }), IN($3, {
LogicalProject(ID=[$0])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_PROJECTS]])
}))])
JdbcTableScan(table=[[INTERNAL, JIRAISSUE]])
{code}
which is valid.
was (Author: ptrbojko):
Update.
The problem is somewhere at the SqlToRelConverter.convertQuery level. The query
{code}
select * from ISSUES
{code}
is resolved to
{code}
SELECT `JI`.`ID`, `JI`.`SUMMARY`, `JI`.`PROJECT`, `JI`.`ISSUENUM` AS `NUM`, `JI`.`CREATOR`, `JI`.`ASSIGNEE`, `JI`.`REPORTER`, `JI`.`CREATED`, `JI`.`UPDATED`, `JI`.`DUEDATE` AS `DUE`, `JI`.`RESOLUTIONDATE` AS `RESOLVED`, `JI`.`DESCRIPTION`, `JI`.`ENVIRONMENT`, `JI`.`ISSUETYPE` AS `TYPE`, `JI`.`PRIORITY`, `JI`.`RESOLUTION`, `JI`.`ISSUESTATUS` AS `STATUS`, `JI`.`SECURITY` AS `SECURITYLEVEL`, `JI`.`VOTES`, `JI`.`WATCHES`, `JI`.`TIMEORIGINALESTIMATE` AS `ORIGINALESTIMATE`, `JI`.`TIMEESTIMATE` AS `ESTIMATE`, `JI`.`TIMESPENT`
FROM `INTERNAL`.`JIRAISSUE` AS `JI`
WHERE `JI`.`ID` IN (SELECT `EAI`.`ID`
FROM `EXPLICIT_ALLOWED_ISSUES` AS `EAI`) OR `JI`.`PROJECT` IN (SELECT `AP`.`ID`
FROM `EXPLICIT_ALLOWED_PROJECTS` AS `AP`)
{code}
which is resolved to
{code}
LogicalProject(ID=[$0], SUMMARY=[$8], PROJECT=[$3], NUM=[$2], CREATOR=[$6], ASSIGNEE=[$5], REPORTER=[$4], CREATED=[$14], UPDATED=[$15], DUE=[$16], RESOLVED=[$17], DESCRIPTION=[$9], ENVIRONMENT=[$10], TYPE=[$7], PRIORITY=[$11], RESOLUTION=[$12], STATUS=[$13], SECURITYLEVEL=[$24], VOTES=[$18], WATCHES=[$19], ORIGINALESTIMATE=[$20], ESTIMATE=[$21], TIMESPENT=[$22])
LogicalFilter(condition=[OR(true, true)])
LogicalJoin(condition=[=($3, $28)], joinType=[inner])
LogicalJoin(condition=[=($0, $27)], joinType=[inner])
JdbcTableScan(table=[[INTERNAL, JIRAISSUE]])
LogicalAggregate(group=[{0}])
LogicalProject(ID=[$0])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_ISSUES]])
LogicalAggregate(group=[{0}])
LogicalProject(ID=[$0])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_PROJECTS]])
{code}
which is invalid.
but when pasting resolved query (not using the declared view ISSUES) explicitly, plan resolves to:
{code}
LogicalProject(ID=[$0], SUMMARY=[$8], PROJECT=[$3], NUM=[$2], CREATOR=[$6], ASSIGNEE=[$5], REPORTER=[$4], CREATED=[$14], UPDATED=[$15], DUE=[$16], RESOLVED=[$17], DESCRIPTION=[$9], ENVIRONMENT=[$10], TYPE=[$7], PRIORITY=[$11], RESOLUTION=[$12], STATUS=[$13], SECURITYLEVEL=[$24], VOTES=[$18], WATCHES=[$19], ORIGINALESTIMATE=[$20], ESTIMATE=[$21], TIMESPENT=[$22])
LogicalFilter(condition=[OR(IN($0, {LogicalProject(ID=[$0]) EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_ISSUES]]) }), IN($3, {
LogicalProject(ID=[$0])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_PROJECTS]])
}))])
JdbcTableScan(table=[[INTERNAL, JIRAISSUE]])
{code}
which is valid.
> Possible bug in interpreting ( IN ) OR ( IN ) logic
> -----------------------------------------------------
>
> Key: CALCITE-2009
> URL: https://issues.apache.org/jira/browse/CALCITE-2009
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.14.0, 1.13.0, 1.15.0
> Reporter: Piotr Bojko
> Assignee: Julian Hyde
> Fix For: 1.16.0
>
> Attachments: logs-when-or-is-used.log, logs-when-union-is-used.log
>
>
> I have schema
> {code:javascript}
> {
> "version": "1.0",
> "defaultSchema": "JIRA",
> "schemas": [
> {
> "name": "INTERNAL",
> "type": "custom",
> "factory": "%%UNDERLYING_SCHEMA_FACTORY",
> "operand": {}
> }, {
> "name": "JIRA",
> "type": "custom",
> "factory": "org.apache.calcite.schema.impl.AbstractSchema$Factory",
> "operand": {},
> "tables": [
> {
> "name": "ISSUES",
> "type": "view",
> "sql": [
> "SELECT JI.ID, JI.SUMMARY, JI.PROJECT ",
> "FROM INTERNAL.JIRAISSUE as JI ",
> "WHERE JI.ID IN (SELECT EAI.ID FROM EXPLICIT_ALLOWED_ISSUES AS EAI) ",
> "UNION ",
> "SELECT JI.ID, JI.SUMMARY, JI.PROJECT ",
> "FROM INTERNAL.JIRAISSUE as JI ",
> "WHERE JI.PROJECT IN (SELECT AP.ID FROM ALLOWED_PROJECTS AS AP)"
> ]
> },
> {
> "name": "ALLOWED_PROJECTS",
> "type": "table",
> "factory": "%%DELEGATING_TABLE_FACTORY"
> },
> {
> "name": "EXPLICIT_ALLOWED_ISSUES",
> "type": "table",
> "factory": "%%DELEGATING_TABLE_FACTORY"
> }
> ]
> }
> ]
> }
> {code}
> Where INTERNAL schema points to JDBC native JIRA Schema (through my custom factory, but it only wraps passing datasource instead of user/password/url to db) and JIRA schema has one view and two tables (factories generate java data wrapped in AbstractQueryableTable)
> When running "SELECT * FROM ISSUES" all works.
> But when changing the view from UNION construction to OR - engine provides no results:
> {code:javascript}
> {
> "version": "1.0",
> "defaultSchema": "JIRA",
> "schemas": [
> {
> "name": "INTERNAL",
> "type": "custom",
> "factory": "%%UNDERLYING_SCHEMA_FACTORY",
> "operand": {}
> }, {
> "name": "JIRA",
> "type": "custom",
> "factory": "org.apache.calcite.schema.impl.AbstractSchema$Factory",
> "operand": {},
> "tables": [
> {
> "name": "ISSUES",
> "type": "view",
> "sql": [
> "SELECT JI.ID, JI.SUMMARY, JI.PROJECT ",
> "FROM INTERNAL.JIRAISSUE as JI ",
> "WHERE JI.ID IN (SELECT EAI.ID FROM EXPLICIT_ALLOWED_ISSUES AS EAI) ",
> "OR (JI.PROJECT IN (SELECT AP.ID FROM ALLOWED_PROJECTS AS AP))"
> ]
> },
> {
> "name": "ALLOWED_PROJECTS",
> "type": "table",
> "factory": "%%DELEGATING_TABLE_FACTORY"
> },
> {
> "name": "EXPLICIT_ALLOWED_ISSUES",
> "type": "table",
> "factory": "%%DELEGATING_TABLE_FACTORY"
> }
> ]
> }
> ]
> }
> {code}
> Note that %%factories are a simple workaround not to changing the schema each time my java code is refactored.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)