bzr push into mysql-5.0-bugteam branch (gshchepa:2701 to 2703) Bug#37894

[Gleb Shchepa]

 2703 Gleb Shchepa	2008-10-10
      Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
      
      Select with a "NULL NOT IN" condition containing complex
      subselect from the same table as in the outer select failed
      with an assertion.
      
      
      The failure was caused by a concatenation of circumstances:
      1) an inner select was optimized by make_join_statistics to use
         the QUICK_RANGE_SELECT access method (that implies an index
         scan of the table);
      2) a subselect was independent (constant) from the outer select;
      3) a condition was pushed down into inner select.
      
      During the evaluation of a constant IN expression an optimizer
      temporary changed the access method from index scan to table
      scan, but an engine handler was already initialized for index
      access by make_join_statistics. That caused an assertion.
      
      
      Unnecessary index initialization has been removed from
      the QUICK_RANGE_SELECT::init method (QUICK_RANGE_SELECT::reset
      reinvokes this initialization).
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/opt_range.cc

 2702 Gleb Shchepa	2008-10-10
      Bug #39283: Date returned as VARBINARY to client for queries
                  with COALESCE and JOIN
      
      The server returned to a client the VARBINARY column type
      instead of the DATE type for a result of the COALESCE,
      IFNULL, IF, CASE, GREATEST or LEAST functions if that result
      was filesorted in an anonymous temporary table during
      the query execution.
      
      For example:
        SELECT COALESCE(t1.date1, t2.date2) AS result
          FROM t1 JOIN t2 ON t1.id = t2.id ORDER BY result;
      
      
      To create a column of various date/time types in a
      temporary table the create_tmp_field_from_item() function
      uses the Item::tmp_table_field_from_field_type() method
      call. However, fields of the MYSQL_TYPE_NEWDATE type were
      missed there, and the VARBINARY columns were created
      by default.
      Necessary condition has been added.
modified:
  mysql-test/r/metadata.result
  mysql-test/t/metadata.test
  sql/sql_select.cc

 2701 Georgi Kodinov	2008-10-07 [merge]
      merged 5.0-5.1.29-rc -> 5.0-bugteam
modified:
  mysql-test/r/binlog.result
  mysql-test/r/ctype_cp932_binlog.result
  mysql-test/r/rpl_sp.result
  mysql-test/t/binlog.test
  sql/item.cc
  sql/sp_head.cc

=== modified file 'mysql-test/r/metadata.result'
--- a/mysql-test/r/metadata.result	2008-08-20 09:49:28 +0000
+++ b/mysql-test/r/metadata.result	2008-10-10 10:13:12 +0000
@@ -181,4 +181,21 @@ c1	c2
 3	3
 DROP VIEW v1,v2;
 DROP TABLE t1,t2;
+CREATE TABLE t1 (i INT, d DATE);
+INSERT INTO t1 VALUES (1, '2008-01-01'), (2, '2008-01-02'), (3, '2008-01-03');
+SELECT COALESCE(d, d), IFNULL(d, d), IF(i, d, d),
+CASE i WHEN i THEN d ELSE d END, GREATEST(d, d), LEAST(d, d)
+FROM t1 ORDER BY RAND();
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max
length	Is_null	Flags	Decimals	Charsetnr
[phone]-01	2008-01-01	2008-01-01	2008-01-01	2008-01-01
[phone]-02	2008-01-02	2008-01-02	2008-01-02	2008-01-02
[phone]-03	2008-01-03	2008-01-03	2008-01-03	2008-01-03
+COALESCE(d, d)	IFNULL(d, d)	IF(i, d, d)	CASE i WHEN i THEN d ELSE d END	GREATEST(d,
d)	LEAST(d, d)
+def				CASE i WHEN i THEN d ELSE d END	CASE i WHEN i THEN d ELSE d
END	10	10	10	Y	128	0	63
+def				COALESCE(d, d)	COALESCE(d, d)	10	10	10	Y	128	0	63
+def				GREATEST(d, d)	GREATEST(d, d)	10	10	10	Y	128	0	63
+def				IF(i, d, d)	IF(i, d, d)	10	10	10	Y	128	0	63
+def				IFNULL(d, d)	IFNULL(d, d)	10	10	10	Y	128	0	63
+def				LEAST(d, d)	LEAST(d, d)	10	10	10	Y	128	0	63
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-04-22 21:27:23 +0000
+++ b/mysql-test/r/subselect3.result	2008-10-10 10:27:58 +0000
@@ -779,4 +779,20 @@ SELECT 1 FROM t1 WHERE t1.a NOT IN (SELE
 1
 1
 DROP TABLE t1, t2;
+CREATE TABLE t1 (
+pk INT PRIMARY KEY,
+int_key INT,
+varchar_key VARCHAR(5) UNIQUE,
+varchar_nokey VARCHAR(5)
+);
+INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p');
+SELECT varchar_nokey
+FROM t1
+WHERE NULL NOT IN (
+SELECT INNR.pk FROM t1 AS INNR2
+LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key )
+WHERE INNR.varchar_key > 'n{'
+);
+varchar_nokey
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/metadata.test'
--- a/mysql-test/t/metadata.test	2007-06-20 09:21:48 +0000
+++ b/mysql-test/t/metadata.test	2008-10-10 10:13:12 +0000
@@ -112,4 +112,21 @@ SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c
 DROP VIEW v1,v2;
 DROP TABLE t1,t2;
 
+#
+# Bug #39283: Date returned as VARBINARY to client for queries
+#             with COALESCE and JOIN
+#
+
+CREATE TABLE t1 (i INT, d DATE);
+INSERT INTO t1 VALUES (1, '2008-01-01'), (2, '2008-01-02'), (3, '2008-01-03');
+
+--enable_metadata
+--sorted_result
+SELECT COALESCE(d, d), IFNULL(d, d), IF(i, d, d),
+       CASE i WHEN i THEN d ELSE d END, GREATEST(d, d), LEAST(d, d)
+  FROM t1 ORDER BY RAND(); # force filesort
+--disable_metadata
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-04-22 21:27:23 +0000
+++ b/mysql-test/t/subselect3.test	2008-10-10 10:27:58 +0000
@@ -618,4 +618,26 @@ SELECT 1 FROM t1 WHERE t1.a NOT IN (SELE
 
 DROP TABLE t1, t2;
 
+#
+# Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
+#
+
+CREATE TABLE t1 (
+  pk INT PRIMARY KEY,
+  int_key INT,
+  varchar_key VARCHAR(5) UNIQUE,
+  varchar_nokey VARCHAR(5)
+);
+INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p');
+
+SELECT varchar_nokey
+FROM t1
+WHERE NULL NOT IN (
+ SELECT INNR.pk FROM t1 AS INNR2
+   LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key )
+   WHERE INNR.varchar_key > 'n{'
+);
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2008-10-01 15:50:55 +0000
+++ b/sql/opt_range.cc	2008-10-10 10:27:58 +0000
@@ -1015,7 +1015,7 @@ int QUICK_RANGE_SELECT::init()
 
   if (file->inited != handler::NONE)
     file->ha_index_or_rnd_end();
-  DBUG_RETURN(error= file->ha_index_init(index));
+  DBUG_RETURN(FALSE);
 }
 
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-08-27 13:03:17 +0000
+++ b/sql/sql_select.cc	2008-10-10 10:13:12 +0000
@@ -8964,6 +8964,7 @@ static Field *create_tmp_field_from_item
     */
     if ((type= item->field_type()) == MYSQL_TYPE_DATETIME ||
         type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE ||
+        type == MYSQL_TYPE_NEWDATE ||
         type == MYSQL_TYPE_TIMESTAMP || type == MYSQL_TYPE_GEOMETRY)
       new_field= item->tmp_table_field_from_field_type(table);
     /*