diff options
Diffstat (limited to 'mysql-test/r/index_merge_myisam.result')
| -rw-r--r-- | mysql-test/r/index_merge_myisam.result | 73 |
1 files changed, 73 insertions, 0 deletions
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index c639b20de91..1ebc94dff5d 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1158,6 +1158,61 @@ key1 key2 key3 38 38 38 39 39 39 drop table t1; +# +# Bug#56423: Different count with SELECT and CREATE SELECT queries +# +CREATE TABLE t1 ( +a INT, +b INT, +c INT, +d INT, +PRIMARY KEY (a), +KEY (c), +KEY bd (b,d) +); +INSERT INTO t1 VALUES +(1, 0, 1, 0), +(2, 1, 1, 1), +(3, 1, 1, 1), +(4, 0, 1, 1); +EXPLAIN +SELECT a +FROM t1 +WHERE c = 1 AND b = 1 AND d = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where +CREATE TABLE t2 ( a INT ) +SELECT a +FROM t1 +WHERE c = 1 AND b = 1 AND d = 1; +SELECT * FROM t2; +a +2 +3 +DROP TABLE t1, t2; +CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) ); +INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2); +SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2; +a b +1 2 +1 2 +1 2 +1 2 +DROP TABLE t1; +# Code coverage of fix. +CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT); +INSERT INTO t1 (b) VALUES (1); +UPDATE t1 SET b = 2 WHERE a = 1; +SELECT * FROM t1; +a b +1 2 +CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) ); +INSERT INTO t2 (b) VALUES ('a'); +UPDATE t2 SET b = 'b' WHERE a = 1; +SELECT * FROM t2; +a b +1 b +DROP TABLE t1, t2; #---------------- 2-sweeps read Index merge test 2 ------------------------------- SET SESSION STORAGE_ENGINE = MyISAM; drop table if exists t1; @@ -1416,6 +1471,24 @@ WHERE `TESTID`='' AND `UCCHECK`=''; drop table t1; # +# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB +# +CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1)); +INSERT INTO t1 VALUES (2); +CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1), +PRIMARY KEY (f1), KEY (f2), KEY (f3) ); +INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, ''); +SELECT t1.f1 FROM t1 +WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; +f1 +2 +EXPLAIN SELECT t1.f1 FROM t1 +WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ref f2,f3 f2 5 1 Using where +DROP TABLE t1,t2; +# # Generic @@optimizer_switch tests (move those into a separate file if # we get another @@optimizer_switch user) # |