MySQL Bugs: #68152: JOIN AND UNION Parsing Error

Bug #68152	JOIN AND UNION Parsing Error
Submitted:	23 Jan 2013 8:00	Modified:	20 Aug 2013 20:16
Reporter:	Freek Bou	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.5	OS:	Linux (Ubuntu 12.04 LTS)
Assigned to:		CPU Architecture:	Any
Tags:	Bug #54382, join, UNION

Description:
Bug #54382 has already been closed for some months. Today received an update on MySQL 5.5 Ubuntu 12.04 LTS and it looks like either this very serious patch that stop many people from upgrading is not in or something else has been solved. Since the bug #54382 is a left join rather than any join and the #54382 is already closed and I assume that patch is already in by now, I've decided to open a second issue.

If you do a join and union on some tables, it crashes while this worked perfectly before on 5.1

How to repeat:
create table f(f varchar(1));
select f from f union select f.f from f join (f f2) on (f.f=f2.f);

it gives an error:
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 (f.f=f2.f)' at line 1

Suggested fix:
Change parser.

That other closed bug says:

"[11 Oct 2012 17:21] Paul Dubois

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."

So, the bug is NOT fixed in 5.5.x, only in 5.6.8+ (don't ask me why...). In 5.6.9 your query works:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.9-rc  |
+-----------+
1 row in set (0.00 sec)

mysql> create table f(f varchar(1));
Query OK, 0 rows affected (0.48 sec)

mysql> select f from f union select f.f from f join (f f2) on (f.f=f2.f);
Empty set (0.00 sec)

Thank you for the bug report. Verified as described:

d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.31 Source distribution

Copyright (c) 2000, 2013, 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 5.5 >use test
Database changed
mysql 5.5 >create table f(f varchar(1));
Query OK, 0 rows affected (0.33 sec)

mysql 5.5 >select f from f union select f.f from f join (f f2) on (f.f=f2.f);
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 (f.f=f2.f)' at line 1
mysql 5.5 >exit
Bye

d:\dbs>57c

d:\dbs>d:\dbs\5.7\bin\mysql -uroot --port=3540 --prompt="mysql 5.7 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.1-m11-log Source distribution

Copyright (c) 2000, 2013, 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 5.7 >use test
Database changed
mysql 5.7 >create table f(f varchar(1));
Query OK, 0 rows affected (0.32 sec)

mysql 5.7 >select f from f union select f.f from f join (f f2) on (f.f=f2.f);
Empty set (0.01 sec)

mysql 5.7 >

Hi,

Is there already a planning for this bug to be closed? For me it sounds strange that it is already solved in other version, so you know what to do, and it's a very serious bug. Ubuntu 12.04 LTS is a version which is Supported for a Long Time, so a lot of servers wish to move to this version. On my systems, this bug is blocking for upgrading to Ubuntu 12.04 LTS. The only alternative there is is to move to MariaDB, which I don't wish to if I don't have to.

I just received an update from MySQL on 12.04 LTS (which does not happen very often) and this bug was still not in... It's painful to have this bug for over several years while it's marked as Serious.

Best regards and thanks in advance,
Freek

Hi,

Is there any progress on this? This bug is already solved in higher releases and it works in lower releases. I don't understand why it takes so long to bring this back, while it's a Serious bug!!! It looks like it's not really treated seriously if it's kept in for more than a year.

Thanks in advance,
Freek

Hi,

I've just started testing Ubuntu 13.04, the version that comes out this month. It also comes with MySQL 5.5. This means that for loads of systems, it is not possible to upgrade Ubuntu to higher versions than 11.10.

This bug is mentioned as serious and kept open for years...

Please could you fix this soon??

Thanks in advance,
Freek

The issue has been fixed in 5.5.32.