diff options
Diffstat (limited to 'mysql-test/main/func_in.result')
-rw-r--r-- | mysql-test/main/func_in.result | 228 |
1 files changed, 226 insertions, 2 deletions
diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result index dd7125c393a..b18aa26777e 100644 --- a/mysql-test/main/func_in.result +++ b/mysql-test/main/func_in.result @@ -1,4 +1,3 @@ -drop table if exists t1, t2; select 1 in (1,2,3); 1 in (1,2,3) 1 @@ -553,7 +552,7 @@ Warning 1292 Truncated incorrect DECIMAL value: 'a' Warning 1292 Truncated incorrect DECIMAL value: 'b' explain select f2 from t2 where f2 in ('a','b'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range t2f2 t2f2 5 NULL 1 Using where; Using index +1 SIMPLE t2 ref t2f2 t2f2 5 const 1 Using index Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'a' Warning 1292 Truncated incorrect DECIMAL value: 'b' @@ -944,3 +943,228 @@ Warning 1292 Truncated incorrect DECIMAL value: '0x' # # End of 10.4 tests # +# +# MDEV-29662 same values in `IN` set vs equal comparison produces +# the different performance +# +CREATE TABLE t1 (a INT, PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1 WHERE a IN (1,1); +a +1 +# 'const' access since 'a IN (1,1)' is converted to equality 'a=1' +EXPLAIN SELECT * FROM t1 WHERE a IN (1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +EXPLAIN SELECT * FROM t1 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +SELECT * FROM t1 WHERE a IN (1,1,2); +a +1 +2 +# Conversion to equality is impossible due to different values +EXPLAIN SELECT * FROM t1 WHERE a IN (1,1,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +SELECT * FROM t1 WHERE a IN (1,NULL,1); +a +1 +# Conversion to equality is impossible due to NULL in the IN list +EXPLAIN SELECT * FROM t1 WHERE a IN (1,NULL,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index +SELECT * FROM t1 WHERE a NOT IN (2,2,2,2,2,2); +a +1 +3 +EXPLAIN SELECT * FROM t1 WHERE a NOT IN (2,2,2,2,2,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a != 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE a NOT IN (3,3,1,1); +a +2 +EXPLAIN SELECT * FROM t1 WHERE a NOT IN (3,3,1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE a NOT IN (1,2,NULL,NULL); +a +# No conversion is possible since elements are not constant +SELECT * FROM t1 WHERE a IN ((SELECT MAX(a) FROM t1), (SELECT MAX(a) FROM t1)); +a +3 +EXPLAIN SELECT * FROM t1 WHERE a IN +((SELECT MAX(a) FROM t1), (SELECT MAX(a) FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +# There must be no conversion here: +SELECT * FROM t1 WHERE a IN (3,2,3,3,1,2,3); +a +1 +2 +3 +# Prepared statement +PREPARE stmt FROM "SELECT * FROM t1 WHERE a IN (3,3,3)"; +EXECUTE stmt; +a +3 +EXECUTE stmt; +a +3 +DEALLOCATE PREPARE stmt; +# Conversion to equality since SELECT 2 is evaluated as const +SELECT * FROM t1 WHERE a IN ((SELECT 2), (SELECT 2)); +a +2 +EXPLAIN SELECT * FROM t1 WHERE a IN ((SELECT 2), (SELECT 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +CREATE TABLE t2 (a INT, b VARCHAR(10), PRIMARY KEY(a,b)); +INSERT INTO t2 VALUES (1,'abc'),(2,'def'),(3,'ghi'); +SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc')); +a b +1 abc +# 'const' access due to conversion to equality +EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc')); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 const PRIMARY PRIMARY 16 const,const 1 Using index +SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,'XYZ')); +a b +2 def +# No conversion due to different values +EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,'XYZ')); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY PRIMARY 16 NULL 2 Using where; Using index +SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,NULL)); +a b +2 def +# No conversion due to NULL +EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,NULL)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY PRIMARY 16 NULL 1 Using where; Using index +SELECT * FROM t2 WHERE (a,b) NOT IN ((2,'def'),(2,'def'),(2,NULL)); +a b +1 abc +3 ghi +SELECT * FROM t2 WHERE a IN (1,1,1,1); +a b +1 abc +EXPLAIN SELECT * FROM t2 WHERE a IN (1,1,1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index +EXPLAIN SELECT * FROM t2 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index +SELECT * FROM t2 WHERE b NOT IN ('abc','abc'); +a b +2 def +3 ghi +EXPLAIN SELECT * FROM t2 WHERE b NOT IN ('abc','abc'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL PRIMARY 16 NULL 3 Using where; Using index +EXPLAIN SELECT * FROM t2 WHERE b != 'abc'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL PRIMARY 16 NULL 3 Using where; Using index +# Prepared statements +PREPARE stmt FROM "EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc'))"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 const PRIMARY PRIMARY 16 const,const 1 Using index +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 const PRIMARY PRIMARY 16 const,const 1 Using index +DEALLOCATE PREPARE stmt; +CREATE TABLE t3(a INT, PRIMARY KEY(a)); +INSERT INTO t3 VALUES (1),(2),(3); +PREPARE stmt FROM "EXPLAIN SELECT * FROM t3 WHERE a IN (?,?,?)"; +EXECUTE stmt USING 1,1,1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 Using index +EXECUTE stmt USING 2,3,4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 3 Using where; Using index +DEALLOCATE PREPARE stmt; +DROP TABLE t1, t2, t3; +# Nested joins +CREATE TABLE t1 (a INT, b VARCHAR(10), PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1,'abc'),(2,'def'),(3,'ghi'); +CREATE TABLE t2 (a INT, b VARCHAR(20), PRIMARY KEY(a)); +INSERT INTO t2 (a) VALUES (2),(3); +CREATE TABLE t3 (a INT, PRIMARY KEY(a)); +INSERT INTO t3 VALUES (1),(2),(3),(4); +CREATE TABLE t4 (a INT); +INSERT INTO t4 VALUES (2),(3); +# Conversion to equalities +EXPLAIN SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a IN (2,2,2) +AND t3.a IN (1,1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 Using where; Using index +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 +# No conversion to equalities due to different values in IN() +EXPLAIN SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a IN (2,3) +AND t3.a IN (1,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) +# Conversion to equalities +EXPLAIN SELECT * FROM t1 LEFT JOIN ((t2, t3) LEFT JOIN t4 ON t2.a = t4.a) +ON t1.a = t2.a WHERE t1.a IN (2,2,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 index NULL PRIMARY 4 NULL 4 Using index +# No conversion to equalities due to different values in IN() +EXPLAIN SELECT * FROM t1 LEFT JOIN ((t2, t3) LEFT JOIN t4 ON t2.a = t4.a) +ON t1.a = t2.a WHERE t1.a IN (1,3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 index NULL PRIMARY 4 NULL 4 Using index +# View +CREATE VIEW v1 AS SELECT t1.*, t2.b AS t2_b FROM t1 LEFT JOIN t2 +ON t1.a = t2.a; +EXPLAIN SELECT * FROM v1 WHERE a IN (2,2,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 +EXPLAIN SELECT * FROM v1 WHERE a IN (1,2,3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +# Stored procedures +CREATE PROCEDURE p1(pa INT, pb INT) +EXPLAIN SELECT * FROM t1 WHERE a IN (pa, pb); +CALL p1(1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +CALL p1(2,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition +DROP TABLE t1, t2, t3, t4; +DROP VIEW v1; +DROP PROCEDURE p1; +# +# MDEV-29895 prepared view crash server (unit.conc_view) +# +create table t1 (username varchar(12) not null, id int(11) not null); +create view v1 as select username from t1 where id = 0; +prepare stmt from "select username from v1 where username in (?, ?)"; +execute stmt using "1", "1"; +username +deallocate prepare stmt; +drop view v1; +drop table t1; +# +# End of 10.6 tests +# |