From: Martin Hansson
Date: December 29 2010 9:59am
Subject: Re: bzr commit into mysql-5.1-bugteam branch (sergey.glukhov:3515)
 Bug#48916
List-Archive: http://lists.mysql.com/commits/127652
Message-Id: <4D1B069E.5080404@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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