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.

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

Wrong DATE/DATETIME comparison in BETWEEN function #9764

Closed
qw4990 opened this issue Mar 18, 2019 · 0 comments · Fixed by #10313
Closed

Wrong DATE/DATETIME comparison in BETWEEN function #9764

qw4990 opened this issue Mar 18, 2019 · 0 comments · Fixed by #10313
Labels
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. type/bug The issue is confirmed as a bug. type/compatibility

Comments

@qw4990
Copy link
Contributor

qw4990 commented Mar 18, 2019

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.
create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
  1. What did you expect to see?
    In Mysql
mysql> create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
Query OK, 1 row affected (0.00 sec)

mysql> select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
+---------------------+
| f2                  |
+---------------------+
| 2001-01-01 01:01:01 |
+---------------------+
1 row in set (0.00 sec)
  1. What did you see instead?
    In TiDB
mysql> create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01');
Query OK, 1 row affected (0.00 sec)

mysql> select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
Empty set (0.00 sec)
  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-227-g06183393e-dirty
Git Commit Hash: 06183393ed536aa248248b72d4bf9f5010947f66
Git Branch: master
UTC Build Time: 2019-03-15 08:22:53
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
@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 type/compatibility and removed component/expression labels Mar 18, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
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. type/bug The issue is confirmed as a bug. type/compatibility
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant