At file:///home/psergey/dev/maria-5.3-subqueries-r7-rel/ ------------------------------------------------------------ revno: 2777 revision-id: psergey@askmonty.org-20100315063254-z1ctm7srl0573s5c parent: psergey@askmonty.org-20100314182543-4t3ehit7df20adu8 committer: Sergey Petrunya branch nick: maria-5.3-subqueries-r7-rel timestamp: Mon 2010-03-15 09:32:54 +0300 message: Apply fix by Roy Lyseng: Bug#48623: Multiple subqueries are optimized incorrectly The function setup_semijoin_dups_elimination() has a major loop that goes through every table in the JOIN object. Usually, there is a normal "plus one" increment in the for loop that implements this, but each semijoin nest is treated as one entity and there is another increment that skips past the semijoin nest to the next table in the JOIN object. However, when combining these two increments, the next joined table is skipped, and if that happens to be the start of another semijoin nest, the correct processing for that nest will not be carried out. === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-03-14 18:25:43 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-03-15 06:32:54 +0000 @@ -1079,3 +1079,36 @@ partner_id partner2 drop table t1,t2,t3,t4; +# +# Bug#48623 Multiple subqueries are optimized incorrectly +# +CREATE TABLE t1(val VARCHAR(10)); +CREATE TABLE t2(val VARCHAR(10)); +CREATE TABLE t3(val VARCHAR(10)); +INSERT INTO t1 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO t2 VALUES('aaa'), ('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO t3 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +EXPLAIN +SELECT * +FROM t1 +WHERE t1.val IN (SELECT t2.val FROM t2 +WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%') +AND t1.val IN (SELECT t3.val FROM t3 +WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1) +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3) +SELECT * +FROM t1 +WHERE t1.val IN (SELECT t2.val FROM t2 +WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%') +AND t1.val IN (SELECT t3.val FROM t3 +WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); +val +aaa +eee +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +# End of Bug#48623 === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-03-14 18:25:43 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-15 06:32:54 +0000 @@ -1083,6 +1083,39 @@ partner_id partner2 drop table t1,t2,t3,t4; +# +# Bug#48623 Multiple subqueries are optimized incorrectly +# +CREATE TABLE t1(val VARCHAR(10)); +CREATE TABLE t2(val VARCHAR(10)); +CREATE TABLE t3(val VARCHAR(10)); +INSERT INTO t1 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO t2 VALUES('aaa'), ('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO t3 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +EXPLAIN +SELECT * +FROM t1 +WHERE t1.val IN (SELECT t2.val FROM t2 +WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%') +AND t1.val IN (SELECT t3.val FROM t3 +WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1); Using join buffer +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3); Using join buffer +SELECT * +FROM t1 +WHERE t1.val IN (SELECT t2.val FROM t2 +WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%') +AND t1.val IN (SELECT t3.val FROM t3 +WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); +val +aaa +eee +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +# End of Bug#48623 # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off # === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2010-03-14 18:25:43 +0000 +++ b/mysql-test/t/subselect_sj.test 2010-03-15 06:32:54 +0000 @@ -943,5 +943,35 @@ execute stmt; drop table t1,t2,t3,t4; - - +--echo # +--echo # Bug#48623 Multiple subqueries are optimized incorrectly +--echo # + +CREATE TABLE t1(val VARCHAR(10)); +CREATE TABLE t2(val VARCHAR(10)); +CREATE TABLE t3(val VARCHAR(10)); + +INSERT INTO t1 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO t2 VALUES('aaa'), ('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO t3 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); + +EXPLAIN +SELECT * +FROM t1 +WHERE t1.val IN (SELECT t2.val FROM t2 + WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%') + AND t1.val IN (SELECT t3.val FROM t3 + WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); + +SELECT * +FROM t1 +WHERE t1.val IN (SELECT t2.val FROM t2 + WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%') + AND t1.val IN (SELECT t3.val FROM t3 + WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; + +--echo # End of Bug#48623 === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-03-14 18:25:43 +0000 +++ b/sql/opt_subselect.cc 2010-03-15 06:32:54 +0000 @@ -3030,7 +3030,7 @@ THD *thd= join->thd; DBUG_ENTER("setup_semijoin_dups_elimination"); - for (i= join->const_tables ; i < join->tables ; i++) + for (i= join->const_tables ; i < join->tables; ) { JOIN_TAB *tab=join->join_tab + i; POSITION *pos= join->best_positions + i; @@ -3039,7 +3039,7 @@ case SJ_OPT_MATERIALIZE: case SJ_OPT_MATERIALIZE_SCAN: /* Do nothing */ - i += pos->n_sj_tables; + i+= pos->n_sj_tables; break; case SJ_OPT_LOOSE_SCAN: { @@ -3055,7 +3055,7 @@ tab->loosescan_key_len= keylen; if (pos->n_sj_tables > 1) tab[pos->n_sj_tables - 1].do_firstmatch= tab; - i += pos->n_sj_tables; + i+= pos->n_sj_tables; break; } case SJ_OPT_DUPS_WEEDOUT: @@ -3152,7 +3152,7 @@ join->join_tab[first_table].flush_weedout_table= sjtbl; join->join_tab[i + pos->n_sj_tables - 1].check_weed_out_table= sjtbl; - i += pos->n_sj_tables; + i+= pos->n_sj_tables; break; } case SJ_OPT_FIRST_MATCH: @@ -3174,10 +3174,11 @@ } } j[-1].do_firstmatch= jump_to; - i += pos->n_sj_tables; + i+= pos->n_sj_tables; break; } case SJ_OPT_NONE: + i++; break; } }