[CALCITE-2009] Possible bug in interpreting ( IN ) OR ( IN ) logic

• 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
• Assignee: Julian Hyde
• Reporter: Piotr Bojko
• Created: 13/Oct/17 00:28
• Updated: 28/Feb/24 01:23
• Resolved: 09/Jan/18 18:53

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.

Issue Links

• relates to: CALCITE-2088 Add more complex end-to-end tests in "plus" module, using Chinook data set