From: Alexander Barkov
Date: November 15 2011 6:53am
Subject: bzr push into mysql-trunk branch (alexander.barkov:3559 to 3560)
 Bug#13383838 Bug#13384069 WL#946
List-Archive: http://lists.mysql.com/commits/141948
X-Bug: 13383838,13384069
Message-Id: <201111150653.pAF6rSq7020672@bar.myoffice.izhnet.ru>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3560 Alexander Barkov	2011-11-15
      WL#946:
      Bug#13384069 - EMBEDED UPDATE WITH CAST AND DATETIME WITH FSP DOES NOT WORK
      Bug#13383838 - DELETE DOESN'T EMPTY RECORDS WHEN CAST WITH DATETIME TYPE IN EMBEDED SELECT

    modified:
      mysql-test/r/type_temporal_fractional.result
      mysql-test/t/type_temporal_fractional.test
      sql/item.cc
      sql/item_timefunc.cc
 3559 Alexander Barkov	2011-11-14
      WL#946: Bug#13386657 ASSERT FAILURE IN ITEM::GET_DATE_FROM_NON_TEMPORAL

    modified:
      mysql-test/r/type_temporal_fractional.result
      mysql-test/t/type_temporal_fractional.test
      sql/item_func.cc
=== modified file 'mysql-test/r/type_temporal_fractional.result'
--- a/mysql-test/r/type_temporal_fractional.result	2011-11-14 19:55:51 +0000
+++ b/mysql-test/r/type_temporal_fractional.result	2011-11-15 06:52:11 +0000
@@ -16377,3 +16377,100 @@ SELECT ROUND(COALESCE(a,a)) FROM t1;
 ROUND(COALESCE(a,a))
 20010101102031
 DROP TABLE t1;
+#
+# Bug#13384069 - EMBEDED UPDATE WITH CAST AND DATETIME WITH FSP DOES NOT WORK
+#
+CREATE TABLE t1 (
+pk timestamp(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00',
+col_datetime_6 datetime(6) DEFAULT NULL,
+PRIMARY KEY (pk));
+INSERT INTO t1 VALUES ('2011-11-11 17:59:41.12','0000-00-00 00:00:00.000000');
+SELECT col_datetime_6, CAST(col_datetime_6 AS DATETIME(2)) AS c1 FROM t1;
+col_datetime_6	c1
+0000-00-00 00:00:00.000000	0000-00-00 00:00:00.00
+CREATE TABLE t2 AS SELECT CAST(col_datetime_6 AS DATETIME(2)) AS c1 FROM t1;
+SELECT * FROM t2;
+c1
+0000-00-00 00:00:00.00
+UPDATE t2 SET c1=(SELECT CAST(col_datetime_6 AS DATETIME(2)) AS c1 FROM t1);
+SELECT * FROM t2;
+c1
+0000-00-00 00:00:00.00
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('0000-00-00');
+SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1;
+CAST(COALESCE(a,a) AS SIGNED)
+0
+SELECT CAST(COALESCE(a,a) AS CHAR) FROM t1;
+CAST(COALESCE(a,a) AS CHAR)
+0000-00-00
+SELECT CAST(COALESCE(a,a) AS DECIMAL(25,3)) FROM t1;
+CAST(COALESCE(a,a) AS DECIMAL(25,3))
+0.000
+SELECT CAST(COALESCE(a,a) AS DATETIME(6)) FROM t1;
+CAST(COALESCE(a,a) AS DATETIME(6))
+0000-00-00 00:00:00.000000
+SELECT CAST(COALESCE(a,a) AS TIME(6)) FROM t1;
+CAST(COALESCE(a,a) AS TIME(6))
+00:00:00.000000
+SELECT ROUND(COALESCE(a,a)) FROM t1;
+ROUND(COALESCE(a,a))
+0
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
+SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1;
+CAST(COALESCE(a,a) AS SIGNED)
+0
+SELECT CAST(COALESCE(a,a) AS CHAR) FROM t1;
+CAST(COALESCE(a,a) AS CHAR)
+0000-00-00 00:00:00
+SELECT CAST(COALESCE(a,a) AS DECIMAL(25,3)) FROM t1;
+CAST(COALESCE(a,a) AS DECIMAL(25,3))
+0.000
+SELECT CAST(COALESCE(a,a) AS DATETIME(6)) FROM t1;
+CAST(COALESCE(a,a) AS DATETIME(6))
+0000-00-00 00:00:00.000000
+SELECT CAST(COALESCE(a,a) AS TIME(6)) FROM t1;
+CAST(COALESCE(a,a) AS TIME(6))
+00:00:00.000000
+SELECT ROUND(COALESCE(a,a)) FROM t1;
+ROUND(COALESCE(a,a))
+0
+DROP TABLE t1;
+#
+# Bug#13383838 - DELETE DOESN'T EMPTY RECORDS WHEN CAST WITH DATETIME TYPE IN EMBEDED SELECT
+#
+CREATE TABLE t1 (
+pk timestamp(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00',
+col_datetime_6 datetime(6) DEFAULT NULL,
+PRIMARY KEY (pk));
+INSERT INTO t1 VALUES ('2011-11-11 17:59:41.12','0000-00-00 00:00:00.000000');
+CREATE TABLE t2 SELECT CAST(col_datetime_6 AS DATETIME) AS c1 FROM t1;
+UPDATE t2 SET c1=(SELECT CAST(col_datetime_6 AS DATETIME) AS c1 FROM t1)
+WHERE c1 IN (SELECT CAST(col_datetime_6 AS DATETIME) AS c1 FROM t1);
+SELECT * FROM t2 WHERE c1 IN  (SELECT CAST( col_datetime_6 AS DATETIME )  AS c1  FROM t1);
+c1
+0000-00-00 00:00:00
+DELETE FROM t2 WHERE c1 IN  (SELECT CAST( col_datetime_6 AS DATETIME )  AS c1  FROM t1);
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+0
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+pk timestamp(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00',
+col_date date DEFAULT NULL,
+PRIMARY KEY (pk));
+INSERT INTO t1 VALUES ('2011-11-11 17:59:41.12','0000-00-00');
+CREATE TABLE t2 SELECT CAST(col_date AS DATE) AS c1 FROM t1;
+UPDATE t2 SET c1=(SELECT CAST(col_date AS DATE) AS c1 FROM t1)
+WHERE c1 IN (SELECT CAST(col_date AS DATE) AS c1 FROM t1);
+SELECT * FROM t2 WHERE c1 IN (SELECT CAST(col_date AS DATE) AS c1 FROM t1);
+c1
+0000-00-00
+DELETE FROM t2 WHERE c1 IN (SELECT CAST(col_date AS DATE) AS c1  FROM t1);
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+0
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/type_temporal_fractional.test'
--- a/mysql-test/t/type_temporal_fractional.test	2011-11-14 19:55:51 +0000
+++ b/mysql-test/t/type_temporal_fractional.test	2011-11-15 06:52:11 +0000
@@ -7007,6 +7007,68 @@ SELECT CAST(COALESCE(a,a) AS TIME(6)) FR
 SELECT ROUND(COALESCE(a,a)) FROM t1;
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#13384069 - EMBEDED UPDATE WITH CAST AND DATETIME WITH FSP DOES NOT WORK
+--echo #
+CREATE TABLE t1 (
+  pk timestamp(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00',
+  col_datetime_6 datetime(6) DEFAULT NULL,
+  PRIMARY KEY (pk));
+INSERT INTO t1 VALUES ('2011-11-11 17:59:41.12','0000-00-00 00:00:00.000000');
+SELECT col_datetime_6, CAST(col_datetime_6 AS DATETIME(2)) AS c1 FROM t1;
+CREATE TABLE t2 AS SELECT CAST(col_datetime_6 AS DATETIME(2)) AS c1 FROM t1;
+SELECT * FROM t2;
+UPDATE t2 SET c1=(SELECT CAST(col_datetime_6 AS DATETIME(2)) AS c1 FROM t1);
+SELECT * FROM t2;
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('0000-00-00');
+SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1;
+SELECT CAST(COALESCE(a,a) AS CHAR) FROM t1;
+SELECT CAST(COALESCE(a,a) AS DECIMAL(25,3)) FROM t1;
+SELECT CAST(COALESCE(a,a) AS DATETIME(6)) FROM t1;
+SELECT CAST(COALESCE(a,a) AS TIME(6)) FROM t1;
+SELECT ROUND(COALESCE(a,a)) FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
+SELECT CAST(COALESCE(a,a) AS SIGNED) FROM t1;
+SELECT CAST(COALESCE(a,a) AS CHAR) FROM t1;
+SELECT CAST(COALESCE(a,a) AS DECIMAL(25,3)) FROM t1;
+SELECT CAST(COALESCE(a,a) AS DATETIME(6)) FROM t1;
+SELECT CAST(COALESCE(a,a) AS TIME(6)) FROM t1;
+SELECT ROUND(COALESCE(a,a)) FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#13383838 - DELETE DOESN'T EMPTY RECORDS WHEN CAST WITH DATETIME TYPE IN EMBEDED SELECT
+--echo #
+CREATE TABLE t1 (
+  pk timestamp(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00',
+  col_datetime_6 datetime(6) DEFAULT NULL,
+  PRIMARY KEY (pk));
+INSERT INTO t1 VALUES ('2011-11-11 17:59:41.12','0000-00-00 00:00:00.000000');
+CREATE TABLE t2 SELECT CAST(col_datetime_6 AS DATETIME) AS c1 FROM t1;
+UPDATE t2 SET c1=(SELECT CAST(col_datetime_6 AS DATETIME) AS c1 FROM t1)
+WHERE c1 IN (SELECT CAST(col_datetime_6 AS DATETIME) AS c1 FROM t1);
+SELECT * FROM t2 WHERE c1 IN  (SELECT CAST( col_datetime_6 AS DATETIME )  AS c1  FROM t1);
+DELETE FROM t2 WHERE c1 IN  (SELECT CAST( col_datetime_6 AS DATETIME )  AS c1  FROM t1);
+SELECT COUNT(*) FROM t2;
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+  pk timestamp(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00',
+  col_date date DEFAULT NULL,
+  PRIMARY KEY (pk));
+INSERT INTO t1 VALUES ('2011-11-11 17:59:41.12','0000-00-00');
+CREATE TABLE t2 SELECT CAST(col_date AS DATE) AS c1 FROM t1;
+UPDATE t2 SET c1=(SELECT CAST(col_date AS DATE) AS c1 FROM t1)
+WHERE c1 IN (SELECT CAST(col_date AS DATE) AS c1 FROM t1);
+SELECT * FROM t2 WHERE c1 IN (SELECT CAST(col_date AS DATE) AS c1 FROM t1);
+DELETE FROM t2 WHERE c1 IN (SELECT CAST(col_date AS DATE) AS c1  FROM t1);
+SELECT COUNT(*) FROM t2;
+DROP TABLE t1, t2;
+
 ## TS-TODO: SELECT CAST('00:00:00' AS DATETIME) -> should it use curdate?
 
 ## TS-TODO: reuse count_datetime_length()/count_string_result_length()

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2011-11-10 14:58:23 +0000
+++ b/sql/item.cc	2011-11-15 06:52:11 +0000
@@ -279,7 +279,7 @@ String *Item::val_string_from_datetime(S
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  if (get_date(&ltime, 0) ||
+  if (get_date(&ltime, TIME_FUZZY_DATE) ||
       (null_value= str->alloc(MAX_DATE_STRING_REP_LENGTH)))
     return (String *) 0;
   make_datetime((DATE_TIME_FORMAT *) 0, &ltime, str, decimals);
@@ -291,7 +291,7 @@ String *Item::val_string_from_date(Strin
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  if (get_date(&ltime, 0) ||
+  if (get_date(&ltime, TIME_FUZZY_DATE) ||
       (null_value= str->alloc(MAX_DATE_STRING_REP_LENGTH)))
     return (String *) 0;
   make_date((DATE_TIME_FORMAT *) 0, &ltime, str);
@@ -486,7 +486,7 @@ longlong Item::val_int_from_datetime()
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  return get_date(&ltime, 0) ?
+  return get_date(&ltime, TIME_FUZZY_DATE) ?
          0LL: (longlong) TIME_to_ulonglong_datetime_round(&ltime);
 }
 

=== modified file 'sql/item_timefunc.cc'
--- a/sql/item_timefunc.cc	2011-11-10 14:58:23 +0000
+++ b/sql/item_timefunc.cc	2011-11-15 06:52:11 +0000
@@ -926,7 +926,7 @@ longlong Item_date_func::val_date_tempor
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  return get_date(&ltime, 0) ?
+  return get_date(&ltime, TIME_FUZZY_DATE) ?
          0LL : TIME_to_longlong_date_packed(&ltime);
 }
 
@@ -935,7 +935,7 @@ longlong Item_datetime_func::val_date_te
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
-  return get_date(&ltime, 0) ?
+  return get_date(&ltime, TIME_FUZZY_DATE) ?
          0LL : TIME_to_longlong_datetime_packed(&ltime);
 }
 

No bundle (reason: useless for push emails).