Description:
Certain queries fail to return all records - when they should. Other queries that appear to be syntactically identical do return all records.
For example:
select * from `dps27`.`orders` limit 0,5000;
[returns 129 rows]
select * from `dps27`.`orders` limit 0,4999;
[returns 131 rows - the correct number]
How to repeat:
You may not be able to repeat easily! But here are the facts:
Apparently irrelevant changes to a query will cause rows to be dropped from the results.
Consider this query:
mysql> select * from `dps27`.`orders` limit 0,5000;
| 147 | 326 | 1 | 2331 Seven Hills Rd | | Fairfield | 22 | 98304 | 14 | 641-472-7034 | 326 | 2004-05-04 10:37:23 | 2 | 2 | NULL | 3 | 1 | 20.00 | NULL | 0.00 | NULL | 1 | Thomas Krystofiak | 0.00 | 1 | NULL | 0.00 | 0.00 | 0.00 | NULL | 0.00 |
| 148 | 327 | 1 | 2331 Seven Hills Rd | | Fairfield | 22 | 98304 | 14 | 641-472-7034 | 327 | 2004-05-04 10:40:11 | 2 | 2 | NULL | 3 | 1 | 20.00 | NULL | 0.00 | NULL | 1 | Thomas Krystofiak | 0.00 | 1 | 123 | 0.00 | 0.00 | 0.00 | NULL | 0.00 |
| 149 | 328 | 1 | 2331 Seven Hills Rd | | Fairfield | 22 | 98304 | 14 | 641-472-7034 | 328 | 2004-05-04 10:42:05 | 2 | 2 | NULL | 3 | 1 | 20.00 | NULL | 0.00 | NULL | 1 | Thomas Krystofiak | 0.00 | 1 | 124 | 0.00 | 0.00 | 0.00 | NULL | 0.00 |
129 rows in set (0.00 sec)
Notice that 129 rows are returned. I only show the last three records here. Missing are orderNumber 329 and 330 (the two most recently added records). orderNumber is the second column returned.
Then I run this query, which I assume should be functionally identical:
mysql> select * from dps27.orders limit 0,5000;
| 149 | 328 | 1 | 2331 Seven Hills Rd | | Fairfield | 22 | 98304 | 14 | 641-472-7034 | 328 | 2004-05-04 10:42:05 | 2 | 2 | NULL | 3 | 1 | 20.00 | NULL | 0.00 | NULL | 1 | Thomas Krystofiak | 0.00 | 1 | 124 | 0.00 | 0.00 | 0.00 | NULL | 0.00 |
| 150 | 329 | 1 | 2331 Seven Hills Rd | | Fairfield | 22 | 98304 | 14 | 641-472-7034 | 329 | 2004-05-04 10:46:45 | 2 | 2 | NULL | 3 | 1 | 20.00 | NULL | 0.00 | NULL | 3 | Thomas Krystofiak | 0.00 | 3 | 125 | 0.00 | 0.00 | 0.00 | NULL | 0.00 |
| 151 | 330 | 1 | 2331 Seven Hills Rd | | Fairfield | 22 | 98304 | 14 | 641-472-7034 | 330 | 2004-05-04 10:54:08 | 2 | 2 | NULL | 3 | 1 | 20.00 | NULL | 0.00 | NULL | 3 | Thomas Krystofiak | 0.00 | 3 | 127 | 0.00 | 0.00 | 0.00 | NULL | 0.00 |
131 rows in set (0.00 sec)
Now I get all 131 records – including the missing last two records.
I find that almost any variation I make on the first query will cause all records to be returned, including all of these examples.
select * from `dps27`.`orders` limit 0,5001;
select * from `dps27`.`orders` limit 0,4999;
select * from `dps27`.`orders` where ordersID > 0 limit 0,5001;
select * from `orders` limit 0,5000;
select * from orders limit 0,5000;
select * from dps27.orders limit 0,5000;
select * from `dps27`.`orders` limit 0,131;
select * from `dps27`.`orders`;
select orderNumber from `dps27`.`orders` limit 0,5000;
But we believe that there are other queries that are also failing to return certain rows – although just now the only one that consistently fails to return all rows is this one:
select * from `dps27`.`orders` limit 0,5000;
