maria-developers team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

Igor Babaev <igor@xxxxxxxxxxxx> 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)