From d0547098271fc1b52f92dbc34b6dfca405e5882a Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sun, 25 Mar 2012 18:31:35 +0400 Subject: BUG#962667: Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() - The problem was that = we've picked a LooseScan that used full index scan (tab->type==JT_ALL) on certain index. = there was also a quick select (tab->quick!=NULL), that used other indexes. = some old code assumes that (tab->type==JT_ALL && tab->quick) -> means that the quick select should be used, which is not true. Fixed by discarding the quick select as soon as we know we're using LooseScan without using the quick select. --- mysql-test/r/subselect_sj2_jcl6.result | 37 ++++++++++++++++++++++++++++++++++ 1 file changed, 37 insertions(+) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index e69548dcf5f..8734e6f8423 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -917,6 +917,43 @@ a b c 3 1 1 4 1 1 DROP TABLE t1,t2; +# +# BUG#962667: Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() +# with index_merge+index_merge_sort_union+loosescan+semijoin +# +CREATE TABLE t1 ( +a INT, b VARCHAR(1), c INT, +KEY(a), KEY(b) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5), +(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9), +(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), +(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); +CREATE TABLE t2 ( +pk INT, d VARCHAR(1), e INT, +PRIMARY KEY(pk), KEY(d,e) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES +(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), +(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), +(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), +(15,'g',6),(16,'x',7),(17,'f',8); +explain +SELECT * FROM t1 WHERE b IN ( +SELECT d FROM t2, t1 +WHERE a = d AND ( pk < 2 OR d = 'z' ) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index PRIMARY,d d 9 NULL 17 Using where; Using index; LooseScan +1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index; FirstMatch(t2) +1 PRIMARY t1 ref b b 4 test.t2.d 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +SELECT * FROM t1 WHERE b IN ( +SELECT d FROM t2, t1 +WHERE a = d AND ( pk < 2 OR d = 'z' ) +); +a b c +DROP TABLE t1, t2; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; # -- cgit v1.2.1 From e2554b50cd93c51b7768aa99ddb46d956888e4a5 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 26 Mar 2012 13:47:00 +0400 Subject: BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view - Fix equality propagation to work with SJM nests and OR clauses (full descirption of problem and solution in the comment in the patch) (The second commit with post-review fixes) --- mysql-test/r/subselect_sj2_jcl6.result | 103 +++++++++++++++++++++++++++++++++ 1 file changed, 103 insertions(+) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 8734e6f8423..cdbb6288971 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -954,6 +954,109 @@ WHERE a = d AND ( pk < 2 OR d = 'z' ) ); a b c DROP TABLE t1, t2; +# +# BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view +# +CREATE TABLE t1 ( +a VARCHAR(1), +b VARCHAR(1) NOT NULL, +KEY(a) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +('j','j'),('v','v'),('c','c'),('m','m'),('d','d'), +('y','y'),('t','t'),('d','d'),('s','s'),('r','r'), +('m','m'),('b','b'),('x','x'),('g','g'),('p','p'), +('q','q'),('w','w'),('d','d'),('e','e'); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +# This query returned 6 rows instead of 19 +SELECT * FROM v1 +WHERE ( a, a ) IN ( +SELECT alias2.b, alias2.a +FROM t1 AS alias1, t1 AS alias2 +WHERE alias2.b = alias1.a +AND ( alias1.b >= alias1.a OR alias2.b = 'z' ) +); +a b +j j +v v +c c +m m +m m +d d +d d +d d +y y +t t +s s +r r +b b +x x +g g +p p +q q +w w +e e +# Another testcase, without the VIEW: +CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB; +INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t2 SELECT * FROM t1; +EXPLAIN +SELECT * FROM t2 +WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 +WHERE +alias2.b = alias1.a AND +(alias1.b >= alias1.a OR alias2.b = 'z')); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL distinct_key NULL NULL NULL 19 +1 PRIMARY t2 ref a a 4 test.alias1.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +2 MATERIALIZED alias1 ALL a NULL NULL NULL 19 Using where +2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +SELECT * FROM t2 +WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 +WHERE +alias2.b = alias1.a AND +(alias1.b >= alias1.a OR alias2.b = 'z')); +a b +j j +v v +c c +m m +d d +t t +d d +s s +r r +m m +b b +g g +p p +q q +w w +d d +e e +j j +v v +c c +m m +d d +t t +d d +s s +r r +m m +b b +g g +p p +q q +d d +e e +y y +x x +y y +x x +w w +DROP VIEW v1; +DROP TABLE t1, t2; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; # -- cgit v1.2.1 From 4e96c579aef84d3b417045403425606ad9f061fd Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Mon, 26 Mar 2012 15:05:50 +0300 Subject: Sorted some test results that can be different on different machines mysql-test/t/subselect_sj2.test: Added --sorted_result --- mysql-test/r/subselect_sj2_jcl6.result | 74 +++++++++++++++++----------------- 1 file changed, 37 insertions(+), 37 deletions(-) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index cdbb6288971..4fc8b722018 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -977,25 +977,25 @@ WHERE alias2.b = alias1.a AND ( alias1.b >= alias1.a OR alias2.b = 'z' ) ); a b -j j -v v +b b c c -m m -m m d d d d d d -y y -t t -s s -r r -b b -x x +e e g g +j j +m m +m m p p q q +r r +s s +t t +v v w w -e e +x x +y y # Another testcase, without the VIEW: CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB; INSERT INTO t2 SELECT * FROM t1; @@ -1017,44 +1017,44 @@ WHERE alias2.b = alias1.a AND (alias1.b >= alias1.a OR alias2.b = 'z')); a b -j j -v v +b b +b b +c c c c -m m d d -t t d d -s s -r r -m m -b b -g g -p p -q q -w w +d d +d d +d d d d e e +e e +g g +g g +j j j j -v v -c c m m -d d -t t -d d -s s -r r m m -b b -g g +m m +m m +p p p p q q -d d -e e -y y +q q +r r +r r +s s +s s +t t +t t +v v +v v +w w +w w x x -y y x x -w w +y y +y y DROP VIEW v1; DROP TABLE t1, t2; # This must be the last in the file: -- cgit v1.2.1 From f2947f97a64e6af7e849caef415161b91803e241 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 26 Mar 2012 21:34:24 +0400 Subject: BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery - The problem was with execution strategy for cases where FirstMatch's inner tables were interleaved with outer-uncorrelated tables. - I was unable to find any cases where such join orders would be practically useful, so fixed it by disabling them. --- mysql-test/r/subselect_sj2_jcl6.result | 54 ++++++++++++++++++++++++++++++---- 1 file changed, 49 insertions(+), 5 deletions(-) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index cdbb6288971..22b51800915 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -1057,6 +1057,50 @@ x x w w DROP VIEW v1; DROP TABLE t1, t2; +# +# BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery +# +set @tmp_951283=@@optimizer_prune_level; +SET optimizer_prune_level=0; +CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(10),(11),(12),(13),(14),(15),(16), +(17),(18),(19),(20),(21),(22),(23); +CREATE TABLE t2 ( +b INT PRIMARY KEY, +c VARCHAR(1), +d VARCHAR(1), +KEY(c) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES +(1,'j','j'),(2,'v','v'),(3,'c','c'),(4,'m','m'), +(5,'d','d'),(6,'d','d'),(7,'y','y'),(8,'t','t'), +(9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), +(13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), +(17,'q','q'),(18,'w','w'),(19,'d','d'); +EXPLAIN +SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 +WHERE alias3.d IN ( +SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 +WHERE alias5.b = alias4.b +AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 Using where; FirstMatch(alias3) +1 PRIMARY alias2 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join) +1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 Using join buffer (incremental, BNL join) +SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 +WHERE alias3.d IN ( +SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 +WHERE alias5.b = alias4.b +AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) +); +COUNT(*) +3724 +set optimizer_prune_level=@tmp_951283; +DROP TABLE t1,t2; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; # @@ -1078,9 +1122,9 @@ SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b WHERE c IN (SELECT t4.b FROM t4 JOIN t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 FirstMatch(t3) +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t3); Using join buffer (incremental, BNL join) 1 PRIMARY t1 ref b b 4 test.t3.b 1 Using index -1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b WHERE c IN (SELECT t4.b FROM t4 JOIN t2); b c @@ -1106,9 +1150,9 @@ EXPLAIN SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t4 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.b 1 Using where; FirstMatch(t1) +1 PRIMARY t4 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.b 1 Using where; FirstMatch(t2) +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); pk a b 1 6 8 -- cgit v1.2.1 From 84a53543c5cca294e771cd7629e8beb8327320f5 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 27 Mar 2012 14:43:26 +0400 Subject: BUG#965872: Server crashes in embedding_sjm on a simple 1-table select with AND and OR - This is a regession introduced by fix for BUG#951937 - The problem was that there were scenarios where check_simple_equality() would create an Item_equal object but would not call item_equal->set_context_field() on it. - The fix was to add the missing calls. --- mysql-test/r/subselect_sj2_jcl6.result | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 1d0cf23f510..c2cfbb44d86 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -1057,6 +1057,15 @@ y y y y DROP VIEW v1; DROP TABLE t1, t2; +# +# BUG#965872: Server crashes in embedding_sjm on a simple 1-table select with AND and OR +# (this is a regression caused by the fix for BUG#951937) +CREATE TABLE t1 ( a INT, b INT, c INT, d INT ); +INSERT INTO t1 VALUES (4,2,8,9),(4,2,7,8); +SELECT * FROM t1 +WHERE a = d AND ( b = 50 AND b = d OR a = c ); +a b c d +DROP TABLE t1; # # BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery # -- cgit v1.2.1