[
https://issues.apache.org/jira/browse/CALCITE-2009?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16313176#comment-16313176
]
Piotr Bojko commented on CALCITE-2009:
--------------------------------------
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)
