MySQL Bugs: #10271: Parsing of database name in ORDER BY clause is NOT case-insensitive

Bug #10271	Parsing of database name in ORDER BY clause is NOT case-insensitive
Submitted:	29 Apr 2005 18:20	Modified:	28 May 2013 10:55
Reporter:	Le Nguyen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
We're using mysql  "Ver 14.7 Distrib 4.1.11, for Win32 (ia32)", and MyODBC identifies itself as "MySQL ODBC 3.51 Driver version 3.51.11.00".

If the database name is used in the ORDER BY clause, it needs to be entered in lower-case. This does not follow the parsing behavior of the rest of the SQL statement, where upper-case works just fine.

The parsing should be consistently case-insensitive.

How to repeat:
Running this query:

SELECT DISTINCT COMPLEX.Cust.cust_name,COMPLEX.Cust.cust_num FROM COMPLEX.Cust ORDER BY COMPLEX.Cust.cust_name ASC, COMPLEX.Cust.cust_num ASC

returns the rows in the same order returned by the query below:

SELECT DISTINCT cust_name,cust_num FROM COMPLEX.Cust ORDER BY cust_name ASC, cust_num ASC

After some investigation, we have determined that the database name in the ORDER BY clause needs to be in lower-case:

SELECT DISTINCT COMPLEX.Cust.cust_name,COMPLEX.Cust.cust_num FROM COMPLEX.Cust ORDER BY complex.Cust.cust_name ASC, complex.Cust.cust_num ASC

This gives us the expected results.

Hi,

Can you please upload your table schema so that I can try and reproduce this bug ?

Thanks!

Hello Jorge,
don't forget that I am a newbie to MySQL. Are you talking about the INFORMATION_SCHEMA table? Can you give me detailed steps on how to get that information to you?

LN

Just in case you meant the output of a SHOW CREATE TABLE, here it is:

| Cust  | CREATE TABLE `cust` (
  `application_date` date default NULL,
  `credit_code` char(2) collate latin1_bin default NULL,
  `credit_limit` double default NULL,
  `credit_rating` int(11) default NULL,
  `cust_name` char(31) collate latin1_bin default NULL,
  `cust_num` int(11) NOT NULL default '0',
  `last_review_date` date default NULL,
  `ship_terms` char(21) collate latin1_bin default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

LN

Could you please to paste any sample values of this table. I tried to repeat on my values and all worked fine.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.