diff options
| -rw-r--r-- | mysql-test/r/bigint.result | 64 | ||||
| -rw-r--r-- | mysql-test/r/func_in.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/bigint.test | 47 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 31 |
4 files changed, 134 insertions, 10 deletions
diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 47a45efa5fd..652c5b96147 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -438,3 +438,67 @@ Level Code Message Note 1003 select 1 AS `1` from `test`.`t1` where ((`test`.`t1`.`a` = 0) and ('0' = `test`.`t1`.`b`)) DROP TABLE t1; # End of 5.1 tests +# +# Bug#13463415 63502: INCORRECT RESULTS OF BIGINT AND DECIMAL COMPARISON +# +CREATE TABLE t_bigint(id BIGINT); +INSERT INTO t_bigint VALUES (1), (2); +SELECT id, id >= 1.1 FROM t_bigint; +id id >= 1.1 +1 0 +2 1 +SELECT id, 1.1 <= id FROM t_bigint; +id 1.1 <= id +1 0 +2 1 +SELECT id, id = 1.1 FROM t_bigint; +id id = 1.1 +1 0 +2 0 +SELECT id, 1.1 = id FROM t_bigint; +id 1.1 = id +1 0 +2 0 +SELECT * from t_bigint WHERE id = 1.1; +id +SELECT * from t_bigint WHERE id = 1.1e0; +id +SELECT * from t_bigint WHERE id = '1.1'; +id +SELECT * from t_bigint WHERE id = '1.1e0'; +id +SELECT * from t_bigint WHERE id IN (1.1, 2.2); +id +SELECT * from t_bigint WHERE id IN (1.1e0, 2.2e0); +id +SELECT * from t_bigint WHERE id IN ('1.1', '2.2'); +id +SELECT * from t_bigint WHERE id IN ('1.1e0', '2.2e0'); +id +SELECT * from t_bigint WHERE id BETWEEN 1.1 AND 1.9; +id +SELECT * from t_bigint WHERE id BETWEEN 1.1e0 AND 1.9e0; +id +SELECT * from t_bigint WHERE id BETWEEN '1.1' AND '1.9'; +id +SELECT * from t_bigint WHERE id BETWEEN '1.1e0' AND '1.9e0'; +id +DROP TABLE t_bigint; +# +# Bug#11758543 50756: BIGINT '100' MATCHES 1.001E2 +# +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (1); +SELECT * FROM t1 WHERE coalesce(a) BETWEEN 0 and 0.9; +a +SELECT * FROM t1 WHERE coalesce(a)=0.9; +a +SELECT * FROM t1 WHERE coalesce(a) in (0.8,0.9); +a +SELECT * FROM t1 WHERE a BETWEEN 0 AND 0.9; +a +SELECT * FROM t1 WHERE a=0.9; +a +SELECT * FROM t1 WHERE a IN (0.8,0.9); +a +DROP TABLE t1; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 0b6117581f3..279a18e344e 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -469,6 +469,8 @@ SELECT HEX(a) FROM t2 WHERE a IN HEX(a) 7FFFFFFFFFFFFFFE 7FFFFFFFFFFFFFFF +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'abc' CREATE TABLE t3 (a BIGINT UNSIGNED); INSERT INTO t3 VALUES (9223372036854775551); SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42); diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index e19bba971f9..41f33b8a7f2 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -361,4 +361,51 @@ DROP TABLE t1; --echo # End of 5.1 tests +--echo # +--echo # Bug#13463415 63502: INCORRECT RESULTS OF BIGINT AND DECIMAL COMPARISON +--echo # + +CREATE TABLE t_bigint(id BIGINT); +INSERT INTO t_bigint VALUES (1), (2); + +SELECT id, id >= 1.1 FROM t_bigint; +SELECT id, 1.1 <= id FROM t_bigint; + +SELECT id, id = 1.1 FROM t_bigint; +SELECT id, 1.1 = id FROM t_bigint; + +SELECT * from t_bigint WHERE id = 1.1; +SELECT * from t_bigint WHERE id = 1.1e0; +SELECT * from t_bigint WHERE id = '1.1'; +SELECT * from t_bigint WHERE id = '1.1e0'; + +SELECT * from t_bigint WHERE id IN (1.1, 2.2); +SELECT * from t_bigint WHERE id IN (1.1e0, 2.2e0); +SELECT * from t_bigint WHERE id IN ('1.1', '2.2'); +SELECT * from t_bigint WHERE id IN ('1.1e0', '2.2e0'); + +SELECT * from t_bigint WHERE id BETWEEN 1.1 AND 1.9; +SELECT * from t_bigint WHERE id BETWEEN 1.1e0 AND 1.9e0; +SELECT * from t_bigint WHERE id BETWEEN '1.1' AND '1.9'; +SELECT * from t_bigint WHERE id BETWEEN '1.1e0' AND '1.9e0'; +DROP TABLE t_bigint; + +--echo # +--echo # Bug#11758543 50756: BIGINT '100' MATCHES 1.001E2 +--echo # + +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (1); + +# a. These queries correctly return 0 rows: +SELECT * FROM t1 WHERE coalesce(a) BETWEEN 0 and 0.9; +SELECT * FROM t1 WHERE coalesce(a)=0.9; +SELECT * FROM t1 WHERE coalesce(a) in (0.8,0.9); + +# b. These queries mistakenely returned 1 row: +SELECT * FROM t1 WHERE a BETWEEN 0 AND 0.9; +SELECT * FROM t1 WHERE a=0.9; +SELECT * FROM t1 WHERE a IN (0.8,0.9); + +DROP TABLE t1; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index f30b6adcb93..fb9878ccb0a 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1,4 +1,4 @@ -/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. +/* Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -435,11 +435,22 @@ static bool convert_constant_item(THD *thd, Item_field *field_item, orig_field_val= field->val_int(); if (!(*item)->is_null() && !(*item)->save_in_field(field, 1)) { - Item *tmp= new Item_int_with_ref(field->val_int(), *item, - test(field->flags & UNSIGNED_FLAG)); - if (tmp) - thd->change_item_tree(item, tmp); - result= 1; // Item was replaced + int field_cmp= 0; + // If item is a decimal value, we must reject it if it was truncated. + if (field->type() == MYSQL_TYPE_LONGLONG) + { + field_cmp= stored_field_cmp_to_item(thd, field, *item); + DBUG_PRINT("info", ("convert_constant_item %d", field_cmp)); + } + + if (0 == field_cmp) + { + Item *tmp= new Item_int_with_ref(field->val_int(), *item, + test(field->flags & UNSIGNED_FLAG)); + if (tmp) + thd->change_item_tree(item, tmp); + result= 1; // Item was replaced + } } /* Restore the original field value. */ if (save_field_value) @@ -2321,10 +2332,10 @@ void Item_func_between::fix_length_and_dec() The following can't be recoded with || as convert_constant_item changes the argument */ - if (convert_constant_item(thd, field_item, &args[1])) - cmp_type=INT_RESULT; // Works for all types. - if (convert_constant_item(thd, field_item, &args[2])) - cmp_type=INT_RESULT; // Works for all types. + const bool cvt_arg1= convert_constant_item(thd, field_item, &args[1]); + const bool cvt_arg2= convert_constant_item(thd, field_item, &args[2]); + if (cvt_arg1 && cvt_arg2) + cmp_type=INT_RESULT; // Works for all types. } } } |