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

Support for parentesized UNION / INTERSECT statements

    XMLWordPrintable

Details

    • Task
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • N/A
    • Server
    • None

    Description

      Parentisizing UNION expressions is sometimes to change the order of evaluation and to add clarity is not only useful, but is also allowed in SQL:2011 as far as I can see. In addition, this gets more important when MariaDB adds support for INTERSECT in 10.3.
      MariaDB Example:

      $ mysql -u visma -pvisma appl_owner
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 116
      Server version: 10.3.0-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [appl_owner]> create table t1(c1 int);
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [appl_owner]> insert into t1 values(1),(2);
      Query OK, 2 rows affected (0.00 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [appl_owner]> select c1 from t1 union all select c1 from t1;
      +------+
      | c1   |
      +------+
      |    1 |
      |    2 |
      |    1 |
      |    2 |
      +------+
      4 rows in set (0.00 sec)
       
      MariaDB [appl_owner]> (select c1 from t1 union all select c1 from t1);
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union all select c1 from t1)' at line 1
      

      With Oracle, this works, and here is also an example where this makes a difference:

      $ sqlplus anders/XXXXXXXX
       
      SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 31 14:36:01 2017
       
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
       
       
      Connected to:
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
       
      SQL> create table t1(c1 int) tablespace users;
       
      Table created.
       
      SQL> insert into t1 values(1);
       
      1 row created.
       
      SQL> insert into t1 values(2);
       
      1 row created.
       
      SQL> select c1 from t1 union select c1 from t1 union all select c1 from t1;
       
              C1
      ----------
               1
               2
               1
               2
       
      SQL> select c1 from t1 union (select c1 from t1 union all select c1 from t1);
       
              C1
      ----------
               1
               2
       
      SQL> (select c1 from t1 union all select c1 from t1);
       
              C1
      ----------
               1
               2
               1
               2
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            karlsson Anders Karlsson
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.