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:Microsoft Windows (WindowsXP)
Assigned to: Georg Richter CPU Architecture:Any

[2 Jun 2005 13:44] Sergey Nikitin
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
[2 Jun 2005 15:19] Jorge del Conde
Hi,

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

Thanks a lot.
[3 Jun 2005 6:13] Sergey Nikitin
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
//...
[3 Jun 2005 8:05] Hartmut Holzgraefe
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 ...?
[3 Jun 2005 13:01] Konstantin Osipov
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.
[6 Jun 2005 9:40] Sergey Nikitin
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.
[11 Jun 2005 7:54] Hartmut Holzgraefe
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 ...
[11 Jun 2005 8:20] Konstantin Osipov
The question is: should I implement mysql_stmt_eof or restore the old behavior?
[11 Jun 2005 8:26] Konstantin Osipov
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.
[15 Jul 2005 12:41] Bugs System
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
[27 Jul 2005 16:52] Georg Richter
fixed in 5.0.11
[3 Aug 2005 17:06] Jon Stephens
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.