RE: LEFT JOIN selects with ORDER BY and WHERE and LIMIT missing results

[Kevin Day]

I've shrunk the dataset down as far as I can to continue to reproduce this issue, but the .gz file is still 1.3MB

Running this on 4.0.3 produces the correct results. 4.0.4 doesn't.

fetch http://www.dragondata.com/ditsy.gz

bash-2.05b# mysql test < ditsy
bash-2.05b# mysql test

mysql> SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.b=t2.b WHERE t1.d > 10 AND (t2.c=2) ORDER BY e;
+----------+
| COUNT(*) |
+----------+
| 909 |
+----------+
1 row in set (0.36 sec)

mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.b=t2.b WHERE t1.d > 10 AND (t2.c=2) ORDER BY e desc limit 0,12;
Empty set (0.16 sec)

mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.b=t2.b WHERE t1.d > 10 AND (t2.c=2) ORDER BY e desc limit 0,100;
+--------+-----+-----+------------------+
| a | b | d | e |
+--------+-----+-----+------------------+
| 508493 | 310 | 173 | 3.66816143497758 |
+--------+-----+-----+------------------+
1 row in set (0.14 sec)

mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.b=t2.b WHERE t1.d > 10 AND (t2.c=2) ORDER BY e limit 0,12;
+--------+-----+----+--------------------+
| a | b | d | e |
+--------+-----+----+--------------------+
| 388402 | 520 | 31 | -0.185185185185185 |
+--------+-----+----+--------------------+
1 row in set (0.13 sec)

mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.b=t2.b WHERE t1.d > 10 AND (t2.c=2) ORDER BY e limit 0,100;
+--------+-----+----+--------------------+
| a | b | d | e |
+--------+-----+----+--------------------+
| 388402 | 520 | 31 | -0.185185185185185 |
| 389682 | 520 | 50 | 0.05 |
| 316393 | 520 | 12 | 0.338709677419355 |
+--------+-----+----+--------------------+
3 rows in set (0.14 sec)








---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php