Description:
If not compiled with InnoDB, test fails with
*** r/join_outer.result Mon Mar 27 07:37:37 2006
--- r/join_outer.reject Mon Apr 3 14:17:16 2006
***************
*** 1137,1156 ****
DROP TABLE t1,t2;
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
INDEX (name)) ENGINE=InnoDB;
CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
! 1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index
! 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%' OR FALSE;
id select_type table type possible_keys key key_len ref rows Extra
! 1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index
! 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index
DROP TABLE t1,t2;
--- 1137,1160 ----
DROP TABLE t1,t2;
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
INDEX (name)) ENGINE=InnoDB;
+ Warnings:
+ Warning 1266 Using storage engine MyISAM for table 't1'
CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB;
+ Warnings:
+ Warning 1266 Using storage engine MyISAM for table 't2'
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
! 1 SIMPLE t1 range PRIMARY,name name 23 NULL 1 Using where
! 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 2 Using where; Using index
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%' OR FALSE;
id select_type table type possible_keys key key_len ref rows Extra
! 1 SIMPLE t1 range PRIMARY,name name 23 NULL 1 Using where
! 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 2 Using where; Using index
DROP TABLE t1,t2;
How to repeat:
Configure, build and test with
./configure --without-innodb --without-berkeley-db --without-ndbcluster
make
cd mysql-test
./mysql-test-run.pl --force
Description: If not compiled with InnoDB, test fails with *** r/join_outer.result Mon Mar 27 07:37:37 2006 --- r/join_outer.reject Mon Apr 3 14:17:16 2006 *************** *** 1137,1156 **** DROP TABLE t1,t2; CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), INDEX (name)) ENGINE=InnoDB; CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra ! 1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index ! 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; id select_type table type possible_keys key key_len ref rows Extra ! 1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index ! 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index DROP TABLE t1,t2; --- 1137,1160 ---- DROP TABLE t1,t2; CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), INDEX (name)) ENGINE=InnoDB; + Warnings: + Warning 1266 Using storage engine MyISAM for table 't1' CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; + Warnings: + Warning 1266 Using storage engine MyISAM for table 't2' INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra ! 1 SIMPLE t1 range PRIMARY,name name 23 NULL 1 Using where ! 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 2 Using where; Using index EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; id select_type table type possible_keys key key_len ref rows Extra ! 1 SIMPLE t1 range PRIMARY,name name 23 NULL 1 Using where ! 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 2 Using where; Using index DROP TABLE t1,t2; How to repeat: Configure, build and test with ./configure --without-innodb --without-berkeley-db --without-ndbcluster make cd mysql-test ./mysql-test-run.pl --force