| Bug #4488 | SELECT...WHERE bigint column > operator doesn't work | ||
|---|---|---|---|
| Submitted: | 9 Jul 2004 17:41 | Modified: | 18 Aug 2004 0:46 |
| Reporter: | Patrick Crosby | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.4 | OS: | |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[9 Jul 2004 23:27]
Dean Ellis
Can you submit a complete test case containing the SQL which creates, populates and queries a table, demonstrating this behavior? I have tested several different variations based on your description but it would be simplest to test something that is known to have the problem for you.
[10 Jul 2004 5:38]
Max Krohn
Try this; note that 0xfffffffffffffff is 2^64 -1; whatever value you insert, it should be greater than 2^63 to see this bug. mysql> create table foo (x bigint unsigned not null auto_increment primary key); mysql> insert into foo(x) values (0xffffffffffffffff); mysql> select count(*) from foo; +----------+ | count(*) | +----------+ | 1 | +----------+ mysql> select count(*) from foo where x>=0; +----------+ | count(*) | +----------+ | 0 | +----------+
[14 Jul 2004 22:52]
Dean Ellis
Verified against 4.1.4 1.1976 with the supplied test case. For MyISAM, SELECT * FROM foo WHERE x < 0; returns the row. For InnoDB, neither x > 0 nor x < 0 nor x=0; return the row. Thank you for the report.
[17 Aug 2004 1:06]
Sergey Petrunya
ChangeSet@1.1954, 2004-08-17 02:59:24+04:00, sergefp@mysql.com Fix for bug#4488: sign-aware equality check ChangeSet@1.1953, 2004-08-11 23:08:20+04:00, sergefp@mysql.com Fix for BUG#4488: first portion: sign aware '<' and '>' comparisons.
[18 Aug 2004 0:46]
Sergey Petrunya
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: A SELECT query that depends on comparing a BIGINT column to a constant doesn't work with the '>' operator. This has existed in 4.1.1, 4.1.2, and 4.1.3. How to repeat: Create a table with a BIGINT column: CREATE TABLE profile_active ( userid BIGINT UNSIGNED NOT NULL PRIMARY KEY, username VARCHAR(20) UNIQUE NOT NULL ); Now look what happens: mysql> select count(*) from profile_active; +----------+ | count(*) | +----------+ | 3388 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) from profile_active WHERE userid < 9200308540776293832; +----------+ | count(*) | +----------+ | 1769 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) from profile_active WHERE userid > 9200308540776293832; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) from profile_active WHERE userid = 9200308540776293832; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec) mysql> SELECT count(*) from profile_active WHERE userid >= 0; +----------+ | count(*) | +----------+ | 1772 | +----------+ 1 row in set (0.00 sec) Clearly, there's a mismatch. 'SELECT count(*) from profile_active' and 'SELECT count(*) from profile_active WHERE userid >= 0' should return the same value. I imagine that the wrong type (too small) is being used for the comparison. Suggested fix: You can do a workaround by using 'userid / 2' in all queries: mysql> SELECT count(*) from profile_active WHERE userid/2 >= 0; +----------+ | count(*) | +----------+ | 3388 | +----------+ 1 row in set (0.01 sec) But as far as fixing it, you should just use an unsigned long long for the comparison.