[MDEV-15751] CURRENT_TIMESTAMP should return a TIMESTAMP (WITH TIME ZONE?) - Jira

Sergei Golubchik created issue - 2018-04-02 14:49

Sergei Golubchik made changes - 2018-04-02 14:49

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-15654~~ [ ~~MDEV-15654~~ ]

Alexander Barkov added a comment - 2019-03-29 08:35 - edited

According to the standard, it should return TIMESTAMP WITH TIMEZONE:

The declared type of a <current timestamp value function> is TIMESTAMP WITH TIME ZONE.

While, the best name for MariaDB's TIMESTAMP is "TIMESTAMP WITH LOCAL TIME ZONE".

So perhaps we should implement MDEV-10018 first.

Alexander Barkov added a comment - 2019-03-29 08:35 - edited According to the standard, it should return TIMESTAMP WITH TIMEZONE: The declared type of a <current timestamp value function> is TIMESTAMP WITH TIME ZONE. While, the best name for MariaDB's TIMESTAMP is " TIMESTAMP WITH LOCAL TIME ZONE ". So perhaps we should implement MDEV-10018 first.

Alexander Barkov made changes - 2019-03-29 08:36

Summary

CURRENT_TIMESTAMP should return a TIMESTAMP

CURRENT_TIMESTAMP should return a TIMESTAMP [WITH TIME ZONE?]

Alexander Barkov made changes - 2020-05-29 10:01

Fix Version/s		10.6 [ 24028 ]
Fix Version/s	10.4 [ 22408 ]

Alexander Barkov made changes - 2020-07-14 10:13

Link

This issue is blocked by MDEV-10018 [ MDEV-10018 ]

Sergei Golubchik made changes - 2020-08-16 18:55

Rank

Ranked higher

Ralf Gebhardt made changes - 2020-10-16 08:31

Fix Version/s

10.6 [ 24028 ]

Ralf Gebhardt made changes - 2021-05-11 08:19

Assignee

Alexander Barkov [ bar ]

Alexander Barkov made changes - 2021-05-11 11:20

Component/s

Data types [ 13906 ]

Sergei Golubchik made changes - 2021-12-06 21:21

Workflow

MariaDB v3 [ 86333 ]

MariaDB v4 [ 130812 ]

AirFocus made changes - 2022-08-09 16:11

Description

{code:sql}
create table t1 as select now();
show create table t1;
{code}
returns
{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}
should be timestamp

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp

Alexander Barkov made changes - 2023-02-10 08:14

Link

This issue relates to MDEV-15750 [ MDEV-15750 ]

Alexander Barkov made changes - 2023-02-14 14:06

Assignee

Alexander Barkov [ bar ]

Alexander Barkov made changes - 2023-02-14 14:07

Link

This issue blocks MDEV-15750 [ MDEV-15750 ]

Alexander Barkov made changes - 2023-02-14 14:08

Description

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

Alexander Barkov made changes - 2023-02-14 14:08

Fix Version/s

11.0 [ 28320 ]

Alexander Barkov made changes - 2023-02-14 14:08

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov added a comment - 2023-02-14 14:40

Hello serg, please review a patch for this task:
https://github.com/MariaDB/server/commit/fec3696fef6fb5a0be5f78632c056382f227e778

Alexander Barkov added a comment - 2023-02-14 14:40 Hello serg , please review a patch for this task: https://github.com/MariaDB/server/commit/fec3696fef6fb5a0be5f78632c056382f227e778

Alexander Barkov made changes - 2023-02-14 14:40

Status

In Progress [ 3 ]

In Review [ 10002 ]

Alexander Barkov made changes - 2023-02-14 14:40

Assignee

Alexander Barkov [ bar ]

Sergei Golubchik [ serg ]

Ralf Gebhardt made changes - 2023-07-13 20:10

Fix Version/s		11.3 [ 28565 ]
Fix Version/s	11.0 [ 28320 ]

Sergei Golubchik made changes - 2023-09-17 18:03

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	11.3 [ 28565 ]

Sergei Golubchik made changes - 2023-12-22 17:45

Fix Version/s		11.5 [ 29506 ]
Fix Version/s	11.4 [ 29301 ]

Sergei Golubchik made changes - 2024-03-19 18:32

Fix Version/s		11.6 [ 29515 ]
Fix Version/s	11.5 [ 29506 ]

Ralf Gebhardt made changes - 2024-05-29 14:59

Fix Version/s		11.7 [ 29815 ]
Fix Version/s	11.6 [ 29515 ]

Sergei Golubchik made changes - 2024-05-29 16:15

Link

This issue is blocked by MDEV-10018 [ MDEV-10018 ]

Sergei Golubchik made changes - 2024-08-22 15:34

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2024-08-25 13:30

Description

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is among other things is expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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 |
| 2010-10-31 02:25:25 | 1288477525 | <-- this value is wrong
+---------------------+-------------------+
{noformat}

Notice, on the second INSERT statement 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 NOW().

Alexander Barkov made changes - 2024-08-25 13:30

Description

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is among other things is expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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 |
| 2010-10-31 02:25:25 | 1288477525 | <-- this value is wrong
+---------------------+-------------------+
{noformat}

Notice, on the second INSERT statement 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 NOW().

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is (among other things) expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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 |
| 2010-10-31 02:25:25 | 1288477525 | <-- this value is wrong
+---------------------+-------------------+
{noformat}

Notice, on the second INSERT statement 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 NOW().

Alexander Barkov made changes - 2024-08-25 13:31

Description

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is (among other things) expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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 |
| 2010-10-31 02:25:25 | 1288477525 | <-- this value is wrong
+---------------------+-------------------+
{noformat}

Notice, on the second INSERT statement 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 NOW().

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is (among other things) expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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
+---------------------+-------------------+
{noformat}

Notice, on the second INSERT statement 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 NOW().

Alexander Barkov made changes - 2024-08-25 13:32

Description

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is (among other things) expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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
+---------------------+-------------------+
{noformat}

Notice, on the second INSERT statement 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 NOW().

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is (among other things) expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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
+---------------------+-------------------+
{noformat}

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 NOW().

Alexander Barkov made changes - 2024-08-25 13:32

Description

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is (among other things) expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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
+---------------------+-------------------+
{noformat}

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 NOW().

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is (among other things) expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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
+---------------------+-------------------+
{noformat}

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().

Alexander Barkov made changes - 2024-08-25 14:28

Description

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

This change is (among other things) expected to fix the following problem:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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
+---------------------+-------------------+
{noformat}

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().

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

Note, the function LOCALTIMESTAMP() according to the SQL Standard return 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:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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
+---------------------+-------------------+
{noformat}

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().

Alexander Barkov made changes - 2024-08-25 14:28

Description

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

should be timestamp.

These functions should also return TIMESTAMP:
- FROM_UNIXTIME()
- SYSDATE()

Note, the function LOCALTIMESTAMP() according to the SQL Standard return 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:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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
+---------------------+-------------------+
{noformat}

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().

{code:sql}
create table t1 as select now();
show create table t1;
{code}

returns

{noformat}
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`now()` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
{noformat}

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:
{code:sql}
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;
{code}
{noformat}
+---------------------+-------------------+
| 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
+---------------------+-------------------+
{noformat}

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().

Alexander Barkov made changes - 2024-08-28 11:08

Link

This issue relates to ~~MDEV-34829~~ [ ~~MDEV-34829~~ ]

Sergei Golubchik made changes - 2024-09-02 14:17

Assignee

Alexander Barkov [ bar ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2024-09-02 14:17

Status

Stalled [ 10000 ]

In Review [ 10002 ]

Sergei Golubchik made changes - 2024-09-03 13:23

Issue Type

Task [ 3 ]

New Feature [ 2 ]

Sergei Golubchik made changes - 2024-09-03 14:31

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik added a comment - 2024-09-03 16:51

b626f5398dc is ok

Sergei Golubchik added a comment - 2024-09-03 16:51 b626f5398dc is ok

Sergei Golubchik made changes - 2024-09-03 16:52

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2024-09-05 09:43

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Alexander Barkov made changes - 2024-09-05 09:43

Assignee

Alexander Barkov [ bar ]

Elena Stepanova [ elenst ]

Alexander Barkov added a comment - 2024-09-05 09:45

elenst, please find the patch here:

https://github.com/MariaDB/server/tree/bb-11.7-MDEV-15751-timestamp

Thanks.

Alexander Barkov added a comment - 2024-09-05 09:45 elenst , please find the patch here: https://github.com/MariaDB/server/tree/bb-11.7-MDEV-15751-timestamp Thanks.

Sergei Golubchik made changes - 2024-09-15 07:06

Link

This issue causes ~~MDEV-34932~~ [ ~~MDEV-34932~~ ]

Elena Stepanova made changes - 2024-09-16 20:43

Assignee

Elena Stepanova [ elenst ]

Ramesh Sivaraman [ JIRAUSER48189 ]

Sergei Golubchik made changes - 2024-09-16 21:30

Link

This issue is part of TODO-4907 [ TODO-4907 ]

Ralf Gebhardt made changes - 2024-09-17 08:51

Labels

Preview_11.7

Alexander Barkov added a comment - 2024-09-18 02:16

ramesh, I force-pushed the branch. There was a test failure on 32bit platforms.

Alexander Barkov added a comment - 2024-09-18 02:16 ramesh , I force-pushed the branch. There was a test failure on 32bit platforms.

Ramesh Sivaraman added a comment - 2024-09-18 03:28

bar ack, latest commit will be used for testing.

Ramesh Sivaraman added a comment - 2024-09-18 03:28 bar ack, latest commit will be used for testing.

Julien Fritsch made changes - 2024-09-18 21:22

Summary

CURRENT_TIMESTAMP should return a TIMESTAMP [WITH TIME ZONE?]

CURRENT_TIMESTAMP should return a TIMESTAMP (WITH TIME ZONE?)

Ramesh Sivaraman added a comment - 2024-10-15 07:10

okay to push

Ramesh Sivaraman added a comment - 2024-10-15 07:10 okay to push

Ramesh Sivaraman made changes - 2024-10-15 07:10

Assignee	Ramesh Sivaraman [ JIRAUSER48189 ]	Alexander Barkov [ bar ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Ralf Gebhardt made changes - 2024-10-15 09:32

Assignee

Alexander Barkov [ bar ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2024-10-19 22:20

Fix Version/s		11.7.1 [ 29913 ]
Fix Version/s	11.7 [ 29815 ]
Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Alexander Barkov made changes - 2024-10-25 10:35

Link

This issue causes ~~MDEV-35250~~ [ ~~MDEV-35250~~ ]

ItsOnlyBinary made changes - 2025-02-19 18:13

Link

This issue is duplicated by ~~MDEV-36126~~ [ ~~MDEV-36126~~ ]

Alexander Barkov made changes - 2025-02-20 11:05

Link

This issue relates to ~~MDEV-36126~~ [ ~~MDEV-36126~~ ]

Alexander Barkov made changes - 2025-02-20 11:06

Link

This issue is duplicated by ~~MDEV-36126~~ [ ~~MDEV-36126~~ ]

Sergei Golubchik made changes - 6 days ago

Link

This issue relates to MDEV-34037 [ MDEV-34037 ]

MariaDB Server

CURRENT_TIMESTAMP should return a TIMESTAMP (WITH TIME ZONE?)

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration