Fixed LP bug #904832.

Do not perform index condition pushdown for conditions containing subqueries
and stored functions.

15 files changed, 457 insertions, 11 deletions

diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc
index ea4fc6439d2..e77cb220375 100644
--- a/mysql-test/include/icp_tests.inc
+++ b/mysql-test/include/icp_tests.inc
@@ -438,6 +438,7 @@ LIMIT 1;
 DROP TABLE t1;
 
 --echo #
+--echo #
 --echo # Bug#59259 "Incorrect rows returned for a correlated subquery
 --echo #            when ICP is on"
 --echo #
@@ -744,3 +745,110 @@ SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
 DROP TABLE t1;
 
 
+--echo #
+--echo # Bug#904832: an attempt to perform an index condition pushdown  
+--echo #             of a condition containing a subquery
+--echo #
+
+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' );
+
+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' );
+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' );
+
+SET optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1,t2;
+
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index b3b59bcdea6..5046b7b4b8b 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"
 #
@@ -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;
diff --git a/mysql-test/r/maria_icp.result b/mysql-test/r/maria_icp.result
index 2d755daf0d6..c0275d41d43 100644
--- a/mysql-test/r/maria_icp.result
+++ b/mysql-test/r/maria_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	4	Using where
 DROP TABLE t1;
 #
+#
 # Bug#59259 "Incorrect rows returned for a correlated subquery
 #            when ICP is on"
 #
@@ -707,5 +708,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	19	Using where
+2	DEPENDENT SUBQUERY	t1	index	PRIMARY	PRIMARY	4	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 storage_engine= @save_storage_engine;
 set optimizer_switch=@maria_icp_tmp;
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 753db6d6fc3..68d0ce4e381 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -410,6 +410,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY,k1	k1	5	NULL	4	Using where
 DROP TABLE t1;
 #
+#
 # Bug#59259 "Incorrect rows returned for a correlated subquery
 #            when ICP is on"
 #
@@ -705,6 +706,112 @@ 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	19	Using where
+2	DEPENDENT SUBQUERY	t1	index	PRIMARY	PRIMARY	4	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;
 drop table if exists t0, t1, t1i, t1m;
 #
 # BUG#826935 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 58a4e61ce0b..8008d887823 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1374,7 +1374,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using index condition
+1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index a1501099d72..51f89fd2a51 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -713,7 +713,7 @@ WHERE f3 = (
 SELECT t1.f3 FROM t1
 WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 ));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	f3	f3	5	const	0	Using index condition
+1	PRIMARY	t1	ref	f3	f3	5	const	0	Using where
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	5	test.t1.f10	1	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary
@@ -728,7 +728,7 @@ WHERE f3 = (
 SELECT f3 FROM t1
 WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 ));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	f3	f3	5	const	0	Using index condition
+1	PRIMARY	t1	ref	f3	f3	5	const	0	Using where
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	10	test.t1.f10,test.t1.f10	1	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary
@@ -1790,7 +1790,7 @@ WHERE t2.f2 = (SELECT f2 FROM t3
 WHERE EXISTS (SELECT DISTINCT f1 FROM t4))
 AND t2.f2 = t1.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	f1	f1	5	const	0	Using index condition
+1	PRIMARY	t1	ref	f1	f1	5	const	0	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	
 3	SUBQUERY	t4	index	NULL	f1	5	NULL	2	Using index; Using temporary
@@ -1807,7 +1807,7 @@ WHERE t2.f2 = (SELECT f2 FROM t3
 WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1)
 AND t2.f2 = t1.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	f1	f1	5	const	0	Using index condition
+1	PRIMARY	t1	ref	f1	f1	5	const	0	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	
 3	SUBQUERY	t4	index	NULL	f1	5	NULL	2	Using index; Using temporary
diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result
index 003d7b0231b..3f465f1a7d0 100644
--- a/mysql-test/r/subselect_mat_cost.result
+++ b/mysql-test/r/subselect_mat_cost.result
@@ -247,7 +247,7 @@ AND CountryLanguage.Language = 'French'
   AND Code = Country;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	CountryLanguage	ref	PRIMARY,Language	Language	30	const	20	Using index condition
-1	PRIMARY	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using index condition
+1	PRIMARY	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 2	DEPENDENT SUBQUERY	CountryLanguage	unique_subquery	PRIMARY,Language	PRIMARY	33	func,const	1	Using index; Using where
 SELECT Country.Name
 FROM Country, CountryLanguage 
@@ -286,7 +286,7 @@ AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish'
 AND Code = Country;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	CountryLanguage	range	PRIMARY,Language	Language	30	NULL	45	Using index condition; Using where; Rowid-ordered scan
-1	PRIMARY	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using index condition
+1	PRIMARY	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
 2	MATERIALIZED	CountryLanguage	ref	PRIMARY,Language	Language	30	const	47	Using index condition
 SELECT Country.Name
 FROM Country, CountryLanguage 
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 6d9cdc08572..59d108dc5c0 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -1381,7 +1381,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using index condition
+1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 53ac091b722..d4c3694f2c6 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -1377,7 +1377,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using index condition
+1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index e3ad1397c02..aac4ce6e39d 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -1380,7 +1380,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using index condition
+1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index d5c6af1640a..3647e2fe2b3 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -1377,7 +1377,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using index condition
+1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/sql/item.h b/sql/item.h
index fb6421c23b2..d9aa6f3497e 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1024,6 +1024,10 @@ public:
   virtual bool eval_not_null_tables(uchar *opt_arg) { return 0; }
   virtual bool clear_sum_processor(uchar *opt_arg) { return 0; }
   virtual bool is_subquery_processor (uchar *opt_arg) { return 0; }
+  virtual bool limit_index_condition_pushdown_processor(uchar *opt_arg)
+  { 
+    return FALSE;
+  }
 
   /* To call bool function for all arguments */
   struct bool_func_call_args
diff --git a/sql/item_func.h b/sql/item_func.h
index bf4dd240253..54635bf21f7 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1870,6 +1870,10 @@ public:
   {
     return trace_unsupported_by_check_vcol_func_processor(func_name());
   }
+  bool limit_index_condition_pushdown_processor(uchar *opt_arg)
+  {
+    return TRUE;
+  }
 };
 
 
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 60ca851c881..79044ccf3f7 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -229,6 +229,10 @@ public:
   virtual bool expr_cache_is_needed(THD *);
   virtual void get_cache_parameters(List<Item> &parameters);
   virtual bool is_subquery_processor (uchar *opt_arg) { return 1; }
+  bool limit_index_condition_pushdown_processor(uchar *opt_arg) 
+  {
+    return TRUE;
+   }
 
   friend class select_result_interceptor;
   friend class Item_in_optimizer;
diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc
index 3a9c813b93c..5240267b4ac 100644
--- a/sql/opt_index_cond_pushdown.cc
+++ b/sql/opt_index_cond_pushdown.cc
@@ -29,6 +29,11 @@
 bool uses_index_fields_only(Item *item, TABLE *tbl, uint keyno,
                             bool other_tbls_ok)
 {
+  if (item->walk(&Item::limit_index_condition_pushdown_processor, FALSE, NULL))
+  {
+    return FALSE;
+  }
+
   if (item->const_item())
     return TRUE;