Function extract is incompatible with Mysql #9716
Description
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
- What did you do?
If possible, provide a recipe for reproducing the error.
select extract(DAY_MINUTE FROM "01 23:45:56.89");
select extract(DAY_HOUR FROM "01 23:45:56.89");
select extract(DAY_MICROSECOND FROM "01 23:45:56.89");
select extract(DAY_SECOND FROM "01 23:45:56.89");
- What did you expect to see?
In Mysql
mysql> select extract(DAY_MINUTE FROM "01 23:45:56.89");
+-------------------------------------------+
| extract(DAY_MINUTE FROM "01 23:45:56.89") |
+-------------------------------------------+
| 4745 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select extract(DAY_HOUR FROM "01 23:45:56.89");
+-----------------------------------------+
| extract(DAY_HOUR FROM "01 23:45:56.89") |
+-----------------------------------------+
| 47 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select extract(DAY_MICROSECOND FROM "01 23:45:56.89");
+------------------------------------------------+
| extract(DAY_MICROSECOND FROM "01 23:45:56.89") |
+------------------------------------------------+
| 474556890000 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select extract(DAY_SECOND FROM "01 23:45:56.89");
+-------------------------------------------+
| extract(DAY_SECOND FROM "01 23:45:56.89") |
+-------------------------------------------+
| 474556 |
+-------------------------------------------+
1 row in set (0.00 sec)
- What did you see instead?
In TiDB
mysql> select extract(DAY_MINUTE FROM "01 23:45:56.89");
+-------------------------------------------+
| extract(DAY_MINUTE FROM "01 23:45:56.89") |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> select extract(DAY_HOUR FROM "01 23:45:56.89");
+-----------------------------------------+
| extract(DAY_HOUR FROM "01 23:45:56.89") |
+-----------------------------------------+
| NULL |
+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select extract(DAY_MICROSECOND FROM "01 23:45:56.89");
+------------------------------------------------+
| extract(DAY_MICROSECOND FROM "01 23:45:56.89") |
+------------------------------------------------+
| NULL |
+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select extract(DAY_SECOND FROM "01 23:45:56.89");
+-------------------------------------------+
| extract(DAY_SECOND FROM "01 23:45:56.89") |
+-------------------------------------------+
| NULL |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
- What version of TiDB are you using (
tidb-server -Vor runselect tidb_version();on TiDB)?
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-212-g0681b8125
Git Commit Hash: 0681b8125080045ee0d4747ce61923164c6f2dd1
Git Branch: master
UTC Build Time: 2019-03-14 04:39:19
GoVersion: go version go1.12 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)
SIG slack channel
Score
- 300