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: | |
Category: | Connector / J | Severity: | S3 (Non-critical) |
Version: | 5.1.5 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[2 May 2008 20:54]
francois valdy
[2 May 2008 21:40]
francois valdy
Quoting doesn't even work, I didn't realize that MySQL only supported back-quotes to quote column names ...
[2 May 2008 22:36]
francois valdy
No good with ServerPreparedStatement as well ... Seems like no workaround is available actually.
[6 May 2008 13:10]
Tonci Grgin
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.
[6 May 2008 13:39]
francois valdy
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).
[9 May 2008 10:21]
Tonci Grgin
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());
[14 May 2008 13:00]
francois valdy
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 :)
[16 May 2008 10:27]
Tonci Grgin
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.
[21 May 2008 9:16]
Tonci Grgin
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.
[19 Apr 2012 1:02]
John Russell
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.