Bug#34731: highest possible value for INT erroneously filtered by WHERE

WHERE f1 < n ignored row if f1 was indexed integer column and
f1 = TYPE_MAX ^ n = TYPE_MAX+1. The latter value when treated
as TYPE overflowed (obviously). This was not handled, it is now.


mysql-test/r/range.result:
  show that on an index int column, we no longer disregard
  a field val of TYPE_MAX in SELECT ... WHERE ... < TYPE_MAX+1
mysql-test/t/range.test:
  show that on an index int column, we no longer disregard
  a field val of TYPE_MAX in SELECT ... WHERE ... < TYPE_MAX+1
sql/opt_range.cc:
  Handle overflowing of int-types in range-optimizer.
  Unfortunately requires re-indentation of entire block.
  Overflow (err == 1) was handled, but only if
  field->cmp_type() != value->result_type(), which it
  just wasn't in our case.

1 files changed, 47 insertions, 0 deletions

diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 87ba3510326..95e0d31ff8f 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -955,4 +955,51 @@ explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '
 
 drop table t1;
 
+
+#
+# Bug #34731: highest possible value for INT erroneously filtered by WHERE
+#
+
+# test UNSIGNED. only occurs when indexed.
+CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1));
+
+INSERT INTO t1 VALUES (127),(254),(0),(1),(255);
+
+# test upper bound
+# count 5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256;
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0;
+# count 4
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255;
+
+# show we don't fiddle with lower bound on UNSIGNED
+# count 0
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1;
+# count 5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1;
+
+DROP TABLE t1;
+
+
+# test signed. only occurs when index.
+CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1));
+
+INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127);
+
+# test upper bound
+# count 5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128;
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0;
+# count 4
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127;
+
+# test lower bound
+# count 5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129;
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0;
+# count 4
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128;
+
+DROP TABLE t1;
+
 # End of 5.0 tests