Fix for BUG#20954: avg(keyval) retuns 0.38 but max(keyval) returns an empty set

The problem was in that opt_sum_query() replaced MIN/MAX functions
with the corresponding constant found in a key, but due to imprecise
representation of float numbers, when evaluating the where clause,
this comparison failed.

When MIN/MAX optimization detects that all tables can be removed,
also remove all conjuncts in a where clause that refer to these
tables. As a result of this fix, these conditions are not evaluated
twice, and in the case of float number comparisons we do not discard
result rows due to imprecise float representation.

As a side-effect this fix also corrects an unnoticed problem in
bug 12882.


mysql-test/r/func_group.result:
  BUG#20954 - test result adjustment.
  Adjusted the test result of bug 12882 which was not preperly fixed.
  The current patch corrects the problem that was fully corrected by the
  patch for 12882.
  
  The problem was that opt_sum_query() indicated that the optimizer may
  remove all tables because all MIN/MAX/COUNT functions are constants,
  but this lead to an empty result instead of NULL because the WHERE
  clause was still evaluated.
  
  The current fix removes all conjuncts in the where clause that
  reference the removed tables, and thus corrects the problem.
mysql-test/r/select.result:
  BUG#20954 - added test
mysql-test/r/subselect.result:
  BUG#20954 - test result adjustment.
  
  The fix removes those conditions in a where clause that refer to
  tables optimized away by MIN/MAX optimization (opt_sum_query()).
mysql-test/t/select.test:
  BUG#20954 - added test
sql/sql_select.cc:
  Fix for BUG#20954: avg(keyval) retuns 0.38 but max(keyval) returns an empty set
  
  When MIN/MAX optimization detects that all tables can be removed,
  also remove all conjuncts in a where clause that refer to these
  tables. As a result of this fix, these conditions are not evaluated
  twice, and in the case of float number comparisons we do not discard
  result rows due to imprecise float representation.
  
  As a side-effect this fix also corrects an unnoticed problem in
  bug 12882.

1 files changed, 30 insertions, 0 deletions

diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 4cdfc220350..acf9fd77c1b 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2297,4 +2297,34 @@ INSERT INTO t1 VALUES (10);
 SELECT i='1e+01',i=1e+01, i in (1e+01), i in ('1e+01') FROM t1;
 DROP TABLE t1;
 
+#
+# Bug #20954 "avg(keyval) retuns 0.38 but max(keyval) returns an empty set"
+#
+--disable_ps_protocol
+CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1));
+CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2));
+INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941);
+INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941);
+
+explain select max(key1) from t1 where key1 <= 0.6158;
+explain select max(key2) from t2 where key2 <= 1.6158;
+explain select min(key1) from t1 where key1 >= 0.3762;
+explain select min(key2) from t2 where key2 >= 1.3762;
+explain select max(key1), min(key2) from t1, t2
+where key1 <= 0.6158 and key2 >= 1.3762;
+explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
+explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
+
+select max(key1) from t1 where key1 <= 0.6158;
+select max(key2) from t2 where key2 <= 1.6158;
+select min(key1) from t1 where key1 >= 0.3762;
+select min(key2) from t2 where key2 >= 1.3762;
+select max(key1), min(key2) from t1, t2
+where key1 <= 0.6158 and key2 >= 1.3762;
+select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
+select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
+
+DROP TABLE t1,t2;
+--enable_ps_protocol
+
 # End of 4.1 tests