MySQL Bugs: #11037: mysql_stmt_fetch() returns error instead of MYSQL_NO

Bug #11037	mysql_stmt_fetch() returns error instead of MYSQL_NO_DATA
Submitted:	2 Jun 2005 13:44	Modified:	3 Aug 2005 17:06
Reporter:	Sergey Nikitin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.6	OS:	Windows (WindowsXP)
Assigned to:	Georg Richter	CPU Architecture:	Any

Description:
Second mysql_stmt_fetch() call after result set is over returns error instead of MYSQL_NO_DATA

How to repeat:
First mysql_stmt_fetch() call after result set was fully fetched returns MYSQL_NO_DATA.
Next calls return error with CR_NO_RESULT_SET code. Is it correct? Previous libmysql version returns  MYSQL_NO_DATA.

Suggested fix:
As I see implementation of  stmt_read_row_no_data() function was changed from 

static int
stmt_read_row_no_data(MYSQL_STMT *stmt  __attribute__((unused)),
                      unsigned char **row  __attribute__((unused)))
{
  if ((int) stmt->state < (int) MYSQL_STMT_PREPARE_DONE)
  {
    set_stmt_error(stmt, CR_NO_PREPARE_STMT, unknown_sqlstate);
    return 1;
  }
  return MYSQL_NO_DATA;
}
 
in v. 5.0.4 to
static int
stmt_read_row_no_data(MYSQL_STMT *stmt  __attribute__((unused)),
                      unsigned char **row  __attribute__((unused)))
{
  set_stmt_error(stmt, CR_NO_RESULT_SET, unknown_sqlstate);
  return 1;
}
in v. 5.0.6

Hi,

Can you please give us a test-case that successfully reproduces this behaviour ?

Thanks a lot.

Test case:
//...
MYSQL session;
mysql_init(&session);
mysql_real_connect(&session, "localhost", "root", "123456", "mysql", MYSQL_PORT, NULL, 0);
MYSQL_STMT* stmt = mysql_stmt_init(&session);
const char* pszQuery = "select host from user";
mysql_stmt_prepare(stmt, pszQuery, strlen(pszQuery));
MYSQL_BIND bind;
memset(&bind, 0, sizeof MYSQL_BIND);
bind.buffer_type = MYSQL_TYPE_NULL;
mysql_stmt_bind_result(stmt, &bind);
mysql_stmt_execute(stmt);
int nRet = 0;
while (!(nRet=mysql_stmt_fetch(stmt))); //Finish to fetch with MYSQL_NO_DATA ret code
nRet = mysql_stmt_fetch(stmt); //Next fetch returns error...
int nErrCode = mysql_stmt_errno(stmt); //with error code CR_NO_RESULT_SET
//...

In the code it says:

  - mysql_stmt_fetch when there are no rows (always returns MYSQL_NO_DATA)

so i think the old behavior was right

in mysql_stmt_fetch() itself there is now an additional check for

  rc != MYSQL_DATA_TRUNCATED

maybe this interferes here ...?

The reason for this change is that in 4.1 there were no CR_NO_RESULT_SET error code.
Honestly I thought that the new behaviour is more consistent, as subsequent fetch while there is no result set usually indicates an error in the application.
And 5.0 - 4.1 is a major change in release numbers so one should expect slight changes like this.
Please let me know if you think it's not an improvement and your rationale for this bug report.

I have a specific problem in own DCOM layer caching classes that prefetch rows. Obviously the logic is incorrect but Oracle, SQLBase and MySQL prior 5.0.6 work similar in this case. Also I think the error CR_NO_RESULT_SET is not fully clear for statements with buffered result set as I can seek any row after result set is fetched. Anyway it's not a problem for me now. Thanks a lot for your attention.

Konstantin,

this is *at least* a documentation problem as the change is not noted on the mysql_stmt_fetch() manaul page and the new error code is not listed as possible error returned by mysql_stmt_fetch() either.

The 

   - mysql_stmt_fetch when there are no rows (always returns MYSQL_NO_DATA)

comment in the code should also be fixed if we stick with the new behavior

There's also no mysql_stmt_eof() or similar function to check against so at times
submitting another mysql_stmt_fetch() may happen, in such a case i would prefere
to receive another MYSQL_NO_DATA instead of an error code ...

The question is: should I implement mysql_stmt_eof or restore the old behavior?

Hm, mysql_stmt_eof is not a solution for the problem. I think I need to have the old behavior and
the new together:
- when someone attempts to call mysql_stmt_fetch prior to mysql_stmt_execute, 
  I should return CR_NO_RESULT_SET
- when someone is just in position 'after-last' of an existing result set, I should return
MYSQL_NO_DATA.
Does it sound good?
The original problem I tried to solve is catching wrong uses of the API.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27146

fixed in 5.0.11

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 5.0.11 changelog; bug closed.