Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
create table t1 as select now(); |
show create table t1; |
returns
+-------+----------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`now()` datetime NOT NULL
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
+-------+----------------------------------------------------------------------------------------+
|
should be timestamp.
These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()
Note, the function LOCALTIMESTAMP() according to the SQL Standard returns TIMESTAMP WITHOUT TIME ZONE, therefore we'll keep its return type as DATETIME.
This change is (among other things) expected to fix the following problem:
SET time_zone='Europe/Moscow'; |
CREATE OR REPLACE TABLE t1 (a TIMESTAMP); |
|
|
SET timestamp=1288477526 /*summer time in Moscow*/; |
INSERT INTO t1 VALUES (CURRENT_TIMESTAMP()), (COALESCE(CURRENT_TIMESTAMP())); |
|
|
SET timestamp=1288477526+3599 /*winter time in Moscow*/; |
INSERT INTO t1 VALUES (CURRENT_TIMESTAMP()), (COALESCE(CURRENT_TIMESTAMP())); |
|
|
SELECT a, UNIX_TIMESTAMP(a) FROM t1; |
DROP TABLE t1; |
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 |
|
| 2010-10-31 02:25:26 | 1288477526 |
|
| 2010-10-31 02:25:25 | 1288481125 | <-- this value is good
|
| 2010-10-31 02:25:25 | 1288477525 | <-- this value is wrong
|
+---------------------+-------------------+
|
Notice, on the second INSERT statement (soon after DST fallback), CURRENT_TIMESTAMP() and COALESCE(CURRENT_TIMESTAMP()) inserted different values because:
- Insertion of CURRENT_TIMESTAMP() works in TIMESTAMP format due to a hack in Item_func_now_local::save_in_field
- Insertion of COALESCE(CURRENT_TIMESTAMP()) works in DATETIME format.
After the change COALESCE(CURRENT_TIMESTAMP()) is expected to insert the same value with CURRENT_TIMESTAMP().
Attachments
Issue Links
- blocks
-
MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetics
-
- Stalled
-
- causes
-
MDEV-34932 differrent result in 32-bit in MDEV-15751
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Stalled
-
-
-
- Closed
-