Bug #15912 LEFT Join Fails by Complaining the Coloum does not exist
Submitted: 21 Dec 2005 17:32 Modified: 22 Dec 2005 10:01
Reporter: Robin Hickmott Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.17 OS:Windows (Windows XP)
Assigned to: MySQL Verification Team CPU Architecture:Any

[21 Dec 2005 17:32] Robin Hickmott
Description:
This might just be one of those Freak Syntax changes in MYSQL 5 but this seems to Fail with just a simple set of tables giving the most bizaar error and works fine in MySQL 4.

It may also be of course that im using two aliases for the same table in and out of the join.

A query with a LEFT join fails when it should return NULL and thorws an error saying that the colomn does not exist.

The Sample Code sets up a simple Quotig Ssytem where by a quote can be "Created" by a user and then Authorised by another member of staff

How to repeat:
CREATE TABLE `users` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL 
) TYPE = innodb;

INSERT INTO `users` ( `id` , `name` ) 
VALUES (
NULL , 'TEST USER 1'
), (
NULL , 'TEST USER 2'
);

CREATE TABLE `quote` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`quotedby` INT( 10 ) NOT NULL ,
`authorisedby` INT( 10 ) NULL 
) TYPE = innodb;

INSERT INTO `quote` ( `id` 'quote id' , `quotedby` 'quoted by' , `authorisedby` 'authorised by' ) 
VALUES (
NULL , '1', '2'
), (
NULL , '1', NULL 
);

The Below Query should output the quoteid the name of the person who created it and the name of the person who Authorised it (or Null if it hasent)

SELECT q.`id`, u1.`name`
FROM `quote` q, `users` u1
LEFT JOIN `users` u2 ON u2.`id` = q.`authorisedby`
WHERE u1.`id`=q.`quotedby`

In This Case and the Case of MySQL 4

ID          quoted by          authorised by
1           TEST USER 1      TEST USER 2
2           TEST USER 1      NULL

MySQL 5 just says

#1054 - Unknown column 'authorisedby' in 'on clause' 

Suggested fix:
Not Sure but seems to be something to do with not liking NULL values.
[21 Dec 2005 17:34] Robin Hickmott
Fixed Spelling Mistake in Title (sorry)
[21 Dec 2005 21:48] MySQL Verification Team
Indeed from version 5.0.12 the join query syntax was changed and some
queries worked in prior versions needs to be changed.

C:\mysql4116>bin\mysql --defaults-file=c:\mysql4116\s4116.ini -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-nt-max

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

mysql> use test
Database changed
mysql> SELECT q.`id`, u1.`name`
    -> FROM `quote` q, `users` u1
    -> LEFT JOIN `users` u2 ON u2.`id` = q.`authorisedby`
    -> WHERE u1.`id`= q.`quotedby`;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | TEST USER 1 |
|  2 | TEST USER 1 |
+----+-------------+
2 rows in set (0.23 sec)

mysql>

C:\mysql5017>bin\mysql --defaults-file=c:\mysql5017\s5017.ini -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.16-nt-max

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

win5018>> use test
Database changed
win5018>> SELECT q.`id`, u1.`name`
    -> FROM `quote` q, `users` u1
    -> LEFT JOIN (`users` u2,`quote` q1)  ON u2.`id` = q1.`authorisedby`
    -> WHERE u1.`id`= q.`quotedby`;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | TEST USER 1 |
|  2 | TEST USER 1 |
+----+-------------+
2 rows in set (0.00 sec)

win5018>>
[22 Dec 2005 10:01] Robin Hickmott
Sorry to be a pain but its intresting to note that

SELECT q.`prefix` , q.`id` , q.`date` , u1.`name` 'by', u2.`name` 'authed'
FROM `user` u1, `quote` q
LEFT JOIN `user` u2 ON q.`authed` = u2.`id`
WHERE q.`status` = '8'
AND q.`userid` = u1.`id`										ORDER BY `date` ASC 

Works a Treat in 5.0.17 where as

SELECT q.`prefix` , q.`id` , q.`date` , u1.`name` 'by', u2.`name` 'authed'
FROM `quote` q, `user` u1
LEFT JOIN `user` u2 ON q.`authed` = u2.`id`
WHERE q.`status` = '8'
AND q.`userid` = u1.`id`										ORDER BY `date` ASC 

Returns #1054 - Unknown column 'q.authed' in 'on clause' 

It would seem that the LEFT JOIN ignores table aliases and will only join with the table specified immediatly before it (Reversing the table quote and user in this instance) this seems a bit pickey I suppose.

SELECT q.`prefix` , q.`id` , q.`date` , u1.`name` 'by', u2.`name` 'authed'
FROM `quote` q, `user` u1
LEFT JOIN (`user` u2, `quote` q1) ON q1.`authed` = u2.`id`
WHERE q.`status` = '8'
AND q.`userid` = u1.`id`										ORDER BY `date` ASC

Gave the the Product of User and Quote joned to xyz and looked pretty nasty :)