diff options
| -rw-r--r-- | mysql-test/r/null.result | 40 | ||||
| -rw-r--r-- | mysql-test/t/null.test | 20 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 53 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 3 | ||||
| -rw-r--r-- | sql/item_func.h | 9 |
5 files changed, 93 insertions, 32 deletions
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index d48985822df..93f3ac4ab7b 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -502,6 +502,10 @@ NULLIF(TIMESTAMP'2001-01-01 00:00:00',1.0), NULLIF(TIMESTAMP'2001-01-01 00:00:00',1e0), NULLIF(TIMESTAMP'2001-01-01 00:00:00','2001-01-01'), NULLIF(TIMESTAMP'2001-01-01 00:00:00',TIME'00:00:00'); +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1.0' +Warning 1292 Incorrect datetime value: '1' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -518,6 +522,10 @@ NULLIF(DATE'2001-01-01',1.0), NULLIF(DATE'2001-01-01',1e0), NULLIF(DATE'2001-01-01','2001-01-01'), NULLIF(DATE'2001-01-01',TIME'00:00:00'); +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1.0' +Warning 1292 Incorrect datetime value: '1' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -1350,5 +1358,37 @@ t2 CREATE TABLE `t2` ( DROP TABLE t2; DROP TABLE t1; # +# MDEV-7759 NULLIF(x,y) is not equal to CASE WHEN x=y THEN NULL ELSE x END +# +CREATE TABLE t1 (a YEAR); +INSERT INTO t1 VALUES (2010),(2020); +SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; +a +2010 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and 1) +SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; +a +2010 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and 1) +DROP TABLE t1; +# Two warnings expected +CREATE TABLE t1 AS SELECT +NULLIF(TIMESTAMP'2001-01-01 00:00:00',1) AS a, +CASE WHEN TIMESTAMP'2001-01-01 00:00:00'=1 THEN NULL +ELSE TIMESTAMP'2001-01-01 00:00:00' + END AS b; +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 4af17490488..65a45b9a21c 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -850,5 +850,25 @@ DROP TABLE t2; DROP TABLE t1; --echo # +--echo # MDEV-7759 NULLIF(x,y) is not equal to CASE WHEN x=y THEN NULL ELSE x END +--echo # +CREATE TABLE t1 (a YEAR); +INSERT INTO t1 VALUES (2010),(2020); +SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; +SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; +DROP TABLE t1; + +--echo # Two warnings expected +CREATE TABLE t1 AS SELECT + NULLIF(TIMESTAMP'2001-01-01 00:00:00',1) AS a, + CASE WHEN TIMESTAMP'2001-01-01 00:00:00'=1 THEN NULL + ELSE TIMESTAMP'2001-01-01 00:00:00' + END AS b; +DROP TABLE t1; + + +--echo # --echo # End of 10.1 tests --echo # diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 110797b87c6..5b7152b57b3 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -552,6 +552,26 @@ void Item_func::convert_const_compared_to_int_field(THD *thd) } +bool Item_func::setup_args_and_comparator(THD *thd, Arg_comparator *cmp) +{ + DBUG_ASSERT(arg_count == 2); + + if (args[0]->cmp_type() == STRING_RESULT && + args[1]->cmp_type() == STRING_RESULT && + agg_arg_charsets_for_comparison(cmp->cmp_collation, args, 2)) + return true; + + args[0]->cmp_context= args[1]->cmp_context= + item_cmp_type(args[0]->result_type(), args[1]->result_type()); + + // Convert constants when compared to int/year field, unless this is LIKE + if (functype() != LIKE_FUNC) + convert_const_compared_to_int_field(thd); + + return cmp->set_cmp_func(this, tmp_arg, tmp_arg + 1, true); +} + + void Item_bool_func2::fix_length_and_dec() { max_length= 1; // Function returns 0 or 1 @@ -562,32 +582,7 @@ void Item_bool_func2::fix_length_and_dec() */ if (!args[0] || !args[1]) return; - - /* - We allow to convert to Unicode character sets in some cases. - The conditions when conversion is possible are: - - arguments A and B have different charsets - - A wins according to coercibility rules - - character set of A is superset for character set of B - - If all of the above is true, then it's possible to convert - B into the character set of A, and then compare according - to the collation of A. - */ - - DTCollation coll; - if (args[0]->cmp_type() == STRING_RESULT && - args[1]->cmp_type() == STRING_RESULT && - agg_arg_charsets_for_comparison(coll, args, 2)) - return; - - args[0]->cmp_context= args[1]->cmp_context= - item_cmp_type(args[0]->result_type(), args[1]->result_type()); - - // Convert constants when compared to int/year field, unless this is LIKE - if (functype() != LIKE_FUNC) - convert_const_compared_to_int_field(current_thd); - set_cmp_func(); + setup_args_and_comparator(current_thd, &cmp); } @@ -2760,12 +2755,8 @@ Item_func_nullif::fix_length_and_dec() decimals= m_args0_copy->decimals; unsigned_flag= m_args0_copy->unsigned_flag; fix_char_length(m_args0_copy->max_char_length()); - - convert_const_compared_to_int_field(current_thd); - m_args0_copy->cmp_context= args[1]->cmp_context= - item_cmp_type(m_args0_copy->result_type(), args[1]->result_type()); - cmp.set_cmp_func(this, tmp_arg, tmp_arg + 1, m_args0_copy->cmp_context); maybe_null=1; + setup_args_and_comparator(current_thd, &cmp); } diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 14b40699060..65dbc6265d0 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -66,10 +66,11 @@ public: Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), set_null(TRUE), comparators(0), thd(0), a_cache(0), b_cache(0) {}; +private: int set_cmp_func(Item_result_field *owner_arg, Item **a1, Item **a2, Item_result type); - +public: inline int set_cmp_func(Item_result_field *owner_arg, Item **a1, Item **a2, bool set_null_arg) { diff --git a/sql/item_func.h b/sql/item_func.h index 3c46d6549a0..84454ad2290 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -398,6 +398,15 @@ public: walk(&Item::call_bool_func_processor, FALSE, (uchar*) &info); } void convert_const_compared_to_int_field(THD *thd); + /** + Prepare arguments and setup a comparator. + Used in Item_func_xxx with two arguments and a comparator, + e.g. Item_bool_func2 and Item_func_nullif. + args[0] or args[1] can be modified: + - converted to character set and collation of the operation + - or replaced to an Item_int_with_ref + */ + bool setup_args_and_comparator(THD *thd, Arg_comparator *cmp); }; |