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

INSERT ... SELECT UNION with parenthesis

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 5.5.38, 10.0.12, 10.3.4, 10.1(EOL)
    • 10.4.0
    • Parser

    Description

      I never noticed this or never thought it is a problem, but please look at this post:
      http://blog.mclaughlinsoftware.com/2014/06/15/mysql-insert-from-query/

      The "problem" is that parenthesis are allowed for INSERT (SELECT), but not for INSERT (SELECT UNION), and someone can conclude that INSERT SELECT UNION is not supported:

      MariaDB [test]> INSERT INTO t3 (SELECT a, NULL AS b FROM t2);
      		 
      Query OK, 3 rows affected (0.08 sec)
      		 
      Records: 3  Duplicates: 0  Warnings: 0
      		 
      MariaDB [test]> INSERT INTO t3 (SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2);
      		 
      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 SELECT * FROM t2)' at line 1
      		 
      MariaDB [test]> INSERT INTO t3 SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2;
      		 
      Query OK, 6 rows affected (0.08 sec)
      		 
      Records: 6  Duplicates: 0  Warnings: 0

      Attachments

        Issue Links

          duplicates

          Task - A task that needs to be done. MDEV-11953 support of brackets (parentheses) in UNION/EXCEPT/INTERSECT operations

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10028 Syntax error on ((SELECT ...) UNION (SELECT ...))

          • Major - Major loss of function.
          • Closed
          links to

          Web Link mysql bug 25734

          Activity

            Ascending order - Click to sort in descending order
            rspadim roberto spadim added a comment -

            considering: https://mariadb.com/kb/en/insert-select/

            MariaDB [test]> INSERT INTO t3 (SELECT a, NULL AS b FROM t2);
            this should not work

            MariaDB [test]> INSERT INTO t3 (SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2);
            this should not work

            MariaDB [test]> INSERT INTO t3 SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2;
            this should work

            rspadim roberto spadim added a comment - considering: https://mariadb.com/kb/en/insert-select/ MariaDB [test] > INSERT INTO t3 (SELECT a, NULL AS b FROM t2); this should not work MariaDB [test] > INSERT INTO t3 (SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2); this should not work MariaDB [test] > INSERT INTO t3 SELECT a, NULL AS b FROM t1 UNION SELECT * FROM t2; this should work
            ypnos Johannes Jordan added a comment - - edited

            This might be related to this bug:
            https://bugs.mysql.com/bug.php?id=25734

            They claim they fixed it last month. How about MariaDB?
            If it is not related I would also open a separate issue.

            ypnos Johannes Jordan added a comment - - edited This might be related to this bug: https://bugs.mysql.com/bug.php?id=25734 They claim they fixed it last month. How about MariaDB? If it is not related I would also open a separate issue.
            serg Sergei Golubchik added a comment -

            Sorry, no parser changes in 5.5 — they tend to be too big and risky for GA versions. We'll fix it in the trunk, just as MySQL did.

            serg Sergei Golubchik added a comment - Sorry, no parser changes in 5.5 — they tend to be too big and risky for GA versions. We'll fix it in the trunk, just as MySQL did.
            bar Alexander Barkov added a comment -

            The same problem is repeatable in CREATE .. SELECT:

            CREATE OR REPLACE TABLE t2 AS (SELECT 1 AS a);
            		 
            CREATE OR REPLACE TABLE t2 AS SELECT 1 AS a UNION SELECT 2;
            		 
            CREATE OR REPLACE TABLE t2 AS (SELECT 1 AS a UNION SELECT 2);

            The first and the second statements succeed, the third statement fails.

            bar Alexander Barkov added a comment - The same problem is repeatable in CREATE .. SELECT : CREATE OR REPLACE TABLE t2 AS ( SELECT 1 AS a); CREATE OR REPLACE TABLE t2 AS SELECT 1 AS a UNION SELECT 2; CREATE OR REPLACE TABLE t2 AS ( SELECT 1 AS a UNION SELECT 2); The first and the second statements succeed, the third statement fails.
            f_razzoli Federico Razzoli added a comment -

            The same goes for CREATE VIEW:

            CREATE OR REPLACE VIEW t2 AS (SELECT 1 AS a);
            		 
            CREATE OR REPLACE VIEW t2 AS SELECT 1 AS a UNION SELECT 2;
            		 
            CREATE OR REPLACE VIEW t2 AS (SELECT 1 AS a UNION SELECT 2);

            1 and 2 work, 3 fails.

            f_razzoli Federico Razzoli added a comment - The same goes for CREATE VIEW: CREATE OR REPLACE VIEW t2 AS (SELECT 1 AS a); CREATE OR REPLACE VIEW t2 AS SELECT 1 AS a UNION SELECT 2; CREATE OR REPLACE VIEW t2 AS (SELECT 1 AS a UNION SELECT 2); 1 and 2 work, 3 fails.
            danblack Daniel Black added a comment -

            Fix in 8.0 as per mysql bug 25734

            danblack Daniel Black added a comment - Fix in 8.0 as per mysql bug 25734
            sanja Oleksandr Byelkin added a comment -

            Duplicate of https://jira.mariadb.org/browse/MDEV-11953 . Test will be there.

            sanja Oleksandr Byelkin added a comment - Duplicate of https://jira.mariadb.org/browse/MDEV-11953 . Test will be there.
            sanja Oleksandr Byelkin added a comment -

            fixed in MDEV-6341

            sanja Oleksandr Byelkin added a comment - fixed in MDEV-6341

            People

              sanja Oleksandr Byelkin
              f_razzoli Federico Razzoli
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.