diff options
Diffstat (limited to 'mysql-test/r/select.result')
| -rw-r--r-- | mysql-test/r/select.result | 86 |
1 files changed, 86 insertions, 0 deletions
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 6704842320f..981c341bc0d 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3642,3 +3642,89 @@ INSERT into t1 values (1), (2), (3); SELECT * FROM t1 LIMIT 2, -1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1 DROP TABLE t1; +CREATE TABLE t1 ( +ID_with_null int NULL, +ID_better int NOT NULL, +INDEX idx1 (ID_with_null), +INDEX idx2 (ID_better) +); +INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; +COUNT(*) +128 +SELECT COUNT(*) FROM t1 WHERE ID_better=1; +COUNT(*) +2 +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); +EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP TABLE t1; +CREATE TABLE t1 ( +ID1_with_null int NULL, +ID2_with_null int NULL, +ID_better int NOT NULL, +INDEX idx1 (ID1_with_null, ID2_with_null), +INDEX idx2 (ID_better) +); +INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), +(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; +INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; +COUNT(*) +24 +SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; +COUNT(*) +24 +SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; +COUNT(*) +192 +SELECT COUNT(*) FROM t1 WHERE ID_better=1; +COUNT(*) +2 +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP INDEX idx1 ON t1; +CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +EXPLAIN SELECT * FROM t1 +WHERE ID_better=1 AND ID1_with_null IS NULL AND +(ID2_with_null=1 OR ID2_with_null=2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +DROP TABLE t1; |