Bug #26705 Tablenames order in FROM when using JOIN

• Submitted: 27 Feb 2007 23:43
• Modified: 28 Feb 2007 0:26
• Reporter: Damijan Kozic
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S2 (Serious)
• Version: 5.0.27
• OS: UNIX (?)
• CPU Architecture: Any
• Tags: from, join, left join, on, SELECT, table-names-order

[27 Feb 2007 23:43] Damijan Kozic

Description:
The order of table names, from which I make a select, makes a difference...

This will generate the an error:

select * from card, card_service
left join card_data on card_data.card_id = card.id

1054: Unknown column 'card.id' in 'on clause'

The following works, though:

select * from card_service, card
left join card_data on card_data.card_id = card.id

This works fine in MySQL 4.1. And it's also not platform specific (tried on 5.0.24a-community-nt locally, too).

How to repeat:
You need three tables (2 to select from, 1 to join).

In the 'on clause' use a field from a table, listed FIRST in the 'from clause'.

This should generate the error... I tried several different tables, always the same.

Suggested fix:
Fix the parser so it 'recognizes' ALL tables listed in 'from' when using the 'on clause'...

[27 Feb 2007 23:51] MySQL Verification Team

Thank you for the bug report. Could you please provide the
complete test script which reproduces the behavior reported.
Thanks in advance.

[28 Feb 2007 0:02] Damijan Kozic

Repeat of the bug.

Attachment: join-on-bug-repeat.sql (application/octet-stream, text), 1.63 KiB.

[28 Feb 2007 0:06] Damijan Kozic

Here it is.

Using MySQL Query Browser I copied the creation SQL to the just attached file.

Hope it helps... for the error to occur you do not have to have data in the tables.

[28 Feb 2007 0:12] MySQL Verification Team

Thank you for the feedback. Please read the Manual about the join syntax
introduced since version 5.0.12:

mysql> select * from card, card_service
    -> left join card_data on card_data.card_id = card.id;
ERROR 1054 (42S22): Unknown column 'card.id' in 'on clause'
mysql> select * from (card, card_service)
    -> left join card_data on card_data.card_id = card.id;
Empty set (0.02 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.27-community-nt |
+---------------------+
1 row in set (0.03 sec)

mysql>

[28 Feb 2007 0:26] Damijan Kozic

Oh. Ok. Not a bug. The comma has now less precedence. Get it.

Darn. Have to check all scripts. :o