Bug #14845 mysql_stmt_fetch returns MYSQL_NO_DATA when COUNT(*) is 0
Submitted: 10 Nov 2005 18:28 Modified: 20 Nov 2005 4:18
Reporter: Geert Vanderkelen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S1 (Critical)
Version:5.0.17 OS:Linux (Linux)
Assigned to: Konstantin Osipov CPU Architecture:Any

[10 Nov 2005 18:28] Geert Vanderkelen
Description:
Hi!

mysql_stmt_fetch is returning MYSQL_NO_DATA when using preparted statements and CURSOR_TYPE_READ_ONLY.

A query like "SELECT COUNT(*) FROM t1 WHERE 1 = 0", should have 1 row with a field value
of zero. mysql_stmt_fetch() whould return 0, but returns MYSQL_NO_DATA.

Does not happen with 5.0.15, does with latest 5.0.17 pull.

Geert

How to repeat:
See attached, small applicaton. If you leave out the cursor attribute setting, then it works
correctly.
[10 Nov 2005 18:30] Geert Vanderkelen
Small app for reproducing

Attachment: prepcursor.cpp (text/plain), 808 bytes.

[10 Nov 2005 18:35] Geert Vanderkelen
Forgot to include the DDL of the table used, though it works with any table:

CREATE TABLE `t1` (
  `id` int(11) default NULL,
  `name` varchar(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into t1 values (1,'abc'),(2,'def');
[11 Nov 2005 9:44] Valeriy Kravchuk
Verified just as described on 5.0.17-BK (ChangeSet@1.1957, 2005-11-09 20:31:01+03:00, ...):

[openxs@Fedora 5.0]$ ./14845
Client info: 5.0.17
Server info: 5.0.17
mysql_stmt_fetch returned: 100

I added the following to lines to the test case:

  printf("Client info: %s\n", mysql_get_client_info());
  printf("Server info: %s\n", mysql_get_server_info(mysql));
[18 Nov 2005 13:47] Konstantin Osipov
I was unable to repeat neither the original issue nor the test case supplied with the bug report:

kostja@dragonfly:~> g++ -I /home/kostja/work/mysql-5.0-root/include -L /home/kostja/work/mysql-5.0-root/libmysql/.libs -lmysqlclient -lz -lnsl ./issue1.cpp 
kostja@dragonfly:~> export LD_LIBRARY_PATH=/home/kostja/work/mysql-5.0-root/libmysql/.libs 
kostja@dragonfly:~> ./a.out 
kostja@dragonfly:~> ./a.out
kostja@dragonfly:~> ./a.out
kostja@dragonfly:~> ./a.out

I added the following test case to MySQL test suite:
*/

static void test_bug14845()
{
  MYSQL_STMT *stmt;
  int rc;
  const ulong type= CURSOR_TYPE_READ_ONLY;
  const char *query= "select count(*) from t1 where 1 = 0";

  myheader("test_bug14845");

  rc= mysql_query(mysql, "drop table if exists t1");
  myquery(rc);
  rc= mysql_query(mysql, "create table t1 (id int(11) default null, "
                         "name varchar(20) default null)"
                         "engine=MyISAM DEFAULT CHARSET=utf8");
  myquery(rc);
  rc= mysql_query(mysql, "insert into t1 values (1,'abc'),(2,'def')");
  myquery(rc);

  stmt= mysql_stmt_init(mysql);
  rc= mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void*) &type);
  check_execute(stmt, rc);

  rc= mysql_stmt_prepare(stmt, query, strlen(query));
  check_execute(stmt, rc);

  rc= mysql_stmt_execute(stmt);
  check_execute(stmt, rc);

  rc= mysql_stmt_fetch(stmt);
  DIE_UNLESS(rc == 0);

  rc= mysql_stmt_fetch(stmt);
  DIE_UNLESS(rc == MYSQL_NO_DATA);

  /* Cleanup */
  mysql_stmt_close(stmt);
  rc= mysql_query(mysql, "drop table t1");
  myquery(rc);
}

Please reopen the bug report if the original problem is still present.
[18 Nov 2005 14:56] 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/32406
[19 Nov 2005 14:48] Konstantin Osipov
The bug is repeatable with compile-pentium-debug-max build.
[19 Nov 2005 14:57] 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/32433
[19 Nov 2005 15:10] Guilhem Bichot
just because I mention the bug report in a commit mail, does not mean I have a proposal to fix it :)
[19 Nov 2005 16:00] 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/32435
[19 Nov 2005 16:25] Konstantin Osipov
Documentation note: fixed in 5.0 tree, currently tagged 5.0.17
[20 Nov 2005 4:18] Paul DuBois
Noted in 5.0.17 changelog.