diff options
| author | unknown <igor@rurik.mysql.com> | 2005-07-28 13:31:15 -0700 |
|---|---|---|
| committer | unknown <igor@rurik.mysql.com> | 2005-07-28 13:31:15 -0700 |
| commit | 74e523d259552645067155e2acb87e30d6068d87 (patch) | |
| tree | f26d5705c46719ad91002f10611dc1c6693f2162 /mysql-test/r | |
| parent | bdc0c6719569e9a5224105c4a15d24d416f36017 (diff) | |
| download | mariadb-git-74e523d259552645067155e2acb87e30d6068d87.tar.gz | |
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.
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/null_key.result | 31 |
1 files changed, 31 insertions, 0 deletions
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index 4b7400e5e60..9f11a0129cd 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -364,3 +364,34 @@ select * from t1; id id2 1 1 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)); +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; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a +LEFT JOIN t3 ON t2.b=t3.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 ref idx idx 5 test.t1.a 1 +1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using index +SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a +LEFT JOIN t3 ON t2.b=t3.b; +a a b b +1 1 1 NULL +2 NULL NULL NULL +3 3 1 NULL +4 NULL NULL NULL +SELECT FOUND_ROWS(); +FOUND_ROWS() +4 +DROP TABLE t1,t2,t3; |