#At file:///work/bzrroot/backporting-next-mr-2/ based on revid:epotemkin@stripped
3173 Evgeny Potemkin 2010-05-13
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.
Original revid:epotemkin@stripped
@ mysql-test/r/select.result
A test case added for the bug#45277.
@ mysql-test/r/select_jcl6.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/r/select_jcl6.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 2010-05-12 16:03:52 +0000
+++ b/mysql-test/r/select.result 2010-05-13 16:40:00 +0000
@@ -4928,3 +4928,34 @@ f1
b
DROP TABLE t1;
SET SESSION optimizer_switch=@old_optimizer_switch;
+#
+# 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 test#45277
=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result 2010-05-12 16:03:52 +0000
+++ b/mysql-test/r/select_jcl6.result 2010-05-13 16:40:00 +0000
@@ -4932,6 +4932,37 @@ f1
b
DROP TABLE t1;
SET SESSION optimizer_switch=@old_optimizer_switch;
+#
+# 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 test#45277
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
Variable_name Value
=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test 2010-05-07 21:14:09 +0000
+++ b/mysql-test/t/select.test 2010-05-13 16:40:00 +0000
@@ -4190,3 +4190,28 @@ DROP TABLE t1;
SET SESSION optimizer_switch=@old_optimizer_switch;
+--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
+
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-05-13 08:48:37 +0000
+++ b/sql/sql_select.cc 2010-05-13 16:40:00 +0000
@@ -18860,6 +18860,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);
@@ -18927,11 +18928,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->set_select_cond(tab->pre_idx_push_select_cond, __LINE__);
- if (tab->select)
- tab->select->cond= tab->select_cond;
- }
+ orig_select_cond= tab->set_cond(tab->pre_idx_push_select_cond, __LINE__);
+
if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts,
&usable_keys)) < MAX_KEY)
{
@@ -18952,7 +18950,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
if (create_ref_for_key(tab->join, tab, keyuse,
tab->join->const_table_map))
- DBUG_RETURN(0);
+ goto use_filesort;
pick_table_access_method(tab);
}
@@ -18977,7 +18975,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
tab->join->unit->select_limit_cnt,0,
TRUE) <=
0)
- DBUG_RETURN(0);
+ goto use_filesort;
}
ref_key= new_ref_key;
}
@@ -19177,7 +19175,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
tab->join->tables > tab->join->const_tables + 1) &&
((unsigned) best_key != table->s->primary_key ||
!table->file->primary_key_is_clustered()))
- DBUG_RETURN(0);
+ goto use_filesort;
if (best_key >= 0)
{
@@ -19217,9 +19215,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
table->set_keyread(TRUE);
if (tab->pre_idx_push_select_cond)
{
- tab->set_select_cond(tab->pre_idx_push_select_cond, __LINE__);
- 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, __LINE__);
+ /* 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)
@@ -19254,7 +19258,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
order_direction= best_key_direction;
}
else
- DBUG_RETURN(0);
+ goto use_filesort;
}
check_reverse_order:
@@ -19278,7 +19282,7 @@ check_reverse_order:
{
tab->limit= 0;
select->quick= save_quick;
- DBUG_RETURN(0); // Use filesort
+ goto use_filesort; // Use filesort
}
/* ORDER BY range_key DESC */
@@ -19289,7 +19293,7 @@ check_reverse_order:
delete tmp;
select->quick= save_quick;
tab->limit= 0;
- DBUG_RETURN(0); // Reverse sort not supported
+ goto use_filesort; // Reverse sort not supported
}
select->quick=tmp;
}
@@ -19309,7 +19313,14 @@ check_reverse_order:
}
else if (select && select->quick)
select->quick->need_sorted_output();
+ if (orig_select_cond)
+ tab->set_cond(orig_select_cond, __LINE__);
DBUG_RETURN(1);
+
+use_filesort:
+ if (orig_select_cond)
+ tab->set_cond(orig_select_cond, __LINE__);
+ DBUG_RETURN(0);
}
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2010-05-11 16:23:10 +0000
+++ b/sql/sql_select.h 2010-05-13 16:40:00 +0000
@@ -355,6 +355,14 @@ typedef struct st_join_table
select_cond, to, line, this));
select_cond= to;
}
+ COND *set_cond(COND *new_cond, uint line)
+ {
+ COND *tmp_select_cond= select_cond;
+ set_select_cond(new_cond, line);
+ if (select)
+ select->cond= new_cond;
+ return tmp_select_cond;
+ }
} JOIN_TAB;
/*
Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20100513164000-3166lrje1ifgl3t3.bundle
| Thread |
|---|
| • bzr commit into mysql-next-mr-bugfixing branch (epotemkin:3173)Bug#45227 | Evgeny Potemkin | 13 May |
