Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13695

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.1
    • 10.3.7
    • Server
    • None
    • 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.

      Attachments

        Activity

          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.

          serg Sergei Golubchik added a comment - 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.

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

          sanja Oleksandr Byelkin added a comment - Yes, we follow standard where INTERSECT has higher priority, Oracle thread all operation equal.

          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.

          karlsson Anders Karlsson added a comment - 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.

          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)

          sanja Oleksandr Byelkin added a comment - 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)

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

          sanja Oleksandr Byelkin added a comment - I raised priority to get in 10.3 because it is behavior changes.

          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

          sanja Oleksandr Byelkin added a comment - 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 —

          People

            sanja Oleksandr Byelkin
            karlsson Anders Karlsson
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.