From 215b0a95e165db504bd8275dd0fb7ebd9b8ec91e Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Fri, 2 Feb 2007 15:22:10 -0800
Subject: Fix bug #24035. This performance degradation for UPDATEs could be
 observed in the update statements for which the search key cannot be
 converted to any valid value of the type of the search column, like for a 
 the condition int_fld=99999999999999999999999999, though it can be guaranteed
 here that there is no row with such a key value.

---
 mysql-test/r/update.result | 57 ++++++++++++++++++++++++++++++++++++++++++++++
 mysql-test/t/update.test   | 35 ++++++++++++++++++++++++++++
 sql/opt_range.cc           | 17 +++++++++++++-
 3 files changed, 108 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 4b7dbb3dbe9..9e2bc52657a 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -377,3 +377,60 @@ create table t1(f1 int, `*f2` int);
 insert into t1 values (1,1);
 update t1 set `*f2`=1;
 drop table t1;
+CREATE TABLE t1 (
+request_id int unsigned NOT NULL auto_increment,
+user_id varchar(12) default NULL,
+time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
+ip_address varchar(15) default NULL,
+PRIMARY KEY (request_id),
+KEY user_id_2 (user_id,time_stamp)
+);
+INSERT INTO t1 (user_id) VALUES ('user1');
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+flush status;
+SELECT user_id FROM t1 WHERE request_id=9999999999999;
+user_id
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	0
+SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
+user_id
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	2
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	7
+UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	3
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	14
+UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	3
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	21
+DROP TABLE t1;
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 5a49de248b1..3ce7ef72670 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -307,3 +307,38 @@ insert into t1 values (1,1);
 update t1 set `*f2`=1;
 drop table t1;
 # End of 4.1 tests
+
+#
+# Bug #24035: performance degradation with condition int_field=big_decimal
+#
+
+CREATE TABLE t1 (
+  request_id int unsigned NOT NULL auto_increment,
+  user_id varchar(12) default NULL,
+  time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
+  ip_address varchar(15) default NULL,
+  PRIMARY KEY (request_id),
+  KEY user_id_2 (user_id,time_stamp)
+);
+
+INSERT INTO t1 (user_id) VALUES ('user1');
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+
+flush status;
+SELECT user_id FROM t1 WHERE request_id=9999999999999; 
+show status like '%Handler_read%';
+SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999; 
+show status like '%Handler_read%';
+UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
+show status like '%Handler_read%';
+UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
+show status like '%Handler_read%';
+
+DROP TABLE t1;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index f0af4b7db2a..744d222b833 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -4296,7 +4296,22 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part,
   err= value->save_in_field_no_warnings(field, 1);
   if (err > 0 && field->cmp_type() != value->result_type())
   {
-    tree= 0;
+    if ((type == Item_func::EQ_FUNC || type == Item_func::EQUAL_FUNC) &&
+	value->result_type() == item_cmp_type(field->result_type(),
+                                              value->result_type()))
+
+    {
+      tree= new (alloc) SEL_ARG(field, 0, 0);
+      tree->type= SEL_ARG::IMPOSSIBLE;
+    }
+    else
+    {
+      /*
+        TODO: We should return trees of the type SEL_ARG::IMPOSSIBLE
+        for the cases like int_field > 999999999999999999999999 as well.
+      */
+      tree= 0;
+    }
     goto end;
   } 
   if (err < 0)
-- 
cgit v1.2.1


From 1dd93dda089981d2c41615dc86364f4680975c04 Mon Sep 17 00:00:00 2001
From: "jani@a88-113-38-195.elisa-laajakaista.fi" <>
Date: Mon, 5 Feb 2007 17:09:44 +0200
Subject: Test and bug fixes and removed unneccessary files.

---
 BitKeeper/etc/gone | 6 ++++++
 mysys/my_getopt.c  | 1 +
 sql/sql_prepare.cc | 3 ++-
 3 files changed, 9 insertions(+), 1 deletion(-)

diff --git a/BitKeeper/etc/gone b/BitKeeper/etc/gone
index 2d5522899d2..150abd46f9a 100644
--- a/BitKeeper/etc/gone
+++ b/BitKeeper/etc/gone
@@ -455,6 +455,12 @@ arjen@co3064164-a.bitbike.com|Docs/section.Comparisons.texi|20011108043647|22614
 arjen@fred.bitbike.com|scripts/mysql_fix_extensions.sh|20020516001337|12363|f1048a78f4759b4d
 ccarkner@nslinuxw10.bedford.progress.com|mysql-test/r/isolation.result|20010327145543|25059|4da11e109a3d93a9
 ccarkner@nslinuxw10.bedford.progress.com|mysql-test/t/isolation.test|20010327145543|39049|6a39e4138dd4a456
+jani@a88-113-38-195.elisa-laajakaista.fi|BUILD/SETUP.sh.rej|20070122013357|30052|b0650da46e7c4e54
+jani@a88-113-38-195.elisa-laajakaista.fi|configure.in.rej|20070122013357|43533|4b7ec608b9c90e83
+jani@a88-113-38-195.elisa-laajakaista.fi|include/my_global.h.rej|20070122013357|29911|dc7f1642f6061af
+jani@a88-113-38-195.elisa-laajakaista.fi|include/my_pthread.h.rej|20070122013357|18348|a6b632d992e5df16
+jani@a88-113-38-195.elisa-laajakaista.fi|mysys/thr_alarm.c.rej|20070122013357|21935|c169568388079966
+jani@a88-113-38-195.elisa-laajakaista.fi|sql/mysqld.cc.rej|20070122013357|09337|84aad00c2111bc3
 jani@hynda.mysql.fi|client/mysqlcheck|20010419221207|26716|363e3278166d84ec
 jcole@tetra.bedford.progress.com|BitKeeper/etc/logging_ok|20001004201211|30554
 miguel@hegel.local|zlib/ChangeLog|20020319032513|28917|5d5425fc84737083
diff --git a/mysys/my_getopt.c b/mysys/my_getopt.c
index 3f87186ccc3..075956c7af9 100644
--- a/mysys/my_getopt.c
+++ b/mysys/my_getopt.c
@@ -70,6 +70,7 @@ static void default_reporter(enum loglevel level,
     fprintf(stderr, "%s", "Info: ");
   vfprintf(stderr, format, args);
   va_end(args);
+  fflush(stderr);
 }
 
 /* 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 75c6dacc4a7..feab4d08c32 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1781,8 +1781,9 @@ static void reset_stmt_params(Prepared_statement *stmt)
       packet_length  Query string length, including terminator character.
 */
 
-void mysql_stmt_execute(THD *thd, char *packet, uint packet_length)
+void mysql_stmt_execute(THD *thd, char *packet_arg, uint packet_length)
 {
+  uchar *packet= (uchar*)packet_arg; // GCC 4.0.1 workaround
   ulong stmt_id= uint4korr(packet);
   /*
     Query text for binary log, or empty string if the query is not put into
-- 
cgit v1.2.1


From 5092f7ab269c75273c791201eb0cc803a2f456ee Mon Sep 17 00:00:00 2001
From: "gkodinov/kgeorge@macbook.gmz" <>
Date: Tue, 6 Feb 2007 11:08:57 +0200
Subject: Bug #22344: InnoDB keys act strange on datetime vs timestamp
 comparison  Ignoring error codes from type conversion allows default (wrong)
 values to  go unnoticed in the formation of index search conditions.  Fixed
 by correctly checking for conversion errors.

---
 mysql-test/r/select.result | 28 ++++++++++++++++++++++++++++
 mysql-test/t/select.test   | 23 +++++++++++++++++++++++
 sql/field.h                |  4 ++--
 sql/field_conv.cc          | 15 +++++++--------
 sql/item.cc                | 11 ++++++-----
 5 files changed, 66 insertions(+), 15 deletions(-)

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index f3938fd6413..96af58789ec 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3728,3 +3728,31 @@ WHERE ID_better=1 AND ID1_with_null IS NULL AND
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
 DROP TABLE t1;
+CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
+INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t1	range	ts	ts	4	NULL	1	Using where
+Warnings:
+Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+a	ts	a	dt1	dt2
+30	2006-01-03 23:00:00	30	2006-01-01 00:00:00	2999-12-31 00:00:00
+Warnings:
+Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 19c7d742f5b..dfa3c1ac2a4 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3207,3 +3207,26 @@ EXPLAIN SELECT * FROM t1
         (ID2_with_null=1 OR ID2_with_null=2);
 
 DROP TABLE t1;
+
+#
+# Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
+#
+CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
+ANALYZE TABLE t1;
+
+CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
+INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
+ANALYZE TABLE t2;
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+  AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+  AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+  AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+  AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+
+DROP TABLE t1,t2;
diff --git a/sql/field.h b/sql/field.h
index 565342637ba..562991f681e 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -29,7 +29,7 @@
 class Send_field;
 class Protocol;
 struct st_cache_field;
-void field_conv(Field *to,Field *from);
+int field_conv(Field *to,Field *from);
 
 inline uint get_enum_pack_length(int elements)
 {
@@ -1242,7 +1242,7 @@ public:
   uint max_packed_col_length(uint max_length);
   void free() { value.free(); }
   inline void clear_temporary() { bzero((char*) &value,sizeof(value)); }
-  friend void field_conv(Field *to,Field *from);
+  friend int field_conv(Field *to,Field *from);
   uint size_of() const { return sizeof(*this); }
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index e5752708123..dbe58d804ad 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -679,7 +679,7 @@ void (*Copy_field::get_copy_func(Field *to,Field *from))(Copy_field*)
 
 /* Simple quick field convert that is called on insert */
 
-void field_conv(Field *to,Field *from)
+int field_conv(Field *to,Field *from)
 {
   if (to->real_type() == from->real_type() &&
       !(to->type() == FIELD_TYPE_BLOB && to->table->copy_blobs))
@@ -707,7 +707,7 @@ void field_conv(Field *to,Field *from)
       if (to->ptr != from->ptr)
 #endif
         memcpy(to->ptr,from->ptr,to->pack_length());
-      return;
+      return 0;
     }
   }
   if (to->type() == FIELD_TYPE_BLOB)
@@ -723,8 +723,7 @@ void field_conv(Field *to,Field *from)
          from->real_type() != MYSQL_TYPE_STRING &&
          from->real_type() != MYSQL_TYPE_VARCHAR))
       blob->value.copy();
-    blob->store(blob->value.ptr(),blob->value.length(),from->charset());
-    return;
+    return blob->store(blob->value.ptr(),blob->value.length(),from->charset());
   }
   if ((from->result_type() == STRING_RESULT &&
        (to->result_type() == STRING_RESULT ||
@@ -741,15 +740,15 @@ void field_conv(Field *to,Field *from)
       end with \0. Can be replaced with .ptr() when we have our own
       string->double conversion.
     */
-    to->store(result.c_ptr_quick(),result.length(),from->charset());
+    return to->store(result.c_ptr_quick(),result.length(),from->charset());
   }
   else if (from->result_type() == REAL_RESULT)
-    to->store(from->val_real());
+    return to->store(from->val_real());
   else if (from->result_type() == DECIMAL_RESULT)
   {
     my_decimal buff;
-    to->store_decimal(from->val_decimal(&buff));
+    return to->store_decimal(from->val_decimal(&buff));
   }
   else
-    to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG));
+    return to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG));
 }
diff --git a/sql/item.cc b/sql/item.cc
index 9a55eb25e2c..6d0297f909e 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4245,18 +4245,19 @@ void Item_field::save_org_in_field(Field *to)
 
 int Item_field::save_in_field(Field *to, bool no_conversions)
 {
+  int res;
   if (result_field->is_null())
   {
     null_value=1;
-    return set_field_to_null_with_conversions(to, no_conversions);
+    res= set_field_to_null_with_conversions(to, no_conversions);
   }
   else
   {
     to->set_notnull();
-    field_conv(to,result_field);
+    res= field_conv(to,result_field);
     null_value=0;
   }
-  return 0;
+  return res;
 }
 
 
@@ -5284,9 +5285,9 @@ int Item_ref::save_in_field(Field *to, bool no_conversions)
       return set_field_to_null_with_conversions(to, no_conversions);
     }
     to->set_notnull();
-    field_conv(to, result_field);
+    res= field_conv(to, result_field);
     null_value= 0;
-    return 0;
+    return res;
   }
   res= (*ref)->save_in_field(to, no_conversions);
   null_value= (*ref)->null_value;
-- 
cgit v1.2.1


From 968369906efe7fa628caeb0e15731770161e7e7c Mon Sep 17 00:00:00 2001
From: "evgen@moonbone.local" <>
Date: Wed, 7 Feb 2007 00:46:03 +0300
Subject: Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records
 were updated.

INSERT ... ON DUPLICATE KEY UPDATE reports that a record was updated when
the duplicate key occurs even if the record wasn't actually changed
because the update values are the same as those in the record.

Now the compare_record() function is used to check whether the record was
changed and the update of a record reported only if the record differs
from the original one.
---
 mysql-test/r/insert_select.result | 12 ++++++++++++
 mysql-test/t/insert_select.test   | 13 +++++++++++++
 sql/mysql_priv.h                  |  1 +
 sql/sql_insert.cc                 | 26 ++++++++++++++++----------
 sql/sql_update.cc                 |  2 +-
 5 files changed, 43 insertions(+), 11 deletions(-)

diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 1453d25ac75..82cc1b036a7 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -705,3 +705,15 @@ use bug21774_1;
 INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;
 DROP DATABASE bug21774_1;
 DROP DATABASE bug21774_2;
+USE test;
+create table t1(f1 int primary key, f2 int);
+insert into t1 values (1,1);
+affected rows: 1
+insert into t1 values (1,1) on duplicate key update f2=1;
+affected rows: 0
+insert into t1 values (1,1) on duplicate key update f2=2;
+affected rows: 2
+select * from t1;
+f1	f2
+1	2
+drop table t1;
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 404d67390ab..6302d5f1dae 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -265,4 +265,17 @@ INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;
 
 DROP DATABASE bug21774_1;
 DROP DATABASE bug21774_2;
+USE test;
 
+#
+# Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were
+#            updated.
+#
+create table t1(f1 int primary key, f2 int);
+--enable_info
+insert into t1 values (1,1);
+insert into t1 values (1,1) on duplicate key update f2=1;
+insert into t1 values (1,1) on duplicate key update f2=2;
+--disable_info
+select * from t1;
+drop table t1;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index ce5df434295..1ffcad12414 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -701,6 +701,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
 		      char* packet, uint packet_length);
 void log_slow_statement(THD *thd);
 bool check_dup(const char *db, const char *name, TABLE_LIST *tables);
+bool compare_record(TABLE *table, query_id_t query_id);
 
 bool table_cache_init(void);
 void table_cache_free(void);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index fb59aeea8e7..1ea01b07166 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1186,23 +1186,29 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
           goto before_trg_err;
 
         if ((error=table->file->update_row(table->record[1],table->record[0])))
-	{
-	  if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore)
+        {
+          if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore)
           {
             table->file->restore_auto_increment();
             goto ok_or_after_trg_err;
           }
           goto err;
-	}
-        info->updated++;
+        }
+        if ((table->file->table_flags() & HA_PARTIAL_COLUMN_READ) ||
+            compare_record(table, query_id))
+        {
+          info->updated++;
 
-        if (table->next_number_field)
-          table->file->adjust_next_insert_id_after_explicit_value(table->next_number_field->val_int());
+          if (table->next_number_field)
+            table->file->adjust_next_insert_id_after_explicit_value(
+              table->next_number_field->val_int());
 
-        trg_error= (table->triggers &&
-                    table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
-                                                      TRG_ACTION_AFTER, TRUE));
-        info->copied++;
+          trg_error= (table->triggers &&
+                      table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+                                                        TRG_ACTION_AFTER,
+                                                        TRUE));
+          info->copied++;
+        }
         goto ok_or_after_trg_err;
       }
       else /* DUP_REPLACE */
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index b85c617b12d..04d341b5f67 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -26,7 +26,7 @@
 
 /* Return 0 if row hasn't changed */
 
-static bool compare_record(TABLE *table, query_id_t query_id)
+bool compare_record(TABLE *table, query_id_t query_id)
 {
   if (table->s->blob_fields + table->s->varchar_fields == 0)
     return cmp_record(table,record[1]);
-- 
cgit v1.2.1


From 5ed805bfd6edb0ab15725556db8d63495fa88b57 Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Wed, 7 Feb 2007 00:18:36 -0800
Subject: Fixed bug #26124: SELECT from a view wrapper over a table with a
 column of the DATETIME type could return a wrong result set if the WHERE
 clause included a BETWEEN condition on the column. Fixed the method
 Item_func_between::fix_length_and_dec where the aggregation type for BETWEEN
 predicates calculated incorrectly if the first argument was a view column of
 the DATETIME type.

---
 mysql-test/r/view.result | 18 ++++++++++++++++++
 mysql-test/t/view.test   | 16 ++++++++++++++++
 sql/item_cmpfunc.cc      |  4 ++--
 3 files changed, 36 insertions(+), 2 deletions(-)

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 7185a70689a..7720acd135a 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3034,4 +3034,22 @@ SHOW CREATE VIEW v1;
 View	Create View
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'The\ZEnd' AS `TheEnd`
 DROP VIEW v1;
+CREATE TABLE t1 (mydate DATETIME);
+INSERT INTO t1 VALUES 
+('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31');
+CREATE VIEW v1 AS SELECT mydate from t1;
+SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+mydate
+2007-01-01 00:00:00
+2007-01-02 00:00:00
+2007-01-30 00:00:00
+2007-01-31 00:00:00
+SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+mydate
+2007-01-01 00:00:00
+2007-01-02 00:00:00
+2007-01-30 00:00:00
+2007-01-31 00:00:00
+DROP VIEW v1;
+DROP TABLE t1;
 End of 5.0 tests.
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 47a5a54007b..bd0329ddf4f 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2986,4 +2986,20 @@ SHOW CREATE VIEW v1;
 
 DROP VIEW v1;
 
+#
+# Bug #26124: BETWEEN over a view column of the DATETIME type
+#
+
+CREATE TABLE t1 (mydate DATETIME);
+INSERT INTO t1 VALUES 
+  ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31');
+
+CREATE VIEW v1 AS SELECT mydate from t1;
+
+SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo End of 5.0 tests.
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index e3505d2b4a9..08f9c16384a 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1185,11 +1185,11 @@ void Item_func_between::fix_length_and_dec()
     They are compared as integers, so for const item this time-consuming
     conversion can be done only once, not for every single comparison
   */
-  if (args[0]->type() == FIELD_ITEM &&
+  if (args[0]->real_item()->type() == FIELD_ITEM &&
       thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
       thd->lex->sql_command != SQLCOM_SHOW_CREATE)
   {
-    Field *field=((Item_field*) args[0])->field;
+    Field *field=((Item_field*) (args[0]->real_item()))->field;
     if (field->can_be_compared_as_longlong())
     {
       /*
-- 
cgit v1.2.1


From 8d4027fd74eb1d40e8e6448c2c9f88bb371c525f Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Wed, 7 Feb 2007 14:41:57 -0800
Subject: Fixed bug #25931. View check option clauses were ignored for updates
 of multi-table views when the updates could not be performed on fly and the
 rows to update had to be put into temporary tables first.

---
 mysql-test/r/view.result | 28 +++++++++++++++++++++++++++-
 mysql-test/t/view.test   | 24 +++++++++++++++++++++++-
 sql/sql_update.cc        |  9 +++++++++
 3 files changed, 59 insertions(+), 2 deletions(-)

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 7720acd135a..a52882fef2f 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2543,7 +2543,7 @@ create table t1(f1 int, f2 int);
 create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb
 .f1 and ta.f2=tb.f2;
 insert into t1 values(1,1),(2,2);
-create view v2 as select * from v1 where a > 1 with check option;
+create view v2 as select * from v1 where a > 1 with local check option;
 select * from v2;
 a	b
 2	2
@@ -3052,4 +3052,30 @@ mydate
 2007-01-31 00:00:00
 DROP VIEW v1;
 DROP TABLE t1;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (1), (2);
+CREATE VIEW v1 AS
+SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION;
+SELECT * FROM v1;
+b
+1
+2
+UPDATE v1 SET b=3;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+SELECT * FROM v1;
+b
+1
+2
+SELECT * FROM t1;
+a
+1
+2
+SELECT * FROM t2;
+b
+1
+2
+DROP VIEW v1;
+DROP TABLE t1,t2;
 End of 5.0 tests.
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index bd0329ddf4f..0fa5765bb64 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2385,7 +2385,7 @@ create table t1(f1 int, f2 int);
 create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb
 .f1 and ta.f2=tb.f2;
 insert into t1 values(1,1),(2,2);
-create view v2 as select * from v1 where a > 1 with check option;
+create view v2 as select * from v1 where a > 1 with local check option;
 select * from v2;
 update v2 set b=3 where a=2;
 select * from v2;
@@ -3002,4 +3002,26 @@ SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
 DROP VIEW v1;
 DROP TABLE t1;
 
+#
+# Bug #25931: update of a multi-table view with check option
+#
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (1), (2);
+
+CREATE VIEW v1 AS
+  SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION;
+
+SELECT * FROM v1;
+--error 1369
+UPDATE v1 SET b=3;
+SELECT * FROM v1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests.
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 04d341b5f67..1db77f8704c 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1445,6 +1445,15 @@ int multi_update::do_updates(bool from_send_error)
 
       if (!can_compare_record || compare_record(table, thd->query_id))
       {
+        int error;
+        if ((error= cur_table->view_check_option(thd, ignore)) !=
+            VIEW_CHECK_OK)
+        {
+          if (error == VIEW_CHECK_SKIP)
+            continue;
+          else if (error == VIEW_CHECK_ERROR)
+            goto err;
+        }
 	if ((local_error=table->file->update_row(table->record[1],
 						 table->record[0])))
 	{
-- 
cgit v1.2.1


From 053f7a42e69bf229f6142600284d46227a7606f8 Mon Sep 17 00:00:00 2001
From: "gkodinov/kgeorge@macbook.gmz" <>
Date: Fri, 9 Feb 2007 11:05:23 +0200
Subject: Addendum to fix of bug #22344 : removed dead code.

---
 sql/item.cc | 13 +------------
 1 file changed, 1 insertion(+), 12 deletions(-)

diff --git a/sql/item.cc b/sql/item.cc
index 6d0297f909e..95001809e9a 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5277,18 +5277,7 @@ my_decimal *Item_ref::val_decimal(my_decimal *decimal_value)
 int Item_ref::save_in_field(Field *to, bool no_conversions)
 {
   int res;
-  if (result_field)
-  {
-    if (result_field->is_null())
-    {
-      null_value= 1;
-      return set_field_to_null_with_conversions(to, no_conversions);
-    }
-    to->set_notnull();
-    res= field_conv(to, result_field);
-    null_value= 0;
-    return res;
-  }
+  DBUG_ASSERT(!result_field);
   res= (*ref)->save_in_field(to, no_conversions);
   null_value= (*ref)->null_value;
   return res;
-- 
cgit v1.2.1


From 380c220545e950a53810bf5a3d553d11281370df Mon Sep 17 00:00:00 2001
From: "evgen@moonbone.local" <>
Date: Fri, 9 Feb 2007 22:25:09 +0300
Subject: Bug#23170: LAST_INSERT_ID isn't reset to 0 in INSERT .. SELECT when
 no rows were inserted.

The select_insert::send_eof() function now resets LAST_INSERT_ID variable if
no rows were inserted.
---
 mysql-test/r/insert_select.result | 14 ++++++++++++++
 mysql-test/t/insert_select.test   | 13 +++++++++++++
 sql/sql_insert.cc                 |  2 +-
 3 files changed, 28 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 82cc1b036a7..2e0acf303c2 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -717,3 +717,17 @@ select * from t1;
 f1	f2
 1	2
 drop table t1;
+create table t1(f1 int primary key auto_increment, f2 int unique);
+insert into t1(f2) values(1);
+select @@identity;
+@@identity
+1
+insert ignore t1(f2) values(1);
+select @@identity;
+@@identity
+0
+insert ignore t1(f2) select 1;
+select @@identity;
+@@identity
+0
+drop table t1;
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 6302d5f1dae..5c60fc8e1f0 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -279,3 +279,16 @@ insert into t1 values (1,1) on duplicate key update f2=2;
 --disable_info
 select * from t1;
 drop table t1;
+
+#
+# Bug#23170: LAST_INSERT_ID isn't reset to 0 in INSERT .. SELECT if no rows
+#            were inserted.
+#
+create table t1(f1 int primary key auto_increment, f2 int unique);
+insert into t1(f2) values(1);
+select @@identity;
+insert ignore t1(f2) values(1);
+select @@identity;
+insert ignore t1(f2) select 1;
+select @@identity;
+drop table t1;
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 1ea01b07166..39d7f8e9b58 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -2630,7 +2630,7 @@ bool select_insert::send_eof()
   }
 
   if (last_insert_id)
-    thd->insert_id(last_insert_id);		// For binary log
+    thd->insert_id(info.copied ? last_insert_id : 0);		// For binary log
   /* Write to binlog before commiting transaction */
   if (mysql_bin_log.is_open())
   {
-- 
cgit v1.2.1


From 951fe3caded6344b4398247f8d2173997a02c633 Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Fri, 9 Feb 2007 12:54:50 -0800
Subject: Fixed bug #26017. Objects of the class Item_equal contain an
 auxiliary member eval_item of the type cmp_item that is used only for direct
 evaluation of multiple equalities. Currently a multiple equality is evaluated
 directly only in the cases when the equality holds at most for one row in the
 result set. The compare collation of eval_item was determined incorectly. It
 could lead to returning incorrect results for some queries.

---
 mysql-test/r/join_outer.result | 20 ++++++++++++++++++++
 mysql-test/t/join_outer.test   | 23 +++++++++++++++++++++++
 sql/item_cmpfunc.cc            |  4 +---
 sql/item_cmpfunc.h             |  1 -
 4 files changed, 44 insertions(+), 4 deletions(-)

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 89bb26c4b3f..df66336bd81 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1194,3 +1194,23 @@ a	b
 3	3
 4	NULL
 DROP TABLE t1,t2;
+CREATE TABLE t1 (
+f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+f2 varchar(16) collate latin1_swedish_ci
+);
+CREATE TABLE t2 (
+f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+f3 varchar(16) collate latin1_swedish_ci
+);
+INSERT INTO t1 VALUES ('bla','blah');
+INSERT INTO t2 VALUES ('bla','sheep');
+SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
+f1	f2	f3
+bla	blah	sheep
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
+f1	f2	f3
+bla	blah	sheep
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
+f1	f2	f3
+bla	blah	sheep
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 20462f2ca3f..a0620e144c2 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -802,3 +802,26 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
 
 DROP TABLE t1,t2;
+
+#
+# Bug 26017: LEFT OUTER JOIN over two constant tables and 
+#            a case-insensitive comparison predicate field=const 
+# 
+
+CREATE TABLE t1 (
+  f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+  f2 varchar(16) collate latin1_swedish_ci
+);
+CREATE TABLE t2 (
+  f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
+  f3 varchar(16) collate latin1_swedish_ci
+);
+
+INSERT INTO t1 VALUES ('bla','blah');
+INSERT INTO t2 VALUES ('bla','sheep');
+
+SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
+SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
+
+DROP TABLE t1,t2;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 08f9c16384a..48e0fecc5f6 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4034,11 +4034,9 @@ longlong Item_equal::val_int()
 
 void Item_equal::fix_length_and_dec()
 {
-  Item *item= const_item ? const_item : get_first();
+  Item *item= get_first();
   eval_item= cmp_item::get_comparator(item->result_type(),
                                       item->collation.collation);
-  if (item->result_type() == STRING_RESULT)
-    eval_item->cmp_charset= cmp_collation.collation;
 }
 
 bool Item_equal::walk(Item_processor processor, byte *arg)
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index f18728c554b..4a06f487126 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1296,7 +1296,6 @@ class Item_equal: public Item_bool_func
   Item *const_item;        /* optional constant item equal to fields items */
   cmp_item *eval_item;
   bool cond_false;
-  DTCollation cmp_collation;
 public:
   inline Item_equal()
     : Item_bool_func(), const_item(0), eval_item(0), cond_false(0)
-- 
cgit v1.2.1


From a24144636295d4f2a1d95477872c56c46e59bbff Mon Sep 17 00:00:00 2001
From: "evgen@moonbone.local" <>
Date: Sat, 10 Feb 2007 00:00:07 +0300
Subject: Bug#12122: The MERGE algorithm isn't applicable if the ORDER BY
 clause is present.

A view created with CREATE VIEW ... ORDER BY ... cannot be resolved with
the MERGE algorithm, even when no other part of the CREATE VIEW statement
would require the view to be resolved using the TEMPTABLE algorithm.

The check for presence of the ORDER BY clause in the underlying select is
removed from the st_lex::can_be_merged() function.
The ORDER BY list of the underlying select is appended to the ORDER BY list
---
 mysql-test/r/view.result | 39 +++++++++++++++++++++++++++++++++++++++
 mysql-test/t/view.test   | 14 ++++++++++++++
 sql/sql_lex.cc           |  1 -
 3 files changed, 53 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index a52882fef2f..462916ec09b 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3078,4 +3078,43 @@ b
 2
 DROP VIEW v1;
 DROP TABLE t1,t2;
+create table t1(f1 int, f2 int);
+insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2);
+select * from t1;
+f1	f2
+1	2
+1	3
+1	1
+2	3
+2	1
+2	2
+create view v1 as select * from t1 order by f2;
+select * from v1;
+f1	f2
+1	1
+2	1
+1	2
+2	2
+1	3
+2	3
+explain extended select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f2`
+select * from v1 order by f1;
+f1	f2
+1	1
+1	2
+1	3
+2	1
+2	2
+2	3
+explain extended select * from v1 order by f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1`,`test`.`t1`.`f2`
+drop view v1;
+drop table t1;
 End of 5.0 tests.
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 0fa5765bb64..7fdca1ff7e0 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3024,4 +3024,18 @@ SELECT * FROM t2;
 DROP VIEW v1;
 DROP TABLE t1,t2;
 
+#
+# Bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm.
+#
+create table t1(f1 int, f2 int);
+insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2);
+select * from t1;
+create view v1 as select * from t1 order by f2;
+select * from v1;
+explain extended select * from v1;
+select * from v1 order by f1;
+explain extended select * from v1 order by f1;
+drop view v1;
+drop table t1;
+
 --echo End of 5.0 tests.
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index a362e6c7fd2..ce76c35b33c 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1751,7 +1751,6 @@ bool st_lex::can_be_merged()
   }
 
   return (selects_allow_merge &&
-	  select_lex.order_list.elements == 0 &&
 	  select_lex.group_list.elements == 0 &&
 	  select_lex.having == 0 &&
           select_lex.with_sum_func == 0 &&
-- 
cgit v1.2.1


From 976e747aafa0c038873a35b2068d27c54f06cd2b Mon Sep 17 00:00:00 2001
From: "igor@olga.mysql.com" <>
Date: Sat, 10 Feb 2007 23:55:56 -0800
Subject: Fixed bug #26159. A wrong order of statements in
 QUICK_GROUP_MIN_MAX_SELECT::reset caused a crash when a query with DISTINCT
 was executed by a loose scan for an InnoDB table that had been emptied.

---
 mysql-test/r/innodb_mysql.result | 27 +++++++++++++++++++++++++++
 mysql-test/t/innodb_mysql.test   | 26 ++++++++++++++++++++++++++
 sql/opt_range.cc                 |  9 ++++-----
 3 files changed, 57 insertions(+), 5 deletions(-)

diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index f150af4b6c2..298fc58ffcb 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -383,6 +383,33 @@ EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using filesort
 DROP TABLE t1;
+CREATE TABLE t1 (
+id int NOT NULL,
+name varchar(20) NOT NULL,
+dept varchar(20) NOT NULL,
+age tinyint(3) unsigned NOT NULL,
+PRIMARY KEY (id),
+INDEX (name,dept)
+) ENGINE=InnoDB;
+INSERT INTO t1(id, dept, age, name) VALUES
+(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
+(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
+(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
+(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	name	name	44	NULL	2	Using where; Using index for group-by
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+name	dept
+rs5	cs10
+rs5	cs9
+DELETE FROM t1;
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	name	name	44	NULL	2	Using where; Using index for group-by
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+name	dept
+DROP TABLE t1;
 show variables like 'innodb_rollback_on_timeout';
 Variable_name	Value
 innodb_rollback_on_timeout	OFF
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 45a2ede091b..c351891d205 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -384,6 +384,32 @@ EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
 DROP TABLE t1;
 
 
+#
+# Bug#26159: crash for a loose scan of a table that has been emptied 
+#
+
+CREATE TABLE t1 (
+  id int NOT NULL,
+  name varchar(20) NOT NULL,
+  dept varchar(20) NOT NULL,
+  age tinyint(3) unsigned NOT NULL,
+  PRIMARY KEY (id),
+  INDEX (name,dept)
+) ENGINE=InnoDB;
+INSERT INTO t1(id, dept, age, name) VALUES
+  (3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
+  (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
+  (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
+  (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
+
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+DELETE FROM t1;
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+
+DROP TABLE t1;
+
 --source include/innodb_rollback_on_timeout.inc
 
 --echo End of 5.0 tests
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 744d222b833..32cf6860d5c 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -8765,14 +8765,13 @@ int QUICK_GROUP_MIN_MAX_SELECT::reset(void)
   DBUG_ENTER("QUICK_GROUP_MIN_MAX_SELECT::reset");
 
   file->extra(HA_EXTRA_KEYREAD); /* We need only the key attributes */
-  result= file->ha_index_init(index);
-  result= file->index_last(record);
-  if (result == HA_ERR_END_OF_FILE)
-    DBUG_RETURN(0);
-  if (result)
+  if ((result= file->ha_index_init(index)))
     DBUG_RETURN(result);
   if (quick_prefix_select && quick_prefix_select->reset())
     DBUG_RETURN(1);
+  result= file->index_last(record);
+  if (result == HA_ERR_END_OF_FILE)
+    DBUG_RETURN(0);
   /* Save the prefix of the last group. */
   key_copy(last_prefix, record, index_info, group_prefix_len);
 
-- 
cgit v1.2.1


From ffdf7b159b8840f249b04cd998b3bf51e2a6f089 Mon Sep 17 00:00:00 2001
From: "evgen@moonbone.local" <>
Date: Sun, 11 Feb 2007 22:52:12 +0300
Subject: sql_view.cc:   Post fix for bug#12122. information_schema.result:  
 Corrected test case after fixing bug#12122.

---
 mysql-test/r/information_schema.result | 3 +--
 sql/sql_view.cc                        | 1 +
 2 files changed, 2 insertions(+), 2 deletions(-)

diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index b93a4c28849..3986dce2c29 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1262,8 +1262,7 @@ from information_schema.tables
 order by object_schema;
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
-2	DERIVED	tables	ALL	NULL	NULL	NULL	NULL	2	Using filesort
+1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	2	Using filesort
 explain select * from (select table_name from information_schema.tables) as a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 18ef3eaf29c..fd2d6c89785 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1263,6 +1263,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table,
 	but it will not be included to SELECT_LEX tree, because it
 	will not be executed
       */
+      table->select_lex->order_list.push_back(&lex->select_lex.order_list);
       goto ok;
     }
 
-- 
cgit v1.2.1


From 47e537b47c81eeda96633c4693a4befce7a43a9d Mon Sep 17 00:00:00 2001
From: "gluh@mysql.com/eagle.(none)" <>
Date: Mon, 12 Feb 2007 16:06:14 +0400
Subject: Bug#24630 Subselect query crashes mysqld The crash happens because
 second filling of the same I_S table happens in case of subselect with order
 by. table->sort.io_cache previously allocated in create_sort_index() is
 deleted during second filling (function get_schema_tables_result). There are
 two places where I_S table can be filled: JOIN::exec and create_sort_index().
 To fix the bug we should check if the table was already filled in one of
 these places and skip processing of the table in second.

---
 mysql-test/r/information_schema.result | 37 ++++++++++++++++++++++++++++++++++
 mysql-test/t/information_schema.test   | 24 ++++++++++++++++++++++
 sql/mysql_priv.h                       |  3 ++-
 sql/sql_select.cc                      |  4 ++--
 sql/sql_show.cc                        | 28 +++++++++++++++++--------
 sql/table.cc                           |  2 +-
 sql/table.h                            |  8 +++++++-
 7 files changed, 93 insertions(+), 13 deletions(-)

diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 3986dce2c29..436bb70d0e7 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1278,3 +1278,40 @@ table_name
 t1
 t2
 drop table t1,t2;
+select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=
+(select cast(table_name as char)  from information_schema.tables
+order by table_name limit 1) limit 1;
+f1
+1
+select t.table_name, group_concat(t.table_schema, '.', t.table_name),
+count(*) as num1
+from information_schema.tables t
+inner join information_schema.columns c1
+on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
+where t.table_schema = 'information_schema' and
+c1.ordinal_position =
+(select isnull(c2.column_type) -
+isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
+count(*) as num
+from information_schema.columns c2 where
+c2.table_schema='information_schema' and
+(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
+group by c2.column_type order by num limit 1)
+group by t.table_name order by num1, t.table_name;
+table_name	group_concat(t.table_schema, '.', t.table_name)	num1
+CHARACTER_SETS	information_schema.CHARACTER_SETS	1
+COLLATIONS	information_schema.COLLATIONS	1
+COLLATION_CHARACTER_SET_APPLICABILITY	information_schema.COLLATION_CHARACTER_SET_APPLICABILITY	1
+COLUMNS	information_schema.COLUMNS	1
+COLUMN_PRIVILEGES	information_schema.COLUMN_PRIVILEGES	1
+KEY_COLUMN_USAGE	information_schema.KEY_COLUMN_USAGE	1
+ROUTINES	information_schema.ROUTINES	1
+SCHEMATA	information_schema.SCHEMATA	1
+SCHEMA_PRIVILEGES	information_schema.SCHEMA_PRIVILEGES	1
+STATISTICS	information_schema.STATISTICS	1
+TABLES	information_schema.TABLES	1
+TABLE_CONSTRAINTS	information_schema.TABLE_CONSTRAINTS	1
+TABLE_PRIVILEGES	information_schema.TABLE_PRIVILEGES	1
+TRIGGERS	information_schema.TRIGGERS	1
+USER_PRIVILEGES	information_schema.USER_PRIVILEGES	1
+VIEWS	information_schema.VIEWS	1
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index d1dd485e21c..f8922317eb3 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -999,4 +999,28 @@ where table_schema = 'test' and table_name not in
  where table_schema = 'test' and column_name = 'f3');
 drop table t1,t2;
 
+
+#
+# Bug#24630  Subselect query crashes mysqld
+#
+select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=
+(select cast(table_name as char)  from information_schema.tables
+ order by table_name limit 1) limit 1;
+
+select t.table_name, group_concat(t.table_schema, '.', t.table_name),
+       count(*) as num1
+from information_schema.tables t
+inner join information_schema.columns c1
+on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
+where t.table_schema = 'information_schema' and
+        c1.ordinal_position =
+        (select isnull(c2.column_type) -
+         isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
+         count(*) as num
+         from information_schema.columns c2 where
+         c2.table_schema='information_schema' and
+         (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
+          group by c2.column_type order by num limit 1)
+group by t.table_name order by num1, t.table_name;
+
 # End of 5.0 tests.
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 1ffcad12414..1dd3c9dceca 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -932,7 +932,8 @@ int fill_schema_user_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
 int fill_schema_schema_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
 int fill_schema_table_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
 int fill_schema_column_privileges(THD *thd, TABLE_LIST *tables, COND *cond);
-bool get_schema_tables_result(JOIN *join);
+bool get_schema_tables_result(JOIN *join,
+                              enum enum_schema_table_state executed_place);
 #define is_schema_db(X) \
   !my_strcasecmp(system_charset_info, information_schema_name.str, (X))
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 05ee0d77c1f..63f2604a934 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1505,7 +1505,7 @@ JOIN::exec()
 
   if ((curr_join->select_lex->options & OPTION_SCHEMA_TABLE) &&
       !thd->lex->describe &&
-      get_schema_tables_result(curr_join))
+      get_schema_tables_result(curr_join, PROCESSED_BY_JOIN_EXEC))
   {
     DBUG_VOID_RETURN;
   }
@@ -12372,7 +12372,7 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
   /* Fill schema tables with data before filesort if it's necessary */
   if ((join->select_lex->options & OPTION_SCHEMA_TABLE) &&
       !thd->lex->describe &&
-      get_schema_tables_result(join))
+      get_schema_tables_result(join, PROCESSED_BY_CREATE_SORT_INDEX))
     goto err;
 
   if (table->s->tmp_table)
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 23059ac545a..659dd49d537 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3938,13 +3938,15 @@ int make_schema_select(THD *thd, SELECT_LEX *sel,
   SYNOPSIS
     get_schema_tables_result()
     join  join which use schema tables
+    executed_place place where I_S table processed
 
   RETURN
     FALSE success
     TRUE  error
 */
 
-bool get_schema_tables_result(JOIN *join)
+bool get_schema_tables_result(JOIN *join,
+                              enum enum_schema_table_state executed_place)
 {
   JOIN_TAB *tmp_join_tab= join->join_tab+join->tables;
   THD *thd= join->thd;
@@ -3964,14 +3966,24 @@ bool get_schema_tables_result(JOIN *join)
       bool is_subselect= (&lex->unit != lex->current_select->master_unit() &&
                           lex->current_select->master_unit()->item);
       /*
-        The schema table is already processed and 
-        the statement is not a subselect.
-        So we don't need to handle this table again.
+        If schema table is already processed and
+        the statement is not a subselect then
+        we don't need to fill this table again.
+        If schema table is already processed and
+        schema_table_state != executed_place then
+        table is already processed and
+        we should skip second data processing.
       */
-      if (table_list->is_schema_table_processed && !is_subselect)
+      if (table_list->schema_table_state &&
+          (!is_subselect || table_list->schema_table_state != executed_place))
         continue;
 
-      if (is_subselect) // is subselect
+      /*
+        if table is used in a subselect and
+        table has been processed earlier with the same
+        'executed_place' value then we should refresh the table.
+      */
+      if (table_list->schema_table_state && is_subselect)
       {
         table_list->table->file->extra(HA_EXTRA_NO_CACHE);
         table_list->table->file->extra(HA_EXTRA_RESET_STATE);
@@ -3988,10 +4000,10 @@ bool get_schema_tables_result(JOIN *join)
       {
         result= 1;
         join->error= 1;
-        table_list->is_schema_table_processed= TRUE;
+        table_list->schema_table_state= executed_place;
         break;
       }
-      table_list->is_schema_table_processed= TRUE;
+      table_list->schema_table_state= executed_place;
     }
   }
   thd->no_warnings_for_error= 0;
diff --git a/sql/table.cc b/sql/table.cc
index 4f1477355b1..acfaef3d030 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3031,7 +3031,7 @@ void st_table_list::reinit_before_use(THD *thd)
   */
   table= 0;
   /* Reset is_schema_table_processed value(needed for I_S tables */
-  is_schema_table_processed= FALSE;
+  schema_table_state= NOT_PROCESSED;
 
   TABLE_LIST *embedded; /* The table at the current level of nesting. */
   TABLE_LIST *embedding= this; /* The parent nested table reference. */
diff --git a/sql/table.h b/sql/table.h
index 70e64439af5..8f013f3de19 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -287,6 +287,12 @@ struct st_table {
   void reset_item_list(List<Item> *item_list) const;
 };
 
+enum enum_schema_table_state
+{ 
+  NOT_PROCESSED= 0,
+  PROCESSED_BY_CREATE_SORT_INDEX,
+  PROCESSED_BY_JOIN_EXEC
+};
 
 typedef struct st_foreign_key_info
 {
@@ -529,7 +535,6 @@ typedef struct st_table_list
   st_select_lex_unit *derived;		/* SELECT_LEX_UNIT of derived table */
   ST_SCHEMA_TABLE *schema_table;        /* Information_schema table */
   st_select_lex	*schema_select_lex;
-  bool is_schema_table_processed;
   /*
     True when the view field translation table is used to convert
     schema table fields for backwards compatibility with SHOW command.
@@ -638,6 +643,7 @@ typedef struct st_table_list
   */
   bool          prelocking_placeholder;
 
+  enum enum_schema_table_state schema_table_state;
   void calc_md5(char *buffer);
   void set_underlying_merge();
   int view_check_option(THD *thd, bool ignore_failure);
-- 
cgit v1.2.1


From 35a84c0aa6290e44f77282ebee17c586e87baa9d Mon Sep 17 00:00:00 2001
From: "gkodinov/kgeorge@macbook.gmz" <>
Date: Mon, 12 Feb 2007 16:02:53 +0200
Subject: Fixed MacOSX/Intel linking problem  Common symbols with and without
 initialization  cause the apple linker to exclude then from the  list of
 global symbols.

---
 mysys/my_pthread.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/mysys/my_pthread.c b/mysys/my_pthread.c
index b9da9d4817a..0422e0c6512 100644
--- a/mysys/my_pthread.c
+++ b/mysys/my_pthread.c
@@ -29,7 +29,7 @@
 #define SCHED_POLICY SCHED_OTHER
 #endif
 
-uint thd_lib_detected;
+uint thd_lib_detected= 0;
 
 #ifndef my_pthread_setprio
 void my_pthread_setprio(pthread_t thread_id,int prior)
-- 
cgit v1.2.1


From 6caff6f7085d3c2121e0b917920523cec030b6b3 Mon Sep 17 00:00:00 2001
From: "gluh@mysql.com/eagle.(none)" <>
Date: Mon, 12 Feb 2007 19:28:49 +0400
Subject: valgrind error fix

---
 sql/opt_range.cc | 19 ++++++++++++-------
 1 file changed, 12 insertions(+), 7 deletions(-)

diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 32cf6860d5c..4838e690dd7 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -8820,7 +8820,7 @@ int QUICK_GROUP_MIN_MAX_SELECT::get_next()
 #else
   int result;
 #endif
-  int is_last_prefix;
+  int is_last_prefix= 0;
 
   DBUG_ENTER("QUICK_GROUP_MIN_MAX_SELECT::get_next");
 
@@ -8835,13 +8835,18 @@ int QUICK_GROUP_MIN_MAX_SELECT::get_next()
       Check if this is the last group prefix. Notice that at this point
       this->record contains the current prefix in record format.
     */
-    is_last_prefix= key_cmp(index_info->key_part, last_prefix,
-                            group_prefix_len);
-    DBUG_ASSERT(is_last_prefix <= 0);
-    if (result == HA_ERR_KEY_NOT_FOUND)
-      continue;
-    else if (result)
+    if (!result)
+    {
+      is_last_prefix= key_cmp(index_info->key_part, last_prefix,
+                              group_prefix_len);
+      DBUG_ASSERT(is_last_prefix <= 0);
+    }
+    else 
+    {
+      if (result == HA_ERR_KEY_NOT_FOUND)
+        continue;
       break;
+    }
 
     if (have_min)
     {
-- 
cgit v1.2.1