diff options
| -rw-r--r-- | mysql-test/r/select.result | 26 | ||||
| -rw-r--r-- | mysql-test/t/select.test | 15 | ||||
| -rw-r--r-- | sql/sql_select.cc | 7 |
3 files changed, 46 insertions, 2 deletions
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index d7c01fa5a57..f09143fcaa6 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2744,6 +2744,32 @@ SELECT i='1e+01',i=1e+01, i in (1e+01), i in ('1e+01') FROM t1; i='1e+01' i=1e+01 i in (1e+01) i in ('1e+01') 0 1 1 1 DROP TABLE t1; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1,1), (2,1), (4,10); +CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b)); +INSERT INTO t2 VALUES (1,NULL), (2,10); +ALTER TABLE t1 ENABLE KEYS; +EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index b b 5 NULL 2 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; +a b a b +1 NULL 1 1 +1 NULL 2 1 +1 NULL 4 10 +2 10 4 10 +EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index b b 5 NULL 2 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; +a b a b +1 NULL 1 1 +1 NULL 2 1 +1 NULL 4 10 +2 10 4 10 +DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1)); CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2)); INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941); diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index acf9fd77c1b..3f9fb59d26f 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2298,6 +2298,21 @@ SELECT i='1e+01',i=1e+01, i in (1e+01), i in ('1e+01') FROM t1; DROP TABLE t1; # +# Bug #21019: First result of SELECT COUNT(*) different than consecutive runs +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1,1), (2,1), (4,10); + +CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b)); +INSERT INTO t2 VALUES (1,NULL), (2,10); +ALTER TABLE t1 ENABLE KEYS; + +EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; +SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; +EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; +SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; +DROP TABLE IF EXISTS t1,t2; +# # Bug #20954 "avg(keyval) retuns 0.38 but max(keyval) returns an empty set" # --disable_ps_protocol diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 339826b3692..df3b7544d8b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2161,8 +2161,11 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end, /* field = expression OR field IS NULL */ old->level= and_level; old->optimize= KEY_OPTIMIZE_REF_OR_NULL; - /* Remember the NOT NULL value */ - if (old->val->is_null()) + /* + Remember the NOT NULL value unless the value does not depend + on other tables. + */ + if (!old->val->used_tables() && old->val->is_null()) old->val= new_fields->val; /* The referred expression can be NULL: */ old->null_rejecting= 0; |