diff options
| -rw-r--r-- | mysql-test/r/join_outer.result | 32 | ||||
| -rw-r--r-- | mysql-test/r/join_outer_jcl6.result | 32 | ||||
| -rw-r--r-- | mysql-test/t/join_outer.test | 33 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 27 | ||||
| -rw-r--r-- | sql/sql_select.cc | 125 | ||||
| -rw-r--r-- | sql/sql_select.h | 2 |
6 files changed, 193 insertions, 58 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 15890e546d2..c9b694c9532 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2139,6 +2139,38 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index drop table t0, t1; # +# MDEV-4836: Wrong result on <not null date column> IS NULL (old documented hack stopped working) +# (this is a regression after fix for MDEV-4817) +# +CREATE TABLE t1 (id INT, d DATE NOT NULL); +INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00'); +CREATE TABLE t2 (i INT); +SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL; +id d i +1 0000-00-00 NULL +2 0000-00-00 NULL +DROP TABLE t1,t2; +CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL); +INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00'); +CREATE TABLE t2 (i2 INT, j2 INT); +INSERT INTO t2 VALUES (1,10),(2,20); +SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2; +i1 d1 i2 j2 +2 0000-00-00 NULL NULL +DROP TABLE t1,t2; +# Another testcase +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); +SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b; +i1 i2 a b +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b; +i1 i2 a b +drop view v2; +drop table t1,t2; +# # Bug mdev-4942: LEFT JOIN with conjunctive # <non-nullable datetime field> IS NULL in WHERE # causes an assert failure diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 7fda52931ca..eae21a090c4 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2150,6 +2150,38 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index drop table t0, t1; # +# MDEV-4836: Wrong result on <not null date column> IS NULL (old documented hack stopped working) +# (this is a regression after fix for MDEV-4817) +# +CREATE TABLE t1 (id INT, d DATE NOT NULL); +INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00'); +CREATE TABLE t2 (i INT); +SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL; +id d i +1 0000-00-00 NULL +2 0000-00-00 NULL +DROP TABLE t1,t2; +CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL); +INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00'); +CREATE TABLE t2 (i2 INT, j2 INT); +INSERT INTO t2 VALUES (1,10),(2,20); +SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2; +i1 d1 i2 j2 +2 0000-00-00 NULL NULL +DROP TABLE t1,t2; +# Another testcase +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); +SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b; +i1 i2 a b +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b; +i1 i2 a b +drop view v2; +drop table t1,t2; +# # Bug mdev-4942: LEFT JOIN with conjunctive # <non-nullable datetime field> IS NULL in WHERE # causes an assert failure diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 0eccb8e1587..7a70c413e8d 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1686,6 +1686,39 @@ explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or drop table t0, t1; +--echo # +--echo # MDEV-4836: Wrong result on <not null date column> IS NULL (old documented hack stopped working) +--echo # (this is a regression after fix for MDEV-4817) +--echo # +CREATE TABLE t1 (id INT, d DATE NOT NULL); +INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00'); +CREATE TABLE t2 (i INT); +SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL; +DROP TABLE t1,t2; + + +CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL); +INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00'); + +CREATE TABLE t2 (i2 INT, j2 INT); +INSERT INTO t2 VALUES (1,10),(2,20); + +SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2; +DROP TABLE t1,t2; + +--echo # Another testcase +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); + +CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; + +INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); +SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b; +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b; + +drop view v2; +drop table t1,t2; --echo # --echo # Bug mdev-4942: LEFT JOIN with conjunctive diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index dcc356ce5b2..cd1881e2970 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4302,7 +4302,8 @@ Item_cond::fix_fields(THD *thd, Item **ref) used_tables_cache|= item->used_tables(); if (item->const_item()) { - if (!item->is_expensive() && item->val_int() == 0) + if (!item->is_expensive() && !cond_has_datetime_is_null(item) && + item->val_int() == 0) { /* This is "... OR false_cond OR ..." @@ -4314,27 +4315,18 @@ Item_cond::fix_fields(THD *thd, Item **ref) /* This is "... OR const_cond OR ..." In this case, cond_or->not_null_tables()=0, because the condition - some_cond_or might be true regardless of what tables are - NULL-complemented. + const_cond might evaluate to true (regardless of whether some tables + were NULL-complemented). */ and_tables_cache= (table_map) 0; } } else { - /* - If an item is a - - constant - - inexpensive - - its value is 0 - then we don't need to account it in not_null_tables_cache - */ - //if (!(item->const_item() && !item->is_expensive() )) - { - table_map tmp_table_map= item->not_null_tables(); - not_null_tables_cache|= tmp_table_map; - and_tables_cache&= tmp_table_map; - } + table_map tmp_table_map= item->not_null_tables(); + not_null_tables_cache|= tmp_table_map; + and_tables_cache&= tmp_table_map; + const_item_cache= FALSE; } @@ -4363,7 +4355,8 @@ Item_cond::eval_not_null_tables(uchar *opt_arg) table_map tmp_table_map; if (item->const_item()) { - if (!item->is_expensive() && item->val_int() == 0) + if (!item->is_expensive() && !cond_has_datetime_is_null(item) && + item->val_int() == 0) { /* This is "... OR false_cond OR ..." diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9609899760a..1ce88b3c48c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -13536,6 +13536,57 @@ void propagate_new_equalities(THD *thd, Item *cond, } } +/* + Check if cond_is_datetime_is_null() is true for the condition cond, or + for any of its AND/OR-children +*/ +bool cond_has_datetime_is_null(Item *cond) +{ + if (cond_is_datetime_is_null(cond)) + return true; + + if (cond->type() == Item::COND_ITEM) + { + List<Item> *cond_arg_list= ((Item_cond*) cond)->argument_list(); + List_iterator<Item> li(*cond_arg_list); + Item *item; + while ((item= li++)) + { + if (cond_has_datetime_is_null(item)) + return true; + } + } + return false; +} + +/* + Check if passed condtition has for of + + not_null_date_col IS NULL + + where not_null_date_col has a datte or datetime type +*/ + +bool cond_is_datetime_is_null(Item *cond) +{ + if (cond->type() == Item::FUNC_ITEM && + ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC) + { + Item **args= ((Item_func_isnull*) cond)->arguments(); + if (args[0]->type() == Item::FIELD_ITEM) + { + Field *field=((Item_field*) args[0])->field; + + if (((field->type() == MYSQL_TYPE_DATE) || + (field->type() == MYSQL_TYPE_DATETIME)) && + (field->flags & NOT_NULL_FLAG)) + { + return TRUE; + } + } + } + return FALSE; +} /** @@ -13804,53 +13855,45 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) return item; } } - else if (cond->type() == Item::FUNC_ITEM && - ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC) + else if (cond_is_datetime_is_null(cond)) { - Item_func_isnull *func=(Item_func_isnull*) cond; - Item **args= func->arguments(); - if (args[0]->type() == Item::FIELD_ITEM) - { - Field *field=((Item_field*) args[0])->field; - /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */ - /* - See BUG#12594011 - Documentation says that - SELECT datetime_notnull d FROM t1 WHERE d IS NULL - shall return rows where d=='0000-00-00' + /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */ + /* + See BUG#12594011 + Documentation says that + SELECT datetime_notnull d FROM t1 WHERE d IS NULL + shall return rows where d=='0000-00-00' - Thus, for DATE and DATETIME columns defined as NOT NULL, - "date_notnull IS NULL" has to be modified to - "date_notnull IS NULL OR date_notnull == 0" (if outer join) - "date_notnull == 0" (otherwise) + Thus, for DATE and DATETIME columns defined as NOT NULL, + "date_notnull IS NULL" has to be modified to + "date_notnull IS NULL OR date_notnull == 0" (if outer join) + "date_notnull == 0" (otherwise) - */ - if (((field->type() == MYSQL_TYPE_DATE) || - (field->type() == MYSQL_TYPE_DATETIME)) && - (field->flags & NOT_NULL_FLAG)) - { - Item *item0= new(thd->mem_root) Item_int((longlong)0, 1); - Item *eq_cond= new(thd->mem_root) Item_func_eq(args[0], item0); - if (!eq_cond) - return cond; + */ + Item **args= ((Item_func_isnull*) cond)->arguments(); + Field *field=((Item_field*) args[0])->field; - if (field->table->pos_in_table_list->is_inner_table_of_outer_join()) - { - // outer join: transform "col IS NULL" to "col IS NULL or col=0" - Item *or_cond= new(thd->mem_root) Item_cond_or(eq_cond, cond); - if (!or_cond) - return cond; - cond= or_cond; - } - else - { - // not outer join: transform "col IS NULL" to "col=0" - cond= eq_cond; - } + Item *item0= new(thd->mem_root) Item_int((longlong)0, 1); + Item *eq_cond= new(thd->mem_root) Item_func_eq(args[0], item0); + if (!eq_cond) + return cond; - cond->fix_fields(thd, &cond); - } + if (field->table->pos_in_table_list->is_inner_table_of_outer_join()) + { + // outer join: transform "col IS NULL" to "col IS NULL or col=0" + Item *or_cond= new(thd->mem_root) Item_cond_or(eq_cond, cond); + if (!or_cond) + return cond; + cond= or_cond; + } + else + { + // not outer join: transform "col IS NULL" to "col=0" + cond= eq_cond; } + + cond->fix_fields(thd, &cond); + if (cond->const_item() && !cond->is_expensive()) { *cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE; diff --git a/sql/sql_select.h b/sql/sql_select.h index af51c7dad48..eb50f65a0cc 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1800,6 +1800,8 @@ ORDER *simple_remove_const(ORDER *order, COND *where); bool const_expression_in_where(COND *cond, Item *comp_item, Field *comp_field= NULL, Item **const_item= NULL); +bool cond_is_datetime_is_null(Item *cond); +bool cond_has_datetime_is_null(Item *cond); /* Table elimination entry point function */ void eliminate_tables(JOIN *join); |