TiDB查询date类型数据时无结果，使用cast(? as DATE)解决

摘要

错误使用的例子：

todayZeroTime, _ := time.Parse("2006-01-02", time.Now().Format("2006-01-02"))
db = db.Where("(start_at = ? || end_at = ?)", todayZeroTime, todayZeroTime.AddDate(0, 0, -1))

例子修改如下：

db = db.Where("(start_at = cast(? as DATE) || end_at = cast(? as DATE))", time.Now(), time.Now().AddDate(0, 0, -1))

问题起因

TiDB某条记录有date字段字段，值例如为2020.09.14，sql直接使用：

SELECT * FROM `rule_tag_ele_num`  WHERE `rule_tag_ele_num`.`deleted_at` IS NULL AND ((tag_id = 1 AND date BETWEEN '2020-09-14 00:00:00' AND '2020-09-16 00:00:00'))

查询的话，2020.09.14这条记录就不会返回，但是mysql是可以的，但是TiDB声称完全兼容mysql，所以是不是因为我between用的话不规范的问题，对TiDB和mysql都不规范，但是这种不规范被mysql漫长的版本迭代中给容错掉了，遂查mysql官方文档的标准推荐用法

mysql官方文档

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.3, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
        -> 1, 0
mysql> SELECT 1 BETWEEN 2 AND 3;
        -> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
        -> 0

For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

果然，其中的：

For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

给出了明确的建议。

修改后的sql

("SELECT * FROM `extension_tag_num`  WHERE `extension_tag_num`.`deleted_at` IS NULL AND ((basic_id = ? AND date BETWEEN cast(? as DATE) AND cast(? as DATE)))", tagId, dateStart, dateEnd)

此时unit test在mysql和TiDB的执行都pass了