diff options
| -rw-r--r-- | mysql-test/r/join_cache.result | 97 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj.result | 54 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 54 | ||||
| -rw-r--r-- | mysql-test/t/join_cache.test | 82 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj.test | 51 |
5 files changed, 338 insertions, 0 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 362158cfdbe..db869ba434a 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5389,4 +5389,101 @@ x 5 5 4 SET join_cache_level = DEFAULT; SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3,t4; +# +# Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8) +# +create table t1 (uid int, fid int, index(uid)); +insert into t1 values +(1,1), (1,2), (1,3), (1,4), +(2,5), (2,6), (2,7), (2,8), +(3,1), (3,2), (3,9); +create table t2 (uid int primary key, name varchar(128), index(name)); +insert into t2 values +(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), +(6, "F"), (7, "G"), (8, "H"), (9, "I"); +create table t3 (uid int, fid int, index(uid)); +insert into t3 values +(1,1), (1,2), (1,3),(1,4), +(2,5), (2,6), (2,7), (2,8), +(3,1), (3,2), (3,9); +set @tmp_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='semijoin=on'; +set optimizer_switch='materialization=off'; +set optimizer_switch='loosescan=off,firstmatch=off'; +set optimizer_switch='mrr_sort_keys=off'; +set join_cache_level=7; +create table t4 (uid int primary key, name varchar(128), index(name)); +insert into t4 values +(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), +(6, "F"), (7, "G"), (8, "H"), (9, "I"); +explain select name from t2, t1 +where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) +and t2.uid=t1.fid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index +1 PRIMARY t1 ALL uid NULL NULL NULL 11 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1 Using join buffer (flat, BKAH join); Rowid-ordered scan +select name from t2, t1 +where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) +and t2.uid=t1.fid; +name +A +A +B +B +C +D +E +F +G +H +I +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; +drop table t1,t2,t3,t4; +# +# Bug#50358 - semijoin execution of subquery with outerjoin +# emplying join buffer +# +CREATE TABLE t1 (i int); +CREATE TABLE t2 (i int); +CREATE TABLE t3 (i int); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (6); +INSERT INTO t3 VALUES (1), (2); +set @tmp_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='semijoin=on'; +set optimizer_switch='materialization=on'; +set join_cache_level=0; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using where +SELECT * FROM t1 WHERE t1.i IN +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +i +1 +2 +set join_cache_level=2; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE t1.i IN +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +i +1 +2 +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; +DROP TABLE t1,t2,t3; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index f5c3f84bcd7..33468198422 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -1231,6 +1231,60 @@ IN (SELECT t3.pk, t3.pk FROM t2 LEFT JOIN t3 ON t3.varchar_key) AND pk = 9; datetime_key DROP TABLE t1, t2, t3; +# +# BUG#53060: LooseScan semijoin strategy does not return all rows +# +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=on,materialization=off'; +set optimizer_switch='firstmatch=off,loosescan=on'; +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE TABLE t2 (i INTEGER, j INTEGER, KEY k(i, j)); +INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); +EXPLAIN +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; LooseScan +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); +i +1 +2 +DROP TABLE t1, t2; +set optimizer_switch=@save_optimizer_switch; +# +# BUG#49453: re-execution of prepared statement with view +# and semijoin crashes +# +CREATE TABLE t1 (city VARCHAR(50), country_id INT); +CREATE TABLE t2 (country_id INT, country VARCHAR(50)); +INSERT INTO t1 VALUES +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ; +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa'); +CREATE VIEW v1 AS +SELECT country_id as vf_country_id +FROM t2 +WHERE LEFT(country,1) = "A"; +PREPARE stmt FROM " +SELECT city, country_id +FROM t1 +WHERE country_id IN (SELECT vf_country_id FROM v1); +"; + +EXECUTE stmt; +city country_id +Batna 2 +Bchar 2 +Skikda 2 +Algeria 2 +EXECUTE stmt; +city country_id +Batna 2 +Bchar 2 +Skikda 2 +Algeria 2 +DROP TABLE t1,t2; +DROP VIEW v1; # # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3 # diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index faa4140d375..c344271ceae 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -1244,6 +1244,60 @@ IN (SELECT t3.pk, t3.pk FROM t2 LEFT JOIN t3 ON t3.varchar_key) AND pk = 9; datetime_key DROP TABLE t1, t2, t3; +# +# BUG#53060: LooseScan semijoin strategy does not return all rows +# +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=on,materialization=off'; +set optimizer_switch='firstmatch=off,loosescan=on'; +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE TABLE t2 (i INTEGER, j INTEGER, KEY k(i, j)); +INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); +EXPLAIN +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; LooseScan +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); +i +1 +2 +DROP TABLE t1, t2; +set optimizer_switch=@save_optimizer_switch; +# +# BUG#49453: re-execution of prepared statement with view +# and semijoin crashes +# +CREATE TABLE t1 (city VARCHAR(50), country_id INT); +CREATE TABLE t2 (country_id INT, country VARCHAR(50)); +INSERT INTO t1 VALUES +('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ; +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa'); +CREATE VIEW v1 AS +SELECT country_id as vf_country_id +FROM t2 +WHERE LEFT(country,1) = "A"; +PREPARE stmt FROM " +SELECT city, country_id +FROM t1 +WHERE country_id IN (SELECT vf_country_id FROM v1); +"; + +EXECUTE stmt; +city country_id +Batna 2 +Bchar 2 +Skikda 2 +Algeria 2 +EXECUTE stmt; +city country_id +Batna 2 +Bchar 2 +Skikda 2 +Algeria 2 +DROP TABLE t1,t2; +DROP VIEW v1; # # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3 # diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index e39dcb72916..cc1941e16b9 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3423,5 +3423,87 @@ SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8) +--echo # + +create table t1 (uid int, fid int, index(uid)); +insert into t1 values + (1,1), (1,2), (1,3), (1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +create table t2 (uid int primary key, name varchar(128), index(name)); +insert into t2 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +create table t3 (uid int, fid int, index(uid)); +insert into t3 values + (1,1), (1,2), (1,3),(1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +set @tmp_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='semijoin=on'; +set optimizer_switch='materialization=off'; +set optimizer_switch='loosescan=off,firstmatch=off'; +set optimizer_switch='mrr_sort_keys=off'; +set join_cache_level=7; + +create table t4 (uid int primary key, name varchar(128), index(name)); +insert into t4 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +explain select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +--sorted_result +select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; + +drop table t1,t2,t3,t4; + +--echo # +--echo # Bug#50358 - semijoin execution of subquery with outerjoin +--echo # emplying join buffer +--echo # + +CREATE TABLE t1 (i int); +CREATE TABLE t2 (i int); +CREATE TABLE t3 (i int); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (6); +INSERT INTO t3 VALUES (1), (2); + +set @tmp_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='semijoin=on'; +set optimizer_switch='materialization=on'; + +set join_cache_level=0; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); + +set join_cache_level=2; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); + +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index f34cf5ba338..64e7bc3236e 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1136,6 +1136,57 @@ WHERE (int_nokey, pk) DROP TABLE t1, t2, t3; +--echo # +--echo # BUG#53060: LooseScan semijoin strategy does not return all rows +--echo # + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=on,materialization=off'; +set optimizer_switch='firstmatch=off,loosescan=on'; + +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE TABLE t2 (i INTEGER, j INTEGER, KEY k(i, j)); +INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); + +EXPLAIN +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); + +DROP TABLE t1, t2; + +set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # BUG#49453: re-execution of prepared statement with view +--echo # and semijoin crashes +--echo # + +CREATE TABLE t1 (city VARCHAR(50), country_id INT); +CREATE TABLE t2 (country_id INT, country VARCHAR(50)); + +INSERT INTO t1 VALUES + ('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ; +INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa'); + +CREATE VIEW v1 AS + SELECT country_id as vf_country_id + FROM t2 + WHERE LEFT(country,1) = "A"; + +PREPARE stmt FROM " +SELECT city, country_id +FROM t1 +WHERE country_id IN (SELECT vf_country_id FROM v1); +"; + +--echo +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1,t2; +DROP VIEW v1; + --echo # --echo # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3 --echo # |