diff options
Diffstat (limited to 'mysql-test/r/type_varchar.result')
| -rw-r--r-- | mysql-test/r/type_varchar.result | 60 |
1 files changed, 60 insertions, 0 deletions
diff --git a/mysql-test/r/type_varchar.result b/mysql-test/r/type_varchar.result index 965d113124b..936f48e6122 100644 --- a/mysql-test/r/type_varchar.result +++ b/mysql-test/r/type_varchar.result @@ -549,5 +549,65 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index PRIMARY PRIMARY 22 NULL 2 Using where; Using index DROP TABLE IF EXISTS t1,t2; # +# MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases +# +CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET latin1, PRIMARY KEY(c1)); +INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'); +SELECT * FROM t1 WHERE c1=BINARY 'a'; +c1 +a +EXPLAIN SELECT * FROM t1 WHERE c1=BINARY 'a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 22 const 1 Using index +SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin; +c1 +a +EXPLAIN SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 22 const 1 Using index +DROP TABLE t1; +CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1)); +INSERT INTO t2 VALUES ('a'),('b'); +SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; +c1 c1 +a a +EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 Using index +ALTER TABLE t1 MODIFY c1 VARBINARY(10); +SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; +c1 c1 +a a +EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 const PRIMARY PRIMARY 12 const 1 Using index +DROP TABLE t1, t2; +CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t1 VALUES ('a'),('c'); +CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1)); +INSERT INTO t2 VALUES ('a'),('b'); +SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); +c1 +a +c +# t2 should be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +ALTER TABLE t1 MODIFY c1 VARBINARY(10); +SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); +c1 +a +c +# t2 should be eliminated +EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +DROP TABLE t1,t2; +# # End of 10.0 tests # |