Igor Babaev <igor@askmonty.org> writes:
Below you'll find a patch for bug #53334 (against mysql-5.1.46).
Thanks! I pushed it into 5.1-release (to be included in upcoming MariaDB 5.1.46).
I investigated other latest fixes pushed by Sergey Glukhov.
The fix for bugs ##51242/52336 looked suspicious for me. And it actually provides a work-around rather than fixes the following bug that causes both failures:
In the function JOIN::exec there should be Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, used_tables, (table_map) 0); instead of Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, used_tables, used_tables);
Very good that you could look into this. So if I understand you correctly, you suggest to revert Sergey Glukhov's fixes for bugs ##51242/52336, and instead apply your suggested fix above? I tried this (patch included below), reverting the code changes but leaving the new test cases in place. The test suite passes with this change, but with the following explain plan change (the change for Note 1003 seen in the patch is just reverting Sergey Glukhov's patches): CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT); INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9); EXPLAIN EXTENDED SELECT table1.f1, table2.f2 FROM t1 AS table1 JOIN t1 AS table2 ON table1.f3 = table2.f3 WHERE table2.f1 = 2 GROUP BY table1.f1, table2.f2 HAVING (table2.f2 = 8 AND table1.f1 >= 6); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables +1 SIMPLE table2 const PRIMARY PRIMARY 4 const 1 100.00 Using filesort +1 SIMPLE table1 ALL NULL NULL NULL NULL 4 100.00 Using where Please let me know if you think I should push this patch. - Kristian. ----------------------------------------------------------------------- === modified file 'mysql-test/r/having.result' --- mysql-test/r/having.result 2010-04-05 11:10:26 +0000 +++ mysql-test/r/having.result 2010-05-03 12:33:32 +0000 @@ -470,9 +470,10 @@ WHERE table2.f1 = 2 GROUP BY table1.f1, table2.f2 HAVING (table2.f2 = 8 AND table1.f1 >= 6); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables +1 SIMPLE table2 const PRIMARY PRIMARY 4 const 1 100.00 Using filesort +1 SIMPLE table1 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having 0 +Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having (('7' = 8) and (`test`.`table1`.`f1` >= 6)) EXPLAIN EXTENDED SELECT table1.f1, table2.f2 FROM t1 AS table1 @@ -481,9 +482,10 @@ WHERE table2.f1 = 2 GROUP BY table1.f1, table2.f2 HAVING (table2.f2 = 8); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables +1 SIMPLE table2 const PRIMARY PRIMARY 4 const 1 100.00 Using filesort +1 SIMPLE table1 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having 0 +Note 1003 select `test`.`table1`.`f1` AS `f1`,'7' AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where ((`test`.`table1`.`f3` = '9')) group by `test`.`table1`.`f1`,'7' having ('7' = 8) DROP TABLE t1; # # Bug#52336 Segfault / crash in 5.1 copy_fields (param=0x9872980) at sql_select.cc:15355 === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2010-05-03 08:44:39 +0000 +++ sql/sql_select.cc 2010-05-03 12:29:54 +0000 @@ -1122,31 +1122,6 @@ JOIN::optimize() { conds=new Item_int((longlong) 0,1); // Always false } - - /* - It's necessary to check const part of HAVING cond as - there is a chance that some cond parts may become - const items after make_join_statisctics(for example - when Item is a reference to cost table field from - outer join). - This check is performed only for those conditions - which do not use aggregate functions. In such case - temporary table may not be used and const condition - elements may be lost during further having - condition transformation in JOIN::exec. - */ - if (having && const_table_map) - { - having->update_used_tables(); - having= remove_eq_conds(thd, having, &having_value); - if (having_value == Item::COND_FALSE) - { - having= new Item_int((longlong) 0,1); - zero_result_cause= "Impossible HAVING noticed after reading const tables"; - DBUG_RETURN(0); - } - } - if (make_join_select(this, select, conds)) { zero_result_cause= @@ -2209,7 +2184,7 @@ JOIN::exec() Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, used_tables, - used_tables); + (table_map) 0); if (sort_table_cond) { if (!curr_table->select)