[MDEV-21249] MariaDB 10.3.10 When referring to bigint to generate timestamp data in the virtual generated column, the value of the generated column does not change when the time zone changes - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.3.20, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.31, 10.3.22, 10.4.12
Component/s: Virtual Columns
Labels:
- datatype
- virtual_columns
Environment:
ubuntu 16.04 mariadb 10.3

Description

When creating a virtual column, if the virtual column is timestamp, the value of the virtual column does not change when the time zone changes, and the mysql version changes.

mariadb10.3：

 create table test_timestamp (`create_timestamp` timestamp(3) NOT NULL COMMENT '记录创建时间',`create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` )) VIRTUAL COMMENT '记录创建时间');

insert into test_timestamp (`create_timestamp`) value (1569495326.861);

mysql> select * from test_timestamp;

+------------------+-------------------------+

| create_timestamp | create_time             |

+------------------+-------------------------+

|    1569495326861 | 2019-09-26 10:55:26.861 |

+------------------+-------------------------+

1 row in set (0.01 sec)

mysql> set time_zone = "+01:00";

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_timestamp;

+------------------+-------------------------+

| create_timestamp | create_time             |

+------------------+-------------------------+

|    1569495326861 | 2019-09-26 10:55:26.861 |

+------------------+-------------------------+

1 row in set (0.00 sec)

mysql5.7：

 mysql> create table test_timestamp (`create_timestamp` bigint(20) NOT NULL COMMENT '记录创建时间',

    ->   `create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` / 1000)) VIRTUAL COMMENT '记录创建时间');

Query OK, 0 rows affected (0.31 sec)

mysql> insert into test_timestamp (`create_timestamp`) value (1569495326861);

Query OK, 1 row affected (0.03 sec)

mysql> select * from test_timestamp;

+------------------+-------------------------+

| create_timestamp | create_time             |

+------------------+-------------------------+

|    1569495326861 | 2019-09-26 10:55:26.861 |

+------------------+-------------------------+

1 row in set (0.01 sec)

mysql> set time_zone = "+01:00";

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_timestamp;

+------------------+-------------------------+

| create_timestamp | create_time             |

+------------------+-------------------------+

|    1569495326861 | 2019-09-26 11:55:26.861 |

+------------------+-------------------------+

1 row in set (0.00 sec)

Attachments

Issue Links

causes

MDEV-21593 from_unixtime can not be longer used in Virtual Column

Stalled

Activity

Ascending order - Click to sort in descending order

hongsheng zhou created issue - 2019-12-08 10:00

hongsheng zhou made changes - 2019-12-09 05:47

Field	Original Value	New Value
Description	mariadb 创建一个虚拟列时，如果虚拟列为timestamp时，当时区发生变化时，虚拟列的值不发生变化，而mysql版本会发生变化 mariadb10.3环境： create table test_timestamp (`create_timestamp` timestamp(3) NOT NULL COMMENT '记录创建时间',`create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` )) VIRTUAL COMMENT '记录创建时间'); insert into test_timestamp (`create_timestamp`) value (1569495326.861); mysql> select * from test_timestamp; +------------------+-------------------------+ \| create_timestamp \| create_time \| +------------------+-------------------------+ \| 1569495326861 \| 2019-09-26 10:55:26.861 \| +------------------+-------------------------+ 1 row in set (0.01 sec) mysql> set time_zone = "+01:00"; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_timestamp; +------------------+-------------------------+ \| create_timestamp \| create_time \| +------------------+-------------------------+ \| 1569495326861 \| 2019-09-26 10:55:26.861 \| +------------------+-------------------------+ 1 row in set (0.00 sec) mysql5.7环境： mysql> create table test_timestamp (`create_timestamp` bigint(20) NOT NULL COMMENT '记录创建时间', -> `create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` / 1000)) VIRTUAL COMMENT '记录创建时间'); Query OK, 0 rows affected (0.31 sec) mysql> insert into test_timestamp (`create_timestamp`) value (1569495326861); Query OK, 1 row affected (0.03 sec) mysql> select * from test_timestamp; +------------------+-------------------------+ \| create_timestamp \| create_time \| +------------------+-------------------------+ \| 1569495326861 \| 2019-09-26 10:55:26.861 \| +------------------+-------------------------+ 1 row in set (0.01 sec) mysql> set time_zone = "+01:00"; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_timestamp; +------------------+-------------------------+ \| create_timestamp \| create_time \| +------------------+-------------------------+ \| 1569495326861 \| 2019-09-26 11:55:26.861 \| +------------------+-------------------------+ 1 row in set (0.00 sec)	When creating a virtual column, if the virtual column is timestamp, the value of the virtual column does not change when the time zone changes, and the mysql version changes. mariadb10.3： create table test_timestamp (`create_timestamp` timestamp(3) NOT NULL COMMENT '记录创建时间',`create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` )) VIRTUAL COMMENT '记录创建时间'); insert into test_timestamp (`create_timestamp`) value (1569495326.861); mysql> select * from test_timestamp; +------------------+-------------------------+ \| create_timestamp \| create_time \| +------------------+-------------------------+ \| 1569495326861 \| 2019-09-26 10:55:26.861 \| +------------------+-------------------------+ 1 row in set (0.01 sec) mysql> set time_zone = "+01:00"; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_timestamp; +------------------+-------------------------+ \| create_timestamp \| create_time \| +------------------+-------------------------+ \| 1569495326861 \| 2019-09-26 10:55:26.861 \| +------------------+-------------------------+ 1 row in set (0.00 sec) mysql5.7： mysql> create table test_timestamp (`create_timestamp` bigint(20) NOT NULL COMMENT '记录创建时间', -> `create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` / 1000)) VIRTUAL COMMENT '记录创建时间'); Query OK, 0 rows affected (0.31 sec) mysql> insert into test_timestamp (`create_timestamp`) value (1569495326861); Query OK, 1 row affected (0.03 sec) mysql> select * from test_timestamp; +------------------+-------------------------+ \| create_timestamp \| create_time \| +------------------+-------------------------+ \| 1569495326861 \| 2019-09-26 10:55:26.861 \| +------------------+-------------------------+ 1 row in set (0.01 sec) mysql> set time_zone = "+01:00"; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_timestamp; +------------------+-------------------------+ \| create_timestamp \| create_time \| +------------------+-------------------------+ \| 1569495326861 \| 2019-09-26 11:55:26.861 \| +------------------+-------------------------+ 1 row in set (0.00 sec)
Environment	生产环境 ubuntu 16.04	ubuntu 16.04 mariadb 10.3
Summary	mariadb 10.3.10 虚拟生成列中引用bigint 生成timestamp类型的数据时，当时区变化时，生成列的值不变化	MariaDB 10.3.10 When referring to bigint to generate timestamp data in the virtual generated column, the value of the generated column does not change when the time zone changes

Alice Sherepa made changes - 2019-12-09 12:27

Description

When creating a virtual column, if the virtual column is timestamp, the value of the virtual column does not change when the time zone changes, and the mysql version changes.

mariadb10.3：

create table test_timestamp (`create_timestamp` timestamp(3) NOT NULL COMMENT '记录创建时间',`create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` )) VIRTUAL COMMENT '记录创建时间');

insert into test_timestamp (`create_timestamp`) value (1569495326.861);

mysql> select * from test_timestamp;
+------------------+-------------------------+
| create_timestamp | create_time |
+------------------+-------------------------+
| 1569495326861 | 2019-09-26 10:55:26.861 |
+------------------+-------------------------+
1 row in set (0.01 sec)

mysql> set time_zone = "+01:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_timestamp;
+------------------+-------------------------+
| create_timestamp | create_time |
+------------------+-------------------------+
| 1569495326861 | 2019-09-26 10:55:26.861 |
+------------------+-------------------------+
1 row in set (0.00 sec)

mysql5.7：

mysql> create table test_timestamp (`create_timestamp` bigint(20) NOT NULL COMMENT '记录创建时间',
-> `create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` / 1000)) VIRTUAL COMMENT '记录创建时间');
Query OK, 0 rows affected (0.31 sec)

mysql> insert into test_timestamp (`create_timestamp`) value (1569495326861);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_timestamp;
+------------------+-------------------------+
| create_timestamp | create_time |
+------------------+-------------------------+
| 1569495326861 | 2019-09-26 10:55:26.861 |
+------------------+-------------------------+
1 row in set (0.01 sec)

mysql> set time_zone = "+01:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_timestamp;
+------------------+-------------------------+
| create_timestamp | create_time |
+------------------+-------------------------+
| 1569495326861 | 2019-09-26 11:55:26.861 |
+------------------+-------------------------+
1 row in set (0.00 sec)

When creating a virtual column, if the virtual column is timestamp, the value of the virtual column does not change when the time zone changes, and the mysql version changes.

mariadb10.3：

{noformat}

create table test_timestamp (`create_timestamp` timestamp(3) NOT NULL COMMENT '记录创建时间',`create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` )) VIRTUAL COMMENT '记录创建时间');

insert into test_timestamp (`create_timestamp`) value (1569495326.861);

mysql> select * from test_timestamp;
+------------------+-------------------------+
| create_timestamp | create_time |
+------------------+-------------------------+
| 1569495326861 | 2019-09-26 10:55:26.861 |
+------------------+-------------------------+
1 row in set (0.01 sec)

mysql> set time_zone = "+01:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_timestamp;
+------------------+-------------------------+
| create_timestamp | create_time |
+------------------+-------------------------+
| 1569495326861 | 2019-09-26 10:55:26.861 |
+------------------+-------------------------+
1 row in set (0.00 sec)
{noformat}

mysql5.7：
{noformat}
mysql> create table test_timestamp (`create_timestamp` bigint(20) NOT NULL COMMENT '记录创建时间',
-> `create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` / 1000)) VIRTUAL COMMENT '记录创建时间');
Query OK, 0 rows affected (0.31 sec)

mysql> insert into test_timestamp (`create_timestamp`) value (1569495326861);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_timestamp;
+------------------+-------------------------+
| create_timestamp | create_time |
+------------------+-------------------------+
| 1569495326861 | 2019-09-26 10:55:26.861 |
+------------------+-------------------------+
1 row in set (0.01 sec)

mysql> set time_zone = "+01:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_timestamp;
+------------------+-------------------------+
| create_timestamp | create_time |
+------------------+-------------------------+
| 1569495326861 | 2019-09-26 11:55:26.861 |
+------------------+-------------------------+
1 row in set (0.00 sec)
{noformat}

Alice Sherepa made changes - 2019-12-09 13:50

Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-12-09 13:50

Labels

datatype

datatype virtual_columns

Alice Sherepa added a comment - 2019-12-09 14:03

Thanks a lot! I repeated on MariaDb 5.5-10.4:

create table t1 (a int, b timestamp GENERATED ALWAYS AS (from_unixtime(a)) VIRTUAL);

insert into t1 (a) value (1569495327);

select a, b, from_unixtime(a) from t1;

set time_zone = "+01:00";

select a, b, from_unixtime(a) from t1;

drop table t1;

MariaDB [test]> select a, b, from_unixtime(a) from t1;

+------------+---------------------+---------------------+

| a          | b                   | from_unixtime(a)    |

+------------+---------------------+---------------------+

| 1569495327 | 2019-09-26 12:55:27 | 2019-09-26 12:55:27 |

+------------+---------------------+---------------------+

1 row in set (0.000 sec)

MariaDB [test]> set time_zone = "+01:00";

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select a, b, from_unixtime(a) from t1;

+------------+---------------------+---------------------+

| a          | b                   | from_unixtime(a)    |

+------------+---------------------+---------------------+

| 1569495327 | 2019-09-26 12:55:27 | 2019-09-26 11:55:27 |

+------------+---------------------+---------------------+

1 row in set (0.000 sec)

Alice Sherepa added a comment - 2019-12-09 14:03 Thanks a lot! I repeated on MariaDb 5.5-10.4: create table t1 (a int , b timestamp GENERATED ALWAYS AS (from_unixtime(a)) VIRTUAL); insert into t1 (a) value (1569495327); select a, b, from_unixtime(a) from t1; set time_zone = "+01:00" ; select a, b, from_unixtime(a) from t1; drop table t1; MariaDB [test]> select a, b, from_unixtime(a) from t1; +------------+---------------------+---------------------+ | a | b | from_unixtime(a) | +------------+---------------------+---------------------+ | 1569495327 | 2019-09-26 12:55:27 | 2019-09-26 12:55:27 | +------------+---------------------+---------------------+ 1 row in set (0.000 sec) MariaDB [test]> set time_zone = "+01:00"; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> select a, b, from_unixtime(a) from t1; +------------+---------------------+---------------------+ | a | b | from_unixtime(a) | +------------+---------------------+---------------------+ | 1569495327 | 2019-09-26 12:55:27 | 2019-09-26 11:55:27 | +------------+---------------------+---------------------+ 1 row in set (0.000 sec)

Alice Sherepa made changes - 2019-12-09 14:03

Fix Version/s		5.5 [ 15800 ]
Fix Version/s		10.0 [ 16000 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-12-09 14:04

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2019-12-09 14:04

Assignee

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2020-01-21 14:25

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2020-01-21 14:25

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2020-01-21 20:24

Component/s		Virtual Columns [ 10803 ]
Component/s	Data Manipulation - Insert [ 10101 ]
Fix Version/s		10.2.31 [ 24017 ]
Fix Version/s		10.3.22 [ 24018 ]
Fix Version/s		10.4.12 [ 24019 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2020-01-29 18:10

Link

This issue causes MDEV-21593 [ MDEV-21593 ]

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

Workflow

MariaDB v3 [ 101724 ]

MariaDB v4 [ 157066 ]

People

Assignee:: Sergei Golubchik

Reporter:: hongsheng zhou

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2019-12-08 10:00

Updated:: 2020-01-29 18:10

Resolved:: 2020-01-21 20:24

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration