Hi Sergey,
the fix is probably alright, although I don't understand 100%. Some
thoughts on tests and comment as well.
Sergey Glukhov wrote:
> #Atfile:///home/gluh/MySQL/mysql-5.1-bugteam/ based
> onrevid:bjorn.munch@stripped
>
> 3515 Sergey Glukhov 2010-12-10
> Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY
> clause
> Before sorting HAVING condition is splitted into two parts,
>
splitted => split
> first past is a table related condition and the rest of is
>
"first past" => "the first part"
> HAVING part. Extraction of HAVING part does not take into account
>
What does it mean that "The rest is the HAVING part"? AFAIU everything
in the HAVING clause is the HAVING part. Do you mean that you move
something to a different clause? E.g. WHERE clause or JOIN predicate? Or
are some parts of the HAVING clause evaluated earlier than other parts?
> the fact that some of conditions might be non-const but
>
> have 'used_tables' == 0 (undependent subqueries)
>
undependent => independent
> and because of that these conditions are cut off by
> make_cond_for_table() function.
>
Could you please describe this on a slightly higher level?
After some studying, this is how I understand it, please correct me if
I'm wrong:
If there is no GROUP BY clause, (parts of) the HAVING condition can be
attached to the table referenced in ORDER BY.
That is the mechanism.
However we have to make sure that conditions that don't reference any
table in this query block are not lost.
The extraction function has two modes of operation: It can either
extract conditions for a particular table or it can extract conditions
for a set of tables (the 'tables' parameter). If extracting conditions
for a particular table, it is assumed that all conditions for other
tables are already checked. But in this case they're not, so we have to
use the other mode.
It is safe to use this mode since the 'tables' parameter is only one
table. It comes from this line:
table_map used_tables= (curr_join->const_table_map |
curr_table->table->map);
Am I correct?
> The fix is to use (table_map) 0 instead of used_tables in
> third argument for make_cond_for_table() function.
> It allows to extract elements which belong to sorted
> table and in addition elements which are undependend
>
I don't really understand why there is only one sorted table. But it
seems there always is. Perhaps you can explain it to me? (No need to
explain it in the bug report, though)
undependend -> independent
> subqueries.
> @ mysql-test/r/having.result
> test case
>
This is probably the test result. :-)
> @ mysql-test/t/having.test
> test case
> @ sql/sql_select.cc
> The fix is to use (table_map) 0 instead of used_tables in
> third argument for make_cond_for_table() function.
> It allows to extract elements which belong to sorted
> table and in addition elements which are undependend
> subqueries.
>
> modified:
> mysql-test/r/having.result
> mysql-test/t/having.test
> sql/sql_select.cc
> === modified file 'mysql-test/r/having.result'
> --- a/mysql-test/r/having.result 2010-07-09 10:39:47 +0000
> +++ b/mysql-test/r/having.result 2010-12-10 12:06:50 +0000
> @@ -545,4 +545,21 @@ FROM t1 JOIN t2 ON t2.f2 LIKE 'x'
> HAVING field1< 7;
> field1
> DROP TABLE t1,t2;
> +#
> +# Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause
> +#
> +CREATE TABLE t1 (f1 INT, f2 INT);
> +INSERT INTO t1 VALUES (1, 0), (2, 1), (3, 2);
> +CREATE TABLE t2 (f1 INT, f2 INT);
> +SELECT t1.f1
> +FROM t1
> +HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1>= 0
> +ORDER BY t1.f1;
> +f1
> +SELECT t1.f1
> +FROM t1
> +HAVING (3, 2) IN (SELECT 4, 2) AND t1.f1>= 0
> +ORDER BY t1.f1;
> +f1
> +DROP TABLE t1,t2;
> End of 5.1 tests
>
> === modified file 'mysql-test/t/having.test'
> --- a/mysql-test/t/having.test 2010-07-09 10:39:47 +0000
> +++ b/mysql-test/t/having.test 2010-12-10 12:06:50 +0000
> @@ -564,4 +564,24 @@ HAVING field1< 7;
>
> DROP TABLE t1,t2;
>
> +--echo #
> +--echo # Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY
> clause
> +--echo #
> +
> +CREATE TABLE t1 (f1 INT, f2 INT);
> +INSERT INTO t1 VALUES (1, 0), (2, 1), (3, 2);
> +CREATE TABLE t2 (f1 INT, f2 INT);
> +
> +SELECT t1.f1
> +FROM t1
> +HAVING (3, 2) IN (SELECT f1, f2 FROM t2) AND t1.f1>= 0
> +ORDER BY t1.f1;
>
Please add a test case with only one column in the subquery. We should
always test both.
And as I said in the other e-mail, please consider back-porting the
comments from next-mr.
Apart from that, great fix!
Best Regards
Martin
