diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/type_varchar.result | 229 |
1 files changed, 229 insertions, 0 deletions
diff --git a/mysql-test/main/type_varchar.result b/mysql-test/main/type_varchar.result index 39225c4c..c7bf59ba 100644 --- a/mysql-test/main/type_varchar.result +++ b/mysql-test/main/type_varchar.result @@ -952,3 +952,232 @@ Note 1105 Cannot use key parts with `test`.`t1`.`indexed_col` in the rewritten c DROP TABLE t2; DROP TABLE t1; SET note_verbosity=DEFAULT; +# +# MDEV-32957 Unusable key notes report wrong predicates for > and >= +# +SET note_verbosity=unusable_keys; +CREATE TABLE t1 (a INT, i CHAR(32), KEY(i)); +FOR i IN 1..31 +DO +INSERT INTO t1 VALUES (i, 10+i); +END FOR; +$$ +EXPLAIN SELECT * FROM t1 WHERE i>30 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL i NULL NULL NULL 31 Using where; Using filesort +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "30" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "30" of type `int` +EXPLAIN SELECT * FROM t1 WHERE i>=30 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL i NULL NULL NULL 31 Using where; Using filesort +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "30" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "30" of type `int` +DROP TABLE t1; +SET note_verbosity=DEFAULT; +# +# MDEV-32958 Unusable key notes do not get reported for some operations +# +SET note_verbosity=unusable_keys; +CREATE TABLE t1 (c1 varchar(10), KEY(c1)) CHARACTER SET latin1; +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('b'); +INSERT INTO t1 VALUES ('c'); +INSERT INTO t1 VALUES ('d'); +INSERT INTO t1 VALUES ('e'); +INSERT INTO t1 VALUES ('f'); +INSERT INTO t1 VALUES ('g'); +INSERT INTO t1 VALUES ('h'); +INSERT INTO t1 VALUES ('i'); +INSERT INTO t1 VALUES ('j'); +EXPLAIN SELECT * FROM t1 WHERE c1=10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index c1 c1 13 NULL 10 Using where; Using index +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int` +SELECT * FROM t1 WHERE c1=10; +c1 +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int` +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'e' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'g' +Warning 1292 Truncated incorrect DECIMAL value: 'h' +Warning 1292 Truncated incorrect DECIMAL value: 'i' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +EXPLAIN SELECT * FROM t1 WHERE c1<10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index c1 c1 13 NULL 10 Using where; Using index +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` < "10" of type `int` +SELECT * FROM t1 WHERE c1<10; +c1 +a +b +c +d +e +f +g +h +i +j +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` < "10" of type `int` +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'e' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'g' +Warning 1292 Truncated incorrect DECIMAL value: 'h' +Warning 1292 Truncated incorrect DECIMAL value: 'i' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index c1 c1 13 NULL 10 Using where; Using index +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` >= "10" of type `int` +SELECT * FROM t1 WHERE c1 BETWEEN 10 AND 11; +c1 +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` >= "10" of type `int` +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'e' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'g' +Warning 1292 Truncated incorrect DECIMAL value: 'h' +Warning 1292 Truncated incorrect DECIMAL value: 'i' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +EXPLAIN SELECT * FROM t1 WHERE c1 BETWEEN 10 AND '11'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index c1 c1 13 NULL 10 Using where; Using index +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` >= "10" of type `int` +SELECT * FROM t1 WHERE c1 BETWEEN 10 AND '11'; +c1 +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` >= "10" of type `int` +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'b' +Warning 1292 Truncated incorrect DOUBLE value: 'c' +Warning 1292 Truncated incorrect DOUBLE value: 'd' +Warning 1292 Truncated incorrect DOUBLE value: 'e' +Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DOUBLE value: 'g' +Warning 1292 Truncated incorrect DOUBLE value: 'h' +Warning 1292 Truncated incorrect DOUBLE value: 'i' +Warning 1292 Truncated incorrect DOUBLE value: 'j' +EXPLAIN SELECT * FROM t1 WHERE c1 IN (10,20); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index c1 c1 13 NULL 10 Using where; Using index +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int` +SELECT * FROM t1 WHERE c1 IN (10,20); +c1 +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of type `varchar` = "10" of type `int` +Warning 1292 Truncated incorrect DECIMAL value: 'a' +Warning 1292 Truncated incorrect DECIMAL value: 'b' +Warning 1292 Truncated incorrect DECIMAL value: 'c' +Warning 1292 Truncated incorrect DECIMAL value: 'd' +Warning 1292 Truncated incorrect DECIMAL value: 'e' +Warning 1292 Truncated incorrect DECIMAL value: 'f' +Warning 1292 Truncated incorrect DECIMAL value: 'g' +Warning 1292 Truncated incorrect DECIMAL value: 'h' +Warning 1292 Truncated incorrect DECIMAL value: 'i' +Warning 1292 Truncated incorrect DECIMAL value: 'j' +EXPLAIN SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index c1 c1 13 NULL 10 Using where; Using index +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of collation `latin1_swedish_ci` = "_latin1'a' collate latin1_german2_ci" of collation `latin1_german2_ci` +SELECT * FROM t1 WHERE c1 IN (_latin1'a' COLLATE latin1_german2_ci,'b'); +c1 +a +b +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of collation `latin1_swedish_ci` = "_latin1'a' collate latin1_german2_ci" of collation `latin1_german2_ci` +EXPLAIN SELECT * FROM t1 WHERE c1 IN ('a',_latin1'b' COLLATE latin1_german2_ci); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index c1 c1 13 NULL 10 Using where; Using index +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of collation `latin1_swedish_ci` = "'a'" of collation `latin1_german2_ci` +SELECT * FROM t1 WHERE c1 IN ('a',_latin1'b' COLLATE latin1_german2_ci); +c1 +a +b +Warnings: +Note 1105 Cannot use key `c1` part[0] for lookup: `test`.`t1`.`c1` of collation `latin1_swedish_ci` = "'a'" of collation `latin1_german2_ci` +DROP TABLE t1; +CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1))); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +EXPLAIN SELECT * FROM t1 WHERE i >= 10 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL i NULL NULL NULL 26 Using where; Using filesort +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +SELECT * FROM t1 WHERE i >= 10 ORDER BY i LIMIT 5; +a i +NULL 10 +NULL 11 +NULL 12 +NULL 13 +NULL 14 +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` +EXPLAIN UPDATE t1 SET a = 1 WHERE i = 10 ORDER BY a, i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where; Using filesort +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` = "10" of type `int` +EXPLAIN UPDATE t1 SET a = 1 WHERE i < 10 ORDER BY a, i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where; Using filesort +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "10" of type `int` +EXPLAIN DELETE FROM t1 WHERE i = 10 ORDER BY a, i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where; Using filesort +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` = "10" of type `int` +EXPLAIN DELETE FROM t1 WHERE i < 10 ORDER BY a, i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where; Using filesort +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "10" of type `int` +EXPLAIN UPDATE t1 SET a = 1 WHERE i = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` = "10" of type `int` +EXPLAIN UPDATE t1 SET a = 1 WHERE i < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "10" of type `int` +EXPLAIN DELETE FROM t1 WHERE i = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` = "10" of type `int` +EXPLAIN DELETE FROM t1 WHERE i < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where +Warnings: +Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "10" of type `int` +DROP TABLE t1; +SET note_verbosity=DEFAULT; |