in mysql-test/include/subquery_sj.inc [1766:3264]
CREATE TABLE t1 (
pk INTEGER PRIMARY KEY,
int_key INTEGER,
KEY int_key(int_key)
);
INSERT INTO t1 VALUES (10,186),(11,NULL),(12,2),(13,3),(14,0),(15,133),(16,1);
CREATE TABLE t2 (
pk INTEGER PRIMARY KEY,
int_key INTEGER,
KEY int_key(int_key)
);
INSERT INTO t2 VALUES (1,7),(2,2);
SELECT * FROM t1 WHERE (140, 4) IN
(SELECT t2.int_key, t2 .pk FROM t2 STRAIGHT_JOIN t1 ON t2.int_key);
DROP TABLE t1, t2;
--echo #
--echo # Bug#42353 "SELECT ... WHERE oe IN (SELECT w/ LEFT JOIN) query
--echo # causes crash."
--echo #
CREATE TABLE t1 (
pk INTEGER PRIMARY KEY,
int_nokey INTEGER,
int_key INTEGER,
date_key DATE,
datetime_nokey DATETIME,
varchar_nokey VARCHAR(1)
);
CREATE TABLE t2 (
date_nokey DATE
);
CREATE TABLE t3 (
pk INTEGER PRIMARY KEY,
int_nokey INTEGER,
date_key date,
varchar_key VARCHAR(1),
varchar_nokey VARCHAR(1),
KEY date_key (date_key)
);
SELECT date_key FROM t1
WHERE (int_key, int_nokey)
IN (SELECT t3.int_nokey, t3.pk
FROM t2 LEFT JOIN t3 ON (t2.date_nokey < t3.date_key)
WHERE t3.varchar_key <= t3.varchar_nokey OR t3.int_nokey <= t3.pk
)
AND (varchar_nokey <> 'f' OR NOT int_key < 7);
--echo #
--echo # Bug#45933 "Crash in optimize_semijoin_nests on JOIN in subquery
--echo # + AND in outer query".
--echo #
INSERT INTO t1 VALUES (10,7,5,'2009-06-16','2002-04-10 14:25:30','w'),
(11,7,0,'0000-00-00','0000-00-00 00:00:00','s'),
(12,4,0,'2003-07-14','2006-09-14 04:01:02','y'),
(13,0,4,'2002-07-25','0000-00-00 00:00:00','c'),
(14,1,8,'2007-07-03','0000-00-00 00:00:00','q'),
(15,6,5,'2001-11-12','0000-00-00 00:00:00',''),
(16,2,9,'0000-00-00','0000-00-00 00:00:00','j'),
(29,9,1,'0000-00-00','2003-08-11 00:00:00','m');
INSERT INTO t3 VALUES (1,9,'0000-00-00','b','b'),
(2,2,'2002-09-17','h','h');
SELECT t1.varchar_nokey FROM t1 JOIN t3 ON t1.datetime_nokey
WHERE t1.varchar_nokey
IN (SELECT varchar_nokey FROM t1
WHERE (pk)
IN (SELECT t3.int_nokey
FROM t3 LEFT JOIN t1 ON t1.varchar_nokey
WHERE t3.date_key BETWEEN '2008-06-07' AND '2006-06-26'
)
);
DROP TABLE t1, t2, t3;
--echo #
--echo # Bug#45219 "Crash on SELECT DISTINCT query containing a
--echo # LEFT JOIN in subquery"
--echo #
CREATE TABLE t1 (
pk INTEGER NOT NULL,
int_nokey INTEGER NOT NULL,
datetime_key DATETIME NOT NULL,
varchar_key VARCHAR(1) NOT NULL,
PRIMARY KEY (pk),
KEY datetime_key (datetime_key),
KEY varchar_key (varchar_key)
);
INSERT INTO t1 VALUES
(1,9,'0000-00-00 00:00:00','p'),(2,0,'2002-02-09 07:38:13','v'),
(3,8,'2001-05-03 12:08:14','t'),(4,3,'0000-00-00 00:00:00','u'),
(5,7,'2009-07-28 03:43:30','n'),(6,0,'2009-08-04 00:00:00','l'),
(7,1,'0000-00-00 00:00:00','h'),(8,9,'0000-00-00 00:00:00','u'),
(9,0,'2005-08-02 17:16:54','n'),(10,9,'2002-12-21 00:00:00','j'),
(11,0,'2005-08-15 12:37:35','k'),(12,5,'0000-00-00 00:00:00','e'),
(13,0,'2006-03-10 00:00:00','i'),(14,8,'2005-05-16 11:02:36','u'),
(15,8,'2008-11-02 00:00:00','n'),(16,5,'2006-03-15 00:00:00','b'),
(17,1,'0000-00-00 00:00:00','x'),(18,7,'0000-00-00 00:00:00',''),
(19,0,'2008-12-17 20:15:40','q'),(20,9,'0000-00-00 00:00:00','u');
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 VALUES
(10,0,'2006-07-07 07:26:28','q'),(11,5,'2002-09-23 00:00:00','m'),
(12,7,'0000-00-00 00:00:00','j'),(13,1,'2006-06-07 00:00:00','z'),
(14,8,'2000-09-16 12:15:34','a'),(15,2,'2007-08-05 15:47:52',''),
(16,1,'0000-00-00 00:00:00','e'),(17,8,'2005-12-02 19:34:26','t'),
(18,5,'0000-00-00 00:00:00','q'),(19,4,'0000-00-00 00:00:00','b'),
(20,5,'2007-12-28 00:00:00','w'),(21,3,'2004-08-02 11:48:43','m'),
(22,0,'0000-00-00 00:00:00','x'),(23,8,'2004-04-19 12:18:43',''),
(24,0,'2009-04-27 00:00:00','w'),(25,4,'2006-10-20 14:52:15','x'),
(26,0,'0000-00-00 00:00:00','e'),(27,0,'2002-03-22 11:48:37','e'),
(28,2,'0000-00-00 00:00:00','p'),(29,0,'2001-01-04 03:55:07','x');
CREATE TABLE t3 LIKE t1;
INSERT INTO t3 VALUES
(10,8,'2007-08-19 08:08:38','i'),(11,0,'2000-05-21 03:51:51','');
SELECT DISTINCT datetime_key FROM t1
WHERE (int_nokey, pk)
IN (SELECT t3.pk, t3.pk FROM t2 LEFT JOIN t3 ON t3.varchar_key)
AND pk = 9;
DROP TABLE t1, t2, t3;
--echo #
--echo # Bug#46550 Azalea returning duplicate results for some IN subqueries
--echo # w/ semijoin=on
--echo #
--disable_warnings
DROP TABLE IF EXISTS t0, t1, t2;
--enable_warnings
CREATE TABLE t0 (
int_key int(11) DEFAULT NULL,
varchar_key varchar(1) DEFAULT NULL,
varchar_nokey varchar(1) DEFAULT NULL,
KEY int_key (int_key),
KEY varchar_key (varchar_key,int_key)
);
INSERT INTO t0 VALUES
(1,'m','m'),
(40,'h','h'),
(1,'r','r'),
(1,'h','h'),
(9,'x','x'),
(NULL,'q','q'),
(NULL,'k','k'),
(7,'l','l'),
(182,'k','k'),
(202,'a','a'),
(7,'x','x'),
(6,'j','j'),
(119,'z','z'),
(4,'d','d'),
(5,'h','h'),
(1,'u','u'),
(3,'q','q'),
(7,'a','a'),
(3,'e','e'),
(6,'l','l');
CREATE TABLE t1 (
int_key int(11) DEFAULT NULL,
varchar_key varchar(1) DEFAULT NULL,
varchar_nokey varchar(1) DEFAULT NULL,
KEY int_key (int_key),
KEY varchar_key (varchar_key,int_key)
);
INSERT INTO t1 VALUES (7,NULL,NULL),(4,'x','x');
CREATE TABLE t2 (
int_key int(11) DEFAULT NULL,
varchar_key varchar(1) DEFAULT NULL,
varchar_nokey varchar(1) DEFAULT NULL,
KEY int_key (int_key),
KEY varchar_key (varchar_key,int_key)
);
INSERT INTO t2 VALUES (123,NULL,NULL);
SELECT int_key
FROM t0
WHERE varchar_nokey IN (
SELECT t1 .varchar_key from t1
);
SELECT t0.int_key
FROM t0
WHERE t0.varchar_nokey IN (
SELECT t1_1 .varchar_key
FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
);
EXPLAIN
SELECT t0.int_key
FROM t0
WHERE t0.varchar_nokey IN (
SELECT t1_1 .varchar_key
FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
);
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
SELECT t1_1 .varchar_key
FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
);
EXPLAIN
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
SELECT t1_1 .varchar_key
FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
);
DROP TABLE t0, t1, t2;
--echo # End of bug#46550
--echo
--echo Bug #48073 Subquery on char columns from view crashes Mysql
--echo
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v1;
--enable_warnings
CREATE TABLE t1 (
city VARCHAR(50) NOT NULL,
country_id SMALLINT UNSIGNED NOT NULL
);
INSERT INTO t1 VALUES
('Batna',2),
('Bchar',2),
('Skikda',2),
('Tafuna',3),
('Algeria',2) ;
CREATE TABLE t2 (
country_id SMALLINT UNSIGNED NOT NULL,
country VARCHAR(50) NOT NULL
);
INSERT INTO t2 VALUES
(2,'Algeria'),
(3,'American Samoa') ;
CREATE VIEW v1 AS
SELECT country_id, country
FROM t2
WHERE LEFT(country,1) = "A"
;
SELECT city, country_id
FROM t1
WHERE city IN (
SELECT country
FROM t2
WHERE LEFT(country, 1) = "A"
);
SELECT city, country_id
FROM t1
WHERE city IN (
SELECT country
FROM v1
);
drop table t1, t2;
drop view v1;
--echo # End of bug#48073
--echo
--echo Bug#49097 subquery with view generates wrong result with
--echo non-prepared statement
--echo
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v1;
--enable_warnings
CREATE TABLE t1 (
city VARCHAR(50) NOT NULL,
country_id SMALLINT UNSIGNED NOT NULL
);
INSERT INTO t1 VALUES
('Batna',2),
('Bchar',2),
('Skikda',2),
('Tafuna',3),
('Algeria',2) ;
CREATE TABLE t2 (
country_id SMALLINT UNSIGNED NOT NULL,
country VARCHAR(50) NOT NULL
);
INSERT INTO t2 VALUES
(2,'Algeria'),
(3,'XAmerican Samoa') ;
CREATE VIEW v1 AS
SELECT country_id, country
FROM t2
WHERE LEFT(country,1) = "A"
;
SELECT city, country_id
FROM t1
WHERE country_id IN (
SELECT country_id
FROM t2
WHERE LEFT(country,1) = "A"
);
SELECT city, country_id
FROM t1
WHERE country_id IN (
SELECT country_id
FROM v1
);
PREPARE stmt FROM
"
SELECT city, country_id
FROM t1
WHERE country_id IN (
SELECT country_id
FROM v1
);
";
execute stmt;
deallocate prepare stmt;
drop table t1, t2;
drop view v1;
--echo # End of Bug#49097
--echo #
--echo # Bug#49198 Wrong result for second call of procedure
--echo # with view in subselect.
--echo #
CREATE TABLE t1 (t1field integer, primary key (t1field));
CREATE TABLE t2 (t2field integer, primary key (t2field));
CREATE TABLE t3 (t3field integer, primary key (t3field));
CREATE VIEW v2 AS SELECT * FROM t2;
CREATE VIEW v3 AS SELECT * FROM t3;
INSERT INTO t1 VALUES(1),(2);
INSERT INTO t2 VALUES(1),(2);
INSERT INTO t3 VALUES(1),(2);
PREPARE stmt FROM
"
SELECT t1field
FROM t1
WHERE t1field IN (SELECT * FROM v2);
";
EXECUTE stmt;
EXECUTE stmt;
PREPARE stmt FROM
"
EXPLAIN
SELECT t1field
FROM t1
WHERE t1field IN (SELECT * FROM v2)
AND t1field IN (SELECT * FROM v3)
";
EXECUTE stmt;
EXECUTE stmt;
DROP TABLE t1, t2, t3;
DROP VIEW v2, v3;
--echo # End of Bug#49198
--echo #
--echo # Bug#48623 Multiple subqueries are optimized incorrectly
--echo #
CREATE TABLE ot(val VARCHAR(10));
CREATE TABLE it1(val VARCHAR(10));
CREATE TABLE it2(val VARCHAR(10));
INSERT INTO ot VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp');
INSERT INTO it1 VALUES('aaa'), ('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp');
INSERT INTO it2 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp');
EXPLAIN
SELECT *
FROM ot
WHERE ot.val IN (SELECT it1.val FROM it1
WHERE it1.val LIKE 'a%' OR it1.val LIKE 'e%')
AND ot.val IN (SELECT it2.val FROM it2
WHERE it2.val LIKE 'a%' OR it2.val LIKE 'e%');
SELECT *
FROM ot
WHERE ot.val IN (SELECT it1.val FROM it1
WHERE it1.val LIKE 'a%' OR it1.val LIKE 'e%')
AND ot.val IN (SELECT it2.val FROM it2
WHERE it2.val LIKE 'a%' OR it2.val LIKE 'e%');
DROP TABLE ot;
DROP TABLE it1;
DROP TABLE it2;
--echo # End of Bug#48623
--echo #
--echo # Bug #51487 Assertion failure when semi-join flattening occurs
--echo # for a subquery in HAVING
--echo #
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,10),(2,11),(1,13);
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
SELECT COUNT(*) FROM t1
GROUP BY t1.a
HAVING t1.a IN (SELECT t3.a FROM t3
WHERE t3.b IN (SELECT b FROM t2 WHERE t2.a=t1.a));
DROP TABLE t1, t2, t3;
--echo # End of Bug#51487
--echo #
--echo # BUG#38075: Wrong result: rows matching a subquery with outer join not returned
--echo #
--disable_warnings
DROP TABLE IF EXISTS ot1, it1, it2;
--enable_warnings
CREATE TABLE it2 (
int_key int(11) NOT NULL,
datetime_key datetime NOT NULL,
KEY int_key (int_key),
KEY datetime_key (datetime_key)
);
INSERT INTO it2 VALUES
(5,'2002-04-10 14:25:30'), (0,'0000-00-00 00:00:00'),
(0,'2006-09-14 04:01:02'), (4,'0000-00-00 00:00:00'),
(8,'0000-00-00 00:00:00'), (5,'0000-00-00 00:00:00'),
(9,'0000-00-00 00:00:00'), (8,'2007-04-01 11:04:17'),
(1,'0000-00-00 00:00:00'), (7,'2009-01-12 00:00:00'),
(0,'2009-06-05 00:00:00'), (3,'2006-02-14 18:06:35'),
(5,'2006-02-21 07:08:16'), (0,'0000-00-00 00:00:00'),
(7,'0000-00-00 00:00:00'), (0,'0000-00-00 00:00:00'),
(0,'2007-02-13 00:00:00'), (1,'0000-00-00 00:00:00'),
(0,'0000-00-00 00:00:00'), (1,'2003-08-11 00:00:00');
CREATE TABLE ot1 (
int_nokey int(11) NOT NULL,
int_key int(11) NOT NULL,
KEY int_key (int_key)
);
INSERT INTO ot1 VALUES
(5,0), (3,0), (0,2), (3,0), (1,3), (0,0), (1,7), (7,0), (1,7), (0,7),
(0,9), (8,2), (4,4), (9,3), (0,9), (2,5), (0,5), (8,0), (5,8), (1,5);
CREATE TABLE it1 (
int_nokey int(11) NOT NULL,
int_key int(11) NOT NULL,
KEY int_key (int_key)
);
INSERT INTO it1 VALUES
(9,5), (0,4);
--sorted_result
SELECT int_key FROM ot1
WHERE int_nokey IN (SELECT it2.int_key
FROM it1 LEFT JOIN it2 ON it2.datetime_key);
EXPLAIN
SELECT int_key FROM ot1
WHERE int_nokey IN (SELECT it2.int_key
FROM it1 LEFT JOIN it2 ON it2.datetime_key);
DROP TABLE ot1, it1, it2;
--echo # End of BUG#38075
--echo #
--echo # BUG#50089: Second call of procedure with view in subselect crashes server
--echo #
CREATE TABLE t1(t1field INTEGER, PRIMARY KEY(t1field));
CREATE VIEW v1 AS
SELECT t1field AS v1field
FROM t1 a
WHERE a.t1field IN (SELECT t1field FROM t1);
INSERT INTO t1 VALUES(1),(2);
SELECT t1field
FROM t1
WHERE t1field IN (SELECT v1field FROM v1);
EXPLAIN
SELECT t1field
FROM t1
WHERE t1field IN (SELECT v1field FROM v1);
--sorted_result
SELECT t1.t1field
FROM t1 LEFT JOIN t1 AS t2 ON t1.t1field IN (SELECT v1field FROM v1);
EXPLAIN
SELECT t1field
FROM t1
WHERE t1field IN (SELECT v1field FROM v1);
delimiter |;
CREATE PROCEDURE p1()
BEGIN
SELECT t1field
FROM t1
WHERE t1field IN (SELECT v1field FROM v1);
END|
delimiter ;|
CALL p1;
CALL p1;
PREPARE stmt FROM
"
SELECT t1field
FROM t1
WHERE t1field IN (SELECT v1field FROM v1);
";
EXECUTE stmt;
EXECUTE stmt;
DROP PROCEDURE p1;
DROP VIEW v1;
DROP TABLE t1;
--echo # End of BUG#50089
--echo #
--echo # Bug#45191: Incorrectly initialized semi-join led to a wrong result.
--echo #
CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL,
EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15));
CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL,
PNAME CHAR(20), PTYPE CHAR(6),
BUDGET DECIMAL(9),
CITY CHAR(15));
CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL,
PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5));
INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');
INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');
INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');
INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');
INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');
INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale');
INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna');
INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa');
INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale');
INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna');
INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale');
INSERT INTO WORKS VALUES ('E1','P1',40);
INSERT INTO WORKS VALUES ('E1','P2',20);
INSERT INTO WORKS VALUES ('E1','P3',80);
INSERT INTO WORKS VALUES ('E1','P4',20);
INSERT INTO WORKS VALUES ('E1','P5',12);
INSERT INTO WORKS VALUES ('E1','P6',12);
INSERT INTO WORKS VALUES ('E2','P1',40);
INSERT INTO WORKS VALUES ('E2','P2',80);
INSERT INTO WORKS VALUES ('E3','P2',20);
INSERT INTO WORKS VALUES ('E4','P2',20);
INSERT INTO WORKS VALUES ('E4','P4',40);
INSERT INTO WORKS VALUES ('E4','P5',80);
explain SELECT EMPNUM, EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM FROM WORKS
WHERE PNUM IN
(SELECT PNUM FROM PROJ));
SELECT EMPNUM, EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM FROM WORKS
WHERE PNUM IN
(SELECT PNUM FROM PROJ));
drop table STAFF,WORKS,PROJ;
--echo # End of bug#45191
--echo #
--echo # BUG#36896: Server crash on SELECT FROM DUAL
--echo #
create table t1 (a int);
select 1 as res from dual where (1) in (select * from t1);
drop table t1;
--echo
--echo BUG#40118 Crash when running Batched Key Access and requiring one match for each key
--echo
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, key(a));
insert into t1 select * from t0;
alter table t1 add b int not null, add filler char(200);
insert into t1 select * from t1;
insert into t1 select * from t1;
select * from t0 where t0.a in (select t1.a from t1 where t1.b=0);
drop table t0, t1;
--echo #
--echo # BUG#32665 Query with dependent subquery is too slow
--echo #
create table t1 (
idIndividual int primary key
);
insert into t1 values (1),(2);
create table t2 (
idContact int primary key,
contactType int,
idObj int
);
insert into t2 values (1,1,1),(2,2,2),(3,3,3);
create table t3 (
idAddress int primary key,
idContact int,
postalStripped varchar(100)
);
insert into t3 values (1,1, 'foo'), (2,2,'bar');
--echo The following must be converted to a semi-join:
explain extended SELECT a.idIndividual FROM t1 a
WHERE a.idIndividual IN
( SELECT c.idObj FROM t3 cona
INNER JOIN t2 c ON c.idContact=cona.idContact
WHERE cona.postalStripped='T2H3B2'
);
drop table t1,t2,t3;
#
# Bug#11867 queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...)
#
CREATE TABLE t1 (one int, two int, flag char(1));
CREATE TABLE t2 (one int, two int, flag char(1));
INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
INSERT INTO t2 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
SELECT * FROM t1
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t2 WHERE flag = 'N');
SELECT * FROM t1
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
insert into t2 values (null,null,'N');
insert into t2 values (null,3,'0');
insert into t2 values (null,5,'0');
insert into t2 values (10,null,'0');
insert into t1 values (10,3,'0');
insert into t1 values (10,5,'0');
insert into t1 values (10,10,'0');
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
DROP TABLE t1,t2;
#
# Bug#12392 where cond with IN predicate for rows and NULL values in table
#
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
DROP TABLE t1;
#
# Bug#30788 Inconsistent retrieval of char/varchar
#
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
INSERT INTO t1 VALUES ('a', 'aa');
INSERT INTO t1 VALUES ('a', 'aaa');
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
CREATE INDEX I1 ON t1 (a);
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
INSERT INTO t2 SELECT * FROM t1;
CREATE INDEX I1 ON t2 (a);
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
EXPLAIN
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
DROP TABLE t1,t2;
--echo #
--echo # BUG#45928 "Differing query results depending on MRR and
--echo # engine_condition_pushdown settings"
--echo #
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`time_nokey` time NOT NULL,
`varchar_key` varchar(1) NOT NULL,
`varchar_nokey` varchar(1) NOT NULL,
PRIMARY KEY (`pk`),
KEY `varchar_key` (`varchar_key`)
) AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (10,'00:00:00','i','i'),(11,'00:00:00','','');
SELECT `time_nokey` G1 FROM t1 WHERE ( `varchar_nokey` , `varchar_key` ) IN (
SELECT `varchar_nokey` , `varchar_nokey` ) AND `varchar_key` >= 'c' HAVING G1 ORDER
BY `pk` ;
DROP TABLE t1;
--echo #
--echo # BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(),
--echo # file item.cc, line 4448"
--echo #
--disable_warnings
DROP TABLE IF EXISTS C, BB;
--enable_warnings
CREATE TABLE C (
varchar_nokey varchar(1) NOT NULL
);
INSERT INTO C VALUES
('k'),('a'),(''),('u'),('e'),('v'),('i'),
('t'),('u'),('f'),('u'),('m'),('j'),('f'),
('v'),('j'),('g'),('e'),('h'),('z');
CREATE TABLE BB (
varchar_nokey varchar(1) NOT NULL
);
INSERT INTO BB VALUES ('i'),('t');
-- error ER_BAD_FIELD_ERROR
SELECT varchar_nokey FROM C
WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey
FROM BB);
-- error ER_BAD_FIELD_ERROR
SELECT varchar_nokey FROM C
WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey, varchar_nokey
FROM BB);
DROP TABLE C,BB;
--echo #
--echo # During work with BUG#45863 I had problems with a query that was
--echo # optimized differently in regular and prepared mode.
--echo # Because there was a bug in one of the selected strategies, I became
--echo # aware of the problem. Adding an EXPLAIN query to catch this.
--disable_warnings
DROP TABLE IF EXISTS t1, t2, t3;
--enable_warnings
CREATE TABLE t1
(EMPNUM CHAR(3) NOT NULL,
EMPNAME CHAR(20),
GRADE DECIMAL(4),
CITY CHAR(15));
CREATE TABLE t2
(PNUM CHAR(3) NOT NULL,
PNAME CHAR(20),
PTYPE CHAR(6),
BUDGET DECIMAL(9),
CITY CHAR(15));
CREATE TABLE t3
(EMPNUM CHAR(3) NOT NULL,
PNUM CHAR(3) NOT NULL,
HOURS DECIMAL(5));
INSERT INTO t1 VALUES ('E1','Alice',12,'Deale');
INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna');
INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna');
INSERT INTO t1 VALUES ('E4','Don',12,'Deale');
INSERT INTO t1 VALUES ('E5','Ed',13,'Akron');
INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale');
INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna');
INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa');
INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale');
INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna');
INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale');
INSERT INTO t3 VALUES ('E1','P1',40);
INSERT INTO t3 VALUES ('E1','P2',20);
INSERT INTO t3 VALUES ('E1','P3',80);
INSERT INTO t3 VALUES ('E1','P4',20);
INSERT INTO t3 VALUES ('E1','P5',12);
INSERT INTO t3 VALUES ('E1','P6',12);
INSERT INTO t3 VALUES ('E2','P1',40);
INSERT INTO t3 VALUES ('E2','P2',80);
INSERT INTO t3 VALUES ('E3','P2',20);
INSERT INTO t3 VALUES ('E4','P2',20);
INSERT INTO t3 VALUES ('E4','P4',40);
INSERT INTO t3 VALUES ('E4','P5',80);
CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'));
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
CREATE INDEX t1_IDX ON t1(EMPNUM);
EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'));
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'));
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'))";
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1, t2, t3;
--echo #
--echo # BUG#45221 Query SELECT pk FROM C WHERE pk IN (SELECT int_key) failing
--echo #
CREATE TABLE t1 (
i1_key INT,
i2 INT,
i3 INT,
KEY i1_index (i1_key)
);
INSERT INTO t1 VALUES (9,1,2), (9,2,1);
CREATE TABLE t2 (
pk INT NOT NULL,
i1 INT,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (9,1);
SELECT pk
FROM t2
WHERE
pk IN (
SELECT i1_key
FROM t1
WHERE t1.i2 < t1.i3 XOR t2.i1 > 1
ORDER BY t1.i2 desc);
DROP TABLE t1,t2;
--echo # BUG#50361 Doublenested noncorrelated subquery with FirstMatch and join cache wrong result
--echo #
CREATE TABLE t1(
id INTEGER
);
INSERT INTO t1 VALUES(10),(20);
create table t2 select * from t1;
create table t3 select * from t1;
SELECT *
FROM t1
WHERE 1 IN(SELECT 1
FROM t2
WHERE 1 IN(SELECT 1
FROM t3));
explain extended SELECT *
FROM t1
WHERE 1 IN(SELECT 1
FROM t2
WHERE 1 IN(SELECT 1
FROM t3));
delete from t2;
delete from t3;
INSERT INTO t1 VALUES(30),(40),(50),(60),(70),(80),(90);
insert into t2 select * from t1;
insert into t3 select * from t1;
create table t4 select * from t1;
SELECT *
FROM t1
WHERE 1 IN(SELECT 1
FROM t2
WHERE 1 IN(SELECT 1
FROM t3
WHERE 1 IN(SELECT 1
FROM t4)));
explain SELECT *
FROM t1
WHERE 1 IN(SELECT 1
FROM t2
WHERE 1 IN(SELECT 1
FROM t3
WHERE 1 IN(SELECT 1
FROM t4)));
SELECT *
FROM t1
WHERE 1 IN(SELECT 1
FROM t1
WHERE 1 IN(SELECT 1
FROM t1
WHERE 1 IN(SELECT 1
FROM t1)));
drop table t1,t2,t3,t4;
--echo #
--echo # Bug#53236 Segfault in DTCollation::set(DTCollation&)
--echo #
CREATE TABLE t1 (
pk INTEGER AUTO_INCREMENT,
col_varchar VARCHAR(1),
PRIMARY KEY (pk)
)
;
INSERT INTO t1 (col_varchar)
VALUES
('w'),
('m')
;
SELECT table1.pk
FROM ( t1 AS table1 JOIN t1 AS table2 ON (table1.col_varchar =
table2.col_varchar) )
WHERE ( 1, 2 ) IN ( SELECT SUBQUERY1_t1.pk AS SUBQUERY1_field1,
SUBQUERY1_t1.pk AS SUBQUERY1_field2
FROM ( t1 AS SUBQUERY1_t1 JOIN t1 AS SUBQUERY1_t2
ON (SUBQUERY1_t2.col_varchar =
SUBQUERY1_t1.col_varchar) ) )
;
drop table t1;
--echo #
--echo # BUG#53298 "wrong result with semijoin (no semijoin strategy chosen)"
--echo #
create table t1 (uid int, fid int);
insert into t1 values (1,1), (3,1);
create table t2 (uid int, name varchar(128));
insert into t2 values (1, "A"), (2, "B");
create table t3 (uid int, fid int, index(uid));
insert into t3 values (1,3), (1,3);
create table t4 (uid int);
insert into t4 values (3);
explain select t2.uid from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
and t2.uid=t1.fid;
--sorted_result
select t2.uid from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
and t2.uid=t1.fid;
drop table t1,t2,t3,t4;
#
# BUG#46548 IN-subqueries return 0 rows with materialization=on
#
CREATE TABLE t1 (
pk int,
a varchar(1),
b varchar(4),
c varchar(4),
d varchar(4),
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
DROP TABLE t1, t2;
#
# Bug #44303 Assertion failures in Field_new_decimal::store_decimal
# when executing materialized InsideOut semijoin
#
CREATE TABLE t1 (f1 INT, f2 DECIMAL(5,3)) ENGINE=MyISAM;
INSERT INTO t1 (f1, f2) VALUES (1, 1.789);
INSERT INTO t1 (f1, f2) VALUES (13, 1.454);
INSERT INTO t1 (f1, f2) VALUES (10, 1.668);
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 VALUES (1, 1.789);
INSERT INTO t2 VALUES (13, 1.454);
EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
DROP TABLE t1, t2;
#
# Bug #27348: Assertion abort for a query with two subqueries to be flattened
# Bug #35674: Range optimizer ignores conditions on inner tables in
# semi-join IN subqueries
#
CREATE TABLE t1 (
ID int(11) NOT NULL auto_increment,
Name char(35) NOT NULL default '',
Country char(3) NOT NULL default '',
Population int(11) NOT NULL default '0',
PRIMARY KEY (ID),
INDEX (Population),
INDEX (Country)
);
CREATE TABLE t2 (
Code char(3) NOT NULL default '',
Name char(52) NOT NULL default '',
SurfaceArea float(10,2) NOT NULL default '0.00',
Population int(11) NOT NULL default '0',
Capital int(11) default NULL,
PRIMARY KEY (Code),
UNIQUE INDEX (Name),
INDEX (Population)
);
CREATE TABLE t3 (
Country char(3) NOT NULL default '',
Language char(30) NOT NULL default '',
Percentage float(3,1) NOT NULL default '0.0',
PRIMARY KEY (Country, Language),
INDEX (Percentage)
);
--disable_query_log
INSERT INTO t1 VALUES
(1,'Kabul','AFG',1780000),(2,'Qandahar','AFG',237500),
(3,'Herat','AFG',186800),(4,'Mazar-e-Sharif','AFG',127800),
(5,'Amsterdam','NLD',731200),(6,'Rotterdam','NLD',593321),
(7,'Haag','NLD',440900),(8,'Utrecht','NLD',234323),
(9,'Eindhoven','NLD',201843),(10,'Tilburg','NLD',193238),
(11,'Groningen','NLD',172701),(12,'Breda','NLD',160398),
(13,'Apeldoorn','NLD',153491),(14,'Nijmegen','NLD',152463),
(15,'Enschede','NLD',149544),(16,'Haarlem','NLD',148772),
(17,'Almere','NLD',142465),(18,'Arnhem','NLD',138020),
(19,'Zaanstad','NLD',135621),(20,'´s-Hertogenbosch','NLD',129170),
(21,'Amersfoort','NLD',126270),(22,'Maastricht','NLD',122087),
(23,'Dordrecht','NLD',119811),(24,'Leiden','NLD',117196),
(25,'Haarlemmermeer','NLD',110722),(26,'Zoetermeer','NLD',110214),
(27,'Emmen','NLD',105853),(28,'Zwolle','NLD',105819),
(29,'Ede','NLD',101574),(30,'Delft','NLD',95268);
INSERT INTO t2 VALUES
('AFG','Afghanistan',652090.00,22720000,1),
('NLD','Netherlands',41526.00,15864000,5),
('ANT','Netherlands Antilles',800.00,217000,33),
('ALB','Albania',28748.00,3401200,34),
('DZA','Algeria',2381741.00,31471000,35),
('ASM','American Samoa',199.00,68000,54),
('AND','Andorra',468.00,78000,55),
('AGO','Angola',1246700.00,12878000,56),
('AIA','Anguilla',96.00,8000,62),
('ATG','Antigua and Barbuda',442.00,68000,63),
('ARE','United Arab Emirates',83600.00,2441000,65),
('ARG','Argentina',2780400.00,37032000,69),
('ARM','Armenia',29800.00,3520000,126),
('ABW','Aruba',193.00,103000,129),
('AUS','Australia',7741220.00,18886000,135),
('AZE','Azerbaijan',86600.00,7734000,144);
INSERT INTO t3 VALUES
('AFG','Pashto',52.4),('NLD','Dutch',95.6),
('ANT','Papiamento',86.2),('ALB','Albaniana',97.9),
('DZA','Arabic',86.0),('ASM','Samoan',90.6),
('AND','Spanish',44.6),('AGO','Ovimbundu',37.2),
('AIA','English',0.0),('ATG','Creole English',95.7),
('ARE','Arabic',42.0),('ARG','Spanish',96.8),
('ARM','Armenian',93.4),('ABW','Papiamento',76.7),
('AUS','English',81.2),('AZE','Azerbaijani',89.0),
('BHS','Creole English',89.7),('BHR','Arabic',67.7),
('BGD','Bengali',97.7),('BRB','Bajan',95.1),
('BEL','Dutch',59.2),('BLZ','English',50.8);
--enable_query_log
let $query=
SELECT Name FROM t2
WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
AND
t2.Code IN (SELECT Country FROM t3
WHERE Language='English' AND Percentage > 10 AND
t2.Population > 100000);
--eval EXPLAIN $query
--eval EXPLAIN FORMAT=JSON $query
DROP TABLE t1,t2,t3;
#
# BUG#30993: Subqueries: LooseScan strategy produces wrong query results, with duplicates
#
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
Name char(52) NOT NULL DEFAULT '',
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
Region char(26) NOT NULL DEFAULT '',
SurfaceArea float(10,2) NOT NULL DEFAULT '0.00',
IndepYear smallint(6) DEFAULT NULL,
Population int(11) NOT NULL DEFAULT '0',
LifeExpectancy float(3,1) DEFAULT NULL,
GNP float(10,2) DEFAULT NULL,
GNPOld float(10,2) DEFAULT NULL,
LocalName char(45) NOT NULL DEFAULT '',
GovernmentForm char(45) NOT NULL DEFAULT '',
HeadOfState char(60) DEFAULT NULL,
Capital int(11) DEFAULT NULL,
Code2 char(2) NOT NULL DEFAULT '',
PRIMARY KEY (Code)
);
CREATE TABLE t2 (
ID int(11) NOT NULL AUTO_INCREMENT,
Name char(35) NOT NULL DEFAULT '',
CountryCode char(3) NOT NULL DEFAULT '',
District char(20) NOT NULL DEFAULT '',
Population int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (ID),
KEY CountryCode (CountryCode)
);
--echo Fill the table with test data
--disable_query_log
insert into t2 (ID, Name, CountryCode, Population) values
(1,'Kabul','AFG',1780000), (2,'Qandahar','AFG',237500), (3,'Herat','AFG',186800),
(4,'Mazar-e-Sharif','AFG',127800), (33,'Willemstad','ANT',2345), (34,'Tirana','ALB',270000),
(55,'Andorra la Vella','AND',21189), (61,'South Hill','AIA',961), (62,'The Valley','AIA',595),
(63,'Saint John�s','ATG',24000), (64,'Dubai','ARE',669181), (65,'Abu Dhabi','ARE',398695),
(66,'Sharja','ARE',320095), (67,'al-Ayn','ARE',225970), (68,'Ajman','ARE',114395),
(126,'Yerevan','ARM',1248700), (127,'Gjumri','ARM',211700), (128,'Vanadzor','ARM',172700),
(129,'Oranjestad','ABW',29034), (144,'Baku','AZE',1787800), (145,'G�nc�','AZE',299300),
(146,'Sumqayit','AZE',283000), (147,'Ming��evir','AZE',93900), (148,'Nassau','BHS',172000),
(149,'al-Manama','BHR',148000), (150,'Dhaka','BGD',3612850), (151,'Chittagong','BGD',1392860),
(152,'Khulna','BGD',663340), (153,'Rajshahi','BGD',294056), (154,'Narayanganj','BGD',202134),
(155,'Rangpur','BGD',191398), (156,'Mymensingh','BGD',188713), (157,'Barisal','BGD',170232),
(158,'Tungi','BGD',168702), (159,'Jessore','BGD',139710), (160,'Comilla','BGD',135313),
(161,'Nawabganj','BGD',130577), (162,'Dinajpur','BGD',127815), (163,'Bogra','BGD',120170),
(164,'Sylhet','BGD',117396), (165,'Brahmanbaria','BGD',109032), (166,'Tangail','BGD',106004),
(167,'Jamalpur','BGD',103556), (168,'Pabna','BGD',103277), (169,'Naogaon','BGD',101266),
(170,'Sirajganj','BGD',99669), (171,'Narsinghdi','BGD',98342), (172,'Saidpur','BGD',96777),
(173,'Gazipur','BGD',96717), (174,'Bridgetown','BRB',6070), (175,'Antwerpen','BEL',446525),
(176,'Gent','BEL',224180), (177,'Charleroi','BEL',200827), (178,'Li�ge','BEL',185639),
(179,'Bruxelles [Brussel]','BEL',133859), (180,'Brugge','BEL',116246), (181,'Schaerbeek','BEL',105692),
(182,'Namur','BEL',105419), (183,'Mons','BEL',90935), (184,'Belize City','BLZ',55810),
(185,'Belmopan','BLZ',7105), (190,'Saint George','BMU',1800), (191,'Hamilton','BMU',1200),
(192,'Thimphu','BTN',22000), (201,'Sarajevo','BIH',360000), (202,'Banja Luka','BIH',143079),
(203,'Zenica','BIH',96027), (538,'Bandar Seri Begawan','BRN',21484), (539,'Sofija','BGR',1122302),
(540,'Plovdiv','BGR',342584), (541,'Varna','BGR',299801), (542,'Burgas','BGR',195255),
(543,'Ruse','BGR',166467), (544,'Stara Zagora','BGR',147939), (545,'Pleven','BGR',121952),
(546,'Sliven','BGR',105530), (547,'Dobric','BGR',100399), (548,'�umen','BGR',94686),
(553,'George Town','CYM',19600), (584,'San Jos�','CRI',339131), (1523,'Wien','AUT',1608144),
(1524,'Graz','AUT',240967), (1525,'Linz','AUT',188022), (1526,'Salzburg','AUT',144247),
(1527,'Innsbruck','AUT',111752), (1528,'Klagenfurt','AUT',91141), (1810,'Montr�al','CAN',1016376),
(1811,'Calgary','CAN',768082), (1812,'Toronto','CAN',688275), (1813,'North York','CAN',622632),
(1814,'Winnipeg','CAN',618477), (1815,'Edmonton','CAN',616306), (1816,'Mississauga','CAN',608072),
(1817,'Scarborough','CAN',594501), (1818,'Vancouver','CAN',514008), (1819,'Etobicoke','CAN',348845),
(1820,'London','CAN',339917), (1821,'Hamilton','CAN',335614), (1822,'Ottawa','CAN',335277),
(1823,'Laval','CAN',330393), (1824,'Surrey','CAN',304477), (1825,'Brampton','CAN',296711),
(1826,'Windsor','CAN',207588), (1827,'Saskatoon','CAN',193647), (1828,'Kitchener','CAN',189959),
(1829,'Markham','CAN',189098), (1830,'Regina','CAN',180400), (1831,'Burnaby','CAN',179209),
(1832,'Qu�bec','CAN',167264), (1833,'York','CAN',154980), (1834,'Richmond','CAN',148867),
(1835,'Vaughan','CAN',147889), (1836,'Burlington','CAN',145150), (1837,'Oshawa','CAN',140173),
(1838,'Oakville','CAN',139192), (1839,'Saint Catharines','CAN',136216), (1840,'Longueuil','CAN',127977),
(1841,'Richmond Hill','CAN',116428), (1842,'Thunder Bay','CAN',115913), (1843,'Nepean','CAN',115100),
(1844,'Cape Breton','CAN',114733), (1845,'East York','CAN',114034), (1846,'Halifax','CAN',113910),
(1847,'Cambridge','CAN',109186), (1848,'Gloucester','CAN',107314), (1849,'Abbotsford','CAN',105403),
(1850,'Guelph','CAN',103593), (1851,'Saint John�s','CAN',101936), (1852,'Coquitlam','CAN',101820),
(1853,'Saanich','CAN',101388), (1854,'Gatineau','CAN',100702), (1855,'Delta','CAN',95411),
(1856,'Sudbury','CAN',92686), (1857,'Kelowna','CAN',89442), (1858,'Barrie','CAN',89269),
(1890,'Shanghai','CHN',9696300), (1891,'Peking','CHN',7472000), (1892,'Chongqing','CHN',6351600),
(1893,'Tianjin','CHN',5286800), (1894,'Wuhan','CHN',4344600), (1895,'Harbin','CHN',4289800),
(1896,'Shenyang','CHN',4265200), (1897,'Kanton [Guangzhou]','CHN',4256300), (1898,'Chengdu','CHN',3361500),
(1899,'Nanking [Nanjing]','CHN',2870300), (1900,'Changchun','CHN',2812000), (1901,'Xi�an','CHN',2761400),
(1902,'Dalian','CHN',2697000), (1903,'Qingdao','CHN',2596000), (1904,'Jinan','CHN',2278100),
(1905,'Hangzhou','CHN',2190500), (1906,'Zhengzhou','CHN',2107200), (1907,'Shijiazhuang','CHN',2041500),
(1908,'Taiyuan','CHN',1968400), (1909,'Kunming','CHN',1829500), (1910,'Changsha','CHN',1809800),
(1911,'Nanchang','CHN',1691600), (1912,'Fuzhou','CHN',1593800), (1913,'Lanzhou','CHN',1565800),
(1914,'Guiyang','CHN',1465200), (1915,'Ningbo','CHN',1371200), (1916,'Hefei','CHN',1369100),
(1917,'Urumt�i [�r�mqi]','CHN',1310100), (1918,'Anshan','CHN',1200000), (1919,'Fushun','CHN',1200000),
(1920,'Nanning','CHN',1161800), (1921,'Zibo','CHN',1140000), (1922,'Qiqihar','CHN',1070000),
(1923,'Jilin','CHN',1040000), (1924,'Tangshan','CHN',1040000), (1925,'Baotou','CHN',980000),
(1926,'Shenzhen','CHN',950500), (1927,'Hohhot','CHN',916700), (1928,'Handan','CHN',840000),
(1929,'Wuxi','CHN',830000), (1930,'Xuzhou','CHN',810000), (1931,'Datong','CHN',800000),
(1932,'Yichun','CHN',800000), (1933,'Benxi','CHN',770000), (1934,'Luoyang','CHN',760000),
(1935,'Suzhou','CHN',710000), (1936,'Xining','CHN',700200), (1937,'Huainan','CHN',700000),
(1938,'Jixi','CHN',683885), (1939,'Daqing','CHN',660000), (1940,'Fuxin','CHN',640000),
(1941,'Amoy [Xiamen]','CHN',627500), (1942,'Liuzhou','CHN',610000), (1943,'Shantou','CHN',580000),
(1944,'Jinzhou','CHN',570000), (1945,'Mudanjiang','CHN',570000), (1946,'Yinchuan','CHN',544500),
(1947,'Changzhou','CHN',530000), (1948,'Zhangjiakou','CHN',530000), (1949,'Dandong','CHN',520000),
(1950,'Hegang','CHN',520000), (1951,'Kaifeng','CHN',510000), (1952,'Jiamusi','CHN',493409),
(1953,'Liaoyang','CHN',492559), (1954,'Hengyang','CHN',487148), (1955,'Baoding','CHN',483155),
(1956,'Hunjiang','CHN',482043), (1957,'Xinxiang','CHN',473762), (1958,'Huangshi','CHN',457601),
(1959,'Haikou','CHN',454300), (1960,'Yantai','CHN',452127), (1961,'Bengbu','CHN',449245),
(1962,'Xiangtan','CHN',441968), (1963,'Weifang','CHN',428522), (1964,'Wuhu','CHN',425740),
(1965,'Pingxiang','CHN',425579), (1966,'Yingkou','CHN',421589), (1967,'Anyang','CHN',420332),
(1968,'Panzhihua','CHN',415466), (1969,'Pingdingshan','CHN',410775), (1970,'Xiangfan','CHN',410407),
(1971,'Zhuzhou','CHN',409924), (1972,'Jiaozuo','CHN',409100), (1973,'Wenzhou','CHN',401871),
(1974,'Zhangjiang','CHN',400997), (1975,'Zigong','CHN',393184), (1976,'Shuangyashan','CHN',386081),
(1977,'Zaozhuang','CHN',380846), (1978,'Yakeshi','CHN',377869), (1979,'Yichang','CHN',371601),
(1980,'Zhenjiang','CHN',368316), (1981,'Huaibei','CHN',366549), (1982,'Qinhuangdao','CHN',364972),
(1983,'Guilin','CHN',364130), (1984,'Liupanshui','CHN',363954), (1985,'Panjin','CHN',362773),
(1986,'Yangquan','CHN',362268), (1987,'Jinxi','CHN',357052), (1988,'Liaoyuan','CHN',354141),
(1989,'Lianyungang','CHN',354139), (1990,'Xianyang','CHN',352125), (1991,'Tai�an','CHN',350696),
(1992,'Chifeng','CHN',350077), (1993,'Shaoguan','CHN',350043), (1994,'Nantong','CHN',343341),
(1995,'Leshan','CHN',341128), (1996,'Baoji','CHN',337765), (1997,'Linyi','CHN',324720),
(1998,'Tonghua','CHN',324600), (1999,'Siping','CHN',317223), (2000,'Changzhi','CHN',317144),
(2001,'Tengzhou','CHN',315083), (2002,'Chaozhou','CHN',313469), (2003,'Yangzhou','CHN',312892),
(2004,'Dongwan','CHN',308669), (2005,'Ma�anshan','CHN',305421), (2006,'Foshan','CHN',303160),
(2007,'Yueyang','CHN',302800), (2008,'Xingtai','CHN',302789), (2009,'Changde','CHN',301276),
(2010,'Shihezi','CHN',299676), (2011,'Yancheng','CHN',296831), (2012,'Jiujiang','CHN',291187),
(2013,'Dongying','CHN',281728), (2014,'Shashi','CHN',281352), (2015,'Xintai','CHN',281248),
(2016,'Jingdezhen','CHN',281183), (2017,'Tongchuan','CHN',280657), (2018,'Zhongshan','CHN',278829),
(2019,'Shiyan','CHN',273786), (2020,'Tieli','CHN',265683), (2021,'Jining','CHN',265248),
(2022,'Wuhai','CHN',264081), (2023,'Mianyang','CHN',262947), (2024,'Luzhou','CHN',262892),
(2025,'Zunyi','CHN',261862), (2026,'Shizuishan','CHN',257862), (2027,'Neijiang','CHN',256012),
(2028,'Tongliao','CHN',255129), (2029,'Tieling','CHN',254842), (2030,'Wafangdian','CHN',251733),
(2031,'Anqing','CHN',250718), (2032,'Shaoyang','CHN',247227), (2033,'Laiwu','CHN',246833),
(2034,'Chengde','CHN',246799), (2035,'Tianshui','CHN',244974), (2036,'Nanyang','CHN',243303),
(2037,'Cangzhou','CHN',242708), (2038,'Yibin','CHN',241019), (2039,'Huaiyin','CHN',239675),
(2040,'Dunhua','CHN',235100), (2041,'Yanji','CHN',230892), (2042,'Jiangmen','CHN',230587),
(2043,'Tongling','CHN',228017), (2044,'Suihua','CHN',227881), (2045,'Gongziling','CHN',226569),
(2046,'Xiantao','CHN',222884), (2047,'Chaoyang','CHN',222394), (2048,'Ganzhou','CHN',220129),
(2049,'Huzhou','CHN',218071), (2050,'Baicheng','CHN',217987), (2051,'Shangzi','CHN',215373),
(2052,'Yangjiang','CHN',215196), (2053,'Qitaihe','CHN',214957), (2054,'Gejiu','CHN',214294),
(2055,'Jiangyin','CHN',213659), (2056,'Hebi','CHN',212976), (2057,'Jiaxing','CHN',211526),
(2058,'Wuzhou','CHN',210452), (2059,'Meihekou','CHN',209038), (2060,'Xuchang','CHN',208815),
(2061,'Liaocheng','CHN',207844), (2062,'Haicheng','CHN',205560), (2063,'Qianjiang','CHN',205504),
(2064,'Baiyin','CHN',204970), (2065,'Bei�an','CHN',204899), (2066,'Yixing','CHN',200824),
(2067,'Laizhou','CHN',198664), (2068,'Qaramay','CHN',197602), (2069,'Acheng','CHN',197595),
(2070,'Dezhou','CHN',195485), (2071,'Nanping','CHN',195064), (2072,'Zhaoqing','CHN',194784),
(2073,'Beipiao','CHN',194301), (2074,'Fengcheng','CHN',193784), (2075,'Fuyu','CHN',192981),
(2076,'Xinyang','CHN',192509), (2077,'Dongtai','CHN',192247), (2078,'Yuci','CHN',191356),
(2079,'Honghu','CHN',190772), (2080,'Ezhou','CHN',190123), (2081,'Heze','CHN',189293),
(2082,'Daxian','CHN',188101), (2083,'Linfen','CHN',187309), (2084,'Tianmen','CHN',186332),
(2085,'Yiyang','CHN',185818), (2086,'Quanzhou','CHN',185154), (2087,'Rizhao','CHN',185048),
(2088,'Deyang','CHN',182488), (2089,'Guangyuan','CHN',182241), (2090,'Changshu','CHN',181805),
(2091,'Zhangzhou','CHN',181424), (2092,'Hailar','CHN',180650), (2093,'Nanchong','CHN',180273),
(2094,'Jiutai','CHN',180130), (2095,'Zhaodong','CHN',179976), (2096,'Shaoxing','CHN',179818),
(2097,'Fuyang','CHN',179572), (2098,'Maoming','CHN',178683), (2099,'Qujing','CHN',178669),
(2100,'Ghulja','CHN',177193), (2101,'Jiaohe','CHN',176367), (2102,'Puyang','CHN',175988),
(2103,'Huadian','CHN',175873), (2104,'Jiangyou','CHN',175753), (2105,'Qashqar','CHN',174570),
(2106,'Anshun','CHN',174142), (2107,'Fuling','CHN',173878), (2108,'Xinyu','CHN',173524),
(2109,'Hanzhong','CHN',169930), (2110,'Danyang','CHN',169603), (2111,'Chenzhou','CHN',169400),
(2112,'Xiaogan','CHN',166280), (2113,'Shangqiu','CHN',164880), (2114,'Zhuhai','CHN',164747),
(2115,'Qingyuan','CHN',164641), (2116,'Aqsu','CHN',164092), (2117,'Jining','CHN',163552),
(2118,'Xiaoshan','CHN',162930), (2119,'Zaoyang','CHN',162198), (2120,'Xinghua','CHN',161910),
(2121,'Hami','CHN',161315), (2122,'Huizhou','CHN',161023), (2123,'Jinmen','CHN',160794),
(2124,'Sanming','CHN',160691), (2125,'Ulanhot','CHN',159538), (2126,'Korla','CHN',159344),
(2127,'Wanxian','CHN',156823), (2128,'Rui�an','CHN',156468), (2129,'Zhoushan','CHN',156317),
(2130,'Liangcheng','CHN',156307), (2131,'Jiaozhou','CHN',153364), (2132,'Taizhou','CHN',152442),
(2133,'Suzhou','CHN',151862), (2134,'Yichun','CHN',151585), (2135,'Taonan','CHN',150168),
(2136,'Pingdu','CHN',150123), (2137,'Ji�an','CHN',148583), (2138,'Longkou','CHN',148362),
(2139,'Langfang','CHN',148105), (2140,'Zhoukou','CHN',146288), (2141,'Suining','CHN',146086),
(2142,'Yulin','CHN',144467), (2143,'Jinhua','CHN',144280), (2144,'Liu�an','CHN',144248),
(2145,'Shuangcheng','CHN',142659), (2146,'Suizhou','CHN',142302), (2147,'Ankang','CHN',142170),
(2148,'Weinan','CHN',140169), (2149,'Longjing','CHN',139417), (2150,'Da�an','CHN',138963),
(2151,'Lengshuijiang','CHN',137994), (2152,'Laiyang','CHN',137080), (2153,'Xianning','CHN',136811),
(2154,'Dali','CHN',136554), (2155,'Anda','CHN',136446), (2156,'Jincheng','CHN',136396),
(2157,'Longyan','CHN',134481), (2158,'Xichang','CHN',134419), (2159,'Wendeng','CHN',133910),
(2160,'Hailun','CHN',133565), (2161,'Binzhou','CHN',133555), (2162,'Linhe','CHN',133183),
(2163,'Wuwei','CHN',133101), (2164,'Duyun','CHN',132971), (2165,'Mishan','CHN',132744),
(2166,'Shangrao','CHN',132455), (2167,'Changji','CHN',132260), (2168,'Meixian','CHN',132156),
(2169,'Yushu','CHN',131861), (2170,'Tiefa','CHN',131807), (2171,'Huai�an','CHN',131149),
(2172,'Leiyang','CHN',130115), (2173,'Zalantun','CHN',130031), (2174,'Weihai','CHN',128888),
(2175,'Loudi','CHN',128418), (2176,'Qingzhou','CHN',128258), (2177,'Qidong','CHN',126872),
(2178,'Huaihua','CHN',126785), (2179,'Luohe','CHN',126438), (2180,'Chuzhou','CHN',125341),
(2181,'Kaiyuan','CHN',124219), (2182,'Linqing','CHN',123958), (2183,'Chaohu','CHN',123676),
(2184,'Laohekou','CHN',123366), (2185,'Dujiangyan','CHN',123357), (2186,'Zhumadian','CHN',123232),
(2187,'Linchuan','CHN',121949), (2188,'Jiaonan','CHN',121397), (2189,'Sanmenxia','CHN',120523),
(2190,'Heyuan','CHN',120101), (2191,'Manzhouli','CHN',120023), (2192,'Lhasa','CHN',120000),
(2193,'Lianyuan','CHN',118858), (2194,'Kuytun','CHN',118553), (2195,'Puqi','CHN',117264),
(2196,'Hongjiang','CHN',116188), (2197,'Qinzhou','CHN',114586), (2198,'Renqiu','CHN',114256),
(2199,'Yuyao','CHN',114065), (2200,'Guigang','CHN',114025), (2201,'Kaili','CHN',113958),
(2202,'Yan�an','CHN',113277), (2203,'Beihai','CHN',112673), (2204,'Xuangzhou','CHN',112673),
(2205,'Quzhou','CHN',112373), (2206,'Yong�an','CHN',111762), (2207,'Zixing','CHN',110048),
(2208,'Liyang','CHN',109520), (2209,'Yizheng','CHN',109268), (2210,'Yumen','CHN',109234),
(2211,'Liling','CHN',108504), (2212,'Yuncheng','CHN',108359), (2213,'Shanwei','CHN',107847),
(2214,'Cixi','CHN',107329), (2215,'Yuanjiang','CHN',107004), (2216,'Bozhou','CHN',106346),
(2217,'Jinchang','CHN',105287), (2218,'Fu�an','CHN',105265), (2219,'Suqian','CHN',105021),
(2220,'Shishou','CHN',104571), (2221,'Hengshui','CHN',104269), (2222,'Danjiangkou','CHN',103211),
(2223,'Fujin','CHN',103104), (2224,'Sanya','CHN',102820), (2225,'Guangshui','CHN',102770),
(2226,'Huangshan','CHN',102628), (2227,'Xingcheng','CHN',102384), (2228,'Zhucheng','CHN',102134),
(2229,'Kunshan','CHN',102052), (2230,'Haining','CHN',100478), (2231,'Pingliang','CHN',99265),
(2232,'Fuqing','CHN',99193), (2233,'Xinzhou','CHN',98667), (2234,'Jieyang','CHN',98531),
(2235,'Zhangjiagang','CHN',97994), (2236,'Tong Xian','CHN',97168), (2237,'Ya�an','CHN',95900),
(2238,'Jinzhou','CHN',95761), (2239,'Emeishan','CHN',94000), (2240,'Enshi','CHN',93056),
(2241,'Bose','CHN',93009), (2242,'Yuzhou','CHN',92889), (2243,'Kaiyuan','CHN',91999),
(2244,'Tumen','CHN',91471), (2245,'Putian','CHN',91030), (2246,'Linhai','CHN',90870),
(2247,'Xilin Hot','CHN',90646), (2248,'Shaowu','CHN',90286), (2249,'Junan','CHN',90222),
(2250,'Huaying','CHN',89400), (2251,'Pingyi','CHN',89373), (2252,'Huangyan','CHN',89288),
(2413,'La Habana','CUB',2256000), (2414,'Santiago de Cuba','CUB',433180), (2415,'Camag�ey','CUB',298726),
(2416,'Holgu�n','CUB',249492), (2417,'Santa Clara','CUB',207350), (2418,'Guant�namo','CUB',205078),
(2419,'Pinar del R�o','CUB',142100), (2420,'Bayamo','CUB',141000), (2421,'Cienfuegos','CUB',132770),
(2422,'Victoria de las Tunas','CUB',132350), (2423,'Matanzas','CUB',123273), (2424,'Manzanillo','CUB',109350),
(2425,'Sancti-Sp�ritus','CUB',100751), (2426,'Ciego de �vila','CUB',98505), (2430,'Nicosia','CYP',195000),
(2431,'Limassol','CYP',154400), (3245,'Z�rich','CHE',336800), (3246,'Geneve','CHE',173500),
(3247,'Basel','CHE',166700), (3248,'Bern','CHE',122700), (3249,'Lausanne','CHE',114500),
(3339,'Praha','CZE',1181126), (3340,'Brno','CZE',381862), (3341,'Ostrava','CZE',320041),
(3342,'Plzen','CZE',166759), (3343,'Olomouc','CZE',102702), (3344,'Liberec','CZE',99155),
(3345,'Cesk� Budejovice','CZE',98186), (3346,'Hradec Kr�lov�','CZE',98080), (3347,'�st� nad Labem','CZE',95491),
(3348,'Pardubice','CZE',91309), (3520,'Minsk','BLR',1674000), (3521,'Gomel','BLR',475000),
(3522,'Mogiljov','BLR',356000), (3523,'Vitebsk','BLR',340000), (3524,'Grodno','BLR',302000),
(3525,'Brest','BLR',286000), (3526,'Bobruisk','BLR',221000), (3527,'Baranovit�i','BLR',167000),
(3528,'Borisov','BLR',151000), (3529,'Pinsk','BLR',130000), (3530,'Or�a','BLR',124000),
(3531,'Mozyr','BLR',110000), (3532,'Novopolotsk','BLR',106000), (3533,'Lida','BLR',101000),
(3534,'Soligorsk','BLR',101000), (3535,'Molodet�no','BLR',97000);
insert into t1 (Code, Name, Continent) values
('AFG','Afghanistan','Asia'), ('ANT','Netherlands Antilles','North America'),
('ALB','Albania','Europe'), ('AND','Andorra','Europe'),
('AIA','Anguilla','North America'), ('ATG','Antigua and Barbuda','North America'),
('ARE','United Arab Emirates','Asia'), ('ARM','Armenia','Asia'),
('ABW','Aruba','North America'), ('AZE','Azerbaijan','Asia'),
('BHS','Bahamas','North America'), ('BHR','Bahrain','Asia'),
('BGD','Bangladesh','Asia'), ('BRB','Barbados','North America'),
('BEL','Belgium','Europe'), ('BLZ','Belize','North America'),
('BMU','Bermuda','North America'), ('BTN','Bhutan','Asia'),
('BIH','Bosnia and Herzegovina','Europe'), ('BRN','Brunei','Asia'),
('BGR','Bulgaria','Europe'), ('CYM','Cayman Islands','North America'),
('CRI','Costa Rica','North America'), ('AUT','Austria','Europe'),
('CAN','Canada','North America'), ('CHN','China','Asia'),
('CUB','Cuba','North America'), ('CYP','Cyprus','Asia'),
('CHE','Switzerland','Europe'), ('CZE','Czech Republic','Europe'),
('BLR','Belarus','Europe');
update t2 set population=6000000 where Name in ('Wien', 'Vancouver', 'Praha');
--enable_query_log
--echo This must not use LooseScan:
EXPLAIN SELECT Name FROM t1
WHERE t1.Code IN (
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
SELECT Name FROM t1
WHERE t1.Code IN (
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
drop table t1, t2;
#
# BUG#35160 "Subquery optimization: table pullout is not reflected in EXPLAIN EXTENDED"
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4);
create table t1 (a int, b int, key(a));
insert into t1 select a,a from t0;
create table t2 (a int, b int, primary key(a));
insert into t2 select * from t1;
# Table t2 should be pulled out because t2.a=t0.a equality
--echo Table t2, unlike table t1, should be displayed as pulled out
explain extended select * from t0
where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
t1.b=t2.b);
#
# BUG#46556 "Returning incorrect, empty results for some IN subqueries
# w/ semijoin=on"
#
# The above query did not have a valid plan before the fix of BUG#46556.
# Add some data that would cause wrong result with the old plan.
update t1 set a=3, b=11 where a=4;
update t2 set b=11 where a=3;
# the query just below may exhibit BUG#49129:
create temporary table tmp select * from t0 where t0.a in
(select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b);
# detect it and warn in result file if it's the case:
create temporary table tmp_as_ref (a int);
insert into tmp_as_ref values(0),(1),(2),(3); # correct desired result
if (`select count(*) from tmp_as_ref left join tmp on tmp.a=tmp_as_ref.a
where tmp.a is null`)
{
--echo
--echo # The result below is wrong due to Bug#49129
}