Multiple digests being generated based on variation in number of elements in "IN" clause on binary column for semantically similar query

[lawrence1223freenew]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

MySQL [(none)]> use test;
Database changed
MySQL [test]> select version();
+---------------------+
| version()           |
+---------------------+
| 5.7.25-TiDB-v6.5.11 |
+---------------------+
1 row in set (0.00 sec)

MySQL [test]> CREATE TABLE test_binary(a binary(4));
Query OK, 0 rows affected (0.12 sec)

MySQL [test]> select * from test_binary where a in ( _binary 'A\ufffd\ufffd\ufffd');
Empty set (0.01 sec)

MySQL [test]> select s.DIGEST ,s.DIGEST_TEXT , s.QUERY_SAMPLE_TEXT , s.EXEC_COUNT , s.PLAN_DIGEST from information_schema.statements_summary s where s.DIGEST_TEXT like '%select%test_binary%'; 
+------------------------------------------------------------------+-----------------------------------------------------------+-----------------------------------------------------------------------+------------+------------------------------------------------------------------+
| DIGEST                                                           | DIGEST_TEXT                                               | QUERY_SAMPLE_TEXT                                                     | EXEC_COUNT | PLAN_DIGEST                                                      |
+------------------------------------------------------------------+-----------------------------------------------------------+-----------------------------------------------------------------------+------------+------------------------------------------------------------------+
| cf7c329dfd3568dd8c79aeb783471fbef8204996f9ef5287ecfa81234d5ce8a6 | select * from `test_binary` where `a` in ( (_charset) ? ) | select * from test_binary where a in ( _binary 'A\ufffd\ufffd\ufffd') |          1 | b71103cdbdd6de5ddfabb01a15604a9888fb246c09a1cf26f3e7d98a74fd14ac |
+------------------------------------------------------------------+-----------------------------------------------------------+-----------------------------------------------------------------------+------------+------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [test]> select * from test_binary where a in ( _binary 'A\ufffd\ufffd\ufffd', _binary 'A\ufffd\ufffd\uffed');
Empty set (0.00 sec)

MySQL [test]> select s.DIGEST ,s.DIGEST_TEXT , s.QUERY_SAMPLE_TEXT , s.EXEC_COUNT , s.PLAN_DIGEST from information_schema.statements_summary s where s.DIGEST_TEXT like '%select%test_binary%'; 
+------------------------------------------------------------------+--------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------+------------------------------------------------------------------+
| DIGEST                                                           | DIGEST_TEXT                                                              | QUERY_SAMPLE_TEXT                                                                                    | EXEC_COUNT | PLAN_DIGEST                                                      |
+------------------------------------------------------------------+--------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------+------------------------------------------------------------------+
| 14bfc516695c75c3345651e76ba3ce7a60fe9af5b5b4e0ae4f61fc59172c6fa3 | select * from `test_binary` where `a` in ( (_charset) ? , (_charset) ? ) | select * from test_binary where a in ( _binary 'A\ufffd\ufffd\ufffd', _binary 'A\ufffd\ufffd\uffed') |          1 | 4b0635a4915a1572a3ea0fd7b82ac442b8ec0298d39f71332a39758d569c3ea8 |
| cf7c329dfd3568dd8c79aeb783471fbef8204996f9ef5287ecfa81234d5ce8a6 | select * from `test_binary` where `a` in ( (_charset) ? )                | select * from test_binary where a in ( _binary 'A\ufffd\ufffd\ufffd')                                |          1 | b71103cdbdd6de5ddfabb01a15604a9888fb246c09a1cf26f3e7d98a74fd14ac |
+------------------------------------------------------------------+--------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------+------------------------------------------------------------------+
2 rows in set (0.00 sec)

MySQL [test]> select * from test_binary where a in ( _binary 'A\ufffd\ufffd\ufffd', _binary 'A\ufffd\ufffd\uffed', _binary 'A\ufffd\ufffd\uffdd');
Empty set (0.00 sec)

MySQL [test]> select s.DIGEST ,s.DIGEST_TEXT , s.QUERY_SAMPLE_TEXT , s.EXEC_COUNT , s.PLAN_DIGEST from information_schema.statements_summary s where s.DIGEST_TEXT like '%select%test_binary%'; 
+------------------------------------------------------------------+-----------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+------------+------------------------------------------------------------------+
| DIGEST                                                           | DIGEST_TEXT                                                                             | QUERY_SAMPLE_TEXT                                                                                                                   | EXEC_COUNT | PLAN_DIGEST                                                      |
+------------------------------------------------------------------+-----------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+------------+------------------------------------------------------------------+
| a2c06a5975b929045cf215cf07cfc2fb3b5dfc5ee0d05163f815d4a252a61e6a | select * from `test_binary` where `a` in ( (_charset) ? , (_charset) ? , (_charset) ? ) | select * from test_binary where a in ( _binary 'A\ufffd\ufffd\ufffd', _binary 'A\ufffd\ufffd\uffed', _binary 'A\ufffd\ufffd\uffdd') |          1 | aaf7bf0e11c5df1598811243030ab9e72b3476928807d8d0638b963692147733 |
| 14bfc516695c75c3345651e76ba3ce7a60fe9af5b5b4e0ae4f61fc59172c6fa3 | select * from `test_binary` where `a` in ( (_charset) ? , (_charset) ? )                | select * from test_binary where a in ( _binary 'A\ufffd\ufffd\ufffd', _binary 'A\ufffd\ufffd\uffed')                                |          1 | 4b0635a4915a1572a3ea0fd7b82ac442b8ec0298d39f71332a39758d569c3ea8 |
| cf7c329dfd3568dd8c79aeb783471fbef8204996f9ef5287ecfa81234d5ce8a6 | select * from `test_binary` where `a` in ( (_charset) ? )                               | select * from test_binary where a in ( _binary 'A\ufffd\ufffd\ufffd')                                                               |          1 | b71103cdbdd6de5ddfabb01a15604a9888fb246c09a1cf26f3e7d98a74fd14ac |
+------------------------------------------------------------------+-----------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+------------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

MySQL [test]> 

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

A single digest should have been generated as the query is semantically same.

3. What did you see instead (Required)

Different digests have been generated for above 3 queries, based on variation in number of elements in "IN" clause.

4. What is your TiDB version? (Required)

tested on both v6.5 and v7.1, other versions should be the same.

[xhebox]

Could be fixed by #61484, but in (_binary'3') will be in ( (_charset) ? ) still, instead of in ( ... ). This is same for queries without _binary, I think that should be a separate issue.