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

Queries with subqueries containing UNION are not parsed

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.4(EOL)
    • 10.4.9, 10.5.0
    • Parser
    • None

    Description

      The following query

      SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;

      returns syntax error message in 10.4:

      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 a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t' at line 1

      Reprodicible in with the following test case

      CREATE TABLE t1 (a int);
      		 
      INSERT INTO t1 VALUES(1),(2),(3),(4);
      		 
      CREATE TABLE t2 (a int);
      		 
      INSERT INTO t2 VALUES (4),(5),(6),(7);
      		 
      SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;

      10.3 and probably all prior versions return the same error message.
      MySQL-8.0 returns a proper result:

      mysql> SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
      		 
      +------+
      		 
      | r    |
      		 
      +------+
      		 
      |    1 |
      		 
      |    1 |
      		 
      |    1 |
      		 
      |    1 |
      		 
      +------+

      This query with a subquery in the WHERE clause also failed to be parsed:

      MariaDB [test]> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a 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 a FROM t2))' at line 1

      10.3 returns the same error message.

      MySQL-8.0 is ok with the above query:

      mysql> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a FROM t2));
      		 
      +------+
      		 
      | a    |
      		 
      +------+
      		 
      |    1 |
      		 
      |    2 |
      		 
      |    3 |
      		 
      |    4 |
      		 
      +------+

      Attachments

        Issue Links

          is blocked by

          Task - A task that needs to be done. MDEV-19996 Bison grammar: turn singe-character operators into <kwd>

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-20634 Report disallowed subquery errors as such (instead of parse error)

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Descending order - Click to sort in ascending order
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 98022 ] MariaDB v4 [ 156439 ]
            sanja Oleksandr Byelkin made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            igor Igor Babaev (Inactive) made changes -
            Fix Version/s 10.4.9 [ 23906 ]
            Fix Version/s 10.5.0 [ 23709 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Igor Babaev [ igor ]
            igor Igor Babaev (Inactive) made changes -
            Assignee Igor Babaev [ igor ] Alexander Barkov [ bar ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4 [ 22408 ]
            igor Igor Babaev (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            igor Igor Babaev (Inactive) made changes -
            Description The following query
            {code:sql}
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            returns syntax error message in 10.4:
            {noformat}
            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 a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t' at line 1
            {noformat}
            Reprodicible in with the following test case
            {code:sql}
            CREATE TABLE t1 (a int);
            INSERT INTO t1 VALUES(1),(2),(3),(4);
            CREATE TABLE t2 (a int);
            INSERT INTO t2 VALUES (4),(5),(6),(7);
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            10.3 and probably all prior versions return the same error message.
            MySQL-8.0 returns a proper result:
            {noformat}
            mysql> SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            +------+
            | r |
            +------+
            | 1 |
            | 1 |
            | 1 |
            | 1 |
            +------+
            {noformat}
            This query with a subquery in the WHERE clause also failed to be parsed:
            {noformat}
            MariaDB [test]> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a 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 a FROM t2))' at line 1
            {noformat}
            MySQL-8.0 is ok with the above query:
            {noformat}
            mysql> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a FROM t2));
            +------+
            | a |
            +------+
            | 1 |
            | 2 |
            | 3 |
            | 4 |
            +------+
            {noformat}




            		The following query
            {code:sql}
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            returns syntax error message in 10.4:
            {noformat}
            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 a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t' at line 1
            {noformat}
            Reprodicible in with the following test case
            {code:sql}
            CREATE TABLE t1 (a int);
            INSERT INTO t1 VALUES(1),(2),(3),(4);
            CREATE TABLE t2 (a int);
            INSERT INTO t2 VALUES (4),(5),(6),(7);
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            10.3 and probably all prior versions return the same error message.
            MySQL-8.0 returns a proper result:
            {noformat}
            mysql> SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            +------+
            | r |
            +------+
            | 1 |
            | 1 |
            | 1 |
            | 1 |
            +------+
            {noformat}
            This query with a subquery in the WHERE clause also failed to be parsed:
            {noformat}
            MariaDB [test]> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a 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 a FROM t2))' at line 1
            {noformat}
            10.3 returns the same error message.

            MySQL-8.0 is ok with the above query:
            {noformat}
            mysql> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a FROM t2));
            +------+
            | a |
            +------+
            | 1 |
            | 2 |
            | 3 |
            | 4 |
            +------+
            {noformat}




            igor Igor Babaev (Inactive) made changes -
            Description The following query
            {code:sql}
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            returns syntax error message in 10.4:
            {noformat}
            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 a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t' at line 1
            {noformat}
            Reprodicible in with the following test case
            {code:sql}
            CREATE TABLE t1 (a int);
            INSERT INTO t1 VALUES(1),(2),(3),(4);
            CREATE TABLE t2 (a int);
            INSERT INTO t2 VALUES (4),(5),(6),(7);
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            10.3 and probably all prior versions return the same error message.
            MySQL-8.0 returns a proper result:
            {noformat}
            mysql> SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            +------+
            | r |
            +------+
            | 1 |
            | 1 |
            | 1 |
            | 1 |
            +------+
            {noformat}
            This query with a subquery in the WHERE clause also failed to be parsed:
            {noformat}
            MariaDB [test]> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a 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 a FROM t2))' at line 1
            {noformat}



            		The following query
            {code:sql}
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            returns syntax error message in 10.4:
            {noformat}
            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 a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t' at line 1
            {noformat}
            Reprodicible in with the following test case
            {code:sql}
            CREATE TABLE t1 (a int);
            INSERT INTO t1 VALUES(1),(2),(3),(4);
            CREATE TABLE t2 (a int);
            INSERT INTO t2 VALUES (4),(5),(6),(7);
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            10.3 and probably all prior versions return the same error message.
            MySQL-8.0 returns a proper result:
            {noformat}
            mysql> SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            +------+
            | r |
            +------+
            | 1 |
            | 1 |
            | 1 |
            | 1 |
            +------+
            {noformat}
            This query with a subquery in the WHERE clause also failed to be parsed:
            {noformat}
            MariaDB [test]> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a 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 a FROM t2))' at line 1
            {noformat}
            MySQL-8.0 is ok with the above query:
            {noformat}
            mysql> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a FROM t2));
            +------+
            | a |
            +------+
            | 1 |
            | 2 |
            | 3 |
            | 4 |
            +------+
            {noformat}




            igor Igor Babaev (Inactive) made changes -
            Description The following query
            {code:sql}
            SELECT t.a + ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            returns syntax error message in 10.4:
            {noformat}
            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 a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t' at line 1
            {noformat}
            Reprodicible in with the following test case
            {code:sql}
            CREATE TABLE t1 (a int);
            INSERT INTO t1 VALUES(1),(2),(3),(4);
            CREATE TABLE t2 (a int);
            INSERT INTO t2 VALUES (4),(5),(6),(7);
            SELECT t.a + ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            10.3 and probably all prior versions return the same error message.
            MySQL-8.0 returns a proper result:
            {noformat}
            SELECT t.a + ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {noformat}

            		The following query
            {code:sql}
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            returns syntax error message in 10.4:
            {noformat}
            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 a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t' at line 1
            {noformat}
            Reprodicible in with the following test case
            {code:sql}
            CREATE TABLE t1 (a int);
            INSERT INTO t1 VALUES(1),(2),(3),(4);
            CREATE TABLE t2 (a int);
            INSERT INTO t2 VALUES (4),(5),(6),(7);
            SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            {code}
            10.3 and probably all prior versions return the same error message.
            MySQL-8.0 returns a proper result:
            {noformat}
            mysql> SELECT ((SELECT a FROM t1 LIMIT 2) UNION (SELECT a FROM t2 LIMIT 2) LIMIT 1) AS r FROM t1 AS t;
            +------+
            | r |
            +------+
            | 1 |
            | 1 |
            | 1 |
            | 1 |
            +------+
            {noformat}
            This query with a subquery in the WHERE clause also failed to be parsed:
            {noformat}
            MariaDB [test]> SELECT * FROM t1 WHERE t1.a IN ((SELECT a FROM t1) UNION (SELECT a 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 a FROM t2))' at line 1
            {noformat}



            igor Igor Babaev (Inactive) made changes -
            Field Original Value New Value
            Summary Query with a single-row subquery in expression is not parsed Queries with subqueries containing UNION are not parsed
            igor Igor Babaev (Inactive) created issue -

            People

              igor Igor Babaev (Inactive)
              igor Igor Babaev (Inactive)
              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.