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

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

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.14.0, 1.13.0, 1.15.0
    • 1.16.0
    • None
    • 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-or-is-used.log
          302 kB
          Piotr Bojko
        2. logs-when-union-is-used.log
          350 kB
          Piotr Bojko

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: