Fix for BUG#11185.

The source of the problem is in Field_longlong::cmp. If 'this' is
an unsigned number, the method casts both the current value, and
the constant that we compare with to an unsigned number. As a
result if the constant we compare with is a negative number, it
wraps to some unsigned number, and the comparison is incorrect.

When the optimizer chooses the "range" access method, this problem
causes handler::read_range_next to reject the current key when the
upper bound key is a negative number because handler::compare_key
incorrectly considers the positive and negative keys to be equal.

The current patch does not correct the source of the problem in
Field_longlong::cmp because it is not easy to propagate sign
information about the constant at query execution time. Instead
the patch changes the range optimizer so that it never compares
unsiged fields with negative constants. As an added benefit,
queries that do such comparisons will execute faster because
the range optimizer replaces conditions like:
(a) (unsigned_int [< | <=] negative_constant) == FALSE
(b) (unsigned_int [> | >=] negative_constant) == TRUE
with the corresponding constants.
In some cases this may even result in constant time execution.


mysql-test/r/range.result:
  - Changed incorrect result of an old test
  - Added new results for BUG#11185
mysql-test/t/range.test:
  - Added new tests for BUG#11185
  - Deleted an old comment because now the problem is fixed
sql/opt_range.cc:
  Added a new optimization to the range optimizer where we detect that
  an UNSIGNED field is compared with a negative constant. Depending on
  the comparison operator, we know directly that the result of the
  comparison is either TRUE or FALSE for all input values, and we need
  not check each value.
  
  This optimization is also necessary so that the index range access
  method produces correct results when comparing unsigned fields with
  negative constants.

2 files changed, 51 insertions, 4 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index afd91ce5fe9..07e96aee22b 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -556,11 +556,42 @@ count(*)
 0
 select count(*) from t1 where x > -16;
 count(*)
-1
+2
 select count(*) from t1 where x = 18446744073709551601;
 count(*)
 1
 drop table t1;
+create table t1 (a bigint unsigned);
+create index t1i on t1(a);
+insert into t1 select 18446744073709551615;
+insert into t1 select 18446744073709551614;
+explain select * from t1 where a <> -1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	t1i	t1i	9	NULL	2	Using where; Using index
+select * from t1 where a <> -1;
+a
+18446744073709551614
+18446744073709551615
+explain select * from t1 where a > -1 or a < -1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	t1i	t1i	9	NULL	2	Using where; Using index
+select * from t1 where a > -1 or a < -1;
+a
+18446744073709551614
+18446744073709551615
+explain select * from t1 where a > -1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	t1i	t1i	9	NULL	2	Using where; Using index
+select * from t1 where a > -1;
+a
+18446744073709551614
+18446744073709551615
+explain select * from t1 where a < -1;
+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
+select * from t1 where a < -1;
+a
+drop table t1;
 set names latin1;
 create table t1 (a char(10), b text, key (a)) character set latin1;
 INSERT INTO t1 (a) VALUES
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index b51a79fba77..92b7b848a8a 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -423,15 +423,31 @@ select count(*) from t1 where x=0;
 select count(*) from t1 where x<0;
 select count(*) from t1 where x < -16;
 select count(*) from t1 where x = -16;
-# The following query returns wrong value because the range optimizer can't
-# handle search on a signed value for an unsigned parameter. This will be fixed in
-# 5.0
 select count(*) from t1 where x > -16;
 select count(*) from t1 where x = 18446744073709551601;
 
 drop table t1;
 
 #
+# Bug #11185 incorrect comparison of unsigned int to signed constant
+#
+create table t1 (a bigint unsigned);
+create index t1i on t1(a);
+insert into t1 select 18446744073709551615;
+insert into t1 select 18446744073709551614;
+
+explain select * from t1 where a <> -1;
+select * from t1 where a <> -1;
+explain select * from t1 where a > -1 or a < -1;
+select * from t1 where a > -1 or a < -1;
+explain select * from t1 where a > -1;
+select * from t1 where a > -1;
+explain select * from t1 where a < -1;
+select * from t1 where a < -1;
+
+drop table t1;
+
+#
 # Bug #6045: Binary Comparison regression in MySQL 4.1
 # Binary searches didn't use a case insensitive index.
 #