MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:timour Date:July 25 2006 7:32pm
Subject:bk commit into 4.1 tree (timour:1.2537) BUG#20954
View as plain text  
Below is the list of changes that have just been committed into a local
4.1 repository of timka. When timka 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, 2006-07-25 22:31:58+03:00, timour@stripped +5 -0
  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@stripped, 2006-07-25 22:31:56+03:00, timour@stripped +2 -0
    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@stripped, 2006-07-25 22:31:56+03:00, timour@stripped +49 -0
    BUG#20954 - added test

  mysql-test/r/subselect.result@stripped, 2006-07-25 22:31:57+03:00, timour@stripped +1 -1
    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@stripped, 2006-07-25 22:31:57+03:00, timour@stripped +28 -0
    BUG#20954 - added test

  sql/sql_select.cc@stripped, 2006-07-25 22:31:57+03:00, timour@stripped +18 -0
    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.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	timour
# Host:	lamia.home
# Root:	/home/timka/mysql/src/4.1-bug-20954

--- 1.460/sql/sql_select.cc	2006-07-25 22:32:05 +03:00
+++ 1.461/sql/sql_select.cc	2006-07-25 22:32:05 +03:00
@@ -547,6 +547,24 @@ JOIN::optimize()
       }
       zero_result_cause= "Select tables optimized away";
       tables_list= 0;				// All tables resolved
+      /*
+        Extract all table-independent conditions and replace the WHERE
+        clause with them. All other conditions were computed by opt_sum_query
+        and the MIN/MAX/COUNT function(s) have been replaced by constants,
+        so there is no need to compute the whole WHERE clause again.
+        Notice that make_cond_for_table() will always succeed to remove all
+        computed conditions, because opt_sum_query() is applicable only to
+        conjunctions.
+      */
+      if (conds)
+      {
+        COND *table_independent_conds=
+          make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0);
+        DBUG_EXECUTE("where",
+                     print_where(table_independent_conds,
+                                 "where after opt_sum_query()"););
+        conds= table_independent_conds;
+      }
     }
   }
   if (!tables_list)

--- 1.178/mysql-test/r/subselect.result	2006-07-25 22:32:05 +03:00
+++ 1.179/mysql-test/r/subselect.result	2006-07-25 22:32:05 +03:00
@@ -539,7 +539,7 @@ EXPLAIN EXTENDED SELECT MAX(numreponse) 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select max(test.t1.numreponse) AS `MAX(numreponse)` from test.t1 where (test.t1.numeropost = _latin1'1')
+Note	1003	select max(test.t1.numreponse) AS `MAX(numreponse)` from test.t1
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	Using index

--- 1.40/mysql-test/r/func_group.result	2006-07-25 22:32:05 +03:00
+++ 1.41/mysql-test/r/func_group.result	2006-07-25 22:32:05 +03:00
@@ -824,6 +824,7 @@ select 1, min(a) from t1m where 1=99;
 1	NULL
 select 1, min(1) from t1m where a=99;
 1	min(1)
+1	NULL
 select 1, min(1) from t1m where 1=99;
 1	min(1)
 1	NULL
@@ -835,6 +836,7 @@ select 1, max(a) from t1m where 1=99;
 1	NULL
 select 1, max(1) from t1m where a=99;
 1	max(1)
+1	NULL
 select 1, max(1) from t1m where 1=99;
 1	max(1)
 1	NULL

--- 1.75/mysql-test/r/select.result	2006-07-25 22:32:05 +03:00
+++ 1.76/mysql-test/r/select.result	2006-07-25 22:32:05 +03:00
@@ -2744,3 +2744,52 @@ SELECT i='1e+01',i=1e+01, i in (1e+01), 
 i='1e+01'	i=1e+01	i in (1e+01)	i in ('1e+01')
 0	1	1	1
 DROP TABLE t1;
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select max(key2) from t2 where key2 <= 1.6158;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select min(key1) from t1 where key1 >= 0.3762;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select min(key2) from t2 where key2 >= 1.3762;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select max(key1), min(key2) from t1, t2
+where key1 <= 0.6158 and key2 >= 1.3762;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select max(key1) from t1 where key1 <= 0.6158;
+max(key1)
+0.61580002307892
+select max(key2) from t2 where key2 <= 1.6158;
+max(key2)
+1.6158000230789
+select min(key1) from t1 where key1 >= 0.3762;
+min(key1)
+0.37619999051094
+select min(key2) from t2 where key2 >= 1.3762;
+min(key2)
+1.3761999607086
+select max(key1), min(key2) from t1, t2
+where key1 <= 0.6158 and key2 >= 1.3762;
+max(key1)	min(key2)
+0.61580002307892	1.3761999607086
+select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
+max(key1)
+0.61580002307892
+select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
+min(key1)
+0.37619999051094
+DROP TABLE t1,t2;

--- 1.58/mysql-test/t/select.test	2006-07-25 22:32:05 +03:00
+++ 1.59/mysql-test/t/select.test	2006-07-25 22:32:05 +03:00
@@ -2297,4 +2297,32 @@ 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"
+#
+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;
+
 # End of 4.1 tests
Thread
bk commit into 4.1 tree (timour:1.2537) BUG#20954timour25 Jul