[ https://issues.apache.org/jira/browse/CALCITE-2009?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16272685#comment-16272685 ]
Piotr Bojko commented on CALCITE-2009: -------------------------------------- 1.15.0-SNAPSHOT has the same issue. > 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.13.0, 1.14.0 > Reporter: Piotr Bojko > Assignee: Julian Hyde > 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)