MySQL Bugs: #11185: index on unsigned bigint modifies result

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

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:
-

Verified on Fedora Core 3 with BK source 4.1.
Thank you for the bug report.

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

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.

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

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

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

Pushed into 4.1.13 and 5.0.9.

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

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>

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