| Bug #49726 | SQL_ATTR_MAX_ROWS make some SELECT statement invalid. | ||
|---|---|---|---|
| Submitted: | 16 Dec 2009 1:50 | Modified: | 25 Jan 2012 23:20 |
| Reporter: | Meiji KIMURA | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 5.1.6 | OS: | Windows |
| Assigned to: | Lawrenty Novitsky | CPU Architecture: | Any |
[16 Dec 2009 7:10]
Tonci Grgin
Thanks Meiji.
[18 Jan 2010 5:26]
Bogdan Degtyariov
patch, the test case comes later
Attachment: patch49726.diff (text/x-diff), 1.56 KiB.
[18 Jan 2010 20:13]
Lawrenty Novitsky
Patch that uses different approach - engaging sql_select_limit server variable
Attachment: bug49726_sql_select_limit.patch (application/octet-stream, text), 5.18 KiB.
[18 Jan 2010 20:18]
Lawrenty Novitsky
I think no additional testcase is needed. There is testcase of SQL_ATTR_MAX_ROWS attribute using(my_result.c/t_max_rows) - i believe it's quite enough.
[12 Mar 2010 14:39]
Lawrenty Novitsky
pushed as rev#871. with certain luck it goes to 5.1.7
[24 Mar 2010 16:02]
Tony Bedford
An entry has been added to the 5.1.7 changelog: MySQL Connector/ODBC incorrectly emitted SELECT ... FOR UPDATE LIMIT 1 instead of SELECT ... LIMIT 1 FOR UPDATE.
[24 Mar 2010 17:38]
Tony Bedford
Changelog entry has been updated: MySQL Connector/ODBC manually added a LIMIT clause to the end of certain SQL statements, causing errors for statements that contained code that should be positioned after the LIMIT clause.
[12 Dec 2011 16:22]
Lawrenty Novitsky
merged to 3.51 tree as rev#773. next version it supposed to appear in is 3.51.30
[25 Jan 2012 23:20]
Philip Olson
This was already documented, as: ODBC manually added a LIMIT clause to the end of certain SQL statements, causing errors for statements that contained code that should be positioned after the LIMIT clause. So, closing.
Description: In some environment, 'SELECT .... FOR UPDATE' is issued via ODBC, it makes SELECT statement invalid. Invalid: SELECT .... FOR UPDATE LIMIT 1 Should be: SELECT .... LIMIT 1 FOR UPDATE I looked into the sources of Connector/ODBC in details. When stmt_options.max_row is set (or not zero), Connector/ODBC try to add 'limit %lu' simply like this, (in execute.c, line 56) 44 if (stmt->stmt_options.max_rows && 45 stmt->stmt_options.max_rows != (SQLULEN)~0L) 46 { 47 /* Add limit to select statement */ 48 char *pos,*tmp_buffer; 49 for ( pos= query; isspace(*pos) ; pos++ ) ; 50 if ( !myodbc_casecmp(pos,"select",6) ) 51 { 52 uint length= strlen(pos); 53 if ( (tmp_buffer= my_malloc(length+30,MYF(0))) ) 54 { 55 memcpy(tmp_buffer,pos,length); 56 sprintf(tmp_buffer+length, " limit %lu", 57 (unsigned long)stmt->stmt_options.max_rows); It is not a good method. Because ordinary SELECT statement can locate some caluses after 'limit' like this, [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]] See. http://dev.mysql.com/doc/refman/5.1/en/select.html in details. So if the Connector/ODBC add limit to an original SELECT statement, it consider these clauses after LIMIT. (or existed LIMIT.) How to repeat: (1) Set stmt_options.max_row via SQLSetStmtAttr's SQL_ATTR_MAX_ROWS. (2) Execute 'SELECT .... FOR UPDATE'. Suggested fix: [Suggested fix] Consider these clauses after LIMIT.