[
https://issues.apache.org/jira/browse/CALCITE-2009?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16292601#comment-16292601
]
Piotr Bojko commented on CALCITE-2009:
--------------------------------------
I've compared the plans for both queries.
The good one (with proper result data):
{code}
explain plan for select * from (
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}
produces plan:
{code}
EnumerableCalc(expr#0..27=[{inputs}], expr#28=[IS NOT NULL($t24)], expr#29=[IS
NOT NULL($t27)], expr#30=[0], expr#31=[=($t25, $t30)], expr#32=[NOT($t31)],
expr#33=[AND($t29, $t32)], expr#34=[CAST($t33):BOOLEAN], expr#35=[OR($t28,
$t34)], ID=[$t0], SUMMARY=[$t7], PROJECT=[$t2], NUM=[$t1], CREATOR=[$t5],
ASSIGNEE=[$t4], REPORTER=[$t3], CREATED=[$t13], UPDATED=[$t14], DUE=[$t15],
RESOLVED=[$t16], DESCRIPTION=[$t8], ENVIRONMENT=[$t9], TYPE=[$t6],
PRIORITY=[$t10], RESOLUTION=[$t11], STATUS=[$t12], SECURITYLEVEL=[$t22],
VOTES=[$t17], WATCHES=[$t18], ORIGINALESTIMATE=[$t19], ESTIMATE=[$t20],
TIMESPENT=[$t21], $condition=[$t35])
EnumerableJoin(condition=[=($2, $26)], joinType=[left])
EnumerableJoin(condition=[true], joinType=[inner])
EnumerableJoin(condition=[=($0, $23)], joinType=[left])
JdbcToEnumerableConverter
JdbcProject(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(table=[[INTERNAL, JIRAISSUE]])
EnumerableAggregate(group=[{0, 1}])
EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_ISSUES]])
EnumerableAggregate(group=[{}], c=[COUNT()])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_PROJECTS]])
EnumerableAggregate(group=[{0, 1}])
EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_PROJECTS]])
{code}
and following query with use of a view:
{code}
explain plan for SELECT * FROM JIRA.ISSUES;
{code}
produces plan
{code}
EnumerableCalc(expr#0..23=[{inputs}], ID=[$t0], SUMMARY=[$t7], PROJECT=[$t2],
NUM=[$t1], CREATOR=[$t5], ASSIGNEE=[$t4], REPORTER=[$t3], CREATED=[$t13],
UPDATED=[$t14], DUE=[$t15], RESOLVED=[$t16], DESCRIPTION=[$t8],
ENVIRONMENT=[$t9], TYPE=[$t6], PRIORITY=[$t10], RESOLUTION=[$t11],
STATUS=[$t12], SECURITYLEVEL=[$t22], VOTES=[$t17], WATCHES=[$t18],
ORIGINALESTIMATE=[$t19], ESTIMATE=[$t20], TIMESPENT=[$t21])
EnumerableSemiJoin(condition=[=($0, $24)], joinType=[inner])
EnumerableCalc(expr#0..23=[{inputs}], ID=[$t1], ISSUENUM=[$t2],
PROJECT=[$t3], REPORTER=[$t4], ASSIGNEE=[$t5], CREATOR=[$t6], ISSUETYPE=[$t7],
SUMMARY=[$t8], DESCRIPTION=[$t9], ENVIRONMENT=[$t10], PRIORITY=[$t11],
RESOLUTION=[$t12], ISSUESTATUS=[$t13], CREATED=[$t14], UPDATED=[$t15],
DUEDATE=[$t16], RESOLUTIONDATE=[$t17], VOTES=[$t18], WATCHES=[$t19],
TIMEORIGINALESTIMATE=[$t20], TIMEESTIMATE=[$t21], TIMESPENT=[$t22],
SECURITY=[$t23], ID0=[$t0])
EnumerableJoin(condition=[=($0, $3)], joinType=[inner])
EnumerableAggregate(group=[{0}])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_PROJECTS]])
JdbcToEnumerableConverter
JdbcProject(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(table=[[INTERNAL, JIRAISSUE]])
EnumerableTableScan(table=[[JIRA, EXPLICIT_ALLOWED_ISSUES]])
{code}
> 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)
