Bug #12934 | Select without `where` does not show row that same select with `where` returns | ||
---|---|---|---|
Submitted: | 1 Sep 2005 17:42 | Modified: | 8 Sep 2005 23:16 |
Reporter: | Oleg Marchuk | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 4.1.11-standard, 4.1.14-nt | OS: | Linux (Linux, Windows) |
Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[1 Sep 2005 17:42]
Oleg Marchuk
[1 Sep 2005 17:51]
Oleg Marchuk
Actual results: mysql> \. export.sql +----------+ | col_0_0_ | +----------+ | 107 | | 389 | | 1392 | | 1393 | | 1555 | | 1740 | | 1790 | | 1798 | | 1799 | | 1813 | +----------+ 10 rows in set (0.03 sec) +----------+ | col_0_0_ | +----------+ | 1853 | +----------+ 1 row in set (0.00 sec)
[2 Sep 2005 12:45]
Valeriy Kravchuk
Verified on 4.1.14 with provided test data: mysql> select distinct commercial0_.id as col_0_0_ -> from commercials commercial0_ -> inner join -> appendixes appendixes1_ on commercial0_.id=appendixes1_.commercial -> inner join -> schedule schedule2_ on appendixes1_.id=schedule2_.appendix -> inner join -> periods period3_ on schedule2_.period=period3_.id -> group by period3_.id , schedule2_.theDay -> order by commercial0_.id, period3_.id, schedule2_.theDay ; +----------+ | col_0_0_ | +----------+ | 107 | | 389 | | 1392 | | 1393 | | 1555 | | 1740 | | 1790 | | 1798 | | 1799 | | 1813 | +----------+ 10 rows in set (0.30 sec) mysql> select distinct commercial0_.id as col_0_0_ -> from commercials commercial0_ -> inner join -> appendixes appendixes1_ on commercial0_.id=appendixes1_.commercial -> inner join -> schedule schedule2_ on appendixes1_.id=schedule2_.appendix -> inner join -> periods period3_ on schedule2_.period=period3_.id -> where appendixes1_.id = 1928 -> group by period3_.id , schedule2_.theDay -> order by commercial0_.id, period3_.id, schedule2_.theDay ; +----------+ | col_0_0_ | +----------+ | 1853 | +----------+ 1 row in set (0.01 sec) This may help also: mysql> explain select distinct commercial0_.id as col_0_0_ -> from commercials commercial0_ -> inner join -> appendixes appendixes1_ on commercial0_.id=appendixes1_.commercial -> inner join -> schedule schedule2_ on appendixes1_.id=schedule2_.appendix -> inner join -> periods period3_ on schedule2_.period=period3_.id -> group by period3_.id , schedule2_.theDay -> order by commercial0_.id, period3_.id, schedule2_.theDay ; +----+-------------+--------------+--------+------------------------------------ ------------+--------------------+---------+------------------------------+----- -+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+------------------------------------ ------------+--------------------+---------+------------------------------+----- -+----------------------------------------------+ | 1 | SIMPLE | period3_ | index | PRIMARY | PRIMARY | 4 | NULL | 73 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | schedule2_ | ref | appendix,FKD6669297ACA91414,FKD6669 297F8E53C84 | FKD6669297F8E53C84 | 5 | test.period3_.id | 45 | Using where | | 1 | SIMPLE | appendixes1_ | eq_ref | PRIMARY,FK80853C37913B7696 | PRIMARY | 4 | test.schedule2_.appendix | 1 | | | 1 | SIMPLE | commercial0_ | eq_ref | PRIMARY | PRIMARY | 4 | test.appendixes1_.commercial | 1 | Using index | +----+-------------+--------------+--------+------------------------------------ ------------+--------------------+---------+------------------------------+----- -+----------------------------------------------+ 4 rows in set (0.02 sec) mysql> explain select distinct commercial0_.id as col_0_0_ -> from commercials commercial0_ -> inner join -> appendixes appendixes1_ on commercial0_.id=appendixes1_.commercial -> inner join -> schedule schedule2_ on appendixes1_.id=schedule2_.appendix -> inner join -> periods period3_ on schedule2_.period=period3_.id -> where appendixes1_.id = 1928 -> group by period3_.id , schedule2_.theDay -> order by commercial0_.id, period3_.id, schedule2_.theDay ; +----+-------------+--------------+--------+------------------------------------ ------------+----------+---------+------------------------+------+-------------- ----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+------------------------------------ ------------+----------+---------+------------------------+------+-------------- ----------------------+ | 1 | SIMPLE | appendixes1_ | const | PRIMARY,FK80853C37913B7696 | PRIMARY | 4 | const | 1 | Using tempora ry; Using filesort | | 1 | SIMPLE | commercial0_ | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | | 1 | SIMPLE | schedule2_ | ref | appendix,FKD6669297ACA91414,FKD6669 297F8E53C84 | appendix | 5 | const | 24 | Using where; Using index; Distinct | | 1 | SIMPLE | period3_ | eq_ref | PRIMARY | PRIMARY | 4 | test.schedule2_.period | 1 | Using index; Distinct | +----+-------------+--------------+--------+------------------------------------ ------------+----------+---------+------------------------+------+-------------- ----------------------+ 4 rows in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 4.1.14-nt | +-----------+ 1 row in set (0.01 sec)
[8 Sep 2005 23:16]
Sergey Petrunya
Looking at the queries select distinct commercial0_.id as col_0_0_ from commercials commercial0_ inner join appendixes appendixes1_ on commercial0_.id=appendixes1_.commercial inner join schedule schedule2_ on appendixes1_.id=schedule2_.appendix inner join periods period3_ on schedule2_.period=period3_.id group by period3_.id , schedule2_.theDay order by commercial0_.id, period3_.id, schedule2_.theDay ; -- (1) and select distinct commercial0_.id as col_0_0_ from commercials commercial0_ inner join appendixes appendixes1_ on commercial0_.id=appendixes1_.commercial inner join schedule schedule2_ on appendixes1_.id=schedule2_.appendix inner join periods period3_ on schedule2_.period=period3_.id where appendixes1_.id = 1928 group by period3_.id , schedule2_.theDay order by commercial0_.id, period3_.id, schedule2_.theDay ; --(2) it seems that it is ok that (2) returns values that (1) doesnt. The value of commercial0_.id is not part of GROUP BY clause, so for each {period3_.id , schedule2_.theDay} value group we get *any* value of commercial0_.id within that group (this is documented, see http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html) Running this query select count( distinct commercial0_.id) from commercials commercial0_ inner join appendixes appendixes1_ on commercial0_.id=appendixes1_.commercial inner join schedule schedule2_ on appendixes1_.id=schedule2_.appendix inner join periods period3_ on schedule2_.period=period3_.id group by period3_.id , schedule2_.theDay ; one sees that almost every {period3_.id, schedule2_.theDay} value group has several different values of commercial0_.id. When one adds 'where appendixes1_.id = 1928' clause all value groups have commercial0_.id=1853 for all rows, and so the query (2) returns 1853 after DISTINCT has been applied. Without the 'where appendixes1_.id = 1928' every single value group that has a row with commercial0_.id=1853 also has rows with other values of commercial0_.id. It happens so that rows with other values of commercial0_.id get "choosen" (see the above mentioned URL), and so the query (1) doesnt return 1853. Considering the above, the query output seems to be correct. Oleg, feel free to re-open the bug if there are any other reasons why it is not.