| Bug #19342 | IN works incorrectly for BIGINT UNSIGNED values | ||
|---|---|---|---|
| Submitted: | 25 Apr 2006 17:08 | Modified: | 15 Mar 2007 13:19 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0-bk (before 5.0.21)/5.1BK | OS: | |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[25 Apr 2006 17:08]
Sergey Petrunya
[25 Apr 2006 17:24]
MySQL Verification Team
Thank you for the bug report. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.1.10-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table bigint_u(a bigint unsigned); Query OK, 0 rows affected (0.02 sec) mysql> insert into bigint_u values (0xFFFFFFFFFFFFFFFF); Query OK, 1 row affected (0.00 sec) mysql> select * from bigint_u where a=-1 or a=-2 ; Empty set (0.01 sec) mysql> select * from bigint_u where a in (-1, -2); +----------------------+ | a | +----------------------+ | 18446744073709551615 | +----------------------+ 1 row in set (0.01 sec)
[13 May 2006 18:41]
Sergey Petrunya
The problem also occurs when field is not a BIGINT UNSIGNED but the values in IN-list are: CREATE TABLE t1 ( `some_id` smallint(5) default NULL, KEY `some_id` (`some_id`) ); mysql> explain select * from t1 where some_id in (18446744073709551615,18446744073709551612); +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.01 sec) mysql> explain select * from t1 where some_id not in (18446744073709551615,18446744073709551612); +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.01 sec)
[13 Sep 2006 6:18]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/11816 ChangeSet@1.2272, 2006-09-12 23:17:58-07:00, igreenhoe@anubis.greendragongames.com +4 -0 Fix for bug #19342 (IN works incorrectly for BIGINT UNSIGNED values) Problem: the in function extracts and stores the integer portion of an integer-valued Item prior to comparison for speed. However, this extraction did not keep track if the value was unsigned in certian circumstances, and assumed that the stored value was signed. Solution: add the extra information so that we can make the determination at comparison time if the origional values were signed or not. Note that this may appear to be an inefficent packing of the data; however, it ensures that the long longs are word-aligned, and also keeps the signedness bit near the value so that we can efficently sort.
[29 Oct 2006 20:45]
pif
I just applied the patch, but it didn't solve my similar problem. Here is how i reproduce it : CREATE TABLE pif (ub bigint unsigned); insert into pif values(13491727406643098568); select * from pif where ub=0xBB3C3E98175D33C8; => 1 row => ok select * from pif where ub in (0xBB3C3E98175D33C8); => 1 row => ok select * from pif where ub in (0xBB3C3E98175D33C8, 42); => 0 row instead of 1 ! some variants which works : select * from pif where hex(ub) in (hex(0xBB3C3E98175D33C8), 42); => 1 row select * from pif where ~ub in (~0xBB3C3E98175D33C8, 42); => 1 row (so it's a signed/unsigned problem) hope this helps.
[1 Feb 2007 0:44]
Ian Greenhoe
Additional test cases for which this fails: CREATE TABLE t1 (c1 bigint unsigned); insert into t1 values(13491727406643098568); insert into t1 values(0x7ffffffffffffffe); insert into t1 values(0x7fffffffffffffff); insert into t1 values(0x8000000000000000); insert into t1 values(0x8000000000000001); insert into t1 values(0x8000000000000002); insert into t1 values(0x8000000000000300); insert into t1 values(0x8000000000000400); insert into t1 values(0x8000000000000401); select * from t1 where c1 in (0xBB3C3E98175D33C8, 42); select * from t1 where c1 in (0xBB3C3E98175D33C8, 0x7fffffffffffffff, 0x8000000000000000, 0x8000000000000400, 0x8000000000000401, 42); select * from t1 where c1 in (0x7fffffffffffffff, 0x8000000000000001); select * from t1 where c1 in (0x7ffffffffffffffe, 0x7fffffffffffffff); drop table t1; The first select returns no rows, and the last three return 9223372036854775806 9223372036854775807 9223372036854775808 9223372036854775809 9223372036854775810 9223372036854776576 9223372036854776832 ... which translates to the following rows: 0x7ffffffffffffffe, 0x7fffffffffffffff, 0x8000000000000000, 0x8000000000000001, 0x8000000000000002, 0x8000000000000300, 0x8000000000000400 The first and last rows seem to not be returned because everything above 0x8000000000000400 is not returned. Further, it seems to require that two things are needed: Two (or more) items in the "in(...)" part of the statement, and that one of the items in the "in" must fall in the magic range. I'm not sure what the lower bound of the magic range is. This was discovered on a version patched with essentially the patch above, and verified on a clean build of the MySQL 5.0 server.
[1 Feb 2007 17:29]
Chad MILLER
+ CREATE TABLE t1 (c1 bigint unsigned); + insert into t1 values(13491727406643098568); + insert into t1 values(0x7fffffefffffffff); + insert into t1 values(0x7ffffffeffffffff); + insert into t1 values(0x7fffffffefffffff); + insert into t1 values(0x7ffffffffeffffff); + insert into t1 values(0x7fffffffffefffff); + insert into t1 values(0x7ffffffffffeffff); + insert into t1 values(0x7fffffffffffefff); + insert into t1 values(0x7ffffffffffffeff); + insert into t1 values(0x7fffffffffffffef); + insert into t1 values(0x7ffffffffffffffe); + insert into t1 values(0x7fffffffffffffff); + insert into t1 values(0x8000000000000000); + insert into t1 values(0x8000000000000001); + insert into t1 values(0x8000000000000002); + insert into t1 values(0x8000000000000300); + insert into t1 values(0x8000000000000400); + insert into t1 values(0x8000000000000401); + insert into t1 values(0x8000000000004001); + insert into t1 values(0x8000000000040001); + insert into t1 values(0x8000000000400001); + insert into t1 values(0x8000000004000001); + insert into t1 values(0x8000000040000001); + insert into t1 values(0x8000000400000001); + insert into t1 values(0x8000004000000001); + insert into t1 values(0x8000040000000001); + select * from t1 where c1 in (0xBB3C3E98175D33C8, 42); + c1 + select hex(c1) from t1 where c1 in + (0xBB3C3E98175D33C8, + 0x7fffffffffffffff, + 0x8000000000000000, + 0x8000000000000400, + 0x8000000000000401, + 42); + hex(c1) + 7FFFFFFFFFFFFEFF + 7FFFFFFFFFFFFFEF + 7FFFFFFFFFFFFFFE + 7FFFFFFFFFFFFFFF + 8000000000000000 + 8000000000000001 + 8000000000000002 + 8000000000000300 + 8000000000000400 + select hex(c1) from t1 where c1 in + (0x7fffffffffffffff, + 0x8000000000000001); + hex(c1) + 7FFFFFFFFFFFFEFF + 7FFFFFFFFFFFFFEF + 7FFFFFFFFFFFFFFE + 7FFFFFFFFFFFFFFF + 8000000000000000 + 8000000000000001 + 8000000000000002 + 8000000000000300 + 8000000000000400 + select hex(c1) from t1 where c1 in + (0x7ffffffffffffffe, + 0x7fffffffffffffff); + hex(c1) + 7FFFFFFFFFFFFEFF + 7FFFFFFFFFFFFFEF + 7FFFFFFFFFFFFFFE + 7FFFFFFFFFFFFFFF + 8000000000000000 + 8000000000000001 + 8000000000000002 + 8000000000000300 + 8000000000000400
[13 Feb 2007 8:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/19754 ChangeSet@1.2408, 2007-02-13 10:29:57+02:00, gkodinov@macbook.gmz +6 -0 Bug #19342: Several problems here : 1. The conversion to double of an hex string const item was not taking into account the unsigned flag. 2. IN was not behaving in the same was way as comparisons when performed over an INT/DATE/DATETIME/TIMESTAMP column and a constant. The ordinary comparisons in that case convert the constant to an INTEGER value and do int comparisons. Fixed the IN to do the same. 3. IN is not taking into account the unsigned flag when calculating <expr> IN (<int_const1>, <int_const2>, ...). Extended the implementation of IN to store and process the unsigned flag for its arguments.
[19 Feb 2007 17:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/20107 ChangeSet@1.2408, 2007-02-19 16:34:09+02:00, gkodinov@macbook.gmz +6 -0 Bug #19342: Several problems here : 1. The conversion to double of an hex string const item was not taking into account the unsigned flag. 2. IN was not behaving in the same was way as comparisons when performed over an INT/DATE/DATETIME/TIMESTAMP column and a constant. The ordinary comparisons in that case convert the constant to an INTEGER value and do int comparisons. Fixed the IN to do the same. 3. IN is not taking into account the unsigned flag when calculating <expr> IN (<int_const1>, <int_const2>, ...). Extended the implementation of IN to store and process the unsigned flag for its arguments.
[19 Feb 2007 17:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/20091 ChangeSet@1.2408, 2007-02-19 15:26:37+02:00, gkodinov@macbook.gmz +6 -0 Bug #19342: Several problems here : 1. The conversion to double of an hex string const item was not taking into account the unsigned flag. 2. IN was not behaving in the same was way as comparisons when performed over an INT/DATE/DATETIME/TIMESTAMP column and a constant. The ordinary comparisons in that case convert the constant to an INTEGER value and do int comparisons. Fixed the IN to do the same. 3. IN is not taking into account the unsigned flag when calculating <expr> IN (<int_const1>, <int_const2>, ...). Extended the implementation of IN to store and process the unsigned flag for its arguments.
[22 Feb 2007 12:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/20354 ChangeSet@1.2408, 2007-02-22 14:50:13+02:00, gkodinov@macbook.gmz +6 -0 Bug #19342: Several problems here : 1. The conversion to double of an hex string const item was not taking into account the unsigned flag. 2. IN was not behaving in the same was way as comparisons when performed over an INT/DATE/DATETIME/TIMESTAMP column and a constant. The ordinary comparisons in that case convert the constant to an INTEGER value and do int comparisons. Fixed the IN to do the same. 3. IN is not taking into account the unsigned flag when calculating <expr> IN (<int_const1>, <int_const2>, ...). Extended the implementation of IN to store and process the unsigned flag for its arguments.
[22 Feb 2007 13:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/20356 ChangeSet@1.2408, 2007-02-22 15:25:57+02:00, gkodinov@macbook.gmz +6 -0 Bug #19342: Several problems here : 1. The conversion to double of an hex string const item was not taking into account the unsigned flag. 2. IN was not behaving in the same was way as comparisons when performed over an INT/DATE/DATETIME/TIMESTAMP column and a constant. The ordinary comparisons in that case convert the constant to an INTEGER value and do int comparisons. Fixed the IN to do the same. 3. IN is not taking into account the unsigned flag when calculating <expr> IN (<int_const1>, <int_const2>, ...). Extended the implementation of IN to store and process the unsigned flag for its arguments.
[2 Mar 2007 10:49]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/20997 ChangeSet@1.2408, 2007-03-02 12:48:35+02:00, gkodinov@macbook.gmz +5 -0 Bug #19342: Several problems here : 1. The conversion to double of an hex string const item was not taking into account the unsigned flag. 2. IN was not behaving in the same was way as comparisons when performed over an INT/DATE/DATETIME/TIMESTAMP column and a constant. The ordinary comparisons in that case convert the constant to an INTEGER value and do int comparisons. Fixed the IN to do the same. 3. IN is not taking into account the unsigned flag when calculating <expr> IN (<int_const1>, <int_const2>, ...). Extended the implementation of IN to store and process the unsigned flag for its arguments.
[2 Mar 2007 14:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/21027 ChangeSet@1.2408, 2007-03-02 16:25:56+02:00, gkodinov@macbook.gmz +5 -0 Bug #19342: Several problems here : 1. The conversion to double of an hex string const item was not taking into account the unsigned flag. 2. IN was not behaving in the same was way as comparisons when performed over an INT/DATE/DATETIME/TIMESTAMP column and a constant. The ordinary comparisons in that case convert the constant to an INTEGER value and do int comparisons. Fixed the IN to do the same. 3. IN is not taking into account the unsigned flag when calculating <expr> IN (<int_const1>, <int_const2>, ...). Extended the implementation of IN to store and process the unsigned flag for its arguments.
[6 Mar 2007 16:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/21241 ChangeSet@1.2410, 2007-03-06 18:52:00+02:00, gkodinov@macbook.gmz +2 -0 Bug#19342: additional test case for code coverage
[9 Mar 2007 23:08]
Alexey Botchkov
pushed in 5.0.38
[15 Mar 2007 3:13]
Paul DuBois
Noted in 5.0.38 changelog. For expr IN(value_list), the result could be incorrect if BIGINT UNSIGNED values were used for expr or in the value list.
[15 Mar 2007 8:40]
Georgi Kodinov
Pushed in 5.1.17
[15 Mar 2007 13:19]
Paul DuBois
Noted in 5.1.17 changelog.
