From 8feff690b6d1f09acb6a68315669e75924da5f80 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 11 Oct 2011 02:36:08 -0700 Subject: Fixed LP bug #870046. This bug is a consequence of the fix in the function add_ref_to_table_cond for LP bug 826935 that turned out to be not quite correct: it tried to AND the same generated condition with two different other conditions. This patch creates a copy of the generated condition if the condition needs to be ANDed with two different items. --- mysql-test/t/myisam_icp.test | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test/t/myisam_icp.test') diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test index 66ffbfa3821..7bb984d815a 100644 --- a/mysql-test/t/myisam_icp.test +++ b/mysql-test/t/myisam_icp.test @@ -209,4 +209,22 @@ INSERT INTO t1 VALUES SELECT b FROM t1 WHERE a != 1 AND c IS NULL ORDER BY 1; DROP TABLE t1; +--echo # +--echo # Bug#870046: ICP for a GROUP BY query +--echo # + +CREATE TABLE t1 (a int, b varchar(1), c varchar(1), INDEX idx(b)); +INSERT INTO t1 VALUES (2,'x','x'), (5,'x','y'); + +SET SESSION optimizer_switch='index_condition_pushdown=off'; +EXPLAIN +SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; +SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; +SET SESSION optimizer_switch='index_condition_pushdown=on'; +EXPLAIN +SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; +SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; + +DROP TABLE t1; + set optimizer_switch=@myisam_icp_tmp; -- cgit v1.2.1 From 3694bb90a47feb463c5a38543650f0f53d1735eb Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 28 Oct 2011 12:38:36 +0400 Subject: - Let t/myisam_icp.test run include/icp_tests.inc with MRR/ICP turned ON (not OFF) - Fix the compile-time-default value of optimizer_switch printed by mysqld --help --defaults --- mysql-test/t/myisam_icp.test | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/t/myisam_icp.test') diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test index 7bb984d815a..7e7affb1b8f 100644 --- a/mysql-test/t/myisam_icp.test +++ b/mysql-test/t/myisam_icp.test @@ -2,11 +2,11 @@ # ICP/MyISAM tests (Index Condition Pushdown) # ---source include/icp_tests.inc - set @myisam_icp_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +--source include/icp_tests.inc + --disable_warnings drop table if exists t0, t1, t1i, t1m; --enable_warnings -- cgit v1.2.1 From 7c7611d728d393dcc39bfd73ab5b3d56491ff9e7 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 7 Nov 2011 23:30:03 +0400 Subject: BUG#887026: Wrong result with ICP, outer join, subquery in maria-5.3-icp - Do not push index condition if we're using a triggered ref access. --- mysql-test/t/myisam_icp.test | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) (limited to 'mysql-test/t/myisam_icp.test') diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test index 7e7affb1b8f..bbff6c30e56 100644 --- a/mysql-test/t/myisam_icp.test +++ b/mysql-test/t/myisam_icp.test @@ -227,4 +227,34 @@ SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; DROP TABLE t1; +--echo # +--echo # BUG#887026: Wrong result with ICP, outer join, subquery in maria-5.3-icp +--echo # + +CREATE TABLE t1 (c varchar(1)); +INSERT INTO t1 VALUES ('c'), ('c'); + +CREATE TABLE t2 (c varchar(1), b int); +INSERT INTO t2 VALUES ('d', NULL),('d', NULL); + +CREATE TABLE t3 (c varchar(1)); +INSERT INTO t3 VALUES ('c'); +INSERT INTO t3 VALUES ('c'); + +CREATE TABLE t4 ( b int, c varchar(1), KEY (b)); +INSERT INTO t4 VALUES (7,'c'); +INSERT INTO t4 VALUES (7,'c'); + +--echo # Must be t1,t2,t3,t4, with t4 having Full-scan-on-NULL but not Using index condition +explain +SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b +WHERE + t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b +WHERE + t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); + +DROP TABLE t1,t2,t3,t4; + set optimizer_switch=@myisam_icp_tmp; -- cgit v1.2.1