[BUG] can't support format '+HH:mm' on interval hour

[imiskolee]

Bug Report

1. What did you do?

select convert_tz(date_add(concat('2018-10-26',' 00:00:00'),interval '+0:00' hour),concat('+8:00'),'UTC');

It's work on MySQL 5.7,but can't work on TiDB.

2. What did you expect to see?

3. What did you see instead?

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

Release Version: v2.1.0-rc.2-49-geb617c5
Git Commit Hash: eb617c5
Git Branch: master
UTC Build Time: 2018-09-29 04:04:52
GoVersion: go version go1.11 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

[XuHuaiyu]

hi, @imiskolee
Thanks for your contribution.

[zz-jason]

@imiskolee Here is the behavior of this query in TiDB:

TiDB(localhost:4000) > select convert_tz(date_add(concat('2018-10-26',' 00:00:00'),interval '+0:00' hour),concat('+8:00'),'UTC');
+----------------------------------------------------------------------------------------------------+
| convert_tz(date_add(concat('2018-10-26',' 00:00:00'),interval '+0:00' hour),concat('+8:00'),'UTC') |
+----------------------------------------------------------------------------------------------------+
| NULL                                                                                               |
+----------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

TiDB(localhost:4000) > show warnings;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1292 | Incorrect datetime value: '+0:00' |
+---------+------+-----------------------------------+
1 row in set (0.00 sec)

TiDB(localhost:4000) > select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: None
Git Commit Hash: 508f5dd6263e4896d53fb37af99f3a1b747de713
Git Branch: master
UTC Build Time: 2018-10-29 02:29:24
GoVersion: go version go1.11 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

And the behavior in MySQL 5.7:

MySQL(localhost:3306) > select convert_tz(date_add(concat('2018-10-26',' 00:00:00'),interval '+0:00' hour),concat('+8:00'),'UTC');
+----------------------------------------------------------------------------------------------------+
| convert_tz(date_add(concat('2018-10-26',' 00:00:00'),interval '+0:00' hour),concat('+8:00'),'UTC') |
+----------------------------------------------------------------------------------------------------+
| NULL                                                                                               |
+----------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

MySQL(localhost:3306) > show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '+0:00' |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

MySQL(localhost:3306) > select version()\G
*************************** 1. row ***************************
version(): 5.7.22
1 row in set (0.00 sec)

What do you mean by:

It's work on MySQL 5.7,but can't work on TiDB.

Could you please post more details? For example, what's the result of your MySQL and TiDB?

[imiskolee]

@zz-jason

mysql [db57-1.c.production-tw.internal] > select convert_tz(date_add(concat('2018-10-26',' 00:00:00'),interval '+0:00' hour),concat('+8:00'),'UTC');
+----------------------------------------------------------------------------------------------------+
| convert_tz(date_add(concat('2018-10-26',' 00:00:00'),interval '+0:00' hour),concat('+8:00'),'UTC') |
+----------------------------------------------------------------------------------------------------+
| 2018-10-25 16:00:00                                                                                |
+----------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql [db57-1.c.production-tw.internal] > select version();
+------------------+
| version()        |
+------------------+
| 5.7.22-22-57-log |
+------------------+
1 row in set (0.00 sec)
mysql [db57-1.c.production-tw.internal] > select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------+
| IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [db57-1.c.production-tw.internal] > select convert_tz(date_add(concat('2018-10-26',' 00:00:00'),interval '+4:00' hour),concat('+8:00'),'UTC');
+----------------------------------------------------------------------------------------------------+
| convert_tz(date_add(concat('2018-10-26',' 00:00:00'),interval '+4:00' hour),concat('+8:00'),'UTC') |
+----------------------------------------------------------------------------------------------------+
| 2018-10-25 20:00:00                                                                                |
+----------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql [db57-1.c.production-tw.internal] > show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '+4:00' |
| Warning | 1292 | Truncated incorrect INTEGER value: '+4:00' |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

looks like mysql 5.7.22 also has wanrings. but still has correct result. maybe we need mapping the behavior on TiDB.