MySQL Bugs: #36478: Client prepared statement bugged if word 'limit' included in the query

Bug #36478	Client prepared statement bugged if word 'limit' included in the query
Submitted:	2 May 2008 20:54	Modified:	19 Apr 2012 1:02
Reporter:	francois valdy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Whenever the string 'limit' appears in unquoted column names in a query, the code in PreparedStatement::ParseInfo() assumes statement has a limit clause.

This leads to various serious defects, such as (maybe more):
 - non-limited prepared statement (with column named foobar_limit, maxrows set to 0) executed after a limited (with setMaxRows) one becomes limited to the same maxrows.

Workaround (applied in my case) is to quote all column names including the word 'limit' (case widened).
Other might be to use server-side prepared statements, but I'm not even sure of that.

My guess is that a maxrow'ed query issued a call to:
SET OPTION SQL_SELECT_LIMIT=X

And that this token isn't reset to default when the client sends a prepared stmt with a (wrongly supposed) limit clause inside.

Database: MySQL - 5.0.41-community-nt
Driver: MySQL-AB JDBC Driver - mysql-connector-java-5.1.5

How to repeat:
execute prepared:
SELECT foo FROM bar (maxrow'ed at 1)
SELECT foobar_limit from foo (maxrow'ed at 0)

second query is limited to 1 row, whereas

SELECT 'foobar_limit' from foo (maxrow'ed at 0)

returns correct results.

Suggested fix:
Re-work parsing code.
ConnectionImpl seems to have similar parsing issues, don't know the impact though.

Quoting doesn't even work, I didn't realize that MySQL only supported back-quotes to quote column names ...

No good with ServerPreparedStatement as well ...
Seems like no workaround is available actually.

Hi Francois and thanks for your report. I am unable to verify it using c/J 5.1.7 (connection string contains nothing of relevance):

.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.58-pb1083-log
java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler
GetColName: foo_limit
Value: bahblah

Time: 0,344

OK (1 test)

Short test:
createTable("bug36478", "(foo_limit varchar(255) not null primary key, id_limit INT)");
this.stmt.execute("INSERT INTO bug36478 VALUES ('bahblah',1)");
this.pstmt = this.conn.prepareStatement("select foo_limit FROM bug36478");
this.rs = this.pstmt.executeQuery();
this.rs.first();
assertEquals("FIRST", true, this.rs.isFirst());
assertEquals("LAST", true, this.rs.isLast());
assertFalse("Error, after last", this.rs.isAfterLast());
assertFalse("Error, before first", this.rs.isBeforeFirst());
ResultSetMetaData metadata = this.rs.getMetaData();
System.out.println("GetColName: "+ metadata.getColumnName(1));
System.out.println("Value: "+ this.rs.getString(1));

Please attach full test case and a connection string if upgrading c/J doesn't help.

Here it is:

createTable("bug36478", "(foo_limit varchar(255) not null primary key, id_limit INT)");
this.stmt.execute("INSERT INTO bug36478 VALUES ('bahblah',1)");
this.stmt.execute("INSERT INTO bug36478 VALUES ('bahblah2',2)");
this.pstmt = this.conn.prepareStatement("select 1 FROM bug36478");

this.pstmt.setMaxRows(1);
this.rs = this.pstmt.executeQuery();
this.rs.first();
assertTrue(this.rs.isFirst());
assertTrue(this.rs.isLast());

this.pstmt = this.conn.prepareStatement("select foo_limit FROM bug36478");
this.pstmt.setMaxRows(0);
this.rs = this.pstmt.executeQuery();
this.rs.first();
assertTrue(this.rs.isFirst());
assertFalse(this.rs.isLast());

Note that skipping the intermediate query would succeed, as well as closing the intermediate PreparedStatement (because the Connection would then have no registered MaxRow'ed query).

Francois, I still can't confirm what you're seeing...

Here's my general query log that shows LIMIT is set back to DEFAULT (after MaxRows = 0) which is correct behavior:
080509 12:15:12	      8 Connect     root@localhost on test
		      8 Query       /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
		      8 Query       SHOW COLLATION
		      8 Query       SET character_set_results = NULL
		      8 Query       SET autocommit=1
		      8 Query       SELECT VERSION()
		      8 Query       DROP TABLE IF EXISTS bug36478
		      8 Query       CREATE  TABLE bug36478 (foo_limit varchar(255) not null primary key, id_limit INT)
		      8 Query       INSERT INTO bug36478 VALUES ('bahblah',1)
		      8 Query       INSERT INTO bug36478 VALUES ('bahblah2',2)
		      8 Query       SET OPTION SQL_SELECT_LIMIT=1
		      8 Query       select 1 FROM bug36478
		      8 Query       select foo_limit FROM bug36478
		      8 Query       SET OPTION SQL_SELECT_LIMIT=DEFAULT
		      8 Query       DROP TABLE IF EXISTS bug36478
		      8 Query       SET OPTION SQL_SELECT_LIMIT=DEFAULT
		      8 Quit       

Test case is changed at the end as after MaxRows(0) last assertion  *should fail* (if there's no bug, we do have 2 records):
			assertTrue(this.rs.isFirst());
			this.rs.next(); << HERE, fetching second row, proving LIMIT is not in effect.
			assertFalse(this.rs.isLast());

Sorry to say that, but I'm a little surprised by the level of java coding you're showing here (not what I thought I could expect from MySQL team), I hope it's a matter of missed sleep :)

1. your query log shows SQL_SELECT_LIMIT set back to default AFTER the query, hence not affecting the query (probably triggered by gc or test teardown).

2. the last assertion in my test was an assert FALSE, checking that the first result fetched was also NOT the last, it was correctly written and shouldn't been altered.

3. the line you added (rs.next()) don't validate anything, it just attempts to move to next row and in this case returns FALSE when failing (as next() contract), which isn't checked by your test.

4. after a failed rs.next(), rs.isLast() returns false, then working with my last assertion. 

Sorry for being hard here, but I was seriously considering MySQL as an Oracle alternative, even more with Sun involvement, but a bug like this one makes me reconsider it (or at least plan much more in testing time), nothing personal :)

Francois, no need to apologize, you are right. And on many levels (Sleep, what's sleep?). I do *all* of connectors meaning all of different languages, clients, frameworks, take your pick, and do fail to see obvious sometimes. Anyway, I'll either sleep on this one or ask for help.

Thanks for your interest in MySQL.

Verified just as described by reporter with test case provided.

There is a problem in code when we have "setMaxRows(0)" and "limit" in column name.

Added to changelog for 5.1.20: 

If the string limit was used in a column name, prepared statements
incorrectly treated the statement as if it used a LIMIT clause. For
example, a prepared statement with maxrows set to 0 could incorrectly
reuse the value from a previous call to setMaxRows(). This issue
applied to both quoted and unquoted column names, and server-side and
client-side prepared statements.