Parsing queries involving UNION in statements

[kkadu]

This may not be a bug but a new feature but I have experienced that queries that have UNION or INTERSECT / MINUS (less common) are not parsed correctly. Especially when you have multiple union all, it becomes important to have columns fetched as table.column however, in the case of union, we do not get table appended to column when you extract parser.column.

(SELECT a.abc, a.bcg, a.sdf, b.cnt FROM (SELECT abc, bcg, sdf FROM xxx.table1 UNION SELECT abc, NULL, sdf FROM yyy.table2 UNION SELECT abc, bcg, NULL FROM zzz.table3 WHERE rxy IN ('test')) AS a FULL OUTER JOIN (SELECT abc, count(r) AS cnt FROM (SELECT abc, r FROM xxx.table1 UNION SELECT abc, r FROM yyy.table2) GROUP BY abc) AS b ON a.abc = b.abc) UNION SELECT abc, NULL, NULL, sdf, 0 FROM xxx.table2 WHERE abc IS NOT NULL AND xyz IS NULL AND mmr IS NULL

when we parse the above statement for parser.tables we get ['xxx.table1', 'yyy.table2', 'zzz.table3', 'xxx.table2'] which is correct, however for parser.columns we get ['abc', 'bcg', 'sdf', 'r', 'NULL', 'rxy', 'xyz', 'mmr'] where we would expect to have table.column appended, so that it is clear which tables those columns are coming from i.e. ['xxx.table1.abc', 'yyy.table2.abc', 'zzz.table3.abc', 'xxx.table2.abc', 'xxx.table1.bcg', 'zzz.table3.bcg', 'xxx.table1.sdf', 'yyy.table2.sdf', 'xxx.table2.sdf', 'xxx.table1.r', 'yyy.table2.r', 'zzz.table3.rxy', 'xxx.table2.xyz', 'xxx.table2.mmr']

[kkadu]

Hi @macbre - Let me know if this is a valid scenario for sql parser to handle. Is there any current workaround for this?

[macbre]

@kkadu - can you try to provide a bit shorter SQL query that is still affected by the bug? It will make fixing it a bit easier (and also be a nice candidate for the unit tests).