MySQL Bugs: #30137: Wrong apostrophe used with database name

Bug #30137	Wrong apostrophe used with database name
Submitted:	31 Jul 2007 8:14	Modified:	4 Feb 2008 9:39
Reporter:	Tonci Grgin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.17GA	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Excel, MSQRY32

Description:
Working on a Bug#28841 I found out that:
 1) table name is tagged with _0
 2) wrong apostrophe used: "[MySQL][ODBC 3.51 Driver][mysqld-5.0.44-max-nt-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test`.`bug28841` LIMIT 0' at line 1"; near 'test`.`bug28841`

Eric M thinks this happens only for tables with CHAR (and variants) fields.

How to repeat:
CREATE TABLE bug28841 (
 Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 VBFld1 VARBINARY(50) DEFAULT NULL,
 VBFld2 BINARY(50));
//Or CHAR etc

INSERT INTO bug28841 VALUES (NULL, "Row 1 FLD 2", "Row1 FLD3");
INSERT INTO bug28841 VALUES (NULL, "Row 2 FLD 2", "Row2 FLD3");

mysql> SHOW CREATE TABLE bug28841;
| Table    | Create Table                                              
                                |
 bug28841 | CREATE TABLE `bug28841` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `VBFld1` varbinary(50) default NULL,
  `VBFld2` binary(50) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Go to Excel, Data / Import external data / New database query. Pick MyODBC DSN and you'll end up with:
SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2, bug28841_0.BLFld FROM bug28841
and
msqry32         fe0-e14	EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
		HSTMT               00941DF0
		UCHAR *             0x0018F170 [     113] "SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2, bug28841_0.BLFld FROM bug28841 ORDER BY bug28841_0.Id"
		SDWORD                   113

msqry32         fe0-e14	ENTER SQLExecute 
		HSTMT               00941DF0

msqry32         fe0-e14	EXIT  SQLExecute  with return code -1 (SQL_ERROR)
		HSTMT               00941DF0

		DIAG [S0022] [MySQL][ODBC 3.51 Driver][mysqld-5.0.44-max-nt-log]Unknown column 'bug28841_0.Id' in 'field list' (1054) 

Suggested fix:
This is easily bypassed by manually editing MSQRY sql but I'm seting this to S2 in hope that the fix will make into next GA release.

I did manually replaced _0 in MSQRY so logs might be a bit unreadable:
070731  9:43:57	     10 Connect     root@localhost on test
		     10 Query       show databases like '%'
070731  9:44:00	     10 Query       SHOW TABLE STATUS FROM `test`
070731  9:44:17	     10 Query       SHOW TABLE STATUS LIKE 'bug28841'
		     10 Field List  bug28841 
070731  9:44:18	     10 Field List  bug28841 
070731  9:44:59	     10 Query       SHOW TABLE STATUS FROM `test` LIKE 'bug28841'
		     10 Query       SHOW COLUMNS FROM `test`.`bug28841`
		     10 Query       SELECT `Id`,VBFld1`,VBFld2`,BLFld` FROM `test`.`bug28841` LIMIT 0
070731  9:45:00	     10 Query       SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2, bug28841_0.BLFld FROM bug28841 ORDER BY bug28841_0.Id
070731  9:45:05	     10 Query       SHOW TABLE STATUS FROM `test` LIKE 'bug28841'
		     10 Query       SHOW COLUMNS FROM `test`.`bug28841`
		     10 Query       SELECT `Id`,VBFld1`,VBFld2`,BLFld` FROM `test`.`bug28841` LIMIT 0
070731  9:45:06	     10 Query       SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2, bug28841_0.BLFld FROM bug28841 ORDER BY bug28841_0.Id
070731  9:45:11	     10 Query       SHOW TABLE STATUS FROM `test` LIKE 'bug28841'
		     10 Query       SHOW COLUMNS FROM `test`.`bug28841`
		     10 Query       SELECT `Id`,VBFld1`,VBFld2`,BLFld` FROM `test`.`bug28841` LIMIT 0
		     10 Query       SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2, bug28841_0.BLFld FROM bug28841 ORDER BY bug28841_0.Id
070731  9:45:44	     10 Query       SHOW TABLE STATUS FROM `test` LIKE 'bug28841'
		     10 Query       SHOW COLUMNS FROM `test`.`bug28841`
		     10 Query       SELECT `Id`,VBFld1`,VBFld2`,BLFld` FROM `test`.`bug28841` LIMIT 0
		     10 Query       SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2, bug28841_0.BLFld FROM bug28841
070731  9:45:46	     10 Query       SHOW TABLE STATUS FROM `test` LIKE 'bug28841'
		     10 Query       SHOW COLUMNS FROM `test`.`bug28841`
		     10 Query       SELECT `Id`,VBFld1`,VBFld2`,BLFld` FROM `test`.`bug28841` LIMIT 0
070731  9:45:47	     10 Query       SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2, bug28841_0.BLFld FROM bug28841
070731  9:45:51	     10 Query       SHOW TABLE STATUS FROM `test` LIKE 'bug28841'
		     10 Query       SHOW COLUMNS FROM `test`.`bug28841`
		     10 Query       SELECT `Id`,VBFld1`,VBFld2`,BLFld` FROM `test`.`bug28841` LIMIT 0
		     10 Query       SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2, bug28841_0.BLFld FROM bug28841
070731  9:45:58	     10 Query       SHOW TABLE STATUS FROM `test` LIKE 'bug28841'
		     10 Query       SHOW COLUMNS FROM `test`.`bug28841`
		     10 Query       SELECT `Id`,VBFld1`,VBFld2`,BLFld` FROM `test`.`bug28841` LIMIT 0
070731  9:45:59	     10 Query       SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2, bug28841_0.BLFld FROM bug28841
070731  9:46:15	     10 Query       SHOW TABLE STATUS FROM `test` LIKE 'bug28841'
		     10 Query       SHOW COLUMNS FROM `test`.`bug28841`
		     10 Query       SELECT `Id`,VBFld1`,VBFld2`,BLFld` FROM `test`.`bug28841` LIMIT 0
070731  9:46:19	     10 Query       SELECT bug28841.Id, bug28841.VBFld1, bug28841.VBFld2, bug28841.BLFld FROM bug28841
070731  9:47:01	     10 Quit

And the ODBC trace

Attachment: SQLlog-bug28841.zip (application/zip, text), 5.57 KiB.

Further investigation shows 2 things:
 1) Removing BLOB column fixes query issued so that it works:
36 Query       SELECT bug28841_0.Id, bug28841_0.VBFld1, bug28841_0.VBFld2
FROM test.bug28841 bug28841_0 (notice bug28841_0 which is freshly added)
 2) Regardless of BLOB field being in the table and the fact that MSQRY shows all fields and values, Only first two fields (Id, VBFld1) are shown in Excel!

This is getting weird...
MS Access shows 3 fields, and data from all of them but:
 - VARBINARY field (2nd) is correctly recognized as TEXT
 - BINARY field (3rd) is incorrectly recognized as BINARY as shows crap

Using generic MS ODBC client, odbcte.exe, I'm unable to retrieve info on 3rd column at all!
SQLColumns:
				In:				StatementHandle = 0x008B1978, CatalogName = "test", 
										NameLength1 = 4, SchemaName = "test", NameLength2 = 4, TableName = "bug28841", 
										NameLength3 = 8, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
				Return:	SQL_SUCCESS=0

Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
"tests", <Null>, "bug28841", "Id", 4, "4", 10, 4, 0, 10, 0, "", <Null>, 4, <Null>, <Null>, 1, "NO"
"tests", <Null>, "bug28841", ",VBFld2", 12, "12", 50, 50, <Null>, <Null>, 1, "", <Null>, 12, <Null>, 50, 2, "YES"
2 rows fetched from 18 columns.

Interesting part of ODBC trace showing how fields are recognized:
1) VARBINARY field
msqry32         fe0-e14	EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
		HSTMT               00941DF0
		UWORD                        2 
		UCHAR *             0x0012F134 [       6] "VBFld1"
		SWORD                      256 
		SWORD *             0x0012F120 (6)
		SWORD *             0x0012F130 (12)
		SQLULEN *           0x0012F090 (50)
		SWORD *             0x0012F0FC (0)
		SWORD *             0x0012F0F4 (1)

msqry32         fe0-e14	ENTER SQLColAttributes 
		HSTMT               00941DF0
		UWORD                        2 
		UWORD                        6 <SQL_COLUMN_DISPLAY_SIZE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x0012F120
		SQLLEN *            0x0012F128

msqry32         fe0-e14	EXIT  SQLColAttributes  with return code 0 (SQL_SUCCESS)
		HSTMT               00941DF0
		UWORD                        2 
		UWORD                        6 <SQL_COLUMN_DISPLAY_SIZE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x0012F120 (6)
		SQLLEN *            0x0012F128 (50)

2) BINARY field
msqry32         fe0-e14	ENTER SQLDescribeCol 
		HSTMT               00941DF0
		UWORD                        3 
		UCHAR *             0x0012F134 
		SWORD                      256 
		SWORD *             0x0012F120
		SWORD *             0x0012F130
		SQLULEN *           0x0012F090
		SWORD *             0x0012F0FC
		SWORD *             0x0012F0F4

msqry32         fe0-e14	EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
		HSTMT               00941DF0
		UWORD                        3 
		UCHAR *             0x0012F134 [       6] "VBFld2"
		SWORD                      256 
		SWORD *             0x0012F120 (6)
		SWORD *             0x0012F130 (-2)
		SQLULEN *           0x0012F090 (50)
		SWORD *             0x0012F0FC (0)
		SWORD *             0x0012F0F4 (1)

ANSI_QUOTES is not relevant to this bug. Needs to be re-tested with 3.51.19.

Jim, it is like this:
 MyODBC 3.51.19 rev.736 (built by Bogdan) and MySLQ server Server version: 5.0.48-pb1038-log MySQL Pushbuild Edition, build 1038
 WinXP Pro SP2 + MSOffice 2003 Pro SP2
 MyODBC options: 1+2 (nothing else set either)

---------

1) MSQRY32 now produces *correct* query

2) Return data to Excel - like before, second field missing
Id	VBFld1
1	Row 1 FLD 2
2	Row 2 FLD 2

3) Access - same behavior like before

New version solved 1 out of 3 problems so Verified again.

MS odbcte32.exe shows the second part of problem is pretty much the same:
	Full Connect(Default)
	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
	Successfully connected to DSN 'myodbccln'.

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

Get Data All:
"Id", "VBFld1", "VBFld2"
1, "Row 1 FLD 2", 0xRow1 FLD3
2, "Row 2 FLD 2", 0xRow2 FLD3
2 rows fetched from 3 columns.

Bug#30272 is a duplicate of this one in MSQRY32 part.

Original problems *are* resolved. Closing the report now.