diff options
Diffstat (limited to 'mysql-test/r/innodb_icp.result')
| -rw-r--r-- | mysql-test/r/innodb_icp.result | 109 |
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; |