commit into mysql-6.0 branch (konstantin:2644) Bug#27430, WL#4166

[Konstantin Osipov]

#At file:///opt/local/work/mysql-6.0-27430/

 2644 Konstantin Osipov	2008-05-26
      Cover four special cases of WL#4166 with tests:
       - when the query cache is disabled at the time of prepare statement
         reprepare
       - when long data parameters are used
       - when character_set_connection != character_set_client, and a parameter
         conversion takes place
       - when parameter data is out of acceptable range, e.g. year 10000 is
         supplied as part of MYSQL_TYPE_DATETIME value. The server is supposed
         to warn in such case.
modified:
  mysql-test/include/query_cache_sql_prepare.inc
  mysql-test/r/query_cache_ps_no_prot.result
  mysql-test/r/query_cache_ps_ps_prot.result
  tests/mysql_client_test.c

per-file comments:
  mysql-test/include/query_cache_sql_prepare.inc
    Addditional test for Bug#27430
  mysql-test/r/query_cache_ps_no_prot.result
    Update result file.
  mysql-test/r/query_cache_ps_ps_prot.result
    Update result file.
  tests/mysql_client_test.c
    Add more tests (Bug#27430 and WL#4166)

=== modified file 'mysql-test/include/query_cache_sql_prepare.inc'
--- a/mysql-test/include/query_cache_sql_prepare.inc	2007-08-31 16:42:14 +0000
+++ b/mysql-test/include/query_cache_sql_prepare.inc	2008-05-26 12:06:47 +0000
@@ -1,11 +1,13 @@
 ############### include/query_cache_sql_prepare.inc ################
 #
 # This is to see how statements prepared via the PREPARE SQL command
-# go into the query cache: if using parameters they cannot; if not
-# using parameters they can.
+# go into the query cache.
 # Query cache is abbreviated as "QC"
 #
 # Last update:
+# 2008-05-26 Kostja
+#               - Add test coverage for automatic statement reprepare
+#
 # 2007-05-03 ML - Move t/query_cache_sql_prepare.test
 #                 to   include/query_cache_sql_prepare.inc
 #               - Create two toplevel tests sourcing this routine
@@ -490,6 +492,37 @@
 
 --echo
 --echo ########################################################################
+--echo #
+--echo # Bug#27430 Crash in subquery code when in PS and table DDL changed
+--echo # after PREPARE
+--echo # Check the effect of automatic reprepare on query cache
+--echo #
+--echo ########################################################################
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a varchar(255));
+insert into t1 (a) values ("Pack my box with five dozen liquor jugs.");
+flush status;
+prepare stmt from "select a from t1";
+execute stmt;
+set @@global.query_cache_size=0;
+alter table t1 add column b int;
+execute stmt;
+set @@global.query_cache_size=100000;
+execute stmt;
+execute stmt;
+--echo #
+--echo # Sic: ALTER TABLE caused an automatic reprepare 
+--echo # of the prepared statement. Since the query cache was disabled
+--echo # at the time of reprepare, the new prepared statement doesn't
+--echo # work with it.
+--echo # 
+show status like 'Qcache_hits';
+show status like 'Qcache_queries_in_cache';
+--echo # Cleanup
+deallocate prepare stmt;
+drop table t1;
 
 ###############################################################################
 

=== modified file 'mysql-test/r/query_cache_ps_no_prot.result'
--- a/mysql-test/r/query_cache_ps_no_prot.result	2007-08-31 16:42:14 +0000
+++ b/mysql-test/r/query_cache_ps_no_prot.result	2008-05-26 12:06:47 +0000
@@ -529,5 +529,46 @@
 use test;
 
 ########################################################################
+#
+# Bug#27430 Crash in subquery code when in PS and table DDL changed
+# after PREPARE
+# Check the effect of automatic reprepare on query cache
+#
+########################################################################
+drop table if exists t1;
+create table t1 (a varchar(255));
+insert into t1 (a) values ("Pack my box with five dozen liquor jugs.");
+flush status;
+prepare stmt from "select a from t1";
+execute stmt;
+a
+Pack my box with five dozen liquor jugs.
+set @@global.query_cache_size=0;
+alter table t1 add column b int;
+execute stmt;
+a
+Pack my box with five dozen liquor jugs.
+set @@global.query_cache_size=100000;
+execute stmt;
+a
+Pack my box with five dozen liquor jugs.
+execute stmt;
+a
+Pack my box with five dozen liquor jugs.
+#
+# Sic: ALTER TABLE caused an automatic reprepare 
+# of the prepared statement. Since the query cache was disabled
+# at the time of reprepare, the new prepared statement doesn't
+# work with it.
+# 
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+show status like 'Qcache_queries_in_cache';
+Variable_name	Value
+Qcache_queries_in_cache	0
+# Cleanup
+deallocate prepare stmt;
+drop table t1;
 set @@global.query_cache_size=@initial_query_cache_size;
 flush status;

=== modified file 'mysql-test/r/query_cache_ps_ps_prot.result'
--- a/mysql-test/r/query_cache_ps_ps_prot.result	2007-08-31 16:42:14 +0000
+++ b/mysql-test/r/query_cache_ps_ps_prot.result	2008-05-26 12:06:47 +0000
@@ -529,5 +529,46 @@
 use test;
 
 ########################################################################
+#
+# Bug#27430 Crash in subquery code when in PS and table DDL changed
+# after PREPARE
+# Check the effect of automatic reprepare on query cache
+#
+########################################################################
+drop table if exists t1;
+create table t1 (a varchar(255));
+insert into t1 (a) values ("Pack my box with five dozen liquor jugs.");
+flush status;
+prepare stmt from "select a from t1";
+execute stmt;
+a
+Pack my box with five dozen liquor jugs.
+set @@global.query_cache_size=0;
+alter table t1 add column b int;
+execute stmt;
+a
+Pack my box with five dozen liquor jugs.
+set @@global.query_cache_size=100000;
+execute stmt;
+a
+Pack my box with five dozen liquor jugs.
+execute stmt;
+a
+Pack my box with five dozen liquor jugs.
+#
+# Sic: ALTER TABLE caused an automatic reprepare 
+# of the prepared statement. Since the query cache was disabled
+# at the time of reprepare, the new prepared statement doesn't
+# work with it.
+# 
+show status like 'Qcache_hits';
+Variable_name	Value
+Qcache_hits	0
+show status like 'Qcache_queries_in_cache';
+Variable_name	Value
+Qcache_queries_in_cache	0
+# Cleanup
+deallocate prepare stmt;
+drop table t1;
 set @@global.query_cache_size=@initial_query_cache_size;
 flush status;

=== modified file 'tests/mysql_client_test.c'
--- a/tests/mysql_client_test.c	2008-05-21 10:17:29 +0000
+++ b/tests/mysql_client_test.c	2008-05-26 12:06:47 +0000
@@ -664,6 +664,29 @@
   return row_count;
 }
 
+/* Print the total number of warnings and the warnings themselves.  */
+
+void my_process_warnings(MYSQL *conn, unsigned expected_warning_count)
+{
+  MYSQL_RES *result;
+  int rc;
+
+  if (!opt_silent)
+    fprintf(stdout, "\n total warnings: %u (expected: %u)\n",
+            mysql_warning_count(conn), expected_warning_count);
+
+  DIE_UNLESS(mysql_warning_count(mysql) == expected_warning_count);
+
+  rc= mysql_query(conn, "SHOW WARNINGS");
+  DIE_UNLESS(rc == 0);
+
+  result= mysql_store_result(conn);
+  mytest(result);
+
+  rc= my_process_result_set(result);
+  mysql_free_result(result);
+}
+
 
 /* Utility function to verify a particular column data */
 
@@ -12491,7 +12514,7 @@
 
   rc= mysql_stmt_execute(stmt);
   check_execute(stmt, rc);
-  DIE_UNLESS(mysql_warning_count(mysql) != 6);
+  my_process_warnings(mysql, 12);
 
   verify_col_data("t1", "year", "0000-00-00 00:00:00");
   verify_col_data("t1", "month", "0000-00-00 00:00:00");
@@ -12522,7 +12545,7 @@
 
   rc= mysql_stmt_execute(stmt);
   check_execute(stmt, rc);
-  DIE_UNLESS(mysql_warning_count(mysql) != 3);
+  my_process_warnings(mysql, 6);
 
   verify_col_data("t1", "year", "0000-00-00 00:00:00");
   verify_col_data("t1", "month", "0000-00-00 00:00:00");
@@ -12561,7 +12584,7 @@
 
   rc= mysql_stmt_execute(stmt);
   check_execute(stmt, rc);
-  DIE_UNLESS(mysql_warning_count(mysql) == 2);
+  my_process_warnings(mysql, 2);
 
   verify_col_data("t1", "day_ovfl", "838:59:59");
   verify_col_data("t1", "day", "828:30:30");
@@ -17599,6 +17622,167 @@
 
 }
 
+
+/**
+  Test how warnings generated during assignment of parameters
+  are (currently not) preserve in case of reprepare.
+*/
+
+static void test_wl4166_3()
+{
+  int rc;
+  MYSQL_STMT *stmt;
+  MYSQL_BIND my_bind[1];
+  MYSQL_TIME tm[1];
+
+  myheader("test_wl4166_3");
+
+  rc= mysql_query(mysql, "drop table if exists t1");
+  myquery(rc);
+
+  rc= mysql_query(mysql, "create table t1 (year datetime)");
+  myquery(rc);
+
+  stmt= mysql_simple_prepare(mysql, "insert into t1 (year) values (?)");
+  check_stmt(stmt);
+  verify_param_count(stmt, 1);
+
+  bzero((char*) my_bind, sizeof(my_bind));
+  my_bind[0].buffer_type= MYSQL_TYPE_DATETIME;
+  my_bind[0].buffer= &tm[0];
+
+  rc= mysql_stmt_bind_param(stmt, my_bind);
+  check_execute(stmt, rc);
+
+  tm[0].year= 10000;
+  tm[0].month= 1; tm[0].day= 1;
+  tm[0].hour= 1; tm[0].minute= 1; tm[0].second= 1;
+  tm[0].second_part= 0; tm[0].neg= 0;
+
+  /* Cause a statement reprepare */
+  rc= mysql_query(mysql, "alter table t1 add column c int");
+  myquery(rc);
+
+  rc= mysql_stmt_execute(stmt);
+  check_execute(stmt, rc);
+  /*
+    Sic: only one warning, instead of two. The warning
+    about data truncation when assigning a parameter is lost.
+    This is a bug.
+  */
+  my_process_warnings(mysql, 1);
+
+  verify_col_data("t1", "year", "0000-00-00 00:00:00");
+
+  mysql_stmt_close(stmt);
+
+  rc= mysql_query(mysql, "drop table t1");
+  myquery(rc);
+}
+
+
+/**
+  Test that long data parameters, as well as parameters
+  that were originally in a different character set, are
+  preserved in case of reprepare.
+*/
+
+static void test_wl4166_4()
+{
+  MYSQL_STMT *stmt;
+  int rc;
+  const char *stmt_text;
+  MYSQL_BIND bind_array[2];
+
+  /* Represented as numbers to keep UTF8 tools from clobbering them. */
+  const char *koi8= "\xee\xd5\x2c\x20\xda\xc1\x20\xd2\xd9\xc2\xc1\xcc\xcb\xd5";
+  const char *cp1251= "\xcd\xf3\x2c\x20\xe7\xe0\x20\xf0\xfb\xe1\xe0\xeb\xea\xf3";
+  char buf1[16], buf2[16];
+  ulong buf1_len, buf2_len;
+
+  myheader("test_wl4166_4");
+
+  rc= mysql_query(mysql, "drop table if exists t1");
+  myquery(rc);
+
+  /*
+    Create table with binary columns, set session character set to cp1251,
+    client character set to koi8, and make sure that there is conversion
+    on insert and no conversion on select
+  */
+  rc= mysql_query(mysql,
+                  "create table t1 (c1 varbinary(255), c2 varbinary(255))");
+  myquery(rc);
+  rc= mysql_query(mysql, "set character_set_client=koi8r, "
+                         "character_set_connection=cp1251, "
+                         "character_set_results=koi8r");
+  myquery(rc);
+
+  bzero((char*) bind_array, sizeof(bind_array));
+
+  bind_array[0].buffer_type= MYSQL_TYPE_STRING;
+
+  bind_array[1].buffer_type= MYSQL_TYPE_STRING;
+  bind_array[1].buffer= (void *) koi8;
+  bind_array[1].buffer_length= strlen(koi8);
+
+  stmt= mysql_stmt_init(mysql);
+  check_stmt(stmt);
+
+  stmt_text= "insert into t1 (c1, c2) values (?, ?)";
+
+  rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
+  check_execute(stmt, rc);
+
+  mysql_stmt_bind_param(stmt, bind_array);
+
+  mysql_stmt_send_long_data(stmt, 0, koi8, strlen(koi8));
+
+  /* Cause a reprepare at statement execute */
+  rc= mysql_query(mysql, "alter table t1 add column d int");
+  myquery(rc);
+
+  rc= mysql_stmt_execute(stmt);
+  check_execute(stmt, rc);
+
+  stmt_text= "select c1, c2 from t1";
+
+  /* c1 and c2 are binary so no conversion will be done on select */
+  rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
+  check_execute(stmt, rc);
+
+  rc= mysql_stmt_execute(stmt);
+  check_execute(stmt, rc);
+
+  bind_array[0].buffer= buf1;
+  bind_array[0].buffer_length= sizeof(buf1);
+  bind_array[0].length= &buf1_len;
+
+  bind_array[1].buffer= buf2;
+  bind_array[1].buffer_length= sizeof(buf2);
+  bind_array[1].length= &buf2_len;
+
+  mysql_stmt_bind_result(stmt, bind_array);
+
+  rc= mysql_stmt_fetch(stmt);
+  check_execute(stmt, rc);
+
+  DIE_UNLESS(buf1_len == strlen(cp1251));
+  DIE_UNLESS(buf2_len == strlen(cp1251));
+  DIE_UNLESS(!memcmp(buf1, cp1251, buf1_len));
+  DIE_UNLESS(!memcmp(buf2, cp1251, buf1_len));
+
+  rc= mysql_stmt_fetch(stmt);
+  DIE_UNLESS(rc == MYSQL_NO_DATA);
+
+  mysql_stmt_close(stmt);
+
+  rc= mysql_query(mysql, "drop table t1");
+  myquery(rc);
+  rc= mysql_query(mysql, "set names default");
+  myquery(rc);
+}
+
 /**
   Bug#36004 mysql_stmt_prepare resets the list of warnings
 */
@@ -17951,6 +18135,8 @@
   { "test_bug28386", test_bug28386 },
   { "test_wl4166_1", test_wl4166_1 },
   { "test_wl4166_2", test_wl4166_2 },
+  { "test_wl4166_3", test_wl4166_3 },
+  { "test_wl4166_4", test_wl4166_4 },
   { "test_bug36004", test_bug36004 },
   { 0, 0 }
 };