Bug #874 Use of quotes in SELECT (Connector/J)

• Submitted: 18 Jul 2003 11:50
• Modified: 18 Jul 2003 12:36
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 3.0.8
• OS: Windows (Windows 2000)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[18 Jul 2003 11:50] [ name withheld ]

Description:
When executing a query where column names are specified with quotes, the string within quotes is used for both the column name and all the values in that column.  This behavior differs from most databases, and may not be a bug, but I though I would report it.

How to repeat:
Execute the following (through JDBC, I'm not sure about any other interfaces)

CREATE TABLE quote ( name CHAR, number INT PRIMARY KEY );
INSERT INTO QUOTE VALUES ( 'bob', 1 );
INSERT INTO QUOTE VALUES ( 'joe', 2 );

SELECT name, number FROM quote;
SELECT name, "number" FROM quote;

The first returns the expected results:
 + ---- + ----------- +
 | name | number      |
 + ---- + ----------- +
 | b    | 1           |
 | j    | 2           |
 + ---- + ----------- +

While the second returns odd results:
 + ---- + ------ +
 | name | number |
 + ---- + ------ +
 | b    | number |
 | j    | number |
 + ---- + ------ +

Suggested fix:
I would like both of the above queries to operate identically.  The obvious workaround is to not use quotes, but I would like to maintain cross-database compatibility if possible.

[18 Jul 2003 12:36] Mark Matthews

This is not an issue caused by Connector/J. The JDBC driver does not alter your statement (unless you use escape sequences.

When not running MySQL in ansi mode, the double quote character does not delimit identifiers, it delimits _strings_, so MySQL is acting as expected (the delimiter for identifiers in MySQL is the backtick "`"). Please refer to http://www.mysql.com/doc/en/String_syntax.html and http://www.mysql.com/doc/en/ANSI_mode.html