[MDEV-13695] INTERSECT precedence is not in line with Oracle even in SQL_MODE=Oracle Created: 2017-09-01  Updated: 2018-04-25  Resolved: 2018-04-25

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.3.1
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Anders Karlsson Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux CentOS 7.2


 Description   

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.

 Comments   
Comment by Sergei Golubchik [ 2017-09-01 ]

As far as I understand, MariaDB and SQL Server do what the SQL Standard says. While Oracle doesn't.

So the behavior in non-Oracle mode should absolutely stay as it is now. The behavior in Oracle mode could, I guess, be changed.

Comment by Oleksandr Byelkin [ 2017-09-01 ]

Yes, we follow standard where INTERSECT has higher priority, Oracle thread all operation equal.

Comment by Anders Karlsson [ 2017-09-01 ]

I just found this in the SQL standard and you are right, INTERSECT has precedence so our implementation is correct from that POV. The biggest issue then is that there is no way to control the order of the evaluation, whereas in Oracle the order of the set queries can be used. In SQL Server this can be achieved using parentheses, but as this is not supported in MariaDB (MDEV-13692) means that some queries that work in Oracle just cannot be rewritten in a useful way with MariaDB, except possibly using CTE's, where in both SQL Server and Oracle the evaluation order can be explicit.

Comment by Oleksandr Byelkin [ 2018-04-24 ]

karlsson JFYI there is a trick (or workaround or hack) which makes operations ordered without MDEV-11953 (which will be implemented in 10.4 hopefully):

SELECT ... INSERSECT SELECT * FROM (SELECT ... UNION ... SELECT ...)...

Not so efficient but it works (BTW internally now it works the same way)

Comment by Oleksandr Byelkin [ 2018-04-24 ]

I raised priority to get in 10.3 because it is behavior changes.

Comment by Oleksandr Byelkin [ 2018-04-24 ]

revision-id: cb6aade130b75f3dc4b7c3a1dd4d33864adffd90 (mariadb-10.3.6-43-gcb6aade130b)
parent(s): f79c5a658cc33a10d7744a748a4328254e2cbaf7
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-04-24 12:04:59 +0200
message:

MDEV-13695: INTERSECT precedence is not in line with Oracle even in SQL_MODE=Oracle

Switch off automatic INTERSECT priority for ORACLE MODE

—

Generated at Thu Feb 08 08:07:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.