Bug #65381 union and multiple left join(s)
Submitted: 21 May 2012 11:29 Modified: 15 Oct 2012 16:18
Reporter: G V Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Parser Severity:S1 (Critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[21 May 2012 11:29] G V
Description:
The following query works fine in mysql 5.1 and fail in 5.5:

mysql> ( select xxx.user, a.user, b.user, c.user from xxx left join ( xxx as a ) on ( a.id = xxx.id ) left join ( xxx as b ) on ( b.id = xxx.id ) left join ( xxx as c ) on ( c.id = xxx.id ) where xxx.id=1 ) union ( select xxx.user, a.user, b.user, c.user from xxx left join ( xxx as a ) on ( a.id = xxx.id )  left join ( xxx as b )  on ( b.id = xxx.id ) left join ( xxx as c ) on ( c.id = xxx.id ) where xxx.id=2 );
+------+------+------+------+
| user | user | user | user |
+------+------+------+------+
| a    | a    | a    | a    |
| b    | b    | b    | b    |
+------+------+------+------+
2 rows in set (0.00 sec)

How to repeat:
mysql> use xxx;
Database changed
mysql> create table xxx ( id int, user varchar(255) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into xxx ( id, user ) values( 1, "a");
Query OK, 1 row affected (0.00 sec)

mysql> insert into xxx ( id, user ) values( 2, "b");
Query OK, 1 row affected (0.00 sec)

mysql> insert into xxx ( id, user ) values( 3, "c");
Query OK, 1 row affected (0.01 sec)

mysql> select * from xxx;
+------+------+
| id   | user |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> ( select xxx.user, a.user, b.user, c.user from xxx left join ( xxx as a ) on ( a.id = xxx.id ) left join ( xxx as b ) on ( b.id = xxx.id ) left join ( xxx as c ) on ( c.id = xxx.id ) where xxx.id=1 ) union ( select xxx.user, a.user, b.user, c.user from xxx left join ( xxx as a ) on ( a.id = xxx.id )  left join ( xxx as b )  on ( b.id = xxx.id ) left join ( xxx as c ) on ( c.id = xxx.id ) where xxx.id=2 );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on ( a.id = xxx.id )  left join ( xxx as b )  on ( b.id = xxx.id ) left join ( x' at line 1
mysql>
[21 May 2012 13:54] Valeriy Kravchuk
Thank you for the bug report. Verified with 5.5.26 (current mysql-5.5 tree) on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.26-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table xxx ( id int, user varchar(255) );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into xxx ( id, user ) values( 1, "a");
Query OK, 1 row affected (0.00 sec)

mysql> insert into xxx ( id, user ) values( 2, "b");
Query OK, 1 row affected (0.00 sec)

mysql> insert into xxx ( id, user ) values( 3, "c");
Query OK, 1 row affected (0.00 sec)

mysql> ( select xxx.user, a.user, b.user, c.user from xxx left join ( xxx as a ) on ( a.id = xxx.id ) left join ( xxx as b ) on ( b.id = xxx.id ) left join ( xxx as c ) on ( c.id = xxx.id ) where xxx.id=1 ) union ( select xxx.user, a.user, b.user, c.user from xxx left join ( xxx as a ) on ( a.id = xxx.id )  left join ( xxx as b )  on ( b.id = xxx.id ) left join ( xxx as c ) on ( c.id = xxx.id ) where xxx.id=2 );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on ( a.id = xxx.id )  left join ( xxx as b )  on ( b.id = xxx.id ) left join ( x' at line 1
mysql> exit
Bye

On 5.1.x everything works:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.64-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table xxx ( id int, user varchar(255) );Query OK, 0 rows affected (0.06 sec)mysql> insert into xxx ( id, user ) values( 1, "a");Query OK, 1 row affected (0.00 sec)mysql> insert into xxx ( id, user ) values( 2, "b");Query OK, 1 row affected (0.01 sec)mysql> insert into xxx ( id, user ) values( 3, "c");Query OK, 1 row affected (0.00 sec)mysql> ( select xxx.user, a.user, b.user, c.user from xxx left join ( xxx as a ) on ( a.id = xxx.id ) left join ( xxx as b ) on ( b.id = xxx.id ) left join ( xxx as c ) on ( c.id = xxx.id ) where xxx.id=1 ) union ( select xxx.user, a.user, b.user, c.user from xxx left join ( xxx as a ) on ( a.id = xxx.id )  left join ( xxx as b )  on ( b.id = xxx.id ) left join ( xxx as c ) on ( c.id = xxx.id ) where xxx.id=2 );
+------+------+------+------+
| user | user | user | user |
+------+------+------+------+
| a    | a    | a    | a    |
| b    | b    | b    | b    |
+------+------+------+------+
2 rows in set (0.00 sec)

So, I'd say we have a regression bug in parser.
[21 May 2012 13:55] MySQL Verification Team
Since version 5.5.3 we have syntax error.  Reading the change history lead me to the bug http://bugs.mysql.com/bug.php?id=33204 that was fixed. Maybe it is the cause?
[15 Oct 2012 16:18] Gleb Shchepa
Closed as a duplicate of the bug #54382.