MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Tatjana A Nuernberg Date:March 10 2008 10:12am
Subject:bk commit into 5.0 tree (tnurnberg:1.2595) BUG#34731
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of tnurnberg.  When tnurnberg does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-03-10 11:12:12+01:00, tnurnberg@stripped +3 -0
  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@stripped, 2008-03-10 11:12:09+01:00, tnurnberg@stripped +39 -0
    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@stripped, 2008-03-10 11:12:10+01:00, tnurnberg@stripped +47 -0
    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@stripped, 2008-03-10 11:12:10+01:00, tnurnberg@stripped +54 -36
    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.

diff -Nrup a/mysql-test/r/range.result b/mysql-test/r/range.result
--- a/mysql-test/r/range.result	2007-12-13 11:38:19 +01:00
+++ b/mysql-test/r/range.result	2008-03-10 11:12:09 +01:00
@@ -1153,3 +1153,42 @@ explain select * from t1 where dateval >
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	dateval	dateval	4	NULL	2	Using where
 drop table t1;
+CREATE TABLE t1 (f1 TINYINT(11) UNSIGNED NOT NULL, PRIMARY KEY (f1));
+INSERT INTO t1 VALUES (127),(254),(0),(1),(255);
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256;
+COUNT(*)
+5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 256.0;
+COUNT(*)
+5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 255;
+COUNT(*)
+4
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < -1;
+COUNT(*)
+0
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -1;
+COUNT(*)
+5
+DROP TABLE t1;
+CREATE TABLE t1 ( f1 TINYINT(11) NOT NULL, PRIMARY KEY (f1));
+INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127);
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128;
+COUNT(*)
+5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 128.0;
+COUNT(*)
+5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 < 127;
+COUNT(*)
+4
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129;
+COUNT(*)
+5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -129.0;
+COUNT(*)
+5
+SELECT SQL_NO_CACHE COUNT(*) FROM t1 WHERE f1 > -128;
+COUNT(*)
+4
+DROP TABLE t1;
diff -Nrup a/mysql-test/t/range.test b/mysql-test/t/range.test
--- a/mysql-test/t/range.test	2007-12-13 11:38:19 +01:00
+++ b/mysql-test/t/range.test	2008-03-10 11:12:10 +01:00
@@ -955,4 +955,51 @@ explain select * from t1 where 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
diff -Nrup a/sql/opt_range.cc b/sql/opt_range.cc
--- a/sql/opt_range.cc	2007-12-13 11:49:12 +01:00
+++ b/sql/opt_range.cc	2008-03-10 11:12:10 +01:00
@@ -4405,52 +4405,70 @@ get_mm_leaf(PARAM *param, COND *conf_fun
        field->type() == FIELD_TYPE_DATETIME))
     field->table->in_use->variables.sql_mode|= MODE_INVALID_DATES;
   err= value->save_in_field_no_warnings(field, 1);
-  if (err > 0 && field->cmp_type() != value->result_type())
+  if (err > 0)
   {
-    if ((type == Item_func::EQ_FUNC || type == Item_func::EQUAL_FUNC) &&
-	value->result_type() == item_cmp_type(field->result_type(),
-                                              value->result_type()))
-
-    {
-      tree= new (alloc) SEL_ARG(field, 0, 0);
-      tree->type= SEL_ARG::IMPOSSIBLE;
-      goto end;
-    }
-    else
+    if (field->cmp_type() != value->result_type())
     {
-      /*
-        TODO: We should return trees of the type SEL_ARG::IMPOSSIBLE
-        for the cases like int_field > 999999999999999999999999 as well.
-      */
-      tree= 0;
-      if (err == 3 && field->type() == FIELD_TYPE_DATE && 
-          (type == Item_func::GT_FUNC || type == Item_func::GE_FUNC || 
-           type == Item_func::LT_FUNC || type == Item_func::LE_FUNC) )
+      if ((type == Item_func::EQ_FUNC || type == Item_func::EQUAL_FUNC) &&
+          value->result_type() == item_cmp_type(field->result_type(),
+                                                value->result_type()))
+      {
+        tree= new (alloc) SEL_ARG(field, 0, 0);
+        tree->type= SEL_ARG::IMPOSSIBLE;
+        goto end;
+      }
+      else
       {
         /*
-          We were saving DATETIME into a DATE column, the conversion went ok
-          but a non-zero time part was cut off.
+          TODO: We should return trees of the type SEL_ARG::IMPOSSIBLE
+          for the cases like int_field > 999999999999999999999999 as well.
+        */
+        tree= 0;
+        if (err == 3 && field->type() == FIELD_TYPE_DATE &&
+            (type == Item_func::GT_FUNC || type == Item_func::GE_FUNC ||
+             type == Item_func::LT_FUNC || type == Item_func::LE_FUNC) )
+        {
+          /*
+            We were saving DATETIME into a DATE column, the conversion went ok
+            but a non-zero time part was cut off.
 
-          In MySQL's SQL dialect, DATE and DATETIME are compared as datetime
-          values. Index over a DATE column uses DATE comparison. Changing 
-          from one comparison to the other is possible:
+            In MySQL's SQL dialect, DATE and DATETIME are compared as datetime
+            values. Index over a DATE column uses DATE comparison. Changing 
+            from one comparison to the other is possible:
 
-          datetime(date_col)< '2007-12-10 12:34:55' -> date_col<='2007-12-10'
-          datetime(date_col)<='2007-12-10 12:34:55' -> date_col<='2007-12-10'
+            datetime(date_col)< '2007-12-10 12:34:55' -> date_col<='2007-12-10'
+            datetime(date_col)<='2007-12-10 12:34:55' -> date_col<='2007-12-10'
 
-          datetime(date_col)> '2007-12-10 12:34:55' -> date_col>='2007-12-10'
-          datetime(date_col)>='2007-12-10 12:34:55' -> date_col>='2007-12-10'
+            datetime(date_col)> '2007-12-10 12:34:55' -> date_col>='2007-12-10'
+            datetime(date_col)>='2007-12-10 12:34:55' -> date_col>='2007-12-10'
 
-          but we'll need to convert '>' to '>=' and '<' to '<='. This will
-          be done together with other types at the end of this function
-          (grep for field_is_equal_to_item)
-        */
+            but we'll need to convert '>' to '>=' and '<' to '<='. This will
+            be done together with other types at the end of this function
+            (grep for field_is_equal_to_item)
+          */
+        }
+        else
+          goto end;
       }
-      else
-        goto end;
     }
-  } 
-  if (err < 0)
+
+    /*
+      guaranteed at this point:  err > 0; field and const of same type
+      If an integer got bounded (e.g. to within 0..255 / -128..127)
+      for < or >, set flags as for <= or >= (no NEAR_MAX / NEAR_MIN)
+    */
+    else if (err == 1 && field->result_type() == INT_RESULT)
+    {
+      if (type == Item_func::LT_FUNC && (value->val_int() > 0))
+        type = Item_func::LE_FUNC;
+      else if (type == Item_func::GT_FUNC &&
+               !((Field_num*)field)->unsigned_flag &&
+               !((Item_int*)value)->unsigned_flag &&
+               (value->val_int() < 0))
+        type = Item_func::GE_FUNC;
+    }
+  }
+  else if (err < 0)
   {
     field->table->in_use->variables.sql_mode= orig_sql_mode;
     /* This happens when we try to insert a NULL field in a not null column */
Thread
bk commit into 5.0 tree (tnurnberg:1.2595) BUG#34731Tatjana A Nuernberg10 Mar