| 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: | |
| Category: | Severity: | S3 (Non-critical) | |
| Version: | 4.1 | OS: | Linux (SuSE 8.0 Linux) |
| Assigned to: | Venu Anuganti | CPU Architecture: | Any |
[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.
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: