Bug #54359 | Extra rows with join_cache_level=7,8 and two joins and multi-column index | ||
---|---|---|---|
Submitted: | 9 Jun 2010 8:49 | Modified: | 23 Nov 2010 3:32 |
Reporter: | John Embretsen | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | bzr_next-mr-opt-backporting, 6.0.14 | OS: | Any |
Assigned to: | Guilhem Bichot | CPU Architecture: | Any |
Tags: | join_cache_level, optimizer_switch |
[9 Jun 2010 8:49]
John Embretsen
[22 Jun 2010 8:47]
Guilhem Bichot
smaller testcase (less rows and tables and less complicated syntax): -- source include/have_innodb.inc set storage_engine=innodb; set optimizer_join_cache_level=7; CREATE TABLE t2 ( `pk` int(11) NOT NULL, `col_int_key` int(11) DEFAULT NULL, `col_varchar_key` varchar(1) DEFAULT NULL, `col_varchar_nokey` varchar(1) DEFAULT NULL, KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)) ; INSERT INTO t2 VALUES (4,9,'k','k'); INSERT INTO t2 VALUES (12,5,'k','k'); SELECT table2 .`col_int_key` FROM t2 table2, t2 table3 force index (`col_varchar_key`) where table3 .`pk` and table3 .`col_int_key` >= table2 .`pk` AND table3 .`col_varchar_key` = table2 .`col_varchar_nokey` ; this returns 9,9,5,5 instead of 9,9.
[28 Jun 2010 9:21]
Guilhem Bichot
sent analysis and waiting for Gluh's feedback as it's his code.
[30 Jun 2010 20:20]
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/112622 3204 Guilhem Bichot 2010-06-30 Fix for BUG#54359 "Extra rows with join_cache_level=7,8 and two joins --and multi-column index"; pushed index condition wasn't correctly checked in BKA_UNIQUE. @ mysql-test/include/join_cache.inc test for bug @ mysql-test/r/join_cache_jcl1.result correct result @ mysql-test/r/join_cache_jcl7.result without the code fix, result of SELECT would be 5,5,9,9 instead of 9,9 @ mysql-test/r/join_cache_jcl8.result without the code fix, result of SELECT would be 5,5,9,9 instead of 9,9 @ sql/sql_join_cache.cc See added doxygen comments. Without this fix, in the testcase (see join_cache.inc) we had this: - this is BKA_UNIQUE, which prepares distinct keys for lookup in table3's index, which lookup will satisfy table3 .`col_varchar_key` = table2 .`col_varchar_nokey` - the two cached records of table2 have the same distinct key prefix 'k' - the index condition pushed to table3 is table3 .`col_int_key` >= table2 .`pk` - a lookup for 'k' in table3 is done using the available index, the found index tuples also provide the value of table3.col_int_key: we have (9,'k') and (5,'k') - both index tuples are not eliminated by bka_unique_skip_index_tuple() as each of them satisfies the pushed condition when paired with at least one table2's cached record: (9,'k') paired with (4,9,'k','k'), (5,'k') paired with (4,9,'k','k') - the two table3 records corresponding to the accepted index tuples are retrieved - and, in join_matching_records(), joined with the two table2 records having distinct key 'k'; the mistake is here; we had determined in bka_unique_skip_index_tuple() than the two table3 records would join with at least one cached table2 record; it doesn't mean that they join with all cached table2 records. A re-check of the index condition is needed. We do it in check_match(). @ sql/sql_select.h subclasses of JOIN_CACHE can now specialize check_match(): BKA_UNIQUE does so.
[1 Jul 2010 8:54]
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/112650 3204 Guilhem Bichot 2010-07-01 Fix for BUG#54359 "Extra rows with join_cache_level=7,8 and two joins --and multi-column index"; pushed index condition wasn't correctly checked in BKA_UNIQUE. @ mysql-test/include/join_cache.inc test for bug @ mysql-test/r/join_cache_jcl1.result correct result @ mysql-test/r/join_cache_jcl7.result without the code fix, result of SELECT would be 5,5,9,9 instead of 9,9 @ mysql-test/r/join_cache_jcl8.result without the code fix, result of SELECT would be 5,5,9,9 instead of 9,9 @ sql/sql_join_cache.cc See added doxygen comments. Without this fix, in the testcase (see join_cache.inc) we had this: - this is BKA_UNIQUE, which prepares distinct keys for lookup in table3's index, which lookup will satisfy table3 .`col_varchar_key` = table2 .`col_varchar_nokey` - the two cached records of table2 have the same distinct key prefix 'k' - the index condition pushed to table3 is table3 .`col_int_key` >= table2 .`pk` - a lookup for 'k' in table3 is done using the available index, the found index tuples also provide the value of table3.col_int_key: we have (9,'k') and (5,'k') - both index tuples are not eliminated by bka_unique_skip_index_tuple() as each of them satisfies the pushed condition when paired with at least one table2's cached record: (9,'k') paired with (4,9,'k','k'), (5,'k') paired with (4,9,'k','k') - the two table3 records corresponding to the accepted index tuples are retrieved - and, in join_matching_records(), joined with the two table2 records having distinct key 'k'; the mistake is here; we had determined in bka_unique_skip_index_tuple() than the two table3 records would join with at least one cached table2 record; it doesn't mean that they join with all cached table2 records. A re-check of the index condition is needed. We do it in check_match(). @ sql/sql_select.h subclasses of JOIN_CACHE can now specialize check_match(): BKA_UNIQUE does so.
[4 Jul 2010 15:47]
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/112832 3209 Guilhem Bichot 2010-07-04 Fix for BUG#54359 "Extra rows with join_cache_level=7,8 and two joins --and multi-column index"; pushed index condition wasn't correctly checked in BKA_UNIQUE. @ mysql-test/include/join_cache.inc test for bug @ mysql-test/r/join_cache_jcl1.result correct result @ mysql-test/r/join_cache_jcl7.result without the code fix, result of SELECT would be 5,5,9,9 instead of 9,9 @ mysql-test/r/join_cache_jcl8.result without the code fix, result of SELECT would be 5,5,9,9 instead of 9,9 @ sql/sql_join_cache.cc See added doxygen comments. Without this fix, in the testcase (see join_cache.inc) we had this: - this is BKA_UNIQUE, which prepares distinct keys for lookup in table3's index, which lookup will satisfy table3 .`col_varchar_key` = table2 .`col_varchar_nokey` - the two cached records of table2 have the same distinct key prefix 'k' - the index condition pushed to table3 is table3 .`col_int_key` >= table2 .`pk` - a lookup for 'k' in table3 is done using the available index, the found index tuples also provide the value of table3.col_int_key: we have (9,'k') and (5,'k') - both index tuples are not eliminated by bka_unique_skip_index_tuple() as each of them satisfies the pushed condition when paired with at least one table2's cached record: (9,'k') paired with (4,9,'k','k'), (5,'k') paired with (4,9,'k','k') - the two table3 records corresponding to the accepted index tuples are retrieved - and, in join_matching_records(), joined with the two table2 records having distinct key 'k'; the mistake is here; we had determined in bka_unique_skip_index_tuple() than the two table3 records would join with at least one cached table2 record; it doesn't mean that they join with all cached table2 records. A re-check of the index condition is needed. We do it in check_match(). @ sql/sql_select.h subclasses of JOIN_CACHE can now specialize check_match(): BKA_UNIQUE does so.
[4 Jul 2010 15:49]
Guilhem Bichot
queued to next-mr-opt-backporting
[16 Aug 2010 6:31]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:21]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[23 Nov 2010 3:32]
Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.