tidb: timestamp column default value bug in multiple time zones

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. What did you do? If possible, provide a recipe for reproducing the error.

the privious version : 08.14 rc version

currecnt version: 2019-01-03 rc version

  1. What did you expect to see?

now=2019-01-05 15:06

mysql> alter table Account add column marstime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; Query OK, 0 rows affected (1.02 sec)

mysql> alter table Account add index(marstime); ^@^@^@^@^@^@^@Query OK, 0 rows affected (7 min 3.18 sec)

mysql> select max(marstime) From Account; ±--------------------+ | max(marstime) | ±--------------------+ | 2019-01-15 22:54:33 | ±--------------------+ 1 row in set (0.38 sec)

mysql> select min(marstime) From Account; ±--------------------+ | min(marstime) | ±--------------------+ | 2019-01-15 14:57:46 | ±--------------------+ 1 row in set (0.26 sec)

mysql> explain select max(marstime) From Account; ±---------------±-------------±---------------±-----±-------------------------------------------------------------------------±------+ | id | parents | children | task | operator info | count | ±---------------±-------------±---------------±-----±-------------------------------------------------------------------------±------+ | IndexScan_29 | Limit_30 | | cop | table:Account, index:marstime, range:[<nil>,+inf], keep order:true, desc | 1.00 | | Limit_30 | | IndexScan_29 | cop | offset:0, count:1 | 1.00 | | IndexReader_31 | Limit_16 | | root | index:Limit_30 | 1.00 | | Limit_16 | StreamAgg_12 | IndexReader_31 | root | offset:0, count:1 | 1.00 | | StreamAgg_12 | | Limit_16 | root | , funcs:max(**.account.marstime) | 1.00 | ±---------------±-------------±---------------±-----±-------------------------------------------------------------------------±------+ 5 rows in set (0.00 sec)

Account schema:

CREATE TABLE Account ( asdfid int(11) NOT NULL AUTO_INCREMENT, nsdfafsdme varchar(60) NOT NULL DEFAULT ‘’, daisdfn varchar(60) NOT NULL DEFAULT ‘’, fsag int(11) DEFAULT ‘0’, unt int(11) DEFAULT ‘0’, uib int(11) NOT NULL DEFAULT ‘0’, pot int(11) DEFAULT ‘0’, blom int(11) DEFAULT ‘0’, im int(10) UNSIGNED DEFAULT ‘0’, bce int(11) DEFAULT ‘0’, newe datetime DEFAULT NULL, pad varchar(16) DEFAULT NULL, marstime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (asdfid), UNIQUE KEY n (nsdfafsdme,daisdfn), UNIQUE KEY d (daisdfn,nsdfafsdme), KEY marstime (marstime) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=22938908 1 row in set (0.03 sec)

Account data count: 2kw+, and the data is imported by myloader in privious tidb_version;;

I do the same thing in : tidb( 2018-08-14 rc version) and (2019-01-03 version) is stil the same

  1. What did you see instead?

when I calculate marstime with max, it return the impossible data.

the value is in the future

  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

| Release Version: v2.0.11 Git Commit Hash: 83889a5d821f40412be1af2ad2f2393737f517fd Git Branch: release-2.0 UTC Build Time: 2019-01-03 09:27:12 GoVersion: go version go1.11.2 linux/amd64 TiKV Min Version: 2.0.0-rc.4.1 |

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Comments: 22 (11 by maintainers)

Most upvoted comments

I reproduce this bug in tidb, pd, tikv release 2.1. bellow is tidb version.

Release Version: v2.1.2-7-g08f56d318                                     
Git Commit Hash: 08f56d31857c75e9187f48a558dc9455105783b3               
Git Branch: release-2.1                                                  
UTC Build Time: 2019-01-16 04:29:17

prepare

use test.
create table t (a int);
insert into t1 values (2);

Then

Create 2 sessions, both execute use test first. then: session 1:

set global time_zone = '+02:00';

session 2:

alter table t1 add column b timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
alter table t1 add index(b);

Then, use follow sql to check:

select b from t1;
select b from t1 use index(b);
admin check table t1;

@XuHuaiyu

ok when u fix it,pls let me know. I will upgrade it