From 74e523d259552645067155e2acb87e30d6068d87 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 28 Jul 2005 13:31:15 -0700 Subject: sql_select.cc: Fixed bug #12144. Added an optimization that avoids key access with null keys for the 'ref' method when used in outer joins. The regilar optimization with adding IS NOT NULL expressions is not applied for outer join on expressions as the predicates of these expressions are not pushed down in 4.1. null_key.result, null_key.test: Added a test case for bug #12144. mysql-test/t/null_key.test: Added a test case for bug #12144. mysql-test/r/null_key.result: Added a test case for bug #12144. sql/sql_select.cc: Fixed bug #12144. Added an optimization that avoids key access with null keys for the 'ref' method when used in outer joins. The regilar optimization with adding IS NOT NULL expressions is not applied for outer join on expressions as the predicates of these expressions are not pushed down in 4.1. --- mysql-test/t/null_key.test | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) (limited to 'mysql-test/t/null_key.test') diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test index 9b346a181bf..0eff53ac371 100644 --- a/mysql-test/t/null_key.test +++ b/mysql-test/t/null_key.test @@ -191,3 +191,29 @@ select * from t1 where id2 is null or id2 > 0; delete from t1 where id <=> NULL; select * from t1; drop table t1; + +# +# Test for bug #12144: optimizations for key access with null keys +# used for outer joins +# + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int, b int, INDEX idx(a)); +CREATE TABLE t3 (b int, INDEX idx(b)); +INSERT INTO t1 VALUES (1), (2), (3), (4); +INSERT INTO t2 VALUES (1, 1), (3, 1); +INSERT INTO t3 VALUES + (NULL), (NULL), (NULL), (NULL), (NULL), + (NULL), (NULL), (NULL), (NULL), (NULL), + (2); +ANALYZE table t1, t2, t3; + +EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a + LEFT JOIN t3 ON t2.b=t3.b; + +SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a + LEFT JOIN t3 ON t2.b=t3.b; + +SELECT FOUND_ROWS(); + +DROP TABLE t1,t2,t3; -- cgit v1.2.1 From a54ddda4c826a4142f9fb779a58577528f525d73 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 28 Jul 2005 16:04:47 -0700 Subject: null_key.test, null_key.result: Modified the test case for patch of the bug #12144 to display status of Handler_read_next before and after the tested query. mysql-test/r/null_key.result: Modified the test case for patch of the bug #12144 to display status of Handler_read_next before and after the tested query. mysql-test/t/null_key.test: Modified the test case for patch of the bug #12144 to display status of Handler_read_next before and after the tested query. --- mysql-test/t/null_key.test | 30 +++++++++++++++++++++++++++--- 1 file changed, 27 insertions(+), 3 deletions(-) (limited to 'mysql-test/t/null_key.test') diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test index 4952d6ac8e2..228b00d528a 100644 --- a/mysql-test/t/null_key.test +++ b/mysql-test/t/null_key.test @@ -200,21 +200,45 @@ drop table t1; CREATE TABLE t1 (a int); CREATE TABLE t2 (a int, b int, INDEX idx(a)); CREATE TABLE t3 (b int, INDEX idx(b)); +CREATE TABLE t4 (b int, INDEX idx(b)); INSERT INTO t1 VALUES (1), (2), (3), (4); INSERT INTO t2 VALUES (1, 1), (3, 1); INSERT INTO t3 VALUES (NULL), (NULL), (NULL), (NULL), (NULL), - (NULL), (NULL), (NULL), (NULL), (NULL), - (2); + (NULL), (NULL), (NULL), (NULL), (NULL); +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t3 VALUES (2), (3); + ANALYZE table t1, t2, t3; +SELECT COUNT(*) FROM t3; + EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b; +SHOW STATUS LIKE "handler_read%"; + SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b; +SHOW STATUS LIKE "handler_read%"; + SELECT FOUND_ROWS(); -DROP TABLE t1,t2,t3; +DROP TABLE t1,t2,t3,t4; # End of 4.1 tests -- cgit v1.2.1 From 0ad1836d23d67e13c88b2c45d253844303503cc0 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 28 Jul 2005 17:53:51 -0700 Subject: null_key.test, null_key.result: Made the test case for bug #12144 independent on other tests. mysql-test/r/null_key.result: Made the test case for bug #12144 independent on other tests. mysql-test/t/null_key.test: Made the test case for bug #12144 independent on other tests. --- mysql-test/t/null_key.test | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) (limited to 'mysql-test/t/null_key.test') diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test index 228b00d528a..bd37a031745 100644 --- a/mysql-test/t/null_key.test +++ b/mysql-test/t/null_key.test @@ -230,14 +230,10 @@ SELECT COUNT(*) FROM t3; EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b; -SHOW STATUS LIKE "handler_read%"; - - +FLUSH STATUS ; SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b; - SHOW STATUS LIKE "handler_read%"; - SELECT FOUND_ROWS(); DROP TABLE t1,t2,t3,t4; -- cgit v1.2.1