What's the default precedence of multiple UNION and EXCEPT clauses?

[VolkerBarth]

As documented, multiple UNIONs are is documented from left to right:

By default, a statement containing multiple UNION operators is evaluated from left to right. Parentheses may be used to specify the order of evaluation.

What's the default precedence when combining multiple UNION and EXCEPT clauses?

With SA 12.0.1.3298, UNION and EXCEPT (and INTERSECT?) seem to have the same precedence, and seem all to be computed from left to right. I.e.

:::SQL
select 1 from dummy
except
select 2 from dummy
union
select 2 from dummy

returns the same as the - obviously not valid ! - statement

:::SQL
(select 1 from dummy
except
select 2 from dummy)
union
select 2 from dummy

namely the rows "1" and "2", whereas

:::SQL
select 1 from dummy
except
(select 2 from dummy
union
select 2 from dummy)

just returns the row "1".

Now what I would like to get is the union of two excepts - as in the very simplified example:

:::SQL
(select 1 from dummy
except
select 2 from dummy)
union
(select 2 from dummy
except
select 1 from dummy)

Unfortunately, I seem to need parantheses to enforce this precedence but can't find the correct syntax - dbisqlc just issues a syntax error for this query.

What am I doing wrong?

[MarkCulp · Accepted solution]

The grammar rules used in SQL Anywhere include the following:

%left T_UNION T_EXCEPT
%left T_INTERSECT
%right T_CONCAT
%left '+' '-' 
%left '*' '/' '%'
%left '&' '|' '^' '~'
%left '.' '('

The above (in human-speak) means that UNION and EXCEPT have equal precedence but will bind from left to right. INTERSECT has a higher precedence than UNION and EXCEPT and also binds left to right when more than one INTERSECT is used. The rest is left as an exercise for the reader (and hopefully should not be too hard to figure out) 🙂

[VolkerBarth · Accepted solution]

I'm particularly grateful for the "human speak" part:) - Thanks for confirming Tyson's statement. FWIW, I've left a note in DCX yesterday to add that information.

[Former Member]

I believe UNION and EXCEPT have equal precedence. INTERSECT has higher precedence. All three are evaluated left to right.

[VolkerBarth]

Is there any official statement on these operations to confirm Tyson's statement (which fit my tests)?

[Former Member]

Volker,

You're last query seems to be correct. If you run it in DBISQL, no Syntax Error is reported. This seems to be a limitation with DBISQLC, and is not likely to be changed as DBISQLC is deprecated.

If you have the java ISQL available, you should be able to run the query with that. Alternatively, you could wrap your actual union into a derived table and select from that.

i.e.

:::SQL
SELECT * FROM (

(SELECT * FROM systab WHERE table_id = 1
EXCEPT
SELECT * FROM systab WHERE table_id = 2)
UNION
(SELECT * FROM systab WHERE table_id = 2
EXCEPT
SELECT * FROM systab WHERE table_id = 1)

) AS dt;

The above is a extremely simplistic example, just be sure that all columns have names.

[VolkerBarth]

Thanks for the pointer - I'm gonna test that with DBISQL...

I wasn't aware that these tools do their own syntax checks - as such I hadn't thought of dbisqlc limitations because it does allow UNION and EXCEPT in general.

[VolkerBarth]

Tyson, I've confirmed that the enclosed statements do work correctly with DBISQL.

However, my title question is still unanswered...

[johnsmirnios]

This problem with select statements that start with an open parenthesis is fixed in dbisqlc 11.0.1.2591, 12.0.0.2670 and 12.0.1.3329.

Dbisqlc does minimal parsing of most statements but it must do some to identify its own statements (e.g. INPUT). For statements not handled by dbisqlc itself, the first token also tells us whether to execute the statement or to open a cursor. Although other mechanisms exist for that today, they didn't when dbisqlc was first written.

[VolkerBarth]

Well, John, your constant ability and readiness to fix dbisqlc issues won't help me to leave that deprecated tool behind ... thanks again:)