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

selection between dates is not compatible with Mysql #9734

Closed
qw4990 opened this issue Mar 14, 2019 · 1 comment
Closed

selection between dates is not compatible with Mysql #9734

qw4990 opened this issue Mar 14, 2019 · 1 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

Comments

@qw4990
Copy link
Contributor

qw4990 commented Mar 14, 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 time, f3 datetime);
insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
  1. What did you expect to see?
    In Mysql
mysql> create table t1(f1 date, f2 time, f3 datetime);
Query OK, 0 rows affected (0.03 sec)

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

mysql> select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
+------------+
| f1         |
+------------+
| 2006-01-01 |
+------------+
1 row in set (0.01 sec)
  1. What did you see instead?
    In TiDB
mysql> create table t1(f1 date, f2 time, f3 datetime);
Query OK, 0 rows affected (0.01 sec)

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

mysql> select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
Empty set, 1 warning (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-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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
@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
@WangXiangUSTC
Copy link
Contributor

fixed in #9933

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

No branches or pull requests

2 participants