MySQL Bugs: #38013: Bad data: subquery + ORDER BY + LIMIT => WHERE condition not enforced

Bug #38013	Bad data: subquery + ORDER BY + LIMIT => WHERE condition not enforced
Submitted:	10 Jul 2008 12:05	Modified:	7 Sep 2008 15:48
Reporter:	Philip Stoev	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	regression

Description:
This query:

SELECT varchar_key FROM C AS OUTR
WHERE int_nokey NOT IN ( SELECT pk FROM BB )
AND varchar_key IS NOT NULL
ORDER BY pk
LIMIT 3;

Returns rows containing NULL , even though the AND varchar_key IS NOT NULL clause explicitly excludes such rows. 5.1 is not affected.

How to repeat:
A test will be uploaded shortly.

Test case for bug 38013

Attachment: bug38013.test (application/octet-stream, text), 2.55 KiB.

Thank you for the bug report. Repeatable on 6.0.4 and 6.0.6. Not repeatable on 6.0.2:

+-------------+
| varchar_key |
+-------------+
| p           |
| e           |
| w           |
+-------------+
3 rows in set (0.03 sec)

mysql> show variables like "%version%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| protocol_version        | 10                             |
| version                 | 6.0.2-alpha-community-nt-debug |
| version_comment         | MySQL Community Server (GPL)   |
| version_compile_machine | ia32                           |
| version_compile_os      | Win32                          |
+-------------------------+--------------------------------+
5 rows in set (0.03 sec)

This is caused by the same problem as BUG#37977. The problem is fixed by fix for BUG#37977. Changing status to Duplicate