summaryrefslogtreecommitdiff
path: root/mysql-test/r/innodb_icp.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/innodb_icp.result')
-rw-r--r--mysql-test/r/innodb_icp.result109
1 files changed, 108 insertions, 1 deletions
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index b3b59bcdea6..08238289330 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -412,6 +412,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 3 Using where; Using index
DROP TABLE t1;
#
+#
# Bug#59259 "Incorrect rows returned for a correlated subquery
# when ICP is on"
#
@@ -430,8 +431,8 @@ SELECT * FROM t1
WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
-2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index
2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition
+2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join)
SELECT * FROM t1
WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10);
pk i
@@ -701,5 +702,111 @@ SET SESSION optimizer_switch='index_condition_pushdown=on';
SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
a
DROP TABLE t1;
+#
+# Bug#904832: an attempt to perform an index condition pushdown
+# of a condition containing a subquery
+#
+CREATE TABLE t1 (
+a INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
+b INT, c INT, d DATE NOT NULL, e VARCHAR(1),
+KEY (c), KEY (d), KEY k2(b), KEY k3(b), KEY k4(b)
+);
+INSERT INTO t1 (b,c,d,e) VALUES
+(6,5,'2006-05-25','y'),(1,5,'2008-01-23','t'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
+(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
+(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
+(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','f'),
+(4,5,'2001-06-05','x'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
+(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
+(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(6,5,'2007-06-18','d'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
+(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
+(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
+(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
+(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
+(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
+(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
+(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
+(6,5,'2007-06-18','d'),(4,1,'1900-01-01','r'),
+(8,8,'1900-01-01','m'),(4,1,'2006-03-09','b'),
+(4,1,'2001-06-05','x'),(7,1,'2006-05-28','g');
+CREATE TABLE t2 (
+f INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
+g INT,
+h VARCHAR(1),
+KEY (g)
+);
+INSERT INTO t2 (g,h) VALUES
+(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
+(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),
+(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),
+(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),
+(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
+(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
+(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
+(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
+(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
+(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
+(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),(0,'p'),
+(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
+(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
+(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
+(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
+(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),
+(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
+(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(7,'d'),
+(7,'f'),(5,'j'),(3,'e'),(1,'u'),(0,'f'),(0,'p'),
+(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
+(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
+(0,'f'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
+(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
+(1,'x'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),
+(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),
+(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
+(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
+(5,'l'),(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),
+(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
+(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
+(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u');
+SET @save_optimize_switch=@@optimizer_switch;
+SET optimizer_switch='materialization=on';
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS t, t2
+WHERE c = g
+AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b)
+OR a = 0 AND h < 'z' );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where
+1 PRIMARY t2 ref g g 5 test.t.c 9 Using where
+2 DEPENDENT SUBQUERY t1 index PRIMARY d 3 NULL 64 Using where; Using index
+2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index condition; Using where
+SELECT COUNT(*) FROM t1 AS t, t2
+WHERE c = g
+AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b)
+OR a = 0 AND h < 'z' );
+COUNT(*)
+1478
+SELECT COUNT(*) FROM t1 AS t, t2
+WHERE c = g
+AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b)
+OR a = 0 AND h < 'z' );
+COUNT(*)
+1478
+SET optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1,t2;
set optimizer_switch=@innodb_icp_tmp;
set storage_engine= @save_storage_engine;