bzr commit into mysql-5.1-bugteam branch (gshchepa:3117) Bug#44139

[Gleb Shchepa]

#At file:///work/44139-5.1/ based on revid:joro@stripped

 3117 Gleb Shchepa	2009-09-19
      Bug #44139: Table scan when NULL appears in IN clause
      
      SELECT ... WHERE ... IN (NULL, ...) does full table scan,
      even if the same query without the NULL uses efficient range scan.
      
      1. The collect_cmp_types() function has been modified to
         heading NULLs in the IN list.
      2. NULL-skipping code of the Item_func_in::fix_length_and_dec()
         function has been updated to prevent SEGVs.
     @ mysql-test/r/func_in.result
        Added test case for the bug #44139.
     @ mysql-test/t/func_in.test
        Added test case for the bug #44139.
     @ sql/item_cmpfunc.cc
        Bug #44139: Table scan when NULL appears in IN clause
        
        1. The collect_cmp_types() function has been modified to
           heading NULLs in the IN list.
        2. NULL-skipping code of the Item_func_in::fix_length_and_dec()
           function has been updated to prevent SEGVs.

    modified:
      mysql-test/r/func_in.result
      mysql-test/t/func_in.test
      sql/item_cmpfunc.cc
=== modified file 'mysql-test/r/func_in.result'
--- a/mysql-test/r/func_in.result	2009-05-25 08:00:40 +0000
+++ b/mysql-test/r/func_in.result	2009-09-19 15:54:44 +0000
@@ -608,4 +608,19 @@ SELECT SUM( DISTINCT e ) FROM t1 GROUP B
 ((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d));
 SUM( DISTINCT e )
 DROP TABLE t1;
+#
+# Bug #44139: Table scan when NULL appears in IN clause
+#
+CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
+INSERT INTO t1 (b) VALUES (1),(1),(1),(1),(1);
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE a IN (160000, 160001, 160002);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using where
+EXPLAIN SELECT * FROM t1 WHERE a IN (NULL, 160000, 160001, 160002);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using where
+DROP TABLE t1;
+#
 End of 5.1 tests

=== modified file 'mysql-test/t/func_in.test'
--- a/mysql-test/t/func_in.test	2009-05-25 08:00:40 +0000
+++ b/mysql-test/t/func_in.test	2009-09-19 15:54:44 +0000
@@ -456,4 +456,21 @@ SELECT SUM( DISTINCT e ) FROM t1 GROUP B
   ((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d));
 DROP TABLE t1;
 
+--echo #
+--echo # Bug #44139: Table scan when NULL appears in IN clause
+--echo #
+
+CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
+
+INSERT INTO t1 (b) VALUES (1),(1),(1),(1),(1);
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE a IN (160000, 160001, 160002);
+EXPLAIN SELECT * FROM t1 WHERE a IN (NULL, 160000, 160001, 160002);
+
+DROP TABLE t1;
+
+--echo #
+
 --echo End of 5.1 tests

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2009-08-28 16:21:54 +0000
+++ b/sql/item_cmpfunc.cc	2009-09-19 15:54:44 +0000
@@ -199,7 +199,7 @@ enum_field_types agg_field_type(Item **i
     Bitmap of collected types - otherwise
 */
 
-static uint collect_cmp_types(Item **items, uint nitems)
+static uint collect_cmp_types(Item **items, uint nitems, bool skip_nulls= false)
 {
   uint i;
   uint found_types;
@@ -208,6 +208,8 @@ static uint collect_cmp_types(Item **ite
   found_types= 0;
   for (i= 1; i < nitems ; i++)
   {
+    if (skip_nulls && items[i]->type() == Item::NULL_ITEM)
+      continue;
     if ((left_result == ROW_RESULT || 
          items[i]->result_type() == ROW_RESULT) &&
         cmp_row_type(items[0], items[i]))
@@ -215,6 +217,8 @@ static uint collect_cmp_types(Item **ite
     found_types|= 1<< (uint)item_cmp_type(left_result,
                                            items[i]->result_type());
   }
+  if (skip_nulls && !found_types && nitems >= 1)
+    found_types= 1 << (uint)item_cmp_type(left_result, items[1]->result_type());
   return found_types;
 }
 
@@ -3515,7 +3519,7 @@ void Item_func_in::fix_length_and_dec()
   uint type_cnt= 0, i;
   Item_result cmp_type= STRING_RESULT;
   left_result_type= args[0]->result_type();
-  if (!(found_types= collect_cmp_types(args, arg_count)))
+  if (!(found_types= collect_cmp_types(args, arg_count, true)))
     return;
   
   for (arg= args + 1, arg_end= args + arg_count; arg != arg_end ; arg++)
@@ -3693,9 +3697,11 @@ void Item_func_in::fix_length_and_dec()
       uint j=0;
       for (uint i=1 ; i < arg_count ; i++)
       {
-	array->set(j,args[i]);
 	if (!args[i]->null_value)			// Skip NULL values
+        {
+          array->set(j,args[i]);
 	  j++;
+        }
 	else
 	  have_null= 1;
       }


Attachment: [text/bzr-bundle] bzr/[e-mail]