pingcap/tidb

inconsistent result of greatest("2017-02-30", cast("2017-02-02" as date))

Open

#6,740 创建于 2018年6月4日

在 GitHub 查看
 (1 评论) (0 反应) (0 负责人)Go (40,090 star) (6,186 fork)batch import
compatibility-mysql80component/expressionhelp wantedtype/compatibility

描述

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?

  2. What did you expect to see?

mysql(127.0.0.1)> select greatest(cast("2017-02-02" as date),"2017-02-30");
Field   1:  `greatest(cast("2017-02-02" as date),"2017-02-30")`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     30
Max_length: 10
Decimals:   31
Flags:


+---------------------------------------------------+
| greatest(cast("2017-02-02" as date),"2017-02-30") |
+---------------------------------------------------+
| 2017-02-30                                        |                                    <====
+---------------------------------------------------+

mysql(127.0.0.1)> select greatest(cast("2017-02-02" as date),cast("2017-02-03" as date));
Field   1:  `greatest(cast("2017-02-02" as date),cast("2017-02-03" as date))`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE   <====
Collation:  utf8_general_ci (33)
Length:     9
Max_length: 10
Decimals:   0
Flags:


+-----------------------------------------------------------------+
| greatest(cast("2017-02-02" as date),cast("2017-02-03" as date)) |
+-----------------------------------------------------------------+
| 2017-02-03                                                      |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
  1. What did you see instead?
tidb(127.0.0.1)> select greatest(cast("2017-02-02" as date),"2017-02-30");
Field   1:  `greatest(cast("2017-02-02" as date),"2017-02-30")`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     0
Max_length: 19
Decimals:   31
Flags:


+---------------------------------------------------+
| greatest(cast("2017-02-02" as date),"2017-02-30") |
+---------------------------------------------------+
| 2017-02-02 00:00:00                               |                                <====
+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)


tidb(127.0.0.1)> select greatest(cast("2017-02-02" as date),cast("2017-02-03" as date));
Field   1:  `greatest(cast("2017-02-02" as date),cast("2017-02-03" as date))`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING        <====
Collation:  utf8_general_ci (33)
Length:     0
Max_length: 19
Decimals:   31
Flags:


+-----------------------------------------------------------------+
| greatest(cast("2017-02-02" as date),cast("2017-02-03" as date)) |
+-----------------------------------------------------------------+
| 2017-02-03 00:00:00                                             |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
Release Version: v2.0.0-rc.4-273-gf6a3c3f
Git Commit Hash: f6a3c3f0a55abf454d352ffa2089d7983fc18307
Git Branch: master
UTC Build Time: 2018-06-04 03:37:54
GoVersion: go version go1.10.1 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.0.0-rc.4.1

贡献者指南

技术栈
gosqlmysql
领域
database
议题类型
bug
难度面向新贡献者的预计实现难度，1 表示很小改动，5 表示专家级工作。
3
预计时间有经验贡献者完成调查、实现、测试并准备 pull request 的粗略时间范围。
half day
活动状态议题当前的可参与程度：新鲜、活跃、陈旧、阻塞或等待维护者输入。
stale
清晰度议题是否清楚说明期望改动、验收标准和下一步。
mostly clear
前置要求
understanding of SQL date functionsfamiliarity with Go
新手友好度1-100 的估计分数，表示该议题对首次贡献者的友好程度。
50
研究方向
Investigate the `greatest` function implementation in the TiDB expression package, likely in `expression/builtin compare.go`. Compare the handling of date types and string types. The issue shows inconsistency when mixing cast date and string date literals. Check if the conversion logic for implicit cast differs from explicit cast. Look at similar issues or PRs related to date handling in greatest/least functions.
inconsistent result of greatest("2017-02-30", cast("2017-02-02" as date)) · pingcap/tidb#6740 | Good First Issue