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#20954 | timour | 25 Jul |
