MySQL Bugs: #15917: wrong result in NIST test on Windows for nested SELECT with GROUP BY

Bug #15917	wrong result in NIST test on Windows for nested SELECT with GROUP BY
Submitted:	21 Dec 2005 21:43	Modified:	14 Apr 2006 14:36
Reporter:	Carsten Segieth	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.17	OS:	Windows (Windows 2003, Windows XP, OXS)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
A SELECT that is running successfull on other systems gives on Windows systems the wrong error

 ERROR 42S22: Unknown column 'HU.PROJ.CITY' in 'having clause'

instead of the expected result set. Removing the database name 'HU.' from all parts in the SELECT lets it work!

Tested with 5.0.17 on Windows against a new 5.0.18-bk compiled yesterday. The test is part of the NIST suite, see attached files for schema and other details:

# fails:
SELECT EMPNUM, GRADE*1000
FROM HU.STAFF WHERE GRADE * 1000 > ANY
(SELECT SUM(BUDGET) FROM HU.PROJ
GROUP BY CITY, PTYPE
HAVING HU.PROJ.CITY = HU.STAFF.CITY);
ERROR 42S22: Unknown column 'HU.PROJ.CITY' in 'having clause'

# success (also on Windows):
SELECT EMPNUM, GRADE*1000
FROM STAFF WHERE GRADE * 1000 > ANY
(SELECT SUM(BUDGET) FROM PROJ
GROUP BY CITY, PTYPE
HAVING PROJ.CITY = STAFF.CITY);
EMPNUM	GRADE*1000
E3	13000

How to repeat:
- run the attached test file and check against the attached .result file (which has been created on Linux)
or
- run the whole NIST suite: ./mysql-test-run.pl -suite=nist

Suggested fix:
fix problem on Windows

test file

Attachment: nist_test.test (application/octet-stream, text), 4.29 KiB.

result file

Attachment: nist_test.result (application/octet-stream, text), 1.55 KiB.

first detected with 5.0.17-pro-cert binaries on Windows 2003
verified with 5.0.16 on Windows XP Home

We have the same type of problem on Linux if we start the server with
  --lower-case-table-names=1

The problem is due to the fact with --lower-case-table-names set to 1 the the function find_field_in_group_list does not convert the prefix 'HU' in HU.PROJ.CITY into lower case when looking for it in the group list. Yet the names in the group list are extended by the database name in lower case.

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/4584

ChangeSet
  1.2141 06/04/06 15:29:15 igor@rurik.mysql.com +3 -0
  Fixed bug #15917: unexpected complain for a NIST test case.
  The problem was due to the fact that with --lower-case-table-names set to 1 
  the function find_field_in_group did not convert the prefix 'HU' in
  HU.PROJ.CITY into lower case when looking for it in the group list. Yet the
  names in the group list were extended by the database name in lower case.

The fix will appear in 5.0.21. It has been merged into 5.1.

After the push of the fix for this bug to 5.0, the testcase 'having' fails on Windows and OSX (case-insensitive file systems):

    https://intranet.mysql.com/~knielsen/pb/mysql-5.0/484.html

having                         [ fail ]

Errors are (from C:/cygwin/home/mysqldev/pb/mysql-5.0/push-igor@rurik.mysql.com-20060407221031.info/mysql-5.0.21-standard/mysql-test/var-n_stm-5/log/mysqltest-time) :
mysqltest: Result content mismatch
(the last lines may be the most important ones)
Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/having.result	2006-04-07 22:11:43 +03:00
--- r/having.reject	2006-04-07 22:57:43 +03:00
***************
*** 361,367 ****
  drop table t1;
  DROP SCHEMA IF EXISTS HU;
  Warnings:
! Note	1008	Can't drop database 'HU'; database doesn't exist
  CREATE SCHEMA HU ;
  USE HU ;
  CREATE TABLE STAFF
--- 361,367 ----
  drop table t1;
  DROP SCHEMA IF EXISTS HU;
  Warnings:
! Note	1008	Can't drop database 'hu'; database doesn't exist
  CREATE SCHEMA HU ;
  USE HU ;
  CREATE TABLE STAFF
-------------------------------------------------------

It seems the warning message displays in different case on different platforms, causing the test suit to fail on Windows/OSX.

Setting the priority to P1 as per the new bug guidelines, as this is a test case failure.

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/4746

I has removed the warning issued by 
DROP SCHEMA IF EXISTS HU;
from the test case as it's not relevant and cannot be made identical 
from case-insensetive platforms:
ChangeSet
  1.2150 06/04/10 14:14:20 igor@rurik.mysql.com +2 -0
  Made the test case for bug #15917 independent on platforms.

The fix in 
ChangeSet
  1.2141 06/04/06 15:29:15 igor@rurik.mysql.com +3 -0
remains valid

Noted in 5.0.21 changelog.

Lettercase in database name qualifiers was not consistently
handled properly in queries when
<literal>lower_case_table_names</literal> was set to 1. (Bug
#15917)