pingcap / pingcap/tidb

Planner doesn't rewrite conflict expressions to dual in `utf8mb4_general_ci` collation

Open
#55,807 0 comments 0 reactions 0 assignees View on GitHub
report/customer sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

```
mysql> create table t(a char(1));
Query OK, 0 rows affected (0.01 sec)
mysql> explain select * from t where a = 'C' and a = 'D';
+-------------------------+----------+-----------+---------------+--------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------------+
| TableReader_7 | 0.00 | root | | data:Selection_6 |
| └─Selection_6 | 0.00 | cop[tikv] | | eq(test.t.a, "C"), eq(test.t.a, "D") |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> show variables like '%collation%';
+---------------------------------------+--------------------+
| Variable_name | Value |
+---------------------------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_bin |
| collation_server | utf8mb4_bin |
| default_collation_for_utf8mb4 | utf8mb4_bin |
| tidb_hash_exchange_with_new_collation | ON |
+---------------------------------------+--------------------+
5 rows in set (0.00 sec)

mysql> set collation_connection='utf8mb4_bin';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t where a = 'C' and a = 'D';
+-------------+---------+------+---------------+---------------+
| id | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| TableDual_5 | 0.00 | root | | rows:0 |
+-------------+---------+------+---------------+---------------+
1 row in set (0.01 sec)
```

I think when collation_connetion equals to `utf8mb4_general_ci`, the plan should also be `TableDual`

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.