expression: Fix different behaviors with MySQL when comparing datetime column with numeric constant | tidb-test=pr/2196#45945
expression: Fix different behaviors with MySQL when comparing datetime column with numeric constant | tidb-test=pr/2196#45945ti-chi-bot[bot] merged 7 commits intopingcap:masterfrom yibin87:fix_38361
Conversation
Signed-off-by: yibin <huyibin@pingcap.com>
|
Hi @yibin87. Thanks for your PR. PRs from untrusted users cannot be marked as trusted with I understand the commands that are listed here. DetailsInstructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository. |
Signed-off-by: yibin <huyibin@pingcap.com>
Signed-off-by: yibin <huyibin@pingcap.com>
Codecov Report
Additional details and impacted files@@ Coverage Diff @@
## master #45945 +/- ##
================================================
- Coverage 73.3497% 72.4669% -0.8829%
================================================
Files 1277 1300 +23
Lines 393388 405127 +11739
================================================
+ Hits 288549 293583 +5034
- Misses 86440 92961 +6521
- Partials 18399 18583 +184
Flags with carried forward coverage won't be shown. Click here to find out more.
|
Signed-off-by: yibin <huyibin@pingcap.com>
|
/test unit-test |
|
@yibin87: Cannot trigger testing until a trusted user reviews the PR and leaves an DetailsIn response to this:
Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository. |
|
/test mysql-test |
|
@yibin87: Cannot trigger testing until a trusted user reviews the PR and leaves an DetailsIn response to this:
Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository. |
|
/test unit-test |
|
@yibin87: Cannot trigger testing until a trusted user reviews the PR and leaves an DetailsIn response to this:
Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository. |
|
/test unit-test |
|
@yibin87: Cannot trigger testing until a trusted user reviews the PR and leaves an DetailsIn response to this:
Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository. |
| tk.MustQuery("select a > 20230809 from t").Check(testkit.Rows("0")) | ||
| tk.MustQuery("select a = 20230809 from t").Check(testkit.Rows("1")) | ||
| tk.MustQuery("select a < 20230810 from t").Check(testkit.Rows("1")) | ||
| tk.MustQuery("select a < 20231310 from t").Check(testkit.Rows("0")) |
There was a problem hiding this comment.
This is the case that numeric can't convert to datetime, so use real data type to compare?
There was a problem hiding this comment.
Add some comments here?
expression/builtin_compare.go
Outdated
| } | ||
| sc := ctx.GetSessionVars().StmtCtx | ||
| var timestampDatum types.Datum | ||
| targetFieldType := types.NewFieldType(mysql.TypeTimestamp) |
There was a problem hiding this comment.
Why the target type is timestamp instead of datetime?
There was a problem hiding this comment.
Check that the MySQL seems convert to datetime instead, because it can handle numeric constant that is beyond timestamp's valid value range.
| timestampDatum, err = dt.ConvertTo(sc, targetFieldType) | ||
| if err != nil { | ||
| return args | ||
| } |
There was a problem hiding this comment.
maybe also need to return original args if timestampDatum is null but dt is not null
There was a problem hiding this comment.
Make sense, add it.
Signed-off-by: yibin <huyibin@pingcap.com>
Signed-off-by: yibin <huyibin@pingcap.com>
| tk.MustQuery("select a > 20230809 from t").Check(testkit.Rows("0")) | ||
| tk.MustQuery("select a = 20230809 from t").Check(testkit.Rows("1")) | ||
| tk.MustQuery("select a < 20230810 from t").Check(testkit.Rows("1")) | ||
| tk.MustQuery("select a < 20231310 from t").Check(testkit.Rows("0")) |
There was a problem hiding this comment.
Add some comments here?
| var datetimeDatum types.Datum | ||
| targetFieldType := types.NewFieldType(mysql.TypeDatetime) | ||
| datetimeDatum, err = dt.ConvertTo(sc, targetFieldType) | ||
| if err != nil || datetimeDatum.IsNull() { |
There was a problem hiding this comment.
Don't need to check dt.IsNull() here?
There was a problem hiding this comment.
ok, added the comments and check.
Signed-off-by: yibin <huyibin@pingcap.com>
|
[APPROVALNOTIFIER] This PR is APPROVED This pull-request has been approved by: windtalker, wshwsh12 The full list of commands accepted by this bot can be found here. The pull request process is described here DetailsNeeds approval from an approver in each of these files:
Approvers can indicate their approval by writing |
[LGTM Timeline notifier]Timeline:
|
|
In response to a cherrypick label: new pull request created to branch |
Signed-off-by: ti-chi-bot <ti-community-prow-bot@tidb.io>
|
In response to a cherrypick label: new pull request created to branch |
Signed-off-by: ti-chi-bot <ti-community-prow-bot@tidb.io>
|
In response to a cherrypick label: new pull request created to branch |
Signed-off-by: ti-chi-bot <ti-community-prow-bot@tidb.io>
|
In response to a cherrypick label: new pull request created to branch |
Signed-off-by: ti-chi-bot <ti-community-prow-bot@tidb.io>
|
In response to a cherrypick label: new pull request created to branch |
What problem does this PR solve?
Issue Number: close #38361
Problem Summary: When compare datetime/timestamp column with numeric constant, try convert numeric constant to timestamp datetime, do nothing if conversion failed.
What is changed and how it works?
Check List
Tests
Side effects
Documentation
Release note
Please refer to Release Notes Language Style Guide to write a quality release note.