#At file:///home/spetrunia/dev/mysql-6.0-exists2in/ based on revid:sergefp@stripped
2729 Sergey Petrunia 2009-03-16
Post-merge fixes
modified:
mysql-test/r/subselect_sj.result
mysql-test/r/subselect_sj2_jcl6.result
mysql-test/r/subselect_sj_jcl6.result
per-file messages:
mysql-test/r/subselect_sj.result
Post-merge fixes
mysql-test/r/subselect_sj2_jcl6.result
Post-merge fixes
mysql-test/r/subselect_sj_jcl6.result
Post-merge fixes
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2009-03-16 12:03:12 +0000
+++ b/mysql-test/r/subselect_sj.result 2009-03-16 13:13:12 +0000
@@ -405,8 +405,9 @@ ON ( INNR2 . pk <= INNR . int_key )
)
AND OUTR . int_key > 218 ;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+1 PRIMARY OUTR range int_key int_key 5 NULL 1 Using index condition; Using MRR
+2 SUBQUERY INNR system NULL NULL NULL NULL 0 const row not found
+2 SUBQUERY INNR2 index NULL PRIMARY 4 NULL 10 Using index
drop table t1, t2;
#
# BUG#42259: SELECT WHERE IN performs poorly (slow) with views and functions
=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result 2009-03-16 12:03:12 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result 2009-03-16 13:13:12 +0000
@@ -684,6 +684,122 @@ select count(a) from t2 where a in ( SEL
count(a)
1000
drop table t0,t1,t2,t3;
+#
+# BUG38010: Wrong result returned when outer join, semijoin and "use index"
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) DEFAULT NULL,
+varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,5,5,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-04-12 00:00:00','2009-04-12 00:00:00','o','o');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) DEFAULT NULL,
+varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,6,6,'0000-00-00','0000-00-00','11:01:28','11:01:28','2007-08-09 06:23:37','2007-08-09 06:23:37','c','c');
+CREATE TABLE C (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) DEFAULT NULL,
+varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO C VALUES
+(1,5,5,'2003-12-26','2003-12-26','08:05:38','08:05:38','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(2,5,5,'0000-00-00','0000-00-00','15:34:08','15:34:08','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(3,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-09-14 08:36:26','2009-09-14 08:36:26',NULL,NULL),
+(4,6,6,'2006-03-07','2006-03-07','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),
+(5,0,0,'2006-07-08','2006-07-08','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(6,0,0,'2006-11-21','2006-11-21','23:58:52','23:58:52','2005-12-02 14:27:44','2005-12-02 14:27:44',NULL,NULL),
+(7,3,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(8,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),
+(9,5,5,'2002-09-23','2002-09-23','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(10,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z');
+CREATE TABLE t3 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) DEFAULT NULL,
+varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES
+(1,3,3,'2005-07-18','2005-07-18','15:58:39','15:58:39','2004-03-27 03:22:05','2004-03-27 03:22:05',NULL,NULL),
+(2,7,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2008-02-28 20:02:09','2008-02-28 20:02:09','x','x'),
+(3,0,0,'2004-07-05','2004-07-05','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','u','u'),
+(4,9,9,'2007-09-06','2007-09-06','12:29:03','12:29:03','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(5,7,7,'2005-01-24','2005-01-24','12:37:03','12:37:03','2006-07-26 00:00:00','2006-07-26 00:00:00','{','{'),
+(6,5,5,'2003-03-07','2003-03-07','04:13:56','04:13:56','2007-06-15 23:13:40','2007-06-15 23:13:40','i','i'),
+(7,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(8,2,2,'2004-08-28','2004-08-28','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','i','i'),
+(9,4,4,'0000-00-00','0000-00-00','22:27:41','22:27:41','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),
+(10,2,2,'2001-06-22','2001-06-22','15:27:12','15:27:12','2001-08-25 20:44:07','2001-08-25 20:44:07','t','t');
+SELECT OUTR . int_key
+FROM t1 AS OUTR2
+LEFT JOIN C AS OUTR
+ON ( OUTR2 . varchar_nokey > OUTR . varchar_key )
+WHERE OUTR . pk IN (
+SELECT INNR . int_key AS Y
+FROM t2 AS INNR2
+LEFT JOIN t3 AS INNR
+ON ( INNR2 . date_nokey >= INNR . date_key )
+);
+int_key
+6
+drop table t1,t2,t3;
set join_cache_level=default;
show variables like 'join_cache_level';
Variable_name Value
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2009-03-16 12:03:12 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2009-03-16 13:13:12 +0000
@@ -409,8 +409,9 @@ ON ( INNR2 . pk <= INNR . int_key )
)
AND OUTR . int_key > 218 ;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+1 PRIMARY OUTR range int_key int_key 5 NULL 1 Using index condition; Using MRR
+2 SUBQUERY INNR system NULL NULL NULL NULL 0 const row not found
+2 SUBQUERY INNR2 index NULL PRIMARY 4 NULL 10 Using index
drop table t1, t2;
#
# BUG#42259: SELECT WHERE IN performs poorly (slow) with views and functions
@@ -435,6 +436,81 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; End materialize; Using join buffer
drop table t0, t1, t2;
drop view v1;
+#
+# BUG#38075: Wrong result: rows matching a subquery with outer join not returned
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+);
+INSERT INTO t1 VALUES
+(1,5,5,'2003-12-26','2003-12-26','08:05:38','08:05:38','0000-00-00 00:00:00','0000-00-00 00:00:00','',''),
+(2,5,5,'0000-00-00','0000-00-00','15:34:08','15:34:08','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(3,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-09-14 08:36:26','2009-09-14 08:36:26','',''),
+(4,6,6,'2006-03-07','2006-03-07','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),
+(5,0,0,'2006-07-08','2006-07-08','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(6,0,0,'2006-11-21','2006-11-21','23:58:52','23:58:52','2005-12-02 14:27:44','2005-12-02 14:27:44','',''),
+(7,3,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(8,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),
+(9,5,5,'2002-09-23','2002-09-23','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(10,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,5,5,'0000-00-00','0000-00-00','22:51:38','22:51:38','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'),
+(2,1,1,'2007-08-24','2007-08-24','07:26:48','07:26:48','2000-11-04 00:00:00','2000-11-04 00:00:00','{','{'),
+(3,1,1,'2009-04-12','2009-04-12','08:19:28','08:19:28','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'),
+(4,8,8,'2009-12-04','2009-12-04','00:00:00','00:00:00','2004-02-24 17:40:14','2004-02-24 17:40:14','x','x'),
+(5,7,7,'2000-09-17','2000-09-17','15:23:34','15:23:34','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'),
+(6,3,3,'0000-00-00','0000-00-00','08:52:49','08:52:49','2008-06-29 15:03:33','2008-06-29 15:03:33','p','p'),
+(7,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2005-04-06 00:00:00','2005-04-06 00:00:00','c','c'),
+(8,0,0,'0000-00-00','0000-00-00','15:10:28','15:10:28','2006-05-01 16:00:17','2006-05-01 16:00:17','k','k'),
+(9,6,6,'0000-00-00','0000-00-00','08:05:45','08:05:45','2002-09-17 00:00:00','2002-09-17 00:00:00','t','t'),
+(10,0,0,'0000-00-00','0000-00-00','19:46:39','19:46:39','0000-00-00 00:00:00','0000-00-00 00:00:00','c','c');
+SELECT OUTR . pk AS X, OUTR . varchar_nokey
+FROM t1 AS OUTR
+WHERE OUTR . varchar_nokey IN (
+SELECT INNR . varchar_nokey AS X
+FROM t2 AS INNR2 LEFT JOIN
+t2 AS INNR ON ( INNR2 . int_nokey = INNR . pk )
+);
+X varchar_nokey
+2 p
+7 p
+10 z
+drop table t1, t2;
set join_cache_level=default;
show variables like 'join_cache_level';
Variable_name Value
| Thread |
|---|
| • bzr commit into mysql-6.0-exists2in branch (sergefp:2729) | Sergey Petrunia | 16 Mar |
