Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2009

Possible bug in interpreting ( IN ) OR ( IN ) logic

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.14.0, 1.13.0, 1.15.0
    • Fix Version/s: 1.16.0
    • Component/s: None
    • Labels:
      None

      Description

      I have schema

      {
      	"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"
      				}
      			]
      		} 
      	]
      }
      

      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:

      {
      	"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"
      				}
      			]
      		} 
      	]
      }
      
      

      Note that %%factories are a simple workaround not to changing the schema each time my java code is refactored.

        Attachments

        1. logs-when-union-is-used.log
          350 kB
          Piotr Bojko
        2. logs-when-or-is-used.log
          302 kB
          Piotr Bojko

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                ptrbojko Piotr Bojko
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: