MySQL Bugs: #17680: Condition in WHERE clause ignored

Bug #17680	Condition in WHERE clause ignored
Submitted:	23 Feb 2006 21:17	Modified:	2 Apr 2006 11:09
Reporter:	Juergen Neuhoff	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySQL 5.0.13	OS:	Linux (Linux Fedora Core 4, Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
The condition in the WHERE-clause is sometimes ignored when using multiple INNER JOINs.

How to repeat:
Run the following query on our attached test database (ohpr_demob.sql.tar.gz):

SELECT DISTINCT pages.page_seq AS page_seq, sections.toc_seq AS toc_seq, sections.fk_mno, pages.page_label
FROM sections
INNER JOIN sect_pages ON sect_pages.fk_mno = sections.fk_mno
AND sect_pages.st_id = sections.st_id
INNER JOIN pages ON pages.fk_mno = sect_pages.fk_mno
AND pages.page_seq = sect_pages.page_seq
WHERE sections.fk_mno =16
ORDER BY page_seq, toc_seq
LIMIT 0 , 30;

Suggested fix:
We managed to get the correct results by changing the WHERE-clause as follows:

WHERE pages.fk_mno =16

It looks like when using the same field name in different tables used by the INNER JOINs, the WHERE-clause only accepts the ones from the inner-most joined tables.

Thank you for the bug report. I was unable to find the below
mentioned file:

Run the following query on our attached test database (ohpr_demob.sql.tar.gz):

Thanks in advance.

test database for reproducing the bug

Attachment: ohpr_demob.sql.zip (application/zip, text), 100.26 KiB.

Hello Miguel,

I tried in vain to upload the database several times yesterday, probably failed because they were too big. Now you should see a smaller DB file which still enables you to reproduce the error.

BTW, the same query works fine in previous 4.x releases of MySQL.
We are not quite sure whether this is a bug or intentional changes in MySQL 5.x. Something must have changed in the way INNER JOINs are optimized.

I've got the following results with your test data on 5.0.21-BK:

mysql> SELECT DISTINCT pages.page_seq AS page_seq, sections.toc_seq AS toc_seq,
    -> sections.fk_mno, pages.page_label
    -> FROM sections
    -> INNER JOIN sect_pages ON sect_pages.fk_mno = sections.fk_mno
    -> AND sect_pages.st_id = sections.st_id
    -> INNER JOIN pages ON pages.fk_mno = sect_pages.fk_mno
    -> AND pages.page_seq = sect_pages.page_seq
    -> WHERE sections.fk_mno =16
    -> ORDER BY page_seq, toc_seq
    -> LIMIT 0 , 30;
+----------+---------+--------+------------+
| page_seq | toc_seq | fk_mno | page_label |
+----------+---------+--------+------------+
| 1        | 100     | 16     | 1          |
| 2        | 200     | 16     | 2          |
| 2        | 300     | 16     | 2          |
| 3        | 200     | 16     | 3          |
| 3        | 400     | 16     | 3          |
| 3        | 500     | 16     | 3          |
| 4        | 200     | 16     | 4          |
| 4        | 600     | 16     | 4          |
| 4        | 700     | 16     | 4          |
| 5        | 800     | 16     | 5          |
| 5        | 900     | 16     | 5          |
| 6        | 800     | 16     | 6          |
| 6        | 1000    | 16     | 6          |
| 6        | 1100    | 16     | 6          |
| 7        | 800     | 16     | 7          |
| 7        | 1200    | 16     | 7          |
| 7        | 1300    | 16     | 7          |
| 8        | 1400    | 16     | 8          |
| 8        | 1500    | 16     | 8          |
| 9        | 1400    | 16     | 9          |
| 9        | 1500    | 16     | 9          |
| 10       | 1400    | 16     | 10         |
| 10       | 1600    | 16     | 10         |
| 10       | 1700    | 16     | 10         |
| 11       | 1400    | 16     | 11         |
| 11       | 1600    | 16     | 11         |
| 11       | 1700    | 16     | 11         |
| 12       | 1400    | 16     | 12         |
| 12       | 1800    | 16     | 12         |
| 12       | 1900    | 16     | 12         |
+----------+---------+--------+------------+
30 rows in set (0.01 sec)

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

These results looks OK for me. So, please, try to use newer version, 5.0.19, generally available for quite a some time already, and reopen this bug report in case of any incorrect results. Your 5.0.13 is really old.