[
https://issues.apache.org/jira/browse/CALCITE-2009?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16311520#comment-16311520
]
Piotr Bojko commented on CALCITE-2009:
--------------------------------------
I've checked that before volcano planner comes in - the plan is as follows:
{code}
LogicalProject(subset=[rel#8075:Subset#11.ENUMERABLE.[]], ID=[$0],
SUMMARY=[$7], PROJECT=[$2], NUM=[$1], CREATOR=[$5], ASSIGNEE=[$4],
REPORTER=[$3], CREATED=[$13], UPDATED=[$14], DUE=[$15], RESOLVED=[$16],
DESCRIPTION=[$8], ENVIRONMENT=[$9], TYPE=[$6], PRIORITY=[$10],
RESOLUTION=[$11], STATUS=[$12], SECURITYLEVEL=[$22], VOTES=[$17],
WATCHES=[$18], ORIGINALESTIMATE=[$19], ESTIMATE=[$20], TIMESPENT=[$21])
LogicalFilter(subset=[rel#8063:Subset#10.NONE.[]], condition=[OR(true, true)])
LogicalJoin(subset=[rel#8061:Subset#9.NONE.[]], condition=[=($2, $24)],
joinType=[inner])
LogicalJoin(subset=[rel#8054:Subset#5.NONE.[]], condition=[=($0, $23)],
joinType=[inner])
LogicalProject(subset=[rel#8047:Subset#1.NONE.[]], ID=[$0],
ISSUENUM=[$2], PROJECT=[$3], REPORTER=[$4], ASSIGNEE=[$5], CREATOR=[$6],
ISSUETYPE=[$7], SUMMARY=[$8], DESCRIPTION=[$9], ENVIRONMENT=[$10],
PRIORITY=[$11], RESOLUTION=[$12], ISSUESTATUS=[$13], CREATED=[$14],
UPDATED=[$15], DUEDATE=[$16], RESOLUTIONDATE=[$17], VOTES=[$18], WATCHES=[$19],
TIMEORIGINALESTIMATE=[$20], TIMEESTIMATE=[$21], TIMESPENT=[$22], SECURITY=[$24])
JdbcTableScan(subset=[rel#8045:Subset#0.JDBC.INTERNAL.[]],
table=[[INTERNAL, JIRAISSUE]])
LogicalAggregate(subset=[rel#8052:Subset#4.NONE.[]], group=[{0}])
LogicalProject(subset=[rel#8050:Subset#3.NONE.[]], ID=[$0])
EnumerableTableScan(subset=[rel#8048:Subset#2.ENUMERABLE.[]],
table=[[JIRA, EXPLICIT_ALLOWED_ISSUES]])
LogicalAggregate(subset=[rel#8059:Subset#8.NONE.[]], group=[{0}])
LogicalProject(subset=[rel#8057:Subset#7.NONE.[]], ID=[$0])
EnumerableTableScan(subset=[rel#8055:Subset#6.ENUMERABLE.[]],
table=[[JIRA, EXPLICIT_ALLOWED_PROJECTS]])
{code}
There are two inner joins where both should be left, I think.
So, the possible bug is somewhere between parsing query and producing relnode
tree.
> 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)
