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: +359 32 634 397 Mobile: +359 887 700 566 Skype: georgekodinov
Are you MySQL certified? www.mysql.com/certification
