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:Microsoft Windows (Windows 2003, Windows XP, OXS)
Assigned to: Igor Babaev CPU Architecture:Any

[21 Dec 2005 21:43] Carsten Segieth
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
[21 Dec 2005 21:45] Carsten Segieth
test file

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

[21 Dec 2005 21:46] Carsten Segieth
result file

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

[21 Dec 2005 21:56] Carsten Segieth
first detected with 5.0.17-pro-cert binaries on Windows 2003
verified with 5.0.16 on Windows XP Home
[6 Apr 2006 19:06] Igor Babaev
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.
[6 Apr 2006 22:29] Bugs System
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
[8 Apr 2006 17:15] Igor Babaev
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.
[10 Apr 2006 7:14] Kristian Nielsen
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.
[10 Apr 2006 21:14] Bugs System
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
[11 Apr 2006 2:21] Igor Babaev
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
[14 Apr 2006 14:36] Paul Dubois
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)