1162
# Bug#56423: Different count with SELECT and CREATE SELECT queries
1173
INSERT INTO t1 VALUES
1181
WHERE c = 1 AND b = 1 AND d = 1;
1182
id select_type table type possible_keys key key_len ref rows Extra
1183
1 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where
1184
CREATE TABLE t2 ( a INT )
1187
WHERE c = 1 AND b = 1 AND d = 1;
1193
CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
1194
INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
1195
SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
1202
# Code coverage of fix.
1203
CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
1204
INSERT INTO t1 (b) VALUES (1);
1205
UPDATE t1 SET b = 2 WHERE a = 1;
1209
CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
1210
INSERT INTO t2 (b) VALUES ('a');
1211
UPDATE t2 SET b = 'b' WHERE a = 1;
1161
1216
#---------------- 2-sweeps read Index merge test 2 -------------------------------
1162
1217
SET SESSION STORAGE_ENGINE = MyISAM;
1163
1218
drop table if exists t1;
1416
1471
`TESTID`='' AND `UCCHECK`='';
1474
# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
1476
CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
1477
INSERT INTO t1 VALUES (2);
1478
CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
1479
PRIMARY KEY (f1), KEY (f2), KEY (f3) );
1480
INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
1481
SELECT t1.f1 FROM t1
1482
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
1485
EXPLAIN SELECT t1.f1 FROM t1
1486
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
1487
id select_type table type possible_keys key key_len ref rows Extra
1488
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
1489
2 DEPENDENT SUBQUERY t2 ref f2,f3 f2 5 1 Using where
1419
1492
# Generic @@optimizer_switch tests (move those into a separate file if
1420
1493
# we get another @@optimizer_switch user)