diff options
| -rw-r--r-- | mysql-test/r/case.result | 20 | ||||
| -rw-r--r-- | mysql-test/t/case.test | 23 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 36 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 3 |
4 files changed, 72 insertions, 10 deletions
diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result index 9ee64ee6dbd..d408a0b6153 100644 --- a/mysql-test/r/case.result +++ b/mysql-test/r/case.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2; +drop table if exists t1, t2; select CASE "b" when "a" then 1 when "b" then 2 END; CASE "b" when "a" then 1 when "b" then 2 END 2 @@ -200,3 +200,21 @@ CEMPNUM EMPMUM1 EMPNUM2 0.00 0 0.00 2.00 2 NULL DROP TABLE t1,t2; +End of 4.1 tests +create table t1 (a int, b bigint unsigned); +create table t2 (c int); +insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997), +(3,11120436154190595086); +insert into t2 (c) values (1), (2), (3); +select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 +join t2 on t1.a=t2.c order by d; +a d +1 4572794622775114594 +3 11120436154190595086 +2 18196094287899841997 +select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 +join t2 on t1.a=t2.c where b=11120436154190595086 order by d; +a d +3 11120436154190595086 +drop table t1, t2; +End of 5.0 tests diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test index 63baeebcf12..028c64d6de7 100644 --- a/mysql-test/t/case.test +++ b/mysql-test/t/case.test @@ -1,9 +1,9 @@ -# + # Testing of CASE # --disable_warnings -drop table if exists t1,t2; +drop table if exists t1, t2; --enable_warnings select CASE "b" when "a" then 1 when "b" then 2 END; @@ -152,4 +152,21 @@ SELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM, FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM; DROP TABLE t1,t2; -# End of 4.1 tests + +--echo End of 4.1 tests + +# +# #30782: Truncated UNSIGNED BIGINT columns +# +create table t1 (a int, b bigint unsigned); +create table t2 (c int); +insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997), + (3,11120436154190595086); +insert into t2 (c) values (1), (2), (3); +select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 + join t2 on t1.a=t2.c order by d; +select t1.a, (case t1.a when 0 then 0 else t1.b end) d from t1 + join t2 on t1.a=t2.c where b=11120436154190595086 order by d; +drop table t1, t2; + +--echo End of 5.0 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 9b7b7b1a62a..5642c671dbd 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2557,6 +2557,23 @@ bool Item_func_case::fix_fields(THD *thd, Item **ref) } +void Item_func_case::agg_str_lengths(Item* arg) +{ + set_if_bigger(max_length, arg->max_length); + set_if_bigger(decimals, arg->decimals); + unsigned_flag= unsigned_flag && arg->unsigned_flag; +} + + +void Item_func_case::agg_num_lengths(Item *arg) +{ + uint len= my_decimal_length_to_precision(arg->max_length, arg->decimals, + arg->unsigned_flag) - arg->decimals; + set_if_bigger(max_length, len); + set_if_bigger(decimals, arg->decimals); + unsigned_flag= unsigned_flag && arg->unsigned_flag; +} + void Item_func_case::fix_length_and_dec() { @@ -2620,15 +2637,22 @@ void Item_func_case::fix_length_and_dec() max_length=0; decimals=0; - for (uint i=0 ; i < ncases ; i+=2) + unsigned_flag= TRUE; + if (cached_result_type == STRING_RESULT) { - set_if_bigger(max_length,args[i+1]->max_length); - set_if_bigger(decimals,args[i+1]->decimals); + for (uint i= 0; i < ncases; i+= 2) + agg_str_lengths(args[i + 1]); + if (else_expr_num != -1) + agg_str_lengths(args[else_expr_num]); } - if (else_expr_num != -1) + else { - set_if_bigger(max_length,args[else_expr_num]->max_length); - set_if_bigger(decimals,args[else_expr_num]->decimals); + for (uint i= 0; i < ncases; i+= 2) + agg_num_lengths(args[i + 1]); + if (else_expr_num != -1) + agg_num_lengths(args[else_expr_num]); + max_length= my_decimal_precision_to_length(max_length + decimals, decimals, + unsigned_flag); } } diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index a5252cc08f7..8ef87d0dd59 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -713,6 +713,7 @@ public: bool is_null(); }; + /* Functions to handle the optimized IN */ @@ -1139,6 +1140,8 @@ public: Item *find_item(String *str); CHARSET_INFO *compare_collation() { return cmp_collation.collation; } void cleanup(); + void agg_str_lengths(Item *arg); + void agg_num_lengths(Item *arg); }; /* |