Bug #28255 Cursor operations on result sets containing only part of a key are incorrect
Submitted: 4 May 2007 20:53 Modified: 6 Jun 2007 11:56
Reporter: Jim Winstead Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.15 OS:Any
Assigned to: Jim Winstead
Tags: multipart key, SQLSetPos

[4 May 2007 20:53] Jim Winstead
Description:
insert_pk_fields() in driver/cursor.c has some faulty code that tries to figure out the missing components of a multi-part key when a result set only contains one component. If the statement used for the result set contains any sort of WHERE clause, or an ORDER BY, or probably some number of other variations, the wrong row will easily be selected.

How to repeat:
DECLARE_TEST(t_bug9999)
{
  SQLLEN nlen;

  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug9999");
  ok_sql(hstmt, "CREATE TABLE t_bug9999 (a INT, b INT, PRIMARY KEY (a,b))");
  ok_sql(hstmt, "INSERT INTO t_bug9999 VALUES (1,3),(1,4)");

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));

  ok_stmt(hstmt, SQLSetCursorName(hstmt, (SQLCHAR *)"bug", SQL_NTS));

  ok_sql(hstmt, "SELECT a FROM t_bug9999 WHERE b > 3");

  ok_stmt(hstmt, SQLFetch(hstmt));
  is_num(my_fetch_int(hstmt, 1), 1);

  ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE));
  ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_DELETE, SQL_LOCK_NO_CHANGE));

  ok_stmt(hstmt, SQLRowCount(hstmt, &nlen));
  is_num(nlen, 1);

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_UNBIND));
  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));

  ok_sql(hstmt, "SELECT * FROM t_bug9999");

  ok_stmt(hstmt, SQLFetch(hstmt));
  is_num(my_fetch_int(hstmt, 1), 1);
  is_num(my_fetch_int(hstmt, 2), 3);

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));

  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug9999");

  return OK;
}

Suggested fix:
The history of this code in insert_pk_fields() needs to be investigated to see if it was the solution to some other bug, otherwise it needs to be ripped out, and we need to fail SQLSetPos() operations on result sets that don't contain all components of a key that can uniquely identify a row.
[10 May 2007 18:55] Jim Winstead
fix building of WHERE clause against a multipart primary key

Attachment: bug28255.patch (application/octet-stream, text), 26.03 KiB.

[10 May 2007 19:19] Jim Winstead
in addition to fixing the originally-described problem, the attached patch also fixes insert_pk_fields() to handle aliased field names in the result set.  it also removes a number of tests that relied upon the old, incorrect, behavior.
[16 May 2007 3:14] Jim Winstead
Bug #19566 is related to this one. Even with this bug fixed, we sometimes try to use too many fields when there is a suitable primary or unique key available.
[30 May 2007 20:35] Jim Winstead
The fix for this has been committed to the source repository, and will be in 3.51.16.
[6 Jun 2007 11:56] MC Brown
A note has been added to the 3.51.16 changelog.