Inconsistent Behavior When Comparing CHAR Column with Binary Values

[dash12653]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t;
create table t (a char(20) charset utf8mb4, b char(20) charset gbk, c binary(20));
insert into t values ('一', '一', 0xe4b880);
insert into t values ('一', '一', 0xd2bb);
insert into t values ('一', '一', 0xe4ba8c);
insert into t values ('一', '一', 0xb6fe);
select * from t where a >= 0xb6fe and a <= 0xb6fe; -- error
select * from t where a between 0xb6fe and 0xb6fe; -- empty set

2. What did you expect to see? (Required)

Both sql statements return the same result.

3. What did you see instead (Required)

tidb> select * from t where a >= 0xb6fe and a <= 0xb6fe;
ERROR 1105 (HY000): Cannot convert string '\xB6\xFE' from binary to utf8mb4
tidb> select * from t where a between 0xb6fe and 0xb6fe;
Empty set, 2 warnings (0.00 sec)

tidb> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 3854 | Cannot convert string '\xB6\xFE' from binary to utf8mb4 |
| Warning | 3854 | Cannot convert string '\xB6\xFE' from binary to utf8mb4 |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

master

[dash12653]

In MySQL, both queries would return an error.

mysql> select * from t where a >= 0xb6fe and a <= 0xb6fe;
ERROR 3854 (HY000): Cannot convert string '\xB6\xFE' from binary to utf8mb4
mysql> select * from t where a between 0xb6fe and 0xb6fe;
ERROR 3854 (HY000): Cannot convert string '\xB6\xFE' from binary to utf8mb4
mysql> 

[dash12653]

May be related to this

tidb/pkg/planner/core/expression_rewriter.go

Lines 2211 to 2218 in 6463db6

	// Handle enum or set. We need to know their real type to decide whether to cast them.
	lt := expression.GetAccurateCmpType(er.sctx.GetEvalCtx(), expr, lexp)
	rt := expression.GetAccurateCmpType(er.sctx.GetEvalCtx(), expr, rexp)
	enumOrSetRealTypeIsStr := lt != types.ETInt && rt != types.ETInt
	expr = expression.BuildCastCollationFunction(er.sctx, expr, coll, enumOrSetRealTypeIsStr)
	lexp = expression.BuildCastCollationFunction(er.sctx, lexp, coll, enumOrSetRealTypeIsStr)
	rexp = expression.BuildCastCollationFunction(er.sctx, rexp, coll, enumOrSetRealTypeIsStr)

[dash12653]

/label sig/planner

[ti-chi-bot]

@dash12653: The label(s) sig/planner cannot be applied. These labels are supported: fuzz/sqlancer, fuzz/comp, challenge-program, compatibility-breaker, first-time-contributor, contribution, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-release-5.4, needs-cherry-pick-release-6.1, needs-cherry-pick-release-6.5, needs-cherry-pick-release-7.1, needs-cherry-pick-release-7.5, needs-cherry-pick-release-8.1, needs-cherry-pick-release-8.5, affects-5.4, affects-6.1, affects-6.5, affects-7.1, affects-7.5, affects-8.1, affects-8.4, affects-8.5, may-affects-5.4, may-affects-6.1, may-affects-6.5, may-affects-7.1, may-affects-7.5, may-affects-8.1, may-affects-8.5.

In response to this:

/label sig/planner

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 ti-community-infra/tichi repository.

[zanmato1984]

Seems for between and, the constant is folded to null and pushed down to tikv and emits two warnings.

tidb> explain select * from t where a between 0xb6fe and 0xb6fe;
+-------------------------+---------+-----------+---------------+----------------------------------------+
| id                      | estRows | task      | access object | operator info                          |
+-------------------------+---------+-----------+---------------+----------------------------------------+
| TableReader_7           | 0.00    | root      |               | data:Selection_6                       |
| └─Selection_6           | 0.00    | cop[tikv] |               | ge(test.t.a, NULL), le(test.t.a, NULL) |
|   └─TableFullScan_5     | 4.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo         |
+-------------------------+---------+-----------+---------------+----------------------------------------+
3 rows in set, 2 warnings (0.01 sec)

tidb> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 3854 | Cannot convert string '\xB6\xFE' from binary to utf8mb4 |
| Warning | 3854 | Cannot convert string '\xB6\xFE' from binary to utf8mb4 |
+---------+------+---------------------------------------------------------+
2 rows in set (0.01 sec)

This is quite corner. Adjusting to minor.