328
# Bug#56423: Different count with SELECT and CREATE SELECT queries
339
INSERT INTO t1 VALUES
347
WHERE c = 1 AND b = 1 AND d = 1;
348
id select_type table type possible_keys key key_len ref rows Extra
349
1 SIMPLE t1 index_merge c,bd c,bd 5,10 NULL 1 Using intersect(c,bd); Using where; Using index
350
CREATE TABLE t2 ( a INT )
353
WHERE c = 1 AND b = 1 AND d = 1;
359
CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
360
INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
361
SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
368
# Code coverage of fix.
369
CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
370
INSERT INTO t1 (b) VALUES (1);
371
UPDATE t1 SET b = 2 WHERE a = 1;
375
CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
376
INSERT INTO t2 (b) VALUES ('a');
377
UPDATE t2 SET b = 'b' WHERE a = 1;
327
382
#---------------- 2-sweeps read Index merge test 2 -------------------------------
328
383
SET SESSION STORAGE_ENGINE = InnoDB;
329
384
drop table if exists t1;
581
636
`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
582
637
`TESTID`='' AND `UCCHECK`='';
640
# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
642
CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
643
INSERT INTO t1 VALUES (2);
644
CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
645
PRIMARY KEY (f1), KEY (f2), KEY (f3) );
646
INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
648
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
651
EXPLAIN SELECT t1.f1 FROM t1
652
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
653
id select_type table type possible_keys key key_len ref rows Extra
654
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index
655
2 DEPENDENT SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index