Exasol doesn't implement standards compliant E071-01 etc set operators

lukaseder
SQL-Fighter

I'm using Exasol 7.0.7

According to the SQL standards compliance specification found here: https://docs.exasol.com/sql_references/sqlstandardcompliance.htm, Exasol claims to be compliant with features E071-01, E071-02, E071-03, F302-01, but what I'm seeing here is subtly different, or at least, it's not very clear what's possible and what isn't: https://docs.exasol.com/sql/table_operators.htm 

Standard SQL's <query expression> allows for set operators UNION/EXCEPT/INTERSECT within the <query expression body>, which is prefixed by the <with clause> and followed by the <order by clause>, etc, so I tried what actually works. This works, but shouldn't according to the syntax diagrams of the docs (parentheses seem mandatory around set operation subqueries):

WITH t (c) AS (SELECT 1)
SELECT c FROM t
UNION ALL 
SELECT c + 1 FROM t
ORDER BY c DESC;

However, when I do add the parentheses as mandated by the syntax diagrams of the documentation, the above no longer works (it still works e.g. in PostgreSQL):

WITH t (c) AS (SELECT 1)
(SELECT c FROM t)
UNION ALL 
(SELECT c + 1 FROM t)
ORDER BY c DESC;

The error being:

SQL Error [42000]: syntax error, unexpected ORDER_BY_, expecting UNION_ or EXCEPT_ or MINUS_ or INTERSECT_ [line 5, column 1] (Session: 1693304236945178624)

I can easily reverse engineer what works, and what doesn't, and emulate all the missing syntactic features with derived tables (e.g. when nesting set operations):

WITH t (c) AS (SELECT 1)
SELECT * FROM (
  SELECT c FROM t
  UNION ALL 
  SELECT c + 1 FROM t
) t
ORDER BY c DESC;

but for forward-compatibility reasons, I still wonder what the exact supported syntax is.

3 REPLIES 3

exa-GeorgD
Team Exasol
Team Exasol

Hi Lukas,

thank you for reporting the problem.
Again you are right, Esasol does not behave as expected.

In the current Exasol versions, an ORDER BY is an optional part of a <query specification> (or <subquery> if you use the Exasol documentation name).
This is different from the SQL standard. I hope this helps to clarify the syntax. In this regard, the syntax of the documentation is also correct. A <subquery> contains a SELECT with an optional ORDER BY.

However, the parenthesis are not mandatory. We will change that in the documentation.

By the way, I am impressed with your knowledge about the SQL standard.
Reading it is a real challenge 😉

Some additional details:
The UNION ALL implementation in Exasol uses the ORDER BY of the last element, to sort the complete result set,
even if this ORDER BY is inside parentheses of the last SELECT. This is of course not standard conform.

In combination with LIMIT this can even lead to results that are incompatible with other databases.
Examples:

-- Based on the SQL standard syntax, this should return 1 row, Exasol returns 2 rows.
SELECT 1 FROM dual UNION SELECT * FROM (SELECT 2 FROM dual UNION SELECT 1 FROM dual ORDER BY 1) ORDER BY 1 DESC LIMIT 1;

 

-- Based on the SQL standard syntax, this should return 2 rows, Exasol returns 1 row.
(select * from dual) union all (select * from dual order by 1 limit 1);


Best wishes
Georg

 

lukaseder
SQL-Fighter

Thanks a lot for the feedback, Georg, and for updating the docs.

One gets used to reading the standard. It's complex, but really well written, and has settled many arguments.

Thanks for the heads-up about that union/orderby/limit behaviour. That's very confusing indeed, but prevents me reporting this soon (I had an investigation for this behaviour, which I've also noticed in our integration tests, planned for this week 😉)

Do you have any plans of fixing this behaviour in a major release, despite the incompatibility? I can't really imagine anyone expecting or wanting that behaviour from a mere syntactic perspective. Especially in your second example, imagine generating this query dynamically:

(select c from t) union all (%s)

So in Exasol, it is possible that for some substitution of %s, the contents of the first union all subquery are not returned, quite contrary to what one might expect from what appears to be an ordinary set operation.

exa-GeorgD
Team Exasol
Team Exasol

Yes, we definitely plan to fix that in a major release.
However, it is not yet scheduled for any and it will not be part of 7.1.

I hope no one wants this behavior ;).
But I could imagine that some existing queries rely on that, as it has been part of Exasol for quite some time now.