tidb: timestamp column default value bug in multiple time zones
Bug Report
Please answer these questions before submitting your issue. Thanks!
- 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
- 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
- What did you see instead?
when I calculate marstime with max, it return the impossible data.
the value is in the future
- What version of TiDB are you using (
tidb-server -V
or runselect 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)
I reproduce this bug in tidb, pd, tikv release 2.1. bellow is tidb version.
prepare
Then
Create 2 sessions, both execute
use test
first. then: session 1:session 2:
Then, use follow sql to check:
@XuHuaiyu
ok when u fix it,pls let me know. I will upgrade it