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/range.result:
  Added a test case for bug #47123.
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.

3 files changed, 30 insertions, 0 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index cc5e8d2be96..69ae81982a9 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -1219,3 +1219,12 @@ explain select * from t2 where a=1000 and b<11;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	a	a	5	const	502	Using where
 drop table t1, t2;
+#
+# 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
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index e1411e7fd46..7b35f69c041 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1046,3 +1046,13 @@ explain select * from t2 where a=1000 and b<11;
 
 drop table t1, t2;
 
+--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
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 2239aafbeec..cfaff76b96b 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -5887,6 +5887,17 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
     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;