presto 查询tidb 日期错误

[wangfeigithub]

你好 这边使用tidb驱动出现的问题，如下图
CREATE TABLE receiving (
receiving_id int(11) NOT NULL,
expected_date date NOT NULL DEFAULT '0000-00-00',
UNIQUE KEY udx_receiving_id (receiving_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
使用presto tidb驱动查询 和使用mysql驱动查询

[humengyu2012]

I use this sql to create table in TiDB, but it throws an exception: Invalid default value for 'expected_date'. How did you create this table in TiDB? Can you provide your TiDB version?

[wangfeigithub]

I use this sql to create table in TiDB, but it throws an exception: Invalid default value for 'expected_date'. How did you create this table in TiDB? Can you provide your TiDB version?

tidb4.07 ，
Probably your SQL_mode limit

[wangfeigithub]

[humengyu2012]

I want to test your case, can you provided some sample data?

[wangfeigithub]

定位到问题了 我们业务库 有这样的数据
select cast('2020-00-00' as TIMESTAMP)
但是这个数据我们用presto mysql驱动查询tidb 没有问题 用你们的tidb驱动 就会有问题。

[humengyu2012]

MySQL jdbc driver use ascii code to parse date , but TiKV java client use java class Date. That's why you can't read '2020-00-00' by presto-tidb-connector. We will update TiKV java client to 3.0.1, and you could try presto-tidb-connector after update tikv client to version 3.0.1 merged.

[wangfeigithub]

I'll try it

[wangfeigithub]

However, I also found the problem of enum. If the enum is empty, an error will be reported

[wangfeigithub]

CREATE TABLE sp_service_channel1 (
sc_id int(11) NOT NULL,
st_split_type enum('n','r','l') NOT NULL,
UNIQUE KEY sc_id (sc_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin/*!90000 SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=1 */;

INSERT INTO sp_service_channel1 (sc_id, st_split_type)
VALUES
(191, '');

[wangfeigithub]

You can try this case

[humengyu2012]

It will be fixed by tikv-java-client in the future(version 3.0.1+).

[sunxiaoguang]

I'll try it

You can try master version with your data now.

[wangfeigithub]

MySQL jdbc driver use ascii code to parse date , but TiKV java client use java class Date. That's why you can't read '2020-00-00' by presto-tidb-connector. We will update TiKV java client to 3.0.1, and you could try presto-tidb-connector after update tikv client to version 3.0.1 merged.

3.01 Illegal dates are also not supported

[humengyu2012]

We have discussed it with PingCAP.