MySQL Bugs: #31427: Simple Left Join Select Queries broken

Bug #31427	Simple Left Join Select Queries broken
Submitted:	5 Oct 2007 19:55	Modified:	5 Oct 2007 21:40
Reporter:	Dathan Pattishall	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Any (2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to:		CPU Architecture:	Any
Tags:	5.0.45, community, LEFT OUTER JOIN, SELECT

Description:

At the Bison layer LEFT Joins are broken.

SELECT p.id, gt.object_id  FROM Photos p, PhotosExtra px LEFT JOIN GeoTagged gt ON gt.object_id=p.id   WHERE px.photo_id=p.id   AND p.id = 2173;  
 ERROR 1054 (42S22): Unknown column 'p.id' in 'on clause'

Alias of a table does not work, Calling the table itself does not work either. The parser is broken.

How to repeat:

Create a 3 tables perform a left join and watcher the parser layer break.

Suggested fix:
Fix the bison layer, roll back the change made a few versions ago. Run UNIT TESTS to make sure that changes to the bison layer doesn't break simple Joins.

Work around replace the comma with JOIN

SELECT p.id, gt.object_id  FROM Photos p JOIN PhotosExtra px LEFT JOIN GeoTagged gt ON gt.object_id=p.id  WHERE px.photo_id=p.id   AND p.id = 2173;

Dathan, 

The parser is not broken. It is working exactly as described at http://dev.mysql.com/doc/refman/5.0/en/join.html . In order to get the behavior you wanted, you need to modify the order of operations in your query. Any of these three variants should perform as you expected

SELECT p.id, gt.object_id  FROM PhotosExtra px,Photos p LEFT JOIN GeoTagged gt ON
gt.object_id=p.id   WHERE px.photo_id=p.id   AND p.id = 2173;  

SELECT p.id, gt.object_id  FROM (Photos p, PhotosExtra px) LEFT JOIN GeoTagged gt ON
gt.object_id=p.id   WHERE px.photo_id=p.id   AND p.id = 2173;  

SELECT p.id, gt.object_id  FROM Photos p INNER JOIN PhotosExtra px ON px.photo_id=p.id  LEFT JOIN GeoTagged gt ON
gt.object_id=p.id  WHERE  p.id = 2173 ;  

This behavior has been unchanged since 5.0.12 was released on 2005-10-02

This is a bug, The "," operator is not working. Version 5.0.33 does not have this problem.

Here's a reduced test case for this:

USE test;

DROP TABLE IF EXISTS a;
CREATE TABLE a (id int);

DROP TABLE IF EXISTS b;
CREATE TABLE b (id int);

DROP TABLE IF EXISTS c;
CREATE TABLE c (id int);

SELECT * FROM a JOIN b ON b.id=a.id LEFT JOIN c ON c.id=a.id;

SELECT * FROM a, b LEFT JOIN c ON c.id=a.id WHERE b.id=a.id;

Isn't this not a bug, but a change in the parser for 5.0 from 4.1?

Thank you for the bug report. That is change done since 5.0.12:

[miguel@skybr 5.0]$ bin/mysql -uroot db8
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.52-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table Photos (id serial);
Query OK, 0 rows affected (0.00 sec)

mysql> create table PhotosExtra (photo_id serial);
Query OK, 0 rows affected (0.00 sec)

mysql> create table GeoTagged (object_id serial);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> SELECT p.id, gt.object_id  FROM Photos p, PhotosExtra px LEFT JOIN GeoTagged gt ON
    -> gt.object_id=p.id   WHERE px.photo_id=p.id   AND p.id = 2173;
ERROR 1054 (42S22): Unknown column 'p.id' in 'on clause'
mysql> 
mysql> 
mysql> SELECT p.id, gt.object_id  FROM (Photos p, PhotosExtra px) LEFT JOIN GeoTagged gt ON
    -> gt.object_id=p.id   WHERE px.photo_id=p.id   AND p.id = 2173;
Empty set (0.00 sec)

I'm able to repeat this same behavior in 5.0.30 and 5.0.48.

mysql 5.0.30-enterprise-gpl (root) [test]> create table t1 (id1 int);
Query OK, 0 rows affected (0.08 sec)

mysql 5.0.30-enterprise-gpl (root) [test]> create table t2 (id2 int);
Query OK, 0 rows affected (0.06 sec)

mysql 5.0.30-enterprise-gpl (root) [test]> create table t3 (id3 int);
Query OK, 0 rows affected (0.08 sec)

mysql 5.0.30-enterprise-gpl (root) [test]> select * from t1 a_t1, t2 a_t2 left join t3 a_t3 on a_t3.id3=a_t1.id1;
ERROR 1054 (42S22): Unknown column 'a_t1.id1' in 'on clause'

mysql 5.0.48-enterprise-gpl (root) [test]> create table t1 (id1 int);
Query OK, 0 rows affected (0.13 sec)

mysql 5.0.48-enterprise-gpl (root) [test]> create table t2 (id2 int);
Query OK, 0 rows affected (0.06 sec)

mysql 5.0.48-enterprise-gpl (root) [test]> create table t3 (id3 int);
Query OK, 0 rows affected (0.06 sec)

mysql 5.0.48-enterprise-gpl (root) [test]> select * from t1 a_t1, t2 a_t2 left join t3 a_t3 on a_t3.id3=a_t1.id1;
ERROR 1054 (42S22): Unknown column 'a_t1.id1' in 'on clause'

This *did* work in MySQL 4.1, on the other hand:

mysql 4.1.22-pro-gpl (root) [test]> create table t2 (id2 int);
Query OK, 0 rows affected (0.06 sec)

mysql 4.1.22-pro-gpl (root) [test]> create table t3 (id3 int);
Query OK, 0 rows affected (0.06 sec)

mysql 4.1.22-pro-gpl (root) [test]> select * from t1 a_t1, t2 a_t2 left join t3 a_t3 on a_t3.id3=a_t1.id1;
Empty set (0.06 sec)

Let me know if you think I've missed some subtle part of the query you're trying to execute.

Yes I am wrong REPLACING "," operator with JOIN was my test case which worked in 5.0.33. This is working according to the description linked above.

My bad, under the impressions with a conversation that I had with Brian, I was under the impression that this is a bug. I didn't notice that my test cases changed between versions.

I still think the quality of mysql has still suffered in the latest releases. But, then again I'm just one person with an opinion based on recent events.