Sort by effecting number of rows returned

[webchalkboard]

Hi,

I've just discovered something extremely strange and I can't work out what on earth could be the reason for it.

If I do this query.

SELECT *
FROM `cashback`
WHERE `user_id` =1912
ORDER BY `user_id` ASC

I get 1037 rows returned

However if I sort by a different column

SELECT *
FROM `cashback`
WHERE `user_id` =1912
ORDER BY `epi_report_id` ASC

Now I get 1204 rows returned.

How can this possibly be? Surely the order you get the columns returned should not effect the number of rows returned!?

Am I missing something really silly here or am I in the twilight zone?

Thanks,
Tom

[guelphdad]

Are you running this directly in mysql or through php or something?
If not directly in mysql change your select to just select (*) and leave the rest the same in both cases. How many rows does it return in both cases?

The order by will not change the number of rows returned. I'm guessing it is something elsewhere in your code.

[webchalkboard]

Hi,

I'm running this in the mysql command line shell.

If I run

select * from cashback where user_id=1912;

I get 1037 rows in set (0.01 sec)

But if I order by the epi_report_id column ie.

select * from cashback where user_id=1912 order by epi_report_id;

I get 1204 rows in set (0.02 sec)

Please can someone explain why sorting by a column should effect the number of rows returned...?

I also noticed that if I count the rows I do get the same result each time, i.e.

mysql> select count(*) as count from cashback where user_id=1912 order by epi_report_id;
+-------+
| count |
+-------+
|  1037 |
+-------+
1 row in set (0.00 sec)

and

mysql> select count(*) as count from cashback where user_id=1912;
+-------+
| count |
+-------+
|  1037 |
+-------+
1 row in set (0.00 sec)

Just incase you think the structure of the table could be effecting things here it is:

mysql> describe cashback;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| cashback_id   | int(11)       |      | PRI | NULL    | auto_increment |
| user_id       | int(11)       |      | MUL | 0       |                |
| epi_report_id | int(11)       |      | MUL | 0       |                |
| payout        | float         |      |     | 0       |                |
| paid          | enum('0','1') |      | MUL | 0       |                |
| reversed      | enum('0','1') |      | MUL | 0       |                |
| revdate       | int(11)       |      |     | 0       |                |
+---------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

Thanks,
Tom

[r937]

Please can someone explain why sorting by a column should effect the number of rows returned...?

it shouldn't

it's friday the 13th

drop and recreate your index(es), repair your table, and reboot the server

i don't actually know if that will solve your problem but it couldn't hurt

[webchalkboard]

Thanks I repaired the table and the results seem to match now!