bk commit into 5.0 tree (jan:1.1934) BUG#12456

[Jan Lindstrom]

Below is the list of changes that have just been committed into a local
5.0 repository of jan. When jan does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1934 05/09/01 14:06:19 jan@stripped +1 -0
  Added test cases for Bug #12456 and high-granularity read view (Bug #12456).

  tests/mysql_client_test.c
    1.158 05/09/01 14:06:08 jan@stripped +379 -0
    Added test cases for Bug #12456 and high-granularity read view (Bug #12456).

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	jan
# Host:	hundin.mysql.fi
# Root:	/home/jan/mysql-5.0

--- 1.157/tests/mysql_client_test.c	2005-09-01 01:19:32 +03:00
+++ 1.158/tests/mysql_client_test.c	2005-09-01 14:06:08 +03:00
@@ -14348,6 +14348,383 @@
   rc= mysql_query(mysql, "drop table t1, t2");
   myquery(rc);
 }
+
+/* Bug#12456: cursor shows incorrect data */
+
+static void test_bug12456()
+{
+  MYSQL_STMT *stmt1;
+  int rc;
+  const char *stmt_text;
+  ulong type;
+  MYSQL_BIND bind;
+  int a=0;
+
+  myheader("test_bug12456");
+
+  if (! have_innodb)
+  {
+    if (!opt_silent)
+      printf("This test requires InnoDB.\n");
+    return;
+  }
+
+  /* create table and data */
+  rc= mysql_query(mysql, "CREATE TABLE bug12456(a int) ENGINE=INNODB;");
+  myquery(rc);
+  mysql_autocommit(mysql, FALSE);
+  rc=mysql_query(mysql, "INSERT INTO bug12456 VALUES (1), (2)");
+  myquery(rc);
+
+  /* create statement */
+  stmt1= mysql_stmt_init(mysql);
+  type= (ulong) CURSOR_TYPE_READ_ONLY;
+  mysql_stmt_attr_set(stmt1, STMT_ATTR_CURSOR_TYPE, (const void*) &type);
+  stmt_text= "select a from bug12456";
+  rc= mysql_stmt_prepare(stmt1, stmt_text, strlen(stmt_text));
+  check_execute(stmt1, rc);
+
+  /* bind variable */
+  memset(&bind, 0, sizeof(bind));
+  bind.buffer_type= MYSQL_TYPE_LONG;
+  bind.buffer=&a;
+  bind.buffer_length= 0;
+  bind.length= 0;
+
+  rc= mysql_stmt_bind_result(stmt1, &bind);
+  check_execute(stmt1, rc);
+
+  /* execute cursor: both rows should be visible */
+  rc= mysql_stmt_execute(stmt1);
+  check_execute(stmt1, rc);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 1);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 2);
+
+  rc= mysql_stmt_close(stmt1);
+  check_execute(stmt1, rc);
+
+  rc=mysql_query(mysql, "DELETE FROM bug12456 WHERE a=1;");
+  myquery(rc);
+
+  /* create statement */
+  stmt1= mysql_stmt_init(mysql);
+  type= (ulong) CURSOR_TYPE_READ_ONLY;
+  mysql_stmt_attr_set(stmt1, STMT_ATTR_CURSOR_TYPE, (const void*) &type);
+  stmt_text= "select a from bug12456";
+  rc= mysql_stmt_prepare(stmt1, stmt_text, strlen(stmt_text));
+  check_execute(stmt1, rc);
+
+  /* bind variable */
+  memset(&bind, 0, sizeof(bind));
+  bind.buffer_type= MYSQL_TYPE_LONG;
+  bind.buffer=&a;
+  bind.buffer_length= 0;
+  bind.length= 0;
+
+  rc= mysql_stmt_bind_result(stmt1, &bind);
+  check_execute(stmt1, rc);
+
+  /* execute cursor: only row (2) visible, row (1) is deleted */
+  rc= mysql_stmt_execute(stmt1);
+  check_execute(stmt1, rc);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 2);
+
+  rc= mysql_stmt_fetch(stmt1);
+  DIE_UNLESS(rc != 0);
+
+  rc= mysql_stmt_close(stmt1);
+  check_execute(stmt1, rc);
+
+  rc= mysql_query(mysql, "drop table bug12456");
+  myquery(rc);
+  mysql_autocommit(mysql, TRUE);                /* restore default */
+}
+
+/* Test case for high-granularity consistent read view for cursors */
+
+static void test_highview()
+{
+  MYSQL_STMT *stmt1;
+  int rc;
+  const char *stmt_text;
+  ulong type;
+  MYSQL_BIND bind[3];
+  int a=0, b=0, c=0;
+
+  myheader("test_highview");
+
+  if (! have_innodb)
+  {
+    if (!opt_silent)
+      printf("This test requires InnoDB.\n");
+    return;
+  }
+
+  /* create table and data*/
+  rc= mysql_query(mysql, "create table high_view(a int not null, b int, "
+	"c int not null,primary key(a), key(b),unique key(c))"
+	" engine = innodb");
+  myquery(rc);
+  rc= mysql_query(mysql, "insert into high_view values(1,1,1),(4,3,4),(8,8,8)");
+  myquery(rc);
+  rc= mysql_query(mysql, "commit");
+  myquery(rc);
+  rc= mysql_autocommit(mysql, FALSE);
+  myquery(rc);
+
+  /* create statement: test 1 uses clustered index */
+  stmt1= mysql_stmt_init(mysql);
+  type= (ulong) CURSOR_TYPE_READ_ONLY;
+  mysql_stmt_attr_set(stmt1, STMT_ATTR_CURSOR_TYPE, (const void*) &type);
+  stmt_text= "select * from high_view order by a";
+  rc= mysql_query(mysql, "insert into high_view values(3,1,3)");
+  myquery(rc);
+  rc= mysql_stmt_prepare(stmt1, stmt_text, strlen(stmt_text));
+  check_execute(stmt1, rc);
+  rc= mysql_query(mysql, "insert into high_view values(6,1,6)");
+  myquery(rc);
+
+  /* bind variables */
+  memset(bind, 0, sizeof(bind));
+  bind[0].buffer_type= MYSQL_TYPE_LONG;
+  bind[0].buffer=&a;
+  bind[0].buffer_length= 0;
+  bind[0].length= 0;
+  bind[1].buffer_type= MYSQL_TYPE_LONG;
+  bind[1].buffer=&b;
+  bind[1].buffer_length= 0;
+  bind[1].length= 0;
+  bind[2].buffer_type= MYSQL_TYPE_LONG;
+  bind[2].buffer=&c;
+  bind[2].buffer_length= 0;
+  bind[2].length= 0;
+
+  rc= mysql_stmt_bind_result(stmt1, bind);
+  check_execute(stmt1, rc);
+
+  /* execute cursor */
+  rc= mysql_stmt_execute(stmt1);
+  check_execute(stmt1, rc);
+
+  /* these should not be seen by the cursor ! */
+  rc= mysql_query(mysql, "insert into high_view values(7,1,7)");
+  myquery(rc);
+  rc= mysql_query(mysql, "delete from high_view where a = 3");
+  myquery(rc);
+  rc= mysql_query(mysql, "update high_view set b = 4 where a =4");
+  myquery(rc);
+  rc= mysql_query(mysql, "commit");
+  myquery(rc);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 1 && b == 1 && c == 1);
+
+  /* This row was deleted after cursor was opened, thus we see it */
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 3 && b == 1 && c == 3);
+
+  /* Update to this row was done after cursor was opened, we see old version */
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 4 && b == 3 && c == 4);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 6 && b == 1 && c == 6);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 8 && b == 8 && c == 8);
+
+  /* No more rows visible to this cursor */
+  rc= mysql_stmt_fetch(stmt1);
+  DIE_UNLESS(rc != 0);
+
+  rc= mysql_stmt_close(stmt1);
+  check_execute(stmt1, rc);
+
+  rc= mysql_query(mysql, "commit");
+  myquery(rc);
+
+  /* create statement: test 2 uses secondary index */
+  stmt1= mysql_stmt_init(mysql);
+  type= (ulong) CURSOR_TYPE_READ_ONLY;
+  mysql_stmt_attr_set(stmt1, STMT_ATTR_CURSOR_TYPE, (const void*) &type);
+  rc = mysql_query(mysql, 
+	"insert into high_view values(2,2,2),(5,3,5),(12,12,12),(9,11,9),(14,14,0)");
+  myquery(rc);
+  rc= mysql_query(mysql, "update high_view set b = 5 where b = 3");
+  myquery(rc);
+  stmt_text= "select b from high_view where b > 2 order by b";
+  rc= mysql_stmt_prepare(stmt1, stmt_text, strlen(stmt_text));
+  check_execute(stmt1, rc);
+
+  /* bind variables */
+  memset(bind, 0, sizeof(bind));
+  bind[0].buffer_type= MYSQL_TYPE_LONG;
+  bind[0].buffer=&b;
+  bind[0].buffer_length= 0;
+  bind[0].length= 0;
+
+  rc= mysql_stmt_bind_result(stmt1, bind);
+  check_execute(stmt1, rc);
+
+  /* execute cursor */
+  rc= mysql_stmt_execute(stmt1);
+  check_execute(stmt1, rc);
+
+  /* these should not be seen by the cursor ! */
+  rc= mysql_query(mysql, "update high_view set b = 9 where b = 11");
+  myquery(rc);
+  rc= mysql_query(mysql, "delete from high_view where b = 12");
+  myquery(rc);
+  rc= mysql_query(mysql, "update high_view set b = 1 where b = 14");
+  rc= mysql_query(mysql, "commit");
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(b == 4);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(b == 5);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(b == 8);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(b == 11);
+
+  /* Delete was done after the cursor was opened, thus we see this row */
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(b == 12);
+
+  /* Update was done after the cursor was opened, we see old version 
+  of the row */
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(b == 14);
+
+  /* No more rows visible to the cursor */
+  rc= mysql_stmt_fetch(stmt1);
+  DIE_UNLESS(rc != 0);
+
+  rc= mysql_stmt_close(stmt1);
+  check_execute(stmt1, rc);
+
+  rc= mysql_query(mysql, "commit");
+  myquery(rc);
+
+  /* create statement: test 3 uses unique secondary index */
+  stmt1= mysql_stmt_init(mysql);
+  type= (ulong) CURSOR_TYPE_READ_ONLY;
+  mysql_stmt_attr_set(stmt1, STMT_ATTR_CURSOR_TYPE, (const void*) &type);
+  stmt_text= "select * from high_view where c > 2 order by c";
+  rc= mysql_query(mysql, "insert into high_view values(13,13,13)");
+  myquery(rc);
+  rc= mysql_stmt_prepare(stmt1, stmt_text, strlen(stmt_text));
+  check_execute(stmt1, rc);
+  rc= mysql_query(mysql, "insert into high_view values(16,16,16)");
+  myquery(rc);
+
+  /* bind variables */
+  memset(bind, 0, sizeof(bind));
+  bind[0].buffer_type= MYSQL_TYPE_LONG;
+  bind[0].buffer=&a;
+  bind[0].buffer_length= 0;
+  bind[0].length= 0;
+  bind[1].buffer_type= MYSQL_TYPE_LONG;
+  bind[1].buffer=&b;
+  bind[1].buffer_length= 0;
+  bind[1].length= 0;
+  bind[2].buffer_type= MYSQL_TYPE_LONG;
+  bind[2].buffer=&c;
+  bind[2].buffer_length= 0;
+  bind[2].length= 0;
+
+  rc= mysql_stmt_bind_result(stmt1, bind);
+  check_execute(stmt1, rc);
+
+  /* execute cursor */
+  rc= mysql_stmt_execute(stmt1);
+  check_execute(stmt1, rc);
+
+  /* these should not be seen by the cursor ! */
+  rc= mysql_query(mysql, "insert into high_view values(17,1,17)");
+  myquery(rc);
+  rc= mysql_query(mysql, "delete from high_view where c = 8");
+  myquery(rc);
+  rc= mysql_query(mysql, "update high_view set c = 14 where c = 0");
+  myquery(rc);
+  rc= mysql_query(mysql, "commit");
+  myquery(rc);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 4 && b == 4 && c == 4);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 5 && b == 5 && c == 5);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 6 && b == 1 && c == 6);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 7 && b == 1 && c == 7);
+
+  /* This was deleted after cursor was opened, thus we see it */
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 8 && b == 8 && c == 8);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 9 && b == 9 && c == 9);
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 13 && b == 13 && c == 13);
+
+  /* Note that we do not see row (14,14,14) because update was done
+  after the cursor was opened, thus this cursor sees (14,1,0) and
+  that row does not belong to the result set */
+
+  rc= mysql_stmt_fetch(stmt1);
+  check_execute(stmt1, rc);
+  DIE_UNLESS(a == 16 && b == 16 && c == 16);
+
+  /* No more rows visible to this cursor */
+  rc= mysql_stmt_fetch(stmt1);
+  DIE_UNLESS(rc != 0);
+
+  rc= mysql_stmt_close(stmt1);
+  check_execute(stmt1, rc);
+
+  rc= mysql_query(mysql, "commit");
+  myquery(rc);
+
+  rc= mysql_query(mysql, "drop table high_view");
+  myquery(rc);
+  mysql_autocommit(mysql, TRUE);                /* restore default */
+}
+
 /*
   Read and parse arguments and MySQL options from my.cnf
 */
@@ -14599,6 +14976,8 @@
   { "test_bug11901", test_bug11901 },
   { "test_bug11904", test_bug11904 },
   { "test_bug12243", test_bug12243 },
+  { "test_bug12456", test_bug12456 },
+  { "test_highview", test_highview },
   { 0, 0 }
 };