Bug #49726 SQL_ATTR_MAX_ROWS make some SELECT statement invalid.

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.

Thanks Meiji.

patch, the test case comes later

Attachment: patch49726.diff (text/x-diff), 1.56 KiB.

Patch that uses different approach - engaging sql_select_limit server variable

Attachment: bug49726_sql_select_limit.patch (application/octet-stream, text), 5.18 KiB.

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.

pushed as rev#871. with certain luck it goes to 5.1.7

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.

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.

merged to 3.51 tree as rev#773. next version it supposed to appear in is 3.51.30

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.