MySQL Bugs: #21991: Column names are broken at certain times when lower_case_table

Bug #21991	Column names are broken at certain times when lower_case_table_names=1 is set
Submitted:	4 Sep 2006 13:56	Modified:	5 Sep 2006 14:05
Reporter:	Craig Atkins	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (Windows Server 2003)
Assigned to:		CPU Architecture:	Any

Description:
When querying a Linux server that has lower_case_table_names=1 set in it's ini file, the query breaks by returning broken column names when the table name used in the query is in upper case.

This functionality seems to work properly (i.e: both examples provided below return the same SQL) on our other machine which talks to the Linux database server - that machine is running the MyODBC connector version 3.51.09

The server is running MySQL Standard 4.1.8

How to repeat:
Query a table using the upper case table name, then try again using the lower case name.

E.g:
szSqlStr = "SELECT * FROM tblCATEGORIES WHERE Prev_Category_Id =''", cbSqlStr = -3
Return:	SQL_SUCCESS=0

Get Data All:
"#B", " ", ""
1, 0, Airframe parts
4, 0, Tools/Ground Equipment
5, 0, Engine parts
6, 0, Instruments
7, 0, Documents / Other
5 rows fetched from 3 columns.

Notice that above the data, the column names are junk (i.e: "#B")

Now, the query is run again, with a lower case table name:
szSqlStr = "SELECT * FROM tblcategories WHERE Prev_Category_Id =''", cbSqlStr = -3
Return:	SQL_SUCCESS=0

Get Data All:
"CATEGORY_ID", "Prev_Category_Id", "Name"
1, 0, "Airframe parts"
4, 0, "Tools/Ground Equipment"
5, 0, "Engine parts"
6, 0, "Instruments"
7, 0, "Documents / Other"
5 rows fetched from 3 columns.

This time, the column names are correct. 

Suggested fix:
Only query the table names in lower case, but this is not a valid work-around for our situation as it requires re-writing a lot of applications.
Otherwise, use MyODBC 3.51.09

Hi Craig and thanks for your problem report.
I was unable to verify it in following environment:
  - MySQL server 4.1.22 BK running on remote Suse 10.0 host
  - MyODBC 3.51.12 GA and 3.51.13
  - WinXP Pro SP2 client

MySQL cl client, lower_case_table_names=1:
C:\mysql\bin>mysql -uroot -hmunja --port=3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> create table tbluctest (
    -> ID Int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Fld1 VARCHAR(10),
    -> FLD2 VARCHAR(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tbluctest values (NULL,'Docs','Internal'),
    -> (NULL,'Docs', 'Cust'), (NULL, 'Invoices', 'Other');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show variables like "lower_%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tblUCtest;
+----+----------+----------+
| ID | Fld1     | FLD2     |
+----+----------+----------+
|  1 | Docs     | Internal |
|  2 | Docs     | Cust     |
|  3 | Invoices | Other    |
+----+----------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tbluctest;
+----+----------+----------+
| ID | Fld1     | FLD2     |
+----+----------+----------+
|  1 | Docs     | Internal |
|  2 | Docs     | Cust     |
|  3 | Invoices | Other    |
+----+----------+----------+
3 rows in set (0.00 sec)
---
MS generic ODBC client (odbct32.exe):
SQLExecDirect:
				In:				hstmt = 0x00841960, szSqlStr = "select * from TbLuCtEsT", 
										cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"ID", "Fld1", "FLD2"
1, "Docs", "Internal"
2, "Docs", "Cust"
3, "Invoices", "Other"
3 rows fetched from 3 columns.

SQLExecDirect:
				In:				hstmt = 0x00841960, szSqlStr = "select * from tbluctest", 
										cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"ID", "Fld1", "FLD2"
1, "Docs", "Internal"
2, "Docs", "Cust"
3, "Invoices", "Other"
3 rows fetched from 3 columns.

SQLExecDirect:
				In:				hstmt = 0x00841960, szSqlStr = "select * from tblUCtest", 
										cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"ID", "Fld1", "FLD2"
1, "Docs", "Internal"
2, "Docs", "Cust"
3, "Invoices", "Other"
3 rows fetched from 3 columns.
--

MySQL cl client, lower_case_table_names=0:
C:\mysql\bin>mysqladmin -uroot -hmunja --port=3307 shutdown

C:\mysql\bin>mysql -uroot -hmunja --port=3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "lower_%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM tblUCtest;
ERROR 1146 (42S02): Table 'test.tblUCtest' doesn't exist
mysql> quit
--
MS generic ODBC client (odbct32.exe):
SQLExecDirect:
				In:				hstmt = 0x00841960, szSqlStr = "select * from tblUCtest", 
										cbSqlStr = -3
				Return:	SQL_ERROR=-1
				stmt:		szSqlState = "42S01", *pfNativeError = 1146, *pcbErrorMsg = 80, *ColumnNumber = -2, *RowNumber = -2
										MessageText = "[MySQL][ODBC 3.51 Driver][mysqld-4.1.22-log]Table 'test.tblUCtest' doesn't exist"

I suggest you recheck your MyODBC 3.51.12 installation. The driver (myodbc3.dll) should be 1,552,384 bytes in size.

OK, this is really weird. I've found this doesn't happen in all circumstances.
If I issue a:
SELECT * FROM tblCATEGORIES
or a
SELECT * FROM tblcategories

These both work.

However, it's when I issue:
SELECT * FROM tblCATEGORIES WHERE Prev_Category_Id =''
that it doesn't work (junk column headers as shown before).

If I issue:
SELECT * FROM tblcategories WHERE Prev_Category_Id =''
then that works fine.

The structure of my table is:
mysql> desc tblCATEGORIES;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| CATEGORY_ID      | int(10) unsigned |      | PRI | NULL    | auto_increment |
| Prev_Category_Id | int(10) unsigned | YES  |     | NULL    |                |
| Name             | text             | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

I also have the ODBC options 'Don't optimize column width' and 'Return matching rows' selected as I'm using this database with an ASP page via ADO.

Craig

Craig, I still can't repeat:

	Successfully connected to DSN 'myodbc1'.
SQLExecDirect:
				In:				hstmt = 0x008419C0, 
										szSqlStr = "select  * from tblUCtest WHERE ID = 0", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"ID", "Fld1", "FLD2"
0 rows fetched from 3 columns.

SQLExecDirect:
				In:				hstmt = 0x008419C0, 
										szSqlStr = "select  * from tblUCtest WHERE Fld1 = "Docs"", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"ID", "Fld1", "FLD2"
1, "Docs", "Internal"
2, "Docs", "Cust"
2 rows fetched from 3 columns.

SQLExecDirect:
				In:				hstmt = 0x008419C0, 
										
//deliberate error just to show version
szSqlStr = "select  * from tblUCtest1 WHERE Fld1 = "Docs"", cbSqlStr = -3
				Return:	SQL_ERROR=-1
				stmt:		szSqlState = "42S01", *pfNativeError = 1146, *pcbErrorMsg = 81, *ColumnNumber = -2, *RowNumber = -2
										MessageText = "[MySQL][ODBC 3.51 Driver][mysqld-4.1.22-log]Table 'test.tbluctest1' doesn't exist"

Ok, I think I have it now.
Looking into it, it's related to my poorly formed SQL statement:
WHERE Prev_Category_Id =''

The Prev_Category_Id field is an integer.
If the statement is re-written to be:
WHERE Prev_Category_Id =0

Then the table can be referenced in upper or lower case and returns correct column names.

So it seems this issue occurs when querying for blank numeric fields using a FIELDNAME = '' syntax.

However, I still believe that this is a bug in the ODBC drive, as the 1st example works when run against the MySQL command line client, but fails when using the latest ODBC driver (and works on the older one).

Still can't repeat:
	Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'myodbc1'.
SQLExecDirect:
				In:				hstmt = 0x00841960, 
										szSqlStr = "select  * from tblUCtest WHERE ID = ''", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"ID", "Fld1", "FLD2"
0 rows fetched from 3 columns.

I suggest to you to recheck your configuration/application. Also, try to repeat the error from odbct32.exe or any other 3rd party ODBC client.

Tonci,

Sorry this is becoming a pain!
I think I've finally nailed it (I've re-installed MyODBC by the way, and I'm testing with odbcTE32.exe)

The error occurs if you format the WHERE statement without a space before the quotes, such as:
WHERE Prev_Category_Id =''

This works though:
WHERE Prev_Category_Id = ''

Enclosed below is the same statements run in odbcte32.exe, if this helps:

	Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'russianENG'.
SQLExecDirect:
				In:				hstmt = 0x00901DE8, 
										szSqlStr = "SELECT * FROM tblCATEGORIES WHERE Prev_Category_Id = '...", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"CATEGORY_ID", "Prev_Category_Id", "Name"
1, 0, "Airframe parts"
4, 0, "Tools/Ground Equipment"
5, 0, "Engine parts"
6, 0, "Instruments"
7, 0, "Documents / Other"
5 rows fetched from 3 columns.

SQLExecDirect:
				In:				hstmt = 0x00901DE8, 
										szSqlStr = "SELECT * FROM tblCATEGORIES WHERE Prev_Category_Id =''", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"#B", " ", ""
1, 0, Airframe parts
4, 0, Tools/Ground Equipment
5, 0, Engine parts
6, 0, Instruments
7, 0, Documents / Other
5 rows fetched from 3 columns.

Although, now I've got conflicting results, as if I change the column I select on, the issue seems to reverse (works with no space, but breaks with a space!).
E.g:

SQLExecDirect:
				In:				hstmt = 0x00901DE8, 
										szSqlStr = "SELECT * FROM tblCATEGORIES WHERE CATEGORY_ID = ''", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"#B", " ", ""
0 rows fetched from 3 columns.

SQLExecDirect:
				In:				hstmt = 0x00901DE8, 
										szSqlStr = "SELECT * FROM tblCATEGORIES WHERE CATEGORY_ID =''", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"CATEGORY_ID", "Prev_Category_Id", "Name"
0 rows fetched from 3 columns.

I'm totally lost now, and this is getting very confusing.
If you duplicate my table structure, can you replicate this behaviour?

Thanks for your help,

Craig

Craig, check your tables, settings and software versions please.
	Successfully connected to DSN 'myodbc1'.
SQLExecDirect:
				In:				hstmt = 0x00842C10, 
										szSqlStr = "select  * from tblUCtest WHERE ID =''", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"ID", "Fld1", "FLD2"
0 rows fetched from 3 columns.

SQLExecDirect:
				In:				hstmt = 0x00842C10, 
										szSqlStr = "select  * from tblUCtest WHERE ID=''", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"ID", "Fld1", "FLD2"
0 rows fetched from 3 columns.