MySQL Bugs: #31562: HAVING and lower case

Bug #31562	HAVING and lower case
Submitted:	12 Oct 2007 11:21	Modified:	29 Nov 2007 0:51
Reporter:	Adam Charzewski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any (Linux, Windows)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	having, VIEW

Description:
I have two serwers Linux (ver. 5.0.19) and Windows (ver. 5.0.45). Linux works as a master and replication is set to Windows. On Windows machine I have config lower_case_table_names=1.
Replication works OK.
The problem is that on Linux machine I created view and it works and on the Windows machine is an error "View xxxv references invalid table(s) or column(s) or function(s".

 

How to repeat:
On master create following table:
CREATE TABLE  `BazaRozwiazan`.`XXX` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(45) NOT NULL,
  `test` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and following View:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`user`@`ip` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`,
`X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name` having (`X`.`test` = 1);

Now on windows machine view looks like this:
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`user`@`ip` SQL SECURITY DEFINER VIEW `xxxv` AS select `x`.`ID` AS `ID`,`x`.`Name` AS `Name`,`x`.`test` AS `test` from `xxx` `X` group by `x`.`Name` having (`x`.`test` = 1);

Suggested fix:
Use in view:
from `XXX` AS `x` insted of from `XXX` AS `X`

or do not use having clause.

I think this is basically this issue:
http://bugs.mysql.com/bug.php?id=20356

'lower_case_table_names' never worked on MySQL since 4.1 (on 4.0 it worked perfectly).  Now a this bug was reported almost 2 years ago, wan't it about time to do something?

This topic is not present in http://bugs.mysql.com/bug.php?id=20356
If lower_case_table_names is set to 1 table name in view should be case insensitive but it is not!
Try following view:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`user`@`ip` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name`;

It works also on windows machine. But following view does not:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`user`@`ip` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name` having (`X`.`test` = 1);

The issue is that having clause is not case insensitive.

You can repeat this problem only using windows machine.
Try this:
CREATE TABLE  `BazaRozwiazan`.`XXX` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(45) NOT NULL,
  `test` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And this select works:

select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name` having (`X`.`test` = 1);

But this view is not:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name` having (`X`.`test` = 1);

view without having clause works ok:

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `XXXV` AS select `X`.`ID` AS `ID`, `X`.`Name` AS `Name`, `X`.`test` from `XXX` AS `X` group by `X`.`Name`;

Thank you for the report.

Verified as described.

test case

Attachment: bug31562.test (application/octet-stream, text), 376 bytes.

options

Attachment: bug31562-master.opt (application/octet-stream, text), 27 bytes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37415

ChangeSet@1.2564, 2007-11-09 11:39:16+02:00, gkodinov@magare.gmz +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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37646

ChangeSet@1.2564, 2007-11-13 11:39:52+02:00, gkodinov@magare.gmz +5 -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 :
  1. Treating table aliases as table names
  and make them lowercase when printing out the SQL
  statement for view persistence.
  2. Using case insensitive comparison for table 
  aliases when requested by lower_case_table_names

Pushed into 5.0.52

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

HAVING could treat lettercase of table aliases incorrectly if
lower_case_table_names was enabled.