Re: bk commit into 5.0 tree (gkodinov:1.2564) BUG#31562

[Georgi Kodinov]

Hi,

Thanks for your review. I've committed an updated fix.
See my reply below.

On 13.11.2007, at 06:57, Sergey Petrunia wrote:

> On Fri, Nov 09, 2007 at 11:39:21AM +0200, kgeorge@stripped wrote:
>> ChangeSet@stripped, 2007-11-09 11:39:16+02:00, gkodinov@stripped +3  
>> -0
>>  Bug #31562: HAVING and lower case
>>
>>  The columns in HAVING can reference the GROUP BY and
>>  SELECT columns. There can be "table" prefixes when
>>  referencing these columns. And these "table" prefixes
>>  in HAVING use the table alias if available.
>>  This means that table aliases are subject to the same
>>  storage rules as table names and are dependent on
>>  lower_case_table_names in the same way as the table
>>  names are.
>>  Fixed by treating table aliases as table names
>>  and make them lowercase when printing out the SQL
>>  statement for view persistence.
>
> This manual page
> http://dev.mysql.com/doc/refman/5.0/en/identifier-case- 
> sensitivity.html
>
> says
>
> "
> Value: 1
>
> Meaning:
> Table names are stored in lowercase on disk and name comparisons are  
> not
> case sensitive. MySQL converts all table names to lowercase on  
> storage and
> lookup. This behavior also applies to database names and table  
> aliases.
> "
>
> Experiments show that there is no problem with table names being in  
> different
> case in select list, GROUP BY and ORDER BY. Why is HAVING different?

It was different because if the different name resolution scope that  
it had : only SELECT and GROUP BY lists compared to SELECT,GROUP BY  
and table columns for the rest of the statement.

> An example: I've applied the fix, run this query:
>
>  select x.a AS a from t1 X group by x.b having (x.b = 1);
>
> and got
>
>  ERROR 1054 (42S22): Unknown column 'x.b' in 'having clause'
>
> There is no error if one removes the HAVING clause.
>
> I think the fix should be in name resolution for HAVING clause, not  
> in VIEW
> defintion printout.

IMHO the fix should include both : the view storage fix is not bad as  
is homogenizes the handling of aliases to what is being done for table  
names. So I took the liberty of leaving that fix.
You're correct that the comparison of the aliases should be done  
according to lower_case_table_name. So I've extended both the fix and  
the test suite to cover these.

Best Regards,
Joro
-- 
Georgi Kodinov, Senior Software Engineer
MySQL AB, Plovdiv, Bulgaria, www.mysql.com
Office: [phone] Mobile: [phone] Skype: georgekodinov

Are you MySQL certified?  www.mysql.com/certification