Bug #1676 Prepared statement two-table join returns no rows when one is expected

• Submitted: 27 Oct 2003 8:49
• Modified: 20 Jan 2004 10:27
• Reporter: Per-Erik Martin
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Assigned to: Alexey Botchkov
• CPU Architecture: Any

[27 Oct 2003 8:49] Per-Erik Martin

Description:
The select below (under How to repeat) is expected to return one row,
but instead no rows are returned. Debugging reveals that make_join_select()
fails with a "Found impossible WHERE condition" warning.

How to repeat:
Call this in tests/client_test.c:

static void test_bug()
{
  MYSQL_STMT *stmt;
  MYSQL_BIND bind[3];
  char buffer[3][10];
  int rc, i;

  myheader("test_bug");

  rc= mysql_query(mysql, "drop table if exists table1, table2");
  myquery(rc);

  rc= mysql_query(mysql,
		  "create table table1("
		  " cola varchar(50) not null,"
		  " colb varchar(8) not null,"
		  " colc varchar(12) not null,"
		  " cold varchar(2) not null,"
		  " primary key (cola, colb, cold))");
  myquery(rc);

  rc= mysql_query(mysql,
		  "create table table2("
		  " cola varchar(50) not null,"
		  " colb varchar(8) not null,"
		  " colc varchar(2) not null,"
		  " cold float,"
		  " primary key (cold))");
  myquery(rc);

  rc= mysql_query(mysql,
		  "insert into table1 values"
		  " ('aaaa', 'yyyy', 'yyyy-dd-mm', 'R')");
  myquery(rc);

  rc= mysql_query(mysql,
		  "insert into table2 values"
		  " ('aaaa', 'yyyy', 'R', 203),"
		  " ('bbbb', 'zzzz', 'C', 201)");
  myquery(rc);

  strmov(query,
	 "select a.cola, a.colb, a.cold from"
	 " table1 a, table2 b where a.cola = ?"
	 "  and a.colb = ? and a.cold = ?"
	 "  and b.cola = a.cola and b.colb = a.colb and b.colc = a.cold");
  stmt = mysql_prepare(mysql, query, strlen(query));
  mystmt_init(stmt);

  verify_param_count(stmt, 3);

  strcpy(buffer[0], "aaaa");
  strcpy(buffer[1], "yyyy");
  strcpy(buffer[2], "R");
  for (i = 0 ; i < 3 ; i++)
  {
    bind[i].buffer_type= FIELD_TYPE_STRING;
    bind[i].buffer= buffer[i];
    bind[i].buffer_length= sizeof(buffer[i]);
    bind[i].length= 0;
    bind[i].is_null= 0;
  }

  rc= mysql_bind_param(stmt, bind);
  mystmt(stmt, rc);

  rc= mysql_execute(stmt);
  mystmt(stmt, rc);

  myassert(1 == my_process_stmt_result(stmt));

  mysql_stmt_close(stmt);
}

[20 Jan 2004 10:27] 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:

Fixed with the big cleanup()-related changeset

[21 Jan 2004 5:25] Alexey Botchkov

Important note: test program isn't correct.
        bind[i].buffer_length= sizeof(buffer[i]);
        bind[i].length= 0;
With that settings we get buffer_length as the length of the parameter.
That way comparation of strings failed because of different lengths.
Should be like that for example (it's not only solution here):
   int lengths[3]
....
  for (i = 0 ; i < 3 ; i++)
  {
    bind[i].buffer_type= FIELD_TYPE_STRING;
    bind[i].buffer= buffer[i];
    bind[i].buffer_length= sizeof(buffer[i]);
    bind[i].length= lengths + i;
    bind[i].is_null= 0;
  }

  for (i = 0 ; i < 3 ; i++)
  {
    lengths[i]= strlen(buffer[i]);
  }