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: | |
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
[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.