Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

dayofmonth month quarter and year are not compatible with Mysql #9728

Closed
qw4990 opened this issue Mar 14, 2019 · 3 comments · Fixed by #21220
Closed

dayofmonth month quarter and year are not compatible with Mysql #9728

qw4990 opened this issue Mar 14, 2019 · 3 comments · Fixed by #21220
Assignees
@lzmhhh123
Labels
challenge-program component/expression good first issue Denotes an issue ready for a new contributor, according to the "help wanted" guidelines. help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.

Comments

@qw4990
Copy link
Contributor

qw4990 commented Mar 14, 2019 •
edited by ti-srebot
Loading

Description

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.
select year(0000-00-00),year("0000-00-00");
select dayofmonth(0000-00-00), dayofmonth("0000-00-00");
select month(0000-00-00), month("0000-00-00");
select quarter(0000-00-00), quarter("0000-00-00");
select extract(MONTH FROM 0000-00-00), extract(MONTH FROM "0000-00-00");
  1. What did you expect to see?
    In Mysql
mysql> select year(0000-00-00),year("0000-00-00");
+------------------+--------------------+
| year(0000-00-00) | year("0000-00-00") |
+------------------+--------------------+
|                0 |               NULL |
+------------------+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select dayofmonth(0000-00-00), dayofmonth("0000-00-00");
+------------------------+--------------------------+
| dayofmonth(0000-00-00) | dayofmonth("0000-00-00") |
+------------------------+--------------------------+
|                      0 |                     NULL |
+------------------------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select month(0000-00-00), month("0000-00-00");
+-------------------+---------------------+
| month(0000-00-00) | month("0000-00-00") |
+-------------------+---------------------+
|                 0 |                NULL |
+-------------------+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select quarter(0000-00-00), quarter("0000-00-00");
+---------------------+-----------------------+
| quarter(0000-00-00) | quarter("0000-00-00") |
+---------------------+-----------------------+
|                   0 |                  NULL |
+---------------------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select extract(MONTH FROM 0000-00-00), extract(MONTH FROM "0000-00-00");
+--------------------------------+----------------------------------+
| extract(MONTH FROM 0000-00-00) | extract(MONTH FROM "0000-00-00") |
+--------------------------------+----------------------------------+
|                              0 |                             NULL |
+--------------------------------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
  1. What did you see instead?
    In TiDB
mysql> select year(0000-00-00),year("0000-00-00");
+------------------+--------------------+
| year(0000-00-00) | year("0000-00-00") |
+------------------+--------------------+
|             NULL |               NULL |
+------------------+--------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select dayofmonth(0000-00-00), dayofmonth("0000-00-00");
+------------------------+--------------------------+
| dayofmonth(0000-00-00) | dayofmonth("0000-00-00") |
+------------------------+--------------------------+
|                   NULL |                     NULL |
+------------------------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select month(0000-00-00), month("0000-00-00");
+-------------------+---------------------+
| month(0000-00-00) | month("0000-00-00") |
+-------------------+---------------------+
|              NULL |                NULL |
+-------------------+---------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select quarter(0000-00-00), quarter("0000-00-00");
+---------------------+-----------------------+
| quarter(0000-00-00) | quarter("0000-00-00") |
+---------------------+-----------------------+
|                NULL |                  NULL |
+---------------------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select extract(MONTH FROM 0000-00-00), extract(MONTH FROM "0000-00-00");
ERROR 1292 (22007): invalid time format: '{0 0 0 0 0 0 0}'
  1. What version of TiDB are you using (tidb-server -V or run select 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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SIG slack channel

#sig-exec

Score

  • 300

Mentor
@qw4990 qw4990 added type/bug The issue is confirmed as a bug. help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. good first issue Denotes an issue ready for a new contributor, according to the "help wanted" guidelines. component/expression labels Mar 14, 2019
@qw4990 qw4990 mentioned this issue Mar 14, 2019
Closed
30 tasks
@WangXiangUSTC WangXiangUSTC mentioned this issue Apr 11, 2019
Merged
@ekalinin ekalinin mentioned this issue Oct 4, 2019
Merged
@ghost
Copy link

ghost commented Jul 13, 2020

Quarter is now correct, but the other expressions are still incorrect in master:

select year(0000-00-00),year("0000-00-00");
select dayofmonth(0000-00-00), dayofmonth("0000-00-00");
select month(0000-00-00), month("0000-00-00");
select quarter(0000-00-00), quarter("0000-00-00");
select extract(MONTH FROM 0000-00-00), extract(MONTH FROM "0000-00-00");

..

mysql> select year(0000-00-00),year("0000-00-00");
+------------------+--------------------+
| year(0000-00-00) | year("0000-00-00") |
+------------------+--------------------+
|             NULL |               NULL |
+------------------+--------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select dayofmonth(0000-00-00), dayofmonth("0000-00-00");
+------------------------+--------------------------+
| dayofmonth(0000-00-00) | dayofmonth("0000-00-00") |
+------------------------+--------------------------+
|                   NULL |                     NULL |
+------------------------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select month(0000-00-00), month("0000-00-00");
+-------------------+---------------------+
| month(0000-00-00) | month("0000-00-00") |
+-------------------+---------------------+
|              NULL |                NULL |
+-------------------+---------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select quarter(0000-00-00), quarter("0000-00-00");
+---------------------+-----------------------+
| quarter(0000-00-00) | quarter("0000-00-00") |
+---------------------+-----------------------+
|                   0 |                  NULL |
+---------------------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select extract(MONTH FROM 0000-00-00), extract(MONTH FROM "0000-00-00");
+--------------------------------+----------------------------------+
| extract(MONTH FROM 0000-00-00) | extract(MONTH FROM "0000-00-00") |
+--------------------------------+----------------------------------+
|                              0 |                                0 |
+--------------------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

@ichn-hu ichn-hu mentioned this issue Nov 3, 2020
Closed
@lzmhhh123 lzmhhh123 self-assigned this Nov 9, 2020
@lzmhhh123 lzmhhh123 mentioned this issue Nov 23, 2020
Merged
@lzmhhh123
Copy link
Contributor

/pick

@ti-srebot
Copy link
Contributor

Please edit this comment or add a new comment to complete the following information

Not a bug

  1. Remove the 'type/bug' label
  2. Add notes to indicate why it is not a bug

Duplicate bug

  1. Add the 'type/duplicate' label
  2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

This was referenced Dec 7, 2020
Closed
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@lzmhhh123 lzmhhh123

Labels
challenge-program component/expression good first issue Denotes an issue ready for a new contributor, according to the "help wanted" guidelines. help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

expression: fix compatibility behaviors in zero datetime with MySQL lzmhhh123/tidb
3 participants
@qw4990 @lzmhhh123 @ti-srebot