MySQL Bugs: #14845: mysql_stmt_fetch returns MYSQL_NO

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

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.

Small app for reproducing

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

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');

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));

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.

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

The bug is repeatable with compile-pentium-debug-max build.

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

just because I mention the bug report in a commit mail, does not mean I have a proposal to fix it :)

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

Documentation note: fixed in 5.0 tree, currently tagged 5.0.17

Noted in 5.0.17 changelog.