bzr commit into mysql-5.1-bugteam branch (Alexey.Kopytov:3181) Bug#47123

[Alexey Kopytov]

#At file:///data/src/bzr/bugteam/mysql-5.1-bugteam/ based on revid:joerg@stripped

 3181 Alexey Kopytov	2009-10-17
      Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN 
       
      The problem was in incorrect handling of predicates involving 
      NULL as a constant value by the range optimizer. 
       
      For example, when creating a SEL_ARG node from a condition of 
      the form "field < const" (which would normally result in the 
      "NULL < field < const" SEL_ARG),  the special case when "const" 
      is NULL was not taken into account, so "NULL < field < NULL" 
      was produced for the "field < NULL" condition. 
       
      As a result, SEL_ARG structures of this form could not be 
      further optimized which in turn could lead to incorrectly 
      constructed SEL_ARG trees. In particular, code assuming SEL_ARG 
      structures to always form a sequence of ordered disjoint 
      intervals could enter an infinite loop under some 
      circumstances. 
       
      Fixed by changing get_mm_leaf() so that for any sargable 
      predicate except "<=>" involving NULL as a constant, "empty" 
      SEL_ARG is returned, since such a predicate is always false. 
     @ mysql-test/r/partition_pruning.result
        Fixed a broken test case.
     @ mysql-test/r/range.result
        Added a test case for bug #47123.
     @ mysql-test/r/subselect.result
        Fixed a broken test cases.
     @ mysql-test/t/range.test
        Added a test case for bug #47123.
     @ sql/opt_range.cc
        Fixed get_mm_leaf() so that for any sargable
        predicate except "<=>" involving NULL as a constant, "empty"
        SEL_ARG is returned, since such a predicate is always false.

    modified:
      mysql-test/r/partition_pruning.result
      mysql-test/r/range.result
      mysql-test/r/subselect.result
      mysql-test/t/range.test
      sql/opt_range.cc
=== modified file 'mysql-test/r/partition_pruning.result'
--- a/mysql-test/r/partition_pruning.result	2009-09-01 12:53:27 +0000
+++ b/mysql-test/r/partition_pruning.result	2009-10-16 20:19:51 +0000
@@ -1272,10 +1272,9 @@ INSERT INTO t1 VALUES (1, '2009-01-01'),
 # test with an invalid date, which lead to item->null_value is set.
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p20090401	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
 Warning	1292	Incorrect datetime value: '2009-04-99'
-Warning	1292	Incorrect datetime value: '2009-04-99'
 DROP TABLE t1;
 CREATE TABLE t1
 (a INT NOT NULL AUTO_INCREMENT,

=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result	2009-10-16 08:42:16 +0000
+++ b/mysql-test/r/range.result	2009-10-16 20:19:51 +0000
@@ -1398,3 +1398,12 @@ a < 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	range	a	a	5	NULL	8	Using where; Using index
 DROP TABLE t1, t2, t3;
+#
+# Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN
+#
+CREATE TABLE t1(a INT, KEY(a));
+INSERT INTO t1 VALUES (1), (NULL);
+SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL);
+a
+DROP TABLE t1;
+End of 5.1 tests

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2009-10-14 14:36:11 +0000
+++ b/mysql-test/r/subselect.result	2009-10-16 20:19:51 +0000
@@ -4403,8 +4403,7 @@ FROM t1 
 WHERE a = 230;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DEPENDENT SUBQUERY	st1	index	NULL	a	5	NULL	2	Using index
-2	DEPENDENT SUBQUERY	st2	index	b	b	5	NULL	2	Using where; Using index; Using join buffer
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
 FROM t1 
 WHERE a = 230;

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2009-10-16 08:42:16 +0000
+++ b/mysql-test/t/range.test	2009-10-16 20:19:51 +0000
@@ -1171,3 +1171,14 @@ a < 5 OR
 a < 10;
 
 DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN
+--echo #
+
+CREATE TABLE t1(a INT, KEY(a));
+INSERT INTO t1 VALUES (1), (NULL);
+SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL);
+DROP TABLE t1;
+
+--echo End of 5.1 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2009-10-16 08:42:16 +0000
+++ b/sql/opt_range.cc	2009-10-16 20:19:51 +0000
@@ -5891,6 +5891,17 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
     goto end;
   }
   field->table->in_use->variables.sql_mode= orig_sql_mode;
+
+  /*
+    Any sargable predicate except "<=>" involving NULL as a constant is always
+    FALSE
+  */
+  if (type != Item_func::EQUAL_FUNC && field->is_real_null())
+  {
+    tree= &null_element;
+    goto end;
+  }
+  
   str= (uchar*) alloc_root(alloc, key_part->store_length+1);
   if (!str)
     goto end;


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