MySQL Bugs: #30858: ParameterMetaData.getParameterType(int i) returns wrong type for "LIMIT ?"

Bug #30858	ParameterMetaData.getParameterType(int i) returns wrong type for "LIMIT ?"
Submitted:	6 Sep 2007 12:22	Modified:	13 Sep 2012 8:34
Reporter:	R. Jäschke	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1, 5.0, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	limit, parameter meta data, prepared statement

Description:
On a query like 

SELECT 'text' LIMIT ?

getParameterType(1) returns "VARCHAR" instead of "UNSIGNED". 

This was no problem until version 3.1 of the driver, because it accepted string values there (e.g. setString("10") worked). As of 5.0 (and 5.1, too), setString("10") throws an exception. Or more exactly: setString("10") works, but when executing the statement, a com.mysql.jdbc.exceptions.MySQLSyntaxErrorException is thrown:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 ''10'' at line 1

This is especially bad for JSP programming, since there the params are typically integers packed in strings. Hence, updating MySQL Connector/J from 3.1 to 5.0 breaks statements like 

<sql:query var="rs" dataSource="${dataSource}">
  SELECT foo FROM bar LIMIT ?
  <sql:param value="${param.limit}" />
</sql:query>

I could reproduce this behaviour with version 5.0.7 and 5.1.2; version 3.1.14 returns the wrong type, too, but accepts a string there.

Note, that 

SELECT 'text' LIMIT CAST(? AS SIGNED)

does not work, too.

How to repeat:
System.out.println("preparing statement");
PreparedStatement  stmtP = conn.prepareStatement("SELECT 'text' LIMIT ?");

ParameterMetaData meta = stmtP.getParameterMetaData();
for (int i = 1; i <= meta.getParameterCount(); i++) {
  System.out.println("i = " + i + ", typeName = " + meta.getParameterTypeName(i) + ", type = " + meta.getParameterType(i));	
}

stmtP.setString(1, "10");

System.out.println("executing query");
ResultSet rst = stmtP.executeQuery();

---------------------------------------
output with version 3.1.14:

preparing statement
i = 1, typeName = VARCHAR, type = 12
executing query

---------------------------------------
output with version 5.0.7 and 5.1.2:

preparing statement
i = 1, typeName = VARCHAR, type = 12
executing query
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 ''10'' at line 1
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 ''10'' at line 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1027)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3361)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3295)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1852)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1975)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2476)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1583)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1725)
	at tests.database.LimitBug.main(LimitBug.java:41)

where line 41 is 

ResultSet rst = stmtP.executeQuery();

Suggested fix:

let getParameterTypeName return the correct type or allow strings for LIMIT queries

Connector/J doesn't return anything _but_ VARCHAR for getParameterMetadata(), because it doesn't have any parameter metadata information available to it.

If anything this is a _server_ change in behavior, because the driver doesn't do anything "special" with a limit clause.

Since you tried this with 3.1.14, my guess is server-side prepared statements were used there, and they did the type conversion differently than normal statements, which would be a bug.

My server version is 5.0.22-Debian_0ubuntu6.06.2-log, I used the following parameters for the connection (with all three versions of the driver) in the sample code:

useUnicode=true
characterEncoding=UTF-8
characterSetResults=UTF-8
emulateUnsupportedPstmts=false
generateSimpleParameterMetadata=true

Without the last option I get no parameter metadata at all. I stumbled accross the problem when switching from 3.1 to 5.0 and suddenly some queries in JSPs broke. In my sample code, removing the last two options I can reproduce the problem: with 3.1 I can set the LIMIT as a string, with 5.* I get a SyntaxErrorException.

Hi and thanks for your report. There is a multitude of things going on here behind the scenes so let's get started:
 - "Wrong type returned": According to manual, I'd say "Not a bug";
 	- Fixed BUG#21267, ParameterMetaData throws NullPointerException when
	  prepared SQL actually has a syntax error. Added
	  "generateSimpleParameterMetadata" configuration property, which when
	  set to "true" will generate metadata reflecting VARCHAR for every
	  parameter (the default is "false", which will cause an exception to be
	  thrown if no parameter metadata for the statement is actually
	  available).
 - When *not using" SS PS: Driver is *not preparing* this statement at all, thus  it sends "SELECT CharCol FROM bug30858 LIMIT '2'" to server resulting in error.
 - When using SS PS (useServerPrepStmts=true) it doesn't actually matter whether parameter is sent as Int or String as server makes necessary adjustments (thus no error):
setString
		     15 Query       DROP TABLE IF EXISTS bug30858
		     15 Query       CREATE  TABLE bug30858 (Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, CharCol CHAR(10) DEFAULT NULL)
		     15 Query       INSERT INTO bug30858 VALUES (NULL, 'TEST ROW 1')
		     15 Query       INSERT INTO bug30858 VALUES (NULL, 'TEST ROW 2')
		     15 Prepare     [1] SELECT CharCol FROM bug30858 LIMIT ?
		     15 Execute     [1] SELECT CharCol FROM bug30858 LIMIT '2'
		     15 Query       DROP TABLE IF EXISTS bug30858

and setInt

		     14 Query       DROP TABLE IF EXISTS bug30858
		     14 Query       CREATE  TABLE bug30858 (Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, CharCol CHAR(10) DEFAULT NULL)
		     14 Query       INSERT INTO bug30858 VALUES (NULL, 'TEST ROW 1')
		     14 Query       INSERT INTO bug30858 VALUES (NULL, 'TEST ROW 2')
		     14 Prepare     [1] SELECT CharCol FROM bug30858 LIMIT ?
		     14 Execute     [1] SELECT CharCol FROM bug30858 LIMIT 2
		     14 Query       DROP TABLE IF EXISTS bug30858

So, there is no bug as described. We might discuss whether CS PS should guess which type parameter really is but I don't think Mark would allow this... For me, this is !Bg.

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".