INTERSECT precedence is not in line with Oracle even in SQL_MODE=Oracle

The order of precedence for set operations seems to differ between implementations. I can not find a defined order in the SQL Standard either. I have looked at INTERSECT in MariaDB 10.3 and compared to Oracle 11 and SQL Server 12.0 and MariaDB aligns with SQL Server, in which case INTERSECT has higher precedence whereas Oracle tends to give bUNION and INTERSECT the same precedence. The result is that the same query with the same data in Oracle and MariaDB will give different results, whereas SQL Server will work the same way as MariaDB. This behavior is not affected by sql_mode=Oracle, which is a shame.

To reproduce, first Oracle:

create table t12(c1 int);

insert into t12 values(1);

insert into t12 values(2);

create table t13(c1 int);

insert into t13 values(1);

insert into t13 values(3);

create table t234(c1 int);

insert into t234 values(2);

insert into t234 values(3);

insert into t234 values(4);

select * from t13 union select * from t234 intersect select * from t12;

        C1

----------

         1

         2

MariaDB:

set sql_mode=oracle;

create or replace table t12(c1 int);

insert into t12 values(1),(2);

create or replace table t13(c1 int);

insert into t13 values(1),(3);

create or replace table t234(c1 int);

insert into t234 values(2),(3),(4);

select * from t13 union select * from t234 intersect select * from t12;

c1

1

3

2

And SQL Server:

create table t12(c1 int)

go

insert into t12 values(1),(2)

go

create table t13(c1 int)

go

insert into t13 values(1),(3)

go

create table t234(c1 int)

go

insert into t234 values(2),(3),(4)

go

select * from t13 union select * from t234 intersect select * from t12

go

1

2

3

This is not necessary a bug, but at least it should be documented and possibly a warning issues, as could be hard to detect in a migration from Oracle and cause wrong data to be processed in the application.

The workaround for the above, to be able to write SQL that works in MariaDB as well as in other databases is to explicit order of evaluation using parentheses, but MariaDB doesn't support this. See MDEV-13692.