Bug #11185 index on unsigned bigint modifies result
Submitted: 8 Jun 2005 20:26 Modified: 24 Jun 2005 17:19
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.12 OS:*, freebsd
Assigned to: Timour Katchaounov CPU Architecture:Any

[8 Jun 2005 20:26] Martin Friebe
Description:
see how to repeat.
All staement are fine, except for "a <> -1" / "not(a=-1)", which (in case a is indexed) seems to thing that 18446744073709551615 equals -1.

before index creation: (all results as expected)

select * from m1 where a = -1;
Empty set (0.01 sec)

select * from m1 where not (a = -1);
+----------------------+
| a                    |
+----------------------+
| 18446744073709551615 |
| 18446744073709551614 |
+----------------------+
2 rows in set (0.13 sec)

after index creation: (2nd result, does miss some data)

select * from m1 where a = -1;
Empty set (0.01 sec)

mysql> select * from m1 where not (a = -1);
+----------------------+
| a                    |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.97 sec)

How to repeat:
create table m1 (a bigint unsigned);
insert into m1 select 18446744073709551615;
insert into m1 select 18446744073709551614;

select * from m1 where a = -1;
select * from m1 where not (a = -1);
select * from m1 where a <> -1;

alter table xxx.m1 add index(a);

select * from m1 where a = -1;
select * from m1 where not (a = -1);
select * from m1 where a <> -1;

drop table m1;

Suggested fix:
-
[8 Jun 2005 20:37] Miguel Solorzano
Verified on Fedora Core 3 with BK source 4.1.
Thank you for the bug report.
[8 Jun 2005 22:04] Patrick Galbraith
Please see bug 9088 http://bugs.mysql.com/bug.php?id=9088.

This problem is due to the fact that either quoting a value above 9223372036854775807 or using the "<>" operator forces this to be evaluated as a string, forcing the conversion of a string to a double, which then you cannot reliably compare two doubles. Anything above 9223372036854775807 should use numeric comparison operators, or, use DECIMAL instead of BIGINT (for 5.0.3 and above). Please see:

http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html

With notes about DECIMAL
[9 Jun 2005 9:18] Martin Friebe
I think it is different from http://bugs.mysql.com/bug.php?id=9088

It is not quoted, in any example, and even so the quote causes the number to be slightly off, not to become negative (http://bugs.mysql.com/bug.php?id=9840)

with regards to the <> operator it fails also on   not(a=-1), while a=-1 works fine.
[21 Jun 2005 17:47] 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/internals/26259
[23 Jun 2005 7:57] 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/internals/26353
[23 Jun 2005 9:09] 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/internals/26355
[23 Jun 2005 20:42] Timour Katchaounov
Pushed into 4.1.13 and 5.0.9.
[24 Jun 2005 6:57] 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/internals/26393
[24 Jun 2005 17:19] Mike Hillyer
Documented in 5.0.9 and 4.1.13 changelogs:

<listitem><para>Optimizer performed range check when comparing unsigned integers to negative constants, could cause errors. (Bug #11185)</para></listitem>
[16 Jul 2005 7: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/internals/27211