Calcite SQL parser -- SELECT statement root node type not always SqlSelect

Question

I have a simple application that does text substitution on literals in the WHERE clause of a SELECT statement. I run SqlParser.parseQuery() and apply .getWhere() to the result.

However, for the following query the root node is not an SqlSelect, but an SqlOrderBy:

select EventID, Subject
from WorkOrder
where OwnerID = 100 and Active = 1 and Type = 2
order by Subject

If we use "group by" instead of "order by" then the root is an SqlSelect as expected.

Is this the intended behaviour?

score 1 · Accepted Answer · answered Sep 04 '19 at 19:45

Yes, this is intended. ORDER BY is not really a clause of SELECT. Consider

SELECT deptno FROM Emp
UNION
SELECT deptno FROM Dept
ORDER BY 1

The ORDER BY clause applies to the whole UNION, not to the second SELECT. Therefore we made it a standalone node.

When you ask Calcite to parse a query, the top-level nodes returned can be a SqlSelect (SELECT), SqlOrderBy (ORDER BY), SqlBasicCall (UNION, INTERSECT, EXCEPT or VALUES) or SqlWith (WITH).

Calcite SQL parser -- SELECT statement root node type not always SqlSelect

1 Answers1