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