diff options
| -rw-r--r-- | mysql-test/r/func_in.result | 66 | ||||
| -rw-r--r-- | mysql-test/t/func_in.test | 58 | ||||
| -rw-r--r-- | sql/item.h | 5 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 130 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 17 |
5 files changed, 268 insertions, 8 deletions
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index d9ca9e80e44..36bcc6f1711 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -398,4 +398,70 @@ WHERE t3.a=t1.a AND t3.a=t2.a; 3 3 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1(a BIGINT UNSIGNED); +INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF); +SELECT * FROM t1 WHERE a=-1 OR a=-2 ; +a +SELECT * FROM t1 WHERE a IN (-1, -2); +a +CREATE TABLE t2 (a BIGINT UNSIGNED); +insert into t2 values(13491727406643098568), +(0x7fffffefffffffff), +(0x7ffffffeffffffff), +(0x7fffffffefffffff), +(0x7ffffffffeffffff), +(0x7fffffffffefffff), +(0x7ffffffffffeffff), +(0x7fffffffffffefff), +(0x7ffffffffffffeff), +(0x7fffffffffffffef), +(0x7ffffffffffffffe), +(0x7fffffffffffffff), +(0x8000000000000000), +(0x8000000000000001), +(0x8000000000000002), +(0x8000000000000300), +(0x8000000000000400), +(0x8000000000000401), +(0x8000000000004001), +(0x8000000000040001), +(0x8000000000400001), +(0x8000000004000001), +(0x8000000040000001), +(0x8000000400000001), +(0x8000004000000001), +(0x8000040000000001); +SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42); +HEX(a) +BB3C3E98175D33C8 +SELECT HEX(a) FROM t2 WHERE a IN +(0xBB3C3E98175D33C8, +0x7fffffffffffffff, +0x8000000000000000, +0x8000000000000400, +0x8000000000000401, +42); +HEX(a) +BB3C3E98175D33C8 +7FFFFFFFFFFFFFFF +8000000000000000 +8000000000000400 +8000000000000401 +SELECT HEX(a) FROM t2 WHERE a IN (0x7fffffffffffffff,0x8000000000000001); +HEX(a) +7FFFFFFFFFFFFFFF +8000000000000001 +SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff); +HEX(a) +7FFFFFFFFFFFFFFE +7FFFFFFFFFFFFFFF +SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff,'abc'); +HEX(a) +7FFFFFFFFFFFFFFE +7FFFFFFFFFFFFFFF +CREATE TABLE t3 (a BIGINT UNSIGNED); +INSERT INTO t3 VALUES (9223372036854775551); +SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42); +HEX(a) +DROP TABLE t1,t2,t3; End of 5.0 tests diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 54b81bed133..7ba54747d4b 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -298,4 +298,62 @@ SELECT STRAIGHT_JOIN DROP TABLE t1,t2,t3,t4; +# +# BUG#19342: IN works incorrectly for BIGINT UNSIGNED values +# +CREATE TABLE t1(a BIGINT UNSIGNED); +INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF); + +SELECT * FROM t1 WHERE a=-1 OR a=-2 ; +SELECT * FROM t1 WHERE a IN (-1, -2); + +CREATE TABLE t2 (a BIGINT UNSIGNED); +insert into t2 values(13491727406643098568), + (0x7fffffefffffffff), + (0x7ffffffeffffffff), + (0x7fffffffefffffff), + (0x7ffffffffeffffff), + (0x7fffffffffefffff), + (0x7ffffffffffeffff), + (0x7fffffffffffefff), + (0x7ffffffffffffeff), + (0x7fffffffffffffef), + (0x7ffffffffffffffe), + (0x7fffffffffffffff), + (0x8000000000000000), + (0x8000000000000001), + (0x8000000000000002), + (0x8000000000000300), + (0x8000000000000400), + (0x8000000000000401), + (0x8000000000004001), + (0x8000000000040001), + (0x8000000000400001), + (0x8000000004000001), + (0x8000000040000001), + (0x8000000400000001), + (0x8000004000000001), + (0x8000040000000001); + +SELECT HEX(a) FROM t2 WHERE a IN (0xBB3C3E98175D33C8, 42); + +SELECT HEX(a) FROM t2 WHERE a IN +(0xBB3C3E98175D33C8, + 0x7fffffffffffffff, + 0x8000000000000000, + 0x8000000000000400, + 0x8000000000000401, + 42); + +SELECT HEX(a) FROM t2 WHERE a IN (0x7fffffffffffffff,0x8000000000000001); +SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff); +SELECT HEX(a) FROM t2 WHERE a IN (0x7ffffffffffffffe,0x7fffffffffffffff,'abc'); + +CREATE TABLE t3 (a BIGINT UNSIGNED); +INSERT INTO t3 VALUES (9223372036854775551); + +SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42); + +DROP TABLE t1,t2,t3; + --echo End of 5.0 tests diff --git a/sql/item.h b/sql/item.h index 6c41aa09f80..f02d4f0c65d 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1766,7 +1766,10 @@ public: Item_hex_string(const char *str,uint str_length); enum Type type() const { return VARBIN_ITEM; } double val_real() - { DBUG_ASSERT(fixed == 1); return (double) Item_hex_string::val_int(); } + { + DBUG_ASSERT(fixed == 1); + return (double) (ulonglong) Item_hex_string::val_int(); + } longlong val_int(); bool basic_const_item() const { return 1; } String *val_str(String*) { DBUG_ASSERT(fixed == 1); return &str_value; } diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 08f9c16384a..f239e3ea18e 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2034,9 +2034,100 @@ void Item_func_coalesce::fix_length_and_dec() Classes and function for the IN operator ****************************************************************************/ -static int cmp_longlong(void *cmp_arg, longlong *a,longlong *b) +/* + Determine which of the signed longlong arguments is bigger + + SYNOPSIS + cmp_longs() + a_val left argument + b_val right argument + + DESCRIPTION + This function will compare two signed longlong arguments + and will return -1, 0, or 1 if left argument is smaller than, + equal to or greater than the right argument. + + RETURN VALUE + -1 left argument is smaller than the right argument. + 0 left argument is equal to the right argument. + 1 left argument is greater than the right argument. +*/ +static inline int cmp_longs (longlong a_val, longlong b_val) { - return *a < *b ? -1 : *a == *b ? 0 : 1; + return a_val < b_val ? -1 : a_val == b_val ? 0 : 1; +} + + +/* + Determine which of the unsigned longlong arguments is bigger + + SYNOPSIS + cmp_ulongs() + a_val left argument + b_val right argument + + DESCRIPTION + This function will compare two unsigned longlong arguments + and will return -1, 0, or 1 if left argument is smaller than, + equal to or greater than the right argument. + + RETURN VALUE + -1 left argument is smaller than the right argument. + 0 left argument is equal to the right argument. + 1 left argument is greater than the right argument. +*/ +static inline int cmp_ulongs (ulonglong a_val, ulonglong b_val) +{ + return a_val < b_val ? -1 : a_val == b_val ? 0 : 1; +} + + +/* + Compare two integers in IN value list format (packed_longlong) + + SYNOPSIS + cmp_longlong() + cmp_arg an argument passed to the calling function (qsort2) + a left argument + b right argument + + DESCRIPTION + This function will compare two integer arguments in the IN value list + format and will return -1, 0, or 1 if left argument is smaller than, + equal to or greater than the right argument. + It's used in sorting the IN values list and finding an element in it. + Depending on the signedness of the arguments cmp_longlong() will + compare them as either signed (using cmp_longs()) or unsigned (using + cmp_ulongs()). + + RETURN VALUE + -1 left argument is smaller than the right argument. + 0 left argument is equal to the right argument. + 1 left argument is greater than the right argument. +*/ +int cmp_longlong(void *cmp_arg, + in_longlong::packed_longlong *a, + in_longlong::packed_longlong *b) +{ + if (a->unsigned_flag != b->unsigned_flag) + { + /* + One of the args is unsigned and is too big to fit into the + positive signed range. Report no match. + */ + if (a->unsigned_flag && ((ulonglong) a->val) > LONGLONG_MAX || + b->unsigned_flag && ((ulonglong) b->val) > LONGLONG_MAX) + return a->unsigned_flag ? 1 : -1; + /* + Although the signedness differs both args can fit into the signed + positive range. Make them signed and compare as usual. + */ + return cmp_longs (a->val, b->val); + } + if (a->unsigned_flag) + return cmp_ulongs ((ulonglong) a->val, (ulonglong) b->val); + else + return cmp_longs (a->val, b->val); } static int cmp_double(void *cmp_arg, double *a,double *b) @@ -2161,19 +2252,23 @@ void in_row::set(uint pos, Item *item) } in_longlong::in_longlong(uint elements) - :in_vector(elements,sizeof(longlong),(qsort2_cmp) cmp_longlong, 0) + :in_vector(elements,sizeof(packed_longlong),(qsort2_cmp) cmp_longlong, 0) {} void in_longlong::set(uint pos,Item *item) { - ((longlong*) base)[pos]=item->val_int(); + struct packed_longlong *buff= &((packed_longlong*) base)[pos]; + + buff->val= item->val_int(); + buff->unsigned_flag= item->unsigned_flag; } byte *in_longlong::get_value(Item *item) { - tmp= item->val_int(); + tmp.val= item->val_int(); if (item->null_value) return 0; + tmp.unsigned_flag= item->unsigned_flag; return (byte*) &tmp; } @@ -2494,6 +2589,31 @@ void Item_func_in::fix_length_and_dec() */ if (const_itm && !nulls_in_row()) { + /* + IN must compare INT/DATE/DATETIME/TIMESTAMP columns and constants + as int values (the same way as equality does). + So we must check here if the column on the left and all the constant + values on the right can be compared as integers and adjust the + comparison type accordingly. + */ + if (args[0]->real_item()->type() == FIELD_ITEM && + thd->lex->sql_command != SQLCOM_CREATE_VIEW && + thd->lex->sql_command != SQLCOM_SHOW_CREATE && + cmp_type != INT_RESULT) + { + Field *field= ((Item_field*) (args[0]->real_item()))->field; + if (field->can_be_compared_as_longlong()) + { + bool all_converted= TRUE; + for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++) + { + if (!convert_constant_item (thd, field, &arg[0])) + all_converted= FALSE; + } + if (all_converted) + cmp_type= INT_RESULT; + } + } switch (cmp_type) { case STRING_RESULT: array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in, diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index f18728c554b..80115549336 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -731,7 +731,16 @@ public: class in_longlong :public in_vector { - longlong tmp; + /* + Here we declare a temporary variable (tmp) of the same type as the + elements of this vector. tmp is used in finding if a given value is in + the list. + */ + struct packed_longlong + { + longlong val; + longlong unsigned_flag; // Use longlong, not bool, to preserve alignment + } tmp; public: in_longlong(uint elements); void set(uint pos,Item *item); @@ -747,8 +756,12 @@ public: } void value_to_item(uint pos, Item *item) { - ((Item_int*)item)->value= ((longlong*)base)[pos]; + ((Item_int*) item)->value= ((packed_longlong*) base)[pos].val; + ((Item_int*) item)->unsigned_flag= + ((packed_longlong*) base)[pos].unsigned_flag; } + + friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b); }; class in_double :public in_vector |