diff options
author | Igor Babaev <igor@askmonty.org> | 2012-02-29 23:28:16 -0800 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2012-02-29 23:28:16 -0800 |
commit | 000deedf3b708681951af6a0629af89c1f5ee85a (patch) | |
tree | fa52373099e3b832ba47081b19a6b4bfeb77c89b /mysql-test | |
parent | 54f9fe6c8b0472f0e12602eabf94ff4d73161806 (diff) | |
download | mariadb-git-000deedf3b708681951af6a0629af89c1f5ee85a.tar.gz |
Fixed LP bug #943543.
This bug appeared after the patch for bug 939009 that in the
function merge_key_fields forgot to reset a proper value for
the val field in the result of the merge operation of the key
field created for a regular key access and the key field
created to look for a NULL key.
Adjusted the results of the test case for bug 939009 that
actually were incorrect.
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/r/join_outer.result | 84 | ||||
-rw-r--r-- | mysql-test/r/join_outer_jcl6.result | 84 | ||||
-rw-r--r-- | mysql-test/r/subselect_mat.result | 5 | ||||
-rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 5 | ||||
-rw-r--r-- | mysql-test/t/join_outer.test | 61 |
5 files changed, 235 insertions, 4 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 60defea291b..5624d9467af 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1656,4 +1656,88 @@ b b a b DEALLOCATE PREPARE stmt; SET SESSION join_cache_level=default; DROP TABLE t1,t2,t3; +# +# LP bug #943543: LEFT JOIN converted to JOIN with +# ORed IS NULL(primary key) in WHERE clause +# +CREATE TABLE t1 ( +a int, b int NOT NULL, pk int NOT NULL, +PRIMARY KEY (pk), INDEX idx(b) +); +INSERT INTO t1 VALUES +(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4), +(1,9,6), (8,5,7), (NULL,8,8), (8,1,5); +CREATE TABLE t2 (pk int PRIMARY KEY); +INSERT INTO t2 VALUES (3), (8), (5); +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index +Warnings: +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5 +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +pk +5 +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY,idx PRIMARY 4 const 1 100.00 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index +Warnings: +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5 +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +pk +5 +DROP TABLE t2; +CREATE TABLE t2 (c int, d int, KEY (c)); +INSERT INTO t2 VALUES +(3,30), (8,88), (5,50), (8,81), +(4,40), (9,90), (7,70), (9,90), +(13,130), (18,188), (15,150), (18,181), +(14,140), (19,190), (17,170), (19,190); +INSERT INTO t1 VALUES (8,5,9); +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref idx idx 4 const 2 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b` +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +b c d +5 8 88 +5 8 81 +5 8 88 +5 8 81 +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref PRIMARY,idx idx 4 const 2 100.00 Using where; Using filesort +1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b` +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +b c d +5 8 88 +5 8 81 +5 8 88 +5 8 81 +DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 767d0544f1b..d80b838e4e7 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1667,6 +1667,90 @@ b b a b DEALLOCATE PREPARE stmt; SET SESSION join_cache_level=default; DROP TABLE t1,t2,t3; +# +# LP bug #943543: LEFT JOIN converted to JOIN with +# ORed IS NULL(primary key) in WHERE clause +# +CREATE TABLE t1 ( +a int, b int NOT NULL, pk int NOT NULL, +PRIMARY KEY (pk), INDEX idx(b) +); +INSERT INTO t1 VALUES +(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4), +(1,9,6), (8,5,7), (NULL,8,8), (8,1,5); +CREATE TABLE t2 (pk int PRIMARY KEY); +INSERT INTO t2 VALUES (3), (8), (5); +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index +Warnings: +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5 +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +pk +5 +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY,idx PRIMARY 4 const 1 100.00 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index +Warnings: +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5 +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a +WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +pk +5 +DROP TABLE t2; +CREATE TABLE t2 (c int, d int, KEY (c)); +INSERT INTO t2 VALUES +(3,30), (8,88), (5,50), (8,81), +(4,40), (9,90), (7,70), (9,90), +(13,130), (18,188), (15,150), (18,181), +(14,140), (19,190), (17,170), (19,190); +INSERT INTO t1 VALUES (8,5,9); +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref idx idx 4 const 2 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b` +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +b c d +5 8 88 +5 8 81 +5 8 88 +5 8 81 +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref PRIMARY,idx idx 4 const 2 100.00 Using where; Using filesort +1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b` +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a +WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +b c d +5 8 88 +5 8 81 +5 8 88 +5 8 81 +DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index b7cc7e8a3ed..97463447e1d 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1892,7 +1892,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 Using where +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: @@ -1900,11 +1900,12 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materializ SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b +7 7 EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 Using where +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t1 diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 9a238eaaccd..db5a27a5d84 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -1930,7 +1930,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 Using where +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: @@ -1938,11 +1938,12 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materializ SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b +7 7 EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 Using where +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t1 diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 04816402205..3fae21ac738 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1221,4 +1221,65 @@ SET SESSION join_cache_level=default; DROP TABLE t1,t2,t3; +--echo # +--echo # LP bug #943543: LEFT JOIN converted to JOIN with +--echo # ORed IS NULL(primary key) in WHERE clause +--echo # + +CREATE TABLE t1 ( + a int, b int NOT NULL, pk int NOT NULL, + PRIMARY KEY (pk), INDEX idx(b) +); +INSERT INTO t1 VALUES + (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4), + (1,9,6), (8,5,7), (NULL,8,8), (8,1,5); + +CREATE TABLE t2 (pk int PRIMARY KEY); +INSERT INTO t2 VALUES (3), (8), (5); + +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; + +EXPLAIN EXTENDED +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; +SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a + WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 +ORDER BY t1.pk; + +DROP TABLE t2; + +CREATE TABLE t2 (c int, d int, KEY (c)); +INSERT INTO t2 VALUES + (3,30), (8,88), (5,50), (8,81), + (4,40), (9,90), (7,70), (9,90), + (13,130), (18,188), (15,150), (18,181), + (14,140), (19,190), (17,170), (19,190); + +INSERT INTO t1 VALUES (8,5,9); + +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; + +EXPLAIN EXTENDED +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; +SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a + WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 +ORDER BY t1.b; + +DROP TABLE t1,t2; + SET optimizer_switch=@save_optimizer_switch; |