| Bug #44821 | select distinct on partitioned table returns wrong results | ||
|---|---|---|---|
| Submitted: | 12 May 2009 13:10 | Modified: | 24 Jun 2009 11:05 |
| Reporter: | Rene Eng | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S1 (Critical) |
| Version: | 5.1.33, 5.1, 6.0 bzr | OS: | Any (Linux, Sun Solaris 10) |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
| Tags: | distinct, partition, SELECT | ||
[12 May 2009 14:39]
Valeriy Kravchuk
Thank you for the problem report. Can you send a dump of table's data? If you can't, please, send the results of EXPLAIN and EXPLAIN PARTITIONS for all the queries.
[13 May 2009 5:59]
Rene Eng
The complete table contains 3748 MByte of data, so I hope the explain output is sufficient. SEARCH> explain select vn,bc from search where vn=206739 and bc != 0 order by vn,bc; +----+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | search | range | vnbc | vnbc | 6 | NULL | 106 | Using where; Using index | +----+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.03 sec) SEARCH> explain partitions select vn,bc from search where vn=206739 and bc != 0 order by vn,bc; +----+-------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | search | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49 | range | vnbc | vnbc | 6 | NULL | 106 | Using where; Using index | +----+-------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) SEARCH> explain select distinct vn,bc from search where vn=206739 and bc != 0;+----+-------------+--------+-------+---------------+------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | search | range | vnbc | vnbc | 6 | NULL | 1 | Using where; Using index for group-by | +----+-------------+--------+-------+---------------+------+---------+------+------+---------------------------------------+ 1 row in set (0.00 sec) SEARCH> explain partitions select distinct vn,bc from search where vn=206739 and bc != 0; +----+-------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | search | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49 | range | vnbc | vnbc | 6 | NULL | 1 | Using where; Using index for group-by | +----+-------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+---------------------------------------+ 1 row in set (0.00 sec) SEARCH> explain select distinct bc from search where vn=206739 and bc != 0;+----+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | search | range | vnbc | vnbc | 6 | NULL | 106 | Using where; Using index | +----+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) SEARCH> explain partitions select distinct bc from search where vn=206739 and bc != 0; +----+-------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | search | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49 | range | vnbc | vnbc | 6 | NULL | 106 | Using where; Using index | +----+-------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
[13 May 2009 8:37]
Sveta Smirnova
Thank you for the feedback. Verified as described.
[13 May 2009 8:38]
Sveta Smirnova
test case
Attachment: bug44821.test (application/octet-stream, text), 4.13 KiB.
[13 May 2009 8:39]
Sveta Smirnova
Bug is not repeatable without partitions.
[14 May 2009 22:11]
Konstantin Osipov
Wrong lead.
[8 Jun 2009 12:28]
Martin Hansson
Minimal test case
Attachment: bug44821.test (, text), 624 bytes.
[8 Jun 2009 12:29]
Martin Hansson
Corresponding expected result
Attachment: bug44821.result (, text), 1.08 KiB.
[8 Jun 2009 17:38]
Mattias Jonsson
The bug does not exist in 6.0, and the change between 5.1 and 6.0 that fixed the bug was:
=== modified file 'sql/opt_range.cc'
--- sql/opt_range.cc 2009-03-24 13:58:52 +0000
+++ sql/opt_range.cc 2009-06-08 17:32:58 +0000
@@ -8556,7 +8556,7 @@
result= file->read_range_first(last_range->min_keypart_map ? &start_key : 0,
last_range->max_keypart_map ? &end_key : 0,
test(last_range->flag & EQ_RANGE),
- sorted);
+ TRUE);
if (last_range->flag == (UNIQUE_RANGE | EQ_RANGE))
last_range= 0; // Stop searching
Please see bug#41136 where it was fix in 6.0 (may be more related changes).
[9 Jun 2009 11:54]
Martin Hansson
That alone should fix this. We should probably backport that fix to 5.1.
[10 Jun 2009 9:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/75994 2931 Martin Hansson 2009-06-10 Bug#44821: select distinct on partitioned table returns wrong results Range analysis did not request sorted output from the storage engine, which cause partitioned handlers to process one partition at a time while reading key prefixes in ascending order, causing values to be missed. Fixed by always requesting sorted order during range analysis. This fix is introduced in 6.0 by the fix for bug no 41136. @ mysql-test/r/group_min_max.result Bug#44821: Test result. @ mysql-test/t/group_min_max.test Bug#44821: Test case @ sql/opt_range.cc Bug#44821: Fix.
[11 Jun 2009 11:41]
Mattias Jonsson
Patch approved, OK to push for me :)
[11 Jun 2009 21:24]
Gleb Shchepa
Ok to push.
[16 Jun 2009 10:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/76364 2949 Georgi Kodinov 2009-06-16 Addendum to the fix for bug #44821: move partition dependent test to a test file that guarantees the presence of partition code
[16 Jun 2009 11:04]
Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:joro@sun.com-20090616095957-x865jcqrqmne7wlu) (merge vers: 5.1.36) (pib:6)
[24 Jun 2009 11:05]
Jon Stephens
Documented bugfix in the 5.1.36 changelog as follows:
Queries using DISTINCT on multiple columns or GROUP BY on
multiple columns did not return correct results with partitioned
tables.
See also Bug #41136.
[10 Jul 2009 11:21]
Bugs System
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:alexey.kopytov@sun.com-20090617124504-kmzeyf4lsu8d5ig1) (merge vers: 5.4.4-alpha) (pib:11)
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
Description: We have a partitioned MyISAM table (see 'how to repeat') A request for a subset of the values returns 15 rows in 5 combinations. When using a 'select distinct' to get each combination only once, only 1 result is returned. A select distinct with only one field works correctly. How to repeat: Table create statement: create table if not exists search ( searchWord char(20) not null, searchWordId mediumint not null, vn int not null, bc smallint not null, unique key word_key (searchWord, searchWordId, vn, bc), key vnbc (vn, bc) ) ENGINE = myisam PARTITION BY KEY( searchWord, vn) PARTITIONS 50 Example output: SEARCH> select vn,bc from search where vn=206739 and bc != 0 order by vn,bc; +--------+------+ | vn | bc | +--------+------+ | 206739 | 684 | | 206739 | 684 | | 206739 | 684 | | 206739 | 870 | | 206739 | 870 | | 206739 | 870 | | 206739 | 2612 | | 206739 | 2612 | | 206739 | 2612 | | 206739 | 8332 | | 206739 | 8332 | | 206739 | 8332 | | 206739 | 8384 | | 206739 | 8384 | | 206739 | 8384 | +--------+------+ 15 rows in set (0.00 sec) ==> Correct. SEARCH> select distinct vn,bc from search where vn=206739 and bc != 0; +--------+-----+ | vn | bc | +--------+-----+ | 206739 | 870 | +--------+-----+ 1 row in set (6.60 sec) ==> Obviously wrong! SEARCH> select distinct bc from search where vn=206739 and bc != 0; +------+ | bc | +------+ | 684 | | 870 | | 2612 | | 8332 | | 8384 | +------+ 5 rows in set (0.00 sec) ==> Correct. The same wrong result is returned if 'group by vn,bc' is used instead of 'select distinct'.