Bug #2188 Query with select in field list doesn't work with prepared statements

• Submitted: 22 Dec 2003 8:22
• Modified: 20 Jan 2004 11:04
• Reporter: Konstantin Osipov (OCA)
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1, 5.0
• OS: Any (all)
• Assigned to: Alexey Botchkov
• CPU Architecture: Any

[22 Dec 2003 8:22] Konstantin Osipov

Description:
See the followint test program and test results:

-- fill data
CREATE TABLE t1 (
  a int(11) default NULL,
  b int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t1`
--

INSERT INTO t1 VALUES (1,2);
INSERT INTO t1 VALUES (2,3);
INSERT INTO t1 VALUES (3,4);
INSERT INTO t1 VALUES (4,5);
INSERT INTO t1 VALUES (4,7);
INSERT INTO t1 VALUES (4,8);

CREATE TABLE t2 SELECT a, b FROM t1;
-- end fill data

compile && run the program:

--cut: prep0.c
#include <mysql.h>
#include <string.h>
#include <stdlib.h>
#include <stdio.h>

MYSQL *mysql; /* zeroed by linker */

const char *query= "SELECT SQL_BUFFER_RESULT "
                   "(SELECT COUNT(DISTINCT b) FROM t1 GROUP BY t1.a LIMIT 1) "
                   "FROM t1,t2 WHERE t1.a=t2.b";

void die_if(int rc, const char *message)
{
  if (rc)
  {
    fprintf(stderr, "%s:\n", message);
    if (mysql)
    {
      fprintf(stderr, "%s\n", mysql_error(mysql));
    }

    exit(1);
  }
}

void my_process_stmt_result(MYSQL_STMT *stmt);

int main(int argc, char *argv[])
{
  die_if(!(mysql= mysql_init(0)),
         "mysql_init failed");

  die_if(!(mysql_real_connect(mysql,
                              0, /* host */
                              "root", /* user */
                              0, /* password */
                              "test", /* database */
                              0, /* port */
                              "/opt/local/var/mysql/mysql.sock",
                              0 /* flags */)),
         "mysql_real_connect failed");

  die_if(mysql_real_query(mysql, query, strlen(query)),
         "mysql_real_query failed");

  mysql_free_result(mysql_store_result(mysql));

  printf("mysql_real_query(): success.\n");

  MYSQL_STMT *stmt;

  die_if(!(stmt= mysql_prepare(mysql, query, strlen(query))),
         "mysql_prepare failed");

  die_if(mysql_execute(stmt),
         "mysql_execute (first time) failed");

  my_process_stmt_result(stmt);

  mysql_stmt_close(stmt);

  mysql_close(mysql);

  return 0;
}

void my_process_stmt_result(MYSQL_STMT *stmt)
{
    while (!mysql_fetch(stmt))
      {}
}

--enc cut

How to repeat:
fill database, run the program

Suggested fix:
something is rotten in sql_prepare.cc:mysql_test_select_fields
THD::query_id could be involved as well.

[26 Dec 2003 3:00] Alexey Botchkov

bk commit - 4.1 tree (hf:1.1669) (26 Dec 2003)

[20 Jan 2004 11:04] Alexey Botchkov

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:

Closed with the big cleanup()-related patch