#At file:///work/bzrroot/45227-bug-next/ based on revid:epotemkin@stripped
2864 Evgeny Potemkin 2009-09-03
Bug#45227: Lost HAVING clause led to a wrong result.
The per-table condition is stored in the select_cond variable for each table.
When index condition pushdown is used it is saved prior to
pushing to pre_idx_push_select_cond variable. After that condition is split
and pushable part is pushed and another part is left in the select_cond
variable. If condition is fully pushed the select_cond variable is set to NULL.
When there is no GROUP BY clause or aggregate functions HAVING condition is appended
to select_cond variable.
The test_if_skip_sort_order function tries to find an index to use for sorting
instead of filesort and to do so it restores original condition. It assumes that
condition is fully saved in the pre_idx_push_select_cond and simply overwrites the
select_cond variable, thus loosing the appended HAVING condition.
Now the test_if_skip_sort_order function saves original condition from
select_cond variable prior to overwriting it and restores it before
returning.
The JOIN_TAB::set_cond function added to correctly set per-table condition.
@ mysql-test/r/select.result
A test case added for the bug#45277.
@ mysql-test/t/select.test
A test case added for the bug#45277.
@ sql/sql_select.cc
Bug#45227: Lost HAVING clause led to a wrong result.
Now the test_if_skip_sort_order function saves original condition from
select_cond variable prior to overwriting it and restores it before
returning.
@ sql/sql_select.h
Bug#45227: Lost HAVING clause led to a wrong result.
The JOIN_TAB::set_cond function added to correctly set per-table condition.
modified:
mysql-test/r/select.result
mysql-test/t/select.test
sql/sql_select.cc
sql/sql_select.h
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result 2009-07-06 08:38:21 +0000
+++ b/mysql-test/r/select.result 2009-09-03 12:50:59 +0000
@@ -4585,4 +4585,35 @@ time_key
15:13:38
drop table A,AA,B,BB;
# end of test#45266
+#
+# Bug#45277: Lost HAVING clause led to a wrong result.
+#
+CREATE TABLE `CC` (
+`int_nokey` int(11) NOT NULL,
+`int_key` int(11) NOT NULL,
+`varchar_key` varchar(1) NOT NULL,
+`varchar_nokey` varchar(1) NOT NULL,
+KEY `int_key` (`int_key`),
+KEY `varchar_key` (`varchar_key`)
+);
+INSERT INTO `CC` VALUES
+(0,8,'q','q'),(5,8,'m','m'),(7,3,'j','j'),(1,2,'z','z'),(8,2,'a','a'),(2,6,'',''),(1,8,'e'
+,'e'),(8,9,'t','t'),(5,2,'q','q'),(4,6,'b','b'),(5,5,'w','w'),(3,2,'m','m'),(0,4,'x','x'),
+(8,9,'',''),(0,6,'w','w'),(4,5,'x','x'),(0,0,'e','e'),(0,0,'e','e'),(2,8,'p','p'),(0,0,'x'
+,'x');
+EXPLAIN SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4
+HAVING G1 ORDER BY `varchar_key` LIMIT 6 ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE CC range int_key int_key 4 NULL 10 Using index condition; Using where; Using MRR; Using filesort
+SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4
+HAVING G1 ORDER BY `varchar_key` LIMIT 6 ;
+G1
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'j'
+Warning 1292 Truncated incorrect DOUBLE value: 'z'
+Warning 1292 Truncated incorrect DOUBLE value: 'a'
+Warning 1292 Truncated incorrect DOUBLE value: 'q'
+Warning 1292 Truncated incorrect DOUBLE value: 'm'
+DROP TABLE CC;
+# End of bug#45277
End of 6.0 tests
=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test 2009-07-06 08:38:21 +0000
+++ b/mysql-test/t/select.test 2009-09-03 12:50:59 +0000
@@ -3938,4 +3938,29 @@ SELECT B.`time_key` FROM B LEFT JOIN BB
drop table A,AA,B,BB;
--echo # end of test#45266
+--echo #
+--echo # Bug#45277: Lost HAVING clause led to a wrong result.
+--echo #
+CREATE TABLE `CC` (
+ `int_nokey` int(11) NOT NULL,
+ `int_key` int(11) NOT NULL,
+ `varchar_key` varchar(1) NOT NULL,
+ `varchar_nokey` varchar(1) NOT NULL,
+ KEY `int_key` (`int_key`),
+ KEY `varchar_key` (`varchar_key`)
+);
+INSERT INTO `CC` VALUES
+(0,8,'q','q'),(5,8,'m','m'),(7,3,'j','j'),(1,2,'z','z'),(8,2,'a','a'),(2,6,'',''),(1,8,'e'
+,'e'),(8,9,'t','t'),(5,2,'q','q'),(4,6,'b','b'),(5,5,'w','w'),(3,2,'m','m'),(0,4,'x','x'),
+(8,9,'',''),(0,6,'w','w'),(4,5,'x','x'),(0,0,'e','e'),(0,0,'e','e'),(2,8,'p','p'),(0,0,'x'
+,'x');
+EXPLAIN SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4
+ HAVING G1 ORDER BY `varchar_key` LIMIT 6 ;
+
+SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4
+ HAVING G1 ORDER BY `varchar_key` LIMIT 6 ;
+
+DROP TABLE CC;
+--echo # End of test#45277
+
--echo End of 6.0 tests
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-07-28 14:16:37 +0000
+++ b/sql/sql_select.cc 2009-09-03 12:50:59 +0000
@@ -18465,6 +18465,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
SQL_SELECT *select=tab->select;
key_map usable_keys;
QUICK_SELECT_I *save_quick= 0;
+ COND *orig_select_cond= 0;
DBUG_ENTER("test_if_skip_sort_order");
LINT_INIT(ref_key_parts);
@@ -18572,11 +18573,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
if (table->covering_keys.is_set(ref_key))
usable_keys.intersect(table->covering_keys);
if (tab->pre_idx_push_select_cond)
- {
- tab->select_cond= tab->pre_idx_push_select_cond;
- if (tab->select)
- tab->select->cond= tab->select_cond;
- }
+ orig_select_cond= tab->set_cond(tab->pre_idx_push_select_cond);
+
if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts,
&usable_keys)) < MAX_KEY)
{
@@ -18852,9 +18850,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
}
if (tab->pre_idx_push_select_cond)
{
- tab->select_cond= tab->pre_idx_push_select_cond;
- if (tab->select)
- tab->select->cond= tab->select_cond;
+ COND *tmp_cond= tab->pre_idx_push_select_cond;
+ if (orig_select_cond)
+ {
+ tmp_cond= and_conds(tmp_cond, orig_select_cond);
+ tmp_cond->quick_fix_field();
+ }
+ tab->set_cond(tmp_cond);
+ /* orig_select_cond was merged, no need to restore original one. */
+ orig_select_cond= 0;
}
table->file->ha_index_or_rnd_end();
if (join->select_options & SELECT_DESCRIBE)
@@ -18944,9 +18948,13 @@ check_reverse_order:
}
else if (select && select->quick)
select->quick->need_sorted_output();
+ if (orig_select_cond)
+ tab->set_cond(orig_select_cond);
DBUG_RETURN(1);
use_filesort:
table->file->extra(HA_EXTRA_NO_ORDERBY_LIMIT);
+ if (orig_select_cond)
+ tab->set_cond(orig_select_cond);
DBUG_RETURN(0);
}
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2009-08-26 09:14:05 +0000
+++ b/sql/sql_select.h 2009-09-03 12:50:59 +0000
@@ -340,6 +340,14 @@ typedef struct st_join_table
return first_inner;
return first_sj_inner_tab;
}
+ COND *set_cond(COND *new_cond)
+ {
+ COND *tmp_select_cond= select_cond;
+ select_cond= new_cond;
+ if (select)
+ select->cond= new_cond;
+ return tmp_select_cond;
+ }
} JOIN_TAB;
/*
Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20090903125059-z195j43fd6nvdws2.bundle
| Thread |
|---|
| • bzr commit into mysql-5.4 branch (epotemkin:2864) Bug#45227 | Evgeny Potemkin | 3 Sep |
