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

Basic 3-way join queries are not parsed

Details

    Description

      The following simple join 

      select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;

      over tables t1,t2,t3

      create table t1 (a int); 
      insert into t1 values (7), (2), (9);
      		 
      create table t2 (a int);
      		 
      insert into t2 values (5), (1), (7);
      		 
      create table t3 (a int);
      		 
      insert into t3 values (2), (7), (3);

      cannot be parsed in MariaDB 10.4:

      MariaDB [test]> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
      		 
      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 'on t1.a=t2.a' at line 1

      MySQL 8.0 handles this query properly:

      mysql> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
      		 
      +------+------+------+
      		 
      | a    | a    | a    |
      		 
      +------+------+------+
      		 
      |    7 |    7 |    7 |
      		 
      +------+------+------+

      This basic valid query also returns syntax error message:

      select * from t1 join t2 left join t3 on t2.a=t3.a on t1.a=t2.a;

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20330 Combination of "," (comma), cross join and left join fails to parse (regression since 10.1.40)

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21894 MariaDB 10.3 onward unable to parse syntax that works in earlier versions

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            igor Igor Babaev (Inactive) created issue -
            igor Igor Babaev (Inactive) made changes -
            Field Original Value New Value
            Status Open [ 1 ] In Progress [ 3 ]
            igor Igor Babaev (Inactive) made changes -
            Description The following simple join
            {code:sql}
            select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            {code}
             over tables t1,t2,t3
            {code:sql}
            create table t1 (a int);
            insert into t1 (7), (2), (9);
            create table t2 (a int);
            insert into t2 values (5), (1), (7);
            create table t3 (a int);
            insert into t3 values (2), (7), (3);
            {code}
            cannot be parsed in MariaDB 10.4:
            {noformat}
            MariaDB [test]> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            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 'on t1.a=t2.a' at line 1
            {noformat}
            MySQL 8.0 handles this query properly:
            {noformat}
            mysql> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            +------+------+------+
            | a | a | a |
            +------+------+------+
            | 7 | 7 | 7 |
            +------+------+------+
            {noformat}
            		The following simple join
            {code:sql}
            select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            {code}
             over tables t1,t2,t3
            {code:sql}
            create table t1 (a int);
            insert into t1 values (7), (2), (9);
            create table t2 (a int);
            insert into t2 values (5), (1), (7);
            create table t3 (a int);
            insert into t3 values (2), (7), (3);
            {code}
            cannot be parsed in MariaDB 10.4:
            {noformat}
            MariaDB [test]> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            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 'on t1.a=t2.a' at line 1
            {noformat}
            MySQL 8.0 handles this query properly:
            {noformat}
            mysql> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            +------+------+------+
            | a | a | a |
            +------+------+------+
            | 7 | 7 | 7 |
            +------+------+------+
            {noformat}
            igor Igor Babaev (Inactive) made changes -
            Summary Unexpected syntax error for a 3-way join Basic 3-way join queries are not parsed
            igor Igor Babaev (Inactive) made changes -
            Description The following simple join
            {code:sql}
            select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            {code}
             over tables t1,t2,t3
            {code:sql}
            create table t1 (a int);
            insert into t1 values (7), (2), (9);
            create table t2 (a int);
            insert into t2 values (5), (1), (7);
            create table t3 (a int);
            insert into t3 values (2), (7), (3);
            {code}
            cannot be parsed in MariaDB 10.4:
            {noformat}
            MariaDB [test]> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            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 'on t1.a=t2.a' at line 1
            {noformat}
            MySQL 8.0 handles this query properly:
            {noformat}
            mysql> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            +------+------+------+
            | a | a | a |
            +------+------+------+
            | 7 | 7 | 7 |
            +------+------+------+
            {noformat}
            		The following simple join
            {code:sql}
            select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            {code}
             over tables t1,t2,t3
            {code:sql}
            create table t1 (a int);
            insert into t1 values (7), (2), (9);
            create table t2 (a int);
            insert into t2 values (5), (1), (7);
            create table t3 (a int);
            insert into t3 values (2), (7), (3);
            {code}
            cannot be parsed in MariaDB 10.4:
            {noformat}
            MariaDB [test]> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            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 'on t1.a=t2.a' at line 1
            {noformat}
            MySQL 8.0 handles this query properly:
            {noformat}
            mysql> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            +------+------+------+
            | a | a | a |
            +------+------+------+
            | 7 | 7 | 7 |
            +------+------+------+
            {noformat}
            This valid query also returns syntax error message:
            {noformat}

            igor Igor Babaev (Inactive) made changes -
            Description The following simple join
            {code:sql}
            select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            {code}
             over tables t1,t2,t3
            {code:sql}
            create table t1 (a int);
            insert into t1 values (7), (2), (9);
            create table t2 (a int);
            insert into t2 values (5), (1), (7);
            create table t3 (a int);
            insert into t3 values (2), (7), (3);
            {code}
            cannot be parsed in MariaDB 10.4:
            {noformat}
            MariaDB [test]> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            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 'on t1.a=t2.a' at line 1
            {noformat}
            MySQL 8.0 handles this query properly:
            {noformat}
            mysql> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            +------+------+------+
            | a | a | a |
            +------+------+------+
            | 7 | 7 | 7 |
            +------+------+------+
            {noformat}
            This valid query also returns syntax error message:
            {noformat}

            		The following simple join
            {code:sql}
            select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            {code}
             over tables t1,t2,t3
            {code:sql}
            create table t1 (a int);
            insert into t1 values (7), (2), (9);
            create table t2 (a int);
            insert into t2 values (5), (1), (7);
            create table t3 (a int);
            insert into t3 values (2), (7), (3);
            {code}
            cannot be parsed in MariaDB 10.4:
            {noformat}
            MariaDB [test]> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            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 'on t1.a=t2.a' at line 1
            {noformat}
            MySQL 8.0 handles this query properly:
            {noformat}
            mysql> select * from t1 join t2 join t3 on t2.a=t3.a on t1.a=t2.a;
            +------+------+------+
            | a | a | a |
            +------+------+------+
            | 7 | 7 | 7 |
            +------+------+------+
            {noformat}
            This basic valid query also returns syntax error message:
            {code:sql}
            select * from t1 join t2 left join t3 on t2.a=t3.a on t1.a=t2.a;
            {code}
            igor Igor Babaev (Inactive) made changes -
            Assignee Igor Babaev [ igor ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            igor Igor Babaev (Inactive) added a comment -

            The bug is reproducible in all versions starting from 5.5.
            A has been prepared for 5.5 and it is supposed to be merged up to 10.3.
            Another patch has been prepared for 10.4 because the grammar rules for 10.4 differ significantly from those in 5.5-10.3.

            igor Igor Babaev (Inactive) added a comment - The bug is reproducible in all versions starting from 5.5. A has been prepared for 5.5 and it is supposed to be merged up to 10.3. Another patch has been prepared for 10.4 because the grammar rules for 10.4 differ significantly from those in 5.5-10.3.
            sanja Oleksandr Byelkin added a comment -

            OK to push.

            sanja Oleksandr Byelkin added a comment - OK to push.
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Igor Babaev [ igor ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            igor Igor Babaev (Inactive) added a comment -

            A fix for this bug was pushed into 5.5. I has to be merged upstream into 10.1-10.3 as it is.
            10.4 requires a slightly different patch that has been already prepared.

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 5.5. I has to be merged upstream into 10.1-10.3 as it is. 10.4 requires a slightly different patch that has been already prepared.
            igor Igor Babaev (Inactive) made changes -
            Fix Version/s 5.5.65 [ 23402 ]
            Fix Version/s 10.1.41 [ 23406 ]
            Fix Version/s 10.2.26 [ 23409 ]
            Fix Version/s 10.3.17 [ 23411 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            alice Alice Sherepa made changes -
            serg Sergei Golubchik made changes -
            Issue Type Task [ 3 ] Bug [ 1 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 96625 ] MariaDB v4 [ 156160 ]

            People

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