JSQLColumResolver must handle operations UNION/INTERSECT/EXCEPT/MINUS

[stbischof]

Set Operations (UNION/INTERSECT/EXCEPT/MINUS) - Second Query Not Validated

Description

When using JSQLColumResolver.getResultSetMetaData() to resolve columns in queries with set operations (UNION, UNION ALL, INTERSECT, EXCEPT, MINUS), only the first SELECT is resolved. The subsequent SELECT statements after the set operator are not validated against the schema.

Affected Operations

• UNION
• UNION ALL
• INTERSECT
• EXCEPT
• MINUS

Expected Behavior

All SELECT statements in a set operation should be resolved against the schema. Invalid columns or tables in any part should trigger an error.

Actual Behavior

Only the first SELECT is resolved. Invalid columns/tables in subsequent SELECTs pass through unchanged without any error.

Reproduction

Schema Definition:

JdbcMetaData metaData = new JdbcMetaData("", "");
metaData.addTable("", "sch", "foo", "TABLE");
metaData.addColumn("", "sch", "foo", "id", Types.INTEGER, "INTEGER", 10, 0, 0, "");
metaData.addColumn("", "sch", "foo", "name", Types.VARCHAR, "VARCHAR", 255, 0, 0, "");
metaData.addTable("", "sch", "fooFact", "TABLE");
metaData.addColumn("", "sch", "fooFact", "id", Types.INTEGER, "INTEGER", 10, 0, 0, "");
metaData.addColumn("", "sch", "fooFact", "value", Types.INTEGER, "INTEGER", 10, 0, 0, "");
metaData.setCurrentSchemaName("sch");

Test Cases:

Input SQL	Expected	Actual Output
SELECT foo.id FROM foo UNION SELECT fooFact.wrongCol FROM fooFact	Error: wrongCol not found	SELECT foo.id FROM sch.foo UNION SELECT fooFact.wrongCol FROM fooFact
SELECT foo.id FROM foo UNION SELECT wrongTable.id FROM wrongTable	Error: wrongTable not found	SELECT foo.id FROM sch.foo UNION SELECT wrongTable.id FROM wrongTable
SELECT foo.id FROM foo UNION ALL SELECT fooFact.wrongCol FROM fooFact	Error: wrongCol not found	SELECT foo.id FROM sch.foo UNION ALL SELECT fooFact.wrongCol FROM fooFact
SELECT foo.id FROM foo INTERSECT SELECT fooFact.wrongCol FROM fooFact	Error: wrongCol not found	SELECT foo.id FROM sch.foo INTERSECT SELECT fooFact.wrongCol FROM fooFact
SELECT foo.id FROM foo EXCEPT SELECT fooFact.wrongCol FROM fooFact	Error: wrongCol not found	SELECT foo.id FROM sch.foo EXCEPT SELECT fooFact.wrongCol FROM fooFact
SELECT foo.id FROM foo MINUS SELECT fooFact.wrongCol FROM fooFact	Error: wrongCol not found	SELECT foo.id FROM sch.foo MINUS SELECT fooFact.wrongCol FROM fooFact
SELECT foo.id FROM foo MINUS SELECT wrongTable.id FROM wrongTable	Error: wrongTable not found	SELECT foo.id FROM sch.foo MINUS SELECT wrongTable.id FROM wrongTable

Suggested Fix

The JSQLColumResolver should iterate over all SELECT bodies in a SetOperationList and resolve columns/tables in each one, not just the first.

@manticore-projects

[stbischof]

testing in #137

[manticore-projects]

Greetings Stefan.

Thank you for your contribution but I do have questions:

1. all the provided tests succeed?
2. do we agree that JSQLColumnResolver and JSQLResolver have different scopes, e.g.

a) JSQLColumnResolver really returns only the columns of a select only without further validation and thus looking at the first query of a Set should suffice.
b) JSQLResolver goes much further and resolves all tables and columns of the query and thus looks at all queries of a Set

My understanding is that you are most interested in JSQLResolver only. If you still want to use JSQLColumnResolver then you would like to use it AFTER invoking JSQLResolver first.

Do we still need to do anything on this one please?