diff options
Diffstat (limited to 'mysql-test/main/func_in.test')
-rw-r--r-- | mysql-test/main/func_in.test | 60 |
1 files changed, 60 insertions, 0 deletions
diff --git a/mysql-test/main/func_in.test b/mysql-test/main/func_in.test index e39932a2..b88b68f2 100644 --- a/mysql-test/main/func_in.test +++ b/mysql-test/main/func_in.test @@ -743,6 +743,66 @@ SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775808 ); drop table `a`; --echo # +--echo # MDEV-18319 BIGINT UNSIGNED Performance issue +--echo # + +CREATE OR REPLACE TABLE t1 ( + id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY +); +DELIMITER $$; +FOR i IN 0..255 +DO + INSERT INTO t1 VALUES (); +END FOR +$$ +DELIMITER ;$$ +SELECT MIN(id), MAX(id), COUNT(*) FROM t1; +EXPLAIN SELECT id FROM t1 WHERE id IN (1,2); +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775806, 9223372036854775807); +EXPLAIN SELECT id FROM t1 WHERE id IN (9223372036854775807, 9223372036854775808); +DROP TABLE t1; + + +--echo # +--echo # MDEV-18898 SELECT using wrong index when using operator IN with mixed types +--echo # + +CREATE TEMPORARY TABLE t1 ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +); +DELIMITER $$; +FOR i IN 1..255 +DO + INSERT INTO t1 VALUES (i, MD5(i)); +END FOR +$$ +DELIMITER ;$$ +--echo # +--echo # Constants alone +--echo # +ANALYZE SELECT id, name FROM t1 WHERE id = 1; +ANALYZE SELECT id, name FROM t1 WHERE id = '2'; +--echo # +--echo # Two constants using IN +--echo # +ANALYZE SELECT id, name FROM t1 WHERE id IN (1, 2); +ANALYZE SELECT id, name FROM t1 WHERE id IN ('1', 2) /* Used a wrong index */; +ANALYZE SELECT id, name FROM t1 WHERE id IN (1, '2') /* Used a wrong index */; +ANALYZE SELECT id, name FROM t1 WHERE id IN ('1', '2'); +--echo # +--echo # Two constants using OR +--echo # +ANALYZE SELECT id, name FROM t1 WHERE id = 1 OR id = 2; +ANALYZE SELECT id, name FROM t1 WHERE id = '1' OR id = '2'; +ANALYZE SELECT id, name FROM t1 WHERE id = 1 OR id = '2'; +ANALYZE SELECT id, name FROM t1 WHERE id = '1' OR id = 2; +DROP TABLE t1; + + +--echo # --echo # End of 10.5 tests --echo # |