Bug #85 PreparedStatement SELECTS Do Not Honor 'SET OPTION SQL_SELECT_LIMIT'
Submitted: 19 Feb 2003 17:28 Modified: 25 Feb 2003 20:35
Reporter: Mark Matthews Email Updates:
Status: Closed Impact on me:
None 
Category: Severity:S3 (Non-critical)
Version:4.1 OS:Linux (SuSE 8.0 Linux)
Assigned to: Bugs System CPU Architecture:Any

[19 Feb 2003 17:28] Mark Matthews
Description:
If you issue a 'SET OPTION SQL_SELECT_LIMIT' before executing a prepared statement that is a SELECT, it has no effect. All rows are returned.

I found this while finishing up the prepared statement implementation for the JDBC driver:

030220  1:11:32      36 Connect     testnew@66.93.114.100 on test
                     36 Query       SHOW VARIABLES
030220  1:11:33      36 Query       SET autocommit=1
                     36 Prepare     SELECT field1 FROM pStmtServer
                     36 Query       SET OPTION SQL_SELECT_LIMIT=1

Notice in my debugging output, three rows returned before the 'LAST DATA PACKET' is sent:

reuseAndReadPacket()

00 00 07 61 62 63 64 65     . . . a b c d e 
66 67                       f g 

Binary row: 

*** MAX ROWS ***-1
reuseAndReadPacket()

00 00 07 61 62 63 64 65     . . . a b c d e 
66 67                       f g 

Binary row: 

maxRows != -1 && rowCount < maxRows: true
reuseAndReadPacket()

00 00 08 31 32 33 34 35     . . . 1 2 3 4 5 
36 37 38                    6 7 8 

Binary row: 

maxRows != -1 && rowCount < maxRows: true
reuseAndReadPacket()

fffffffe 00 00 00 00              . . . . .

How to repeat:
[21 Feb 2003 23:03] Venu Anuganti
Hi Mark,

Sorry for the delay in getting on to this topic.

I tested this now, and it all works just fine at my end. I also added a test 'test_set_option' to simulate this in tests/client_test.c. 

Could it be possible to cross check this from your end ?

Here is the test and its output.

TEST:

/*
  To test SET OPTION feature with prepare stmts
*/
static void test_set_option()
{
  MYSQL_STMT *stmt;
  MYSQL_RES  *result;
  int        rc;

  myheader("test_set_option");

  mysql_autocommit(mysql, TRUE);

  /* LIMIT the rows count to 2 */
  rc= mysql_query(mysql,"SET OPTION SQL_SELECT_LIMIT=2");
  myquery(rc);

  rc= mysql_query(mysql,"DROP TABLE IF EXISTS test_limit");
  myquery(rc);
  
  rc= mysql_query(mysql,"CREATE TABLE test_limit(a tinyint)");
  myquery(rc);
  
  rc= mysql_query(mysql,"INSERT INTO test_limit VALUES(10),(20),(30),(40)");
  myquery(rc);  
  
  fprintf(stdout,"\n with SQL_SELECT_LIMIT=2 (direct)");
  rc = mysql_query(mysql,"SELECT * FROM test_limit");
  myquery(rc);

  result = mysql_store_result(mysql);
  mytest(result);

  myassert(2 == my_process_result_set(result));

  mysql_free_result(result);
  
  fprintf(stdout,"\n with SQL_SELECT_LIMIT=2 (prepare)");  
  stmt = mysql_prepare(mysql, "SELECT * FROM test_limit", 50);
  mystmt_init(stmt);

  rc = mysql_execute(stmt);
  mystmt(stmt,rc);

  myassert(2 == my_process_stmt_result(stmt));

  mysql_stmt_close(stmt);

  /* RESET the LIMIT the rows count to 0 */  
  fprintf(stdout,"\n with SQL_SELECT_LIMIT=DEFAULT (prepare)");
  rc= mysql_query(mysql,"SET OPTION SQL_SELECT_LIMIT=DEFAULT");
  myquery(rc);
  
  stmt = mysql_prepare(mysql, "SELECT * FROM test_limit", 50);
  mystmt_init(stmt);

  rc = mysql_execute(stmt);
  mystmt(stmt,rc);

  myassert(4 == my_process_stmt_result(stmt));

  mysql_stmt_close(stmt);
}

OUTPUT:

#####################################
1 of (1/1): test_set_option
#####################################

 with SQL_SELECT_LIMIT=2 (direct)

        +------+
        | a    |
        +------+
        |   10 |
        |   20 |
        +------+

        2 rows returned

 with SQL_SELECT_LIMIT=2 (prepare)

        +------+
        | a    |
        +------+
        |   10 |
        |   20 |
        +------+

        2 rows returned

 with SQL_SELECT_LIMIT=DEFAULT (prepare)

        +------+
        | a    |
        +------+
        |   10 |
        |   20 |
        |   30 |
        |   40 |
        +------+

        4 rows returned

Let me know if you have any comments.

Thanks
[25 Feb 2003 20:35] Mark Matthews
Went away with latest bk pull.