MySQL Bugs: #54382: LEFT JOIN AND UNION Parsing Error

Bug #54382	LEFT JOIN AND UNION Parsing Error
Submitted:	9 Jun 2010 21:04	Modified:	11 Oct 2012 17:21
Reporter:	Ian Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.5.4-m3	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	join, left, parenthesis, UNION

Description:
Between 5.4 and 5.5 there is a change in how mysql parse queries using both left join and union.

The problem happens only if the left join is on a single table.

How to repeat:
######## Create two simple tables #########

CREATE TABLE `tTable1` (
`pkiTable1ID` TINYINT NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `tTable2` (
`pkiTable2ID` TINYINT NOT NULL,
`fkiTable1ID` TINYINT NOT NULL
) ENGINE = MYISAM ;

######## These queries all works #########
# Left join without parenthesis
SELECT tTable1.pkiTable1ID
FROM tTable1
LEFT JOIN tTable2
ON tTable1.pkiTable1ID = tTable2.fkiTable1ID;

# Left join with parenthesis
SELECT tTable1.pkiTable1ID
FROM tTable1
LEFT JOIN (tTable2)
ON (tTable1.pkiTable1ID = tTable2.fkiTable1ID);

# Left join + Union without parenthesis
SELECT tTable1.pkiTable1ID
FROM tTable1
LEFT JOIN tTable2
ON tTable1.pkiTable1ID = tTable2.fkiTable1ID
UNION
SELECT tTable1.pkiTable1ID
FROM tTable1
LEFT JOIN tTable2
ON tTable1.pkiTable1ID = tTable2.fkiTable1ID;

######## These queries were working in previous version but don't work anymore #########
# Left join + Union with parenthesis
SELECT tTable1.pkiTable1ID
FROM tTable1
LEFT JOIN (tTable2)
ON (tTable1.pkiTable1ID = tTable2.fkiTable1ID)
UNION
SELECT tTable1.pkiTable1ID
FROM tTable1
LEFT JOIN (tTable2)
ON (tTable1.pkiTable1ID = tTable2.fkiTable1ID);

Suggested fix:
Since the syntax is valid and always worked, this is probably an error in the parser.

It needs to be fixed

Thank you for the bug report.

mysql> SELECT tTable1.pkiTable1ID
    -> FROM tTable1
    -> LEFT JOIN (tTable2)
    -> ON (tTable1.pkiTable1ID = tTable2.fkiTable1ID)
    -> UNION
    -> SELECT tTable1.pkiTable1ID
    -> FROM tTable1
    -> LEFT JOIN (tTable2)
    -> ON (tTable1.pkiTable1ID = tTable2.fkiTable1ID);
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 (tTable1.pkiTable1ID = tTable2.fkiTable1ID)' at line 9
mysql> 
mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.0.6               |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.6.99-m4           |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

mysql> exit
Bye
[miguel@tikal 5.5mr]$ mysql -umiguel -p test
Enter password: 
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 2
Server version: 5.1.49-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> SELECT tTable1.pkiTable1ID
    -> FROM tTable1
    -> LEFT JOIN (tTable2)
    -> ON (tTable1.pkiTable1ID = tTable2.fkiTable1ID)
    -> UNION
    -> SELECT tTable1.pkiTable1ID
    -> FROM tTable1
    -> LEFT JOIN (tTable2)
    -> ON (tTable1.pkiTable1ID = tTable2.fkiTable1ID);
Empty set (0.00 sec)

mysql>

This bug has been "In progress" for 575 days. Is there any intention to actually fix it?

revision-id: martin.hansson@sun.com-20091110125246-wom2bbxt5koahstl
parent: bar@mysql.com-20091109113518-4nz701wqpo9it753
committer: Martin Hansson <martin.hansson@sun.com>
branch nick: n-mr-bf
timestamp: Tue 2009-11-10 13:52:46 +0100
message:
  Backport of Bug#33204 from mysql-pe to
  mysql-next-mr-bugfixing.
  
  Bug no 32858 was fixed in two different ways in what was
  then called mysql 5.1 and 6.0. The fix in 6.0 was very
  different since bugfix no 33204 was present.  Furthermore,
  the two fixes were not compatible. Hence in order to
  backport Bug#33204 to the 5.1-based mysql-next-mr-bugfixing,
  it was necessary to remove the 5.1 fix of 32858 and apply
  the 6.0 version of the fix.

Sorry, I've meant to say that this bug is caused by the above mentioned revision.

Hi,

Could anyone please solve this. It's a major show stopper for loads of programs to be upgraded to 5.5 or higher.
A simpler way to reproduce this by the way is:

create table f(f varchar(1));
select a.f from f a; 
select a.f from f a left join (f b) on (a.f=b.f);
select a.f from f a union select a.f from f a left join (f b) on (a.f=b.f);

Statement 2 and 3 will work, number 4 which is the union of the two above will generate the error.

Loads of systems use union statements and joins.

Thanks in advance,
Freek

Hi,

I've tested this meanwhile under MariaDB and there it has been solved/released already a few months ago. Does this mean that we get a patch soon? I'm waiting on this, since now I don't upgrade my Ubuntu servers to 12.04LTS due to the fact that it comes with this MySQL version.

It is a real serious bug and I don't understand that it takes this long.

Best regards and thanks in advance,
Freek

Noted in 5.6.8, 5.7.0 changelogs.

The parser rejected legal queries that involved a UNION where the
right hand side query term has a table in parenthese.

Could not find the fix in 5.6.8 or 5.7.0 version changelog. Where is it exactly mentioned . Please provide the link.

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-8.html

Noted in 5.5.32 changelog.