plan-cache: join on bit column

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

prepared-plan-cache.enabled=true

use test;
drop table if exists IDT_26207;
CREATE TABLE `IDT_26207` (col1 bit(1));
insert into  IDT_26207 values(0x0), (0x1);
prepare stmt from 'select t1.col1 from IDT_26207 as t1 left join IDT_26207 as t2 on t1.col1 = t2.col1 where t1.col1 in (?, ?, ?) ';
set @a=0x01, @b=0x01, @c=0x01;
execute stmt using @a,@b,@c;
set @a=0x00, @b=0x00, @c=0x01;
execute stmt using @a,@b,@c;
select t1.col1 from IDT_26207 as t1 left join IDT_26207 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x00, 0x00, 0x01);

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

mysql> set @a=0x00, @b=0x00, @c=0x01;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a,@b,@c;
+------------+
| col1       |
+------------+
| 0x00       |
| 0x01       |
+------------+
2 row in set (0.00 sec)

mysql> select t1.col1 from IDT_26207 as t1 left join IDT_26207 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x00, 0x00, 0x01);
+------------+
| col1       |
+------------+
| 0x00       |
| 0x01       |
+------------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

release-4.0 & release-5.0 & release-5.1 & release-5.2 & master

mysql> set @a=0x00, @b=0x00, @c=0x01;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a,@b,@c;
+------------+
| col1       |
+------------+
| 0x00       |
+------------+
1 row in set (0.00 sec)

mysql> select t1.col1 from IDT_26207 as t1 left join IDT_26207 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x00, 0x00, 0x01);
+------------+
| col1       |
+------------+
| 0x00       |
| 0x01       |
+------------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

 Release Version: v5.3.0-alpha-118-ge4c58a298
Edition: Community
Git Commit Hash: e4c58a298a611f033abe3eb0c58d370780f5c5e1
Git Branch: master
UTC Build Time: 2021-09-16 02:02:31
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[Reminiscent]

mysql> prepare stmt from 'select t1.col1 from IDT_26207 as t1 left join IDT_26207 as t2 on t1.col1 = t2.col1 where t1.col1 in (?, ?, ?) ';
Query OK, 0 rows affected (0.00 sec)

mysql> set @a=0x01, @b=0x01, @c=0x01;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a,@b,@c;
+------------+
| col1       |
+------------+
| 0x01       |
+------------+
1 row in set (0.01 sec)

mysql> explain for connection 3;
+------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                                                                                                                                                                          |
+------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_9                   | 2.00    | root      |               | left outer join, equal:[eq(test.idt_26207.col1, test.idt_26207.col1)]                                                                                                                                                  |
| ├─Selection_14(Build)        | 1.60    | root      |               | not(isnull(test.idt_26207.col1)), or(eq(cast(test.idt_26207.col1, double UNSIGNED BINARY), 1), or(eq(cast(test.idt_26207.col1, double UNSIGNED BINARY), 1), eq(cast(test.idt_26207.col1, double UNSIGNED BINARY), 1))) |
| │ └─TableReader_16           | 2.00    | root      |               | data:TableFullScan_15                                                                                                                                                                                                  |
| │   └─TableFullScan_15       | 2.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                                                                                                                         |
| └─Selection_11(Probe)        | 1.60    | root      |               | eq(cast(test.idt_26207.col1, double UNSIGNED BINARY), 1)                                                                                                                                                               |
|   └─TableReader_13           | 2.00    | root      |               | data:TableFullScan_12                                                                                                                                                                                                  |
|     └─TableFullScan_12       | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                                                                                                         |
+------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> set @a=0x00, @b=0x00, @c=0x01;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a,@b,@c;
+------------+
| col1       |
+------------+
| 0x00       |
+------------+
1 row in set (0.00 sec)

mysql> explain for connection 3;
+------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                                                                                                                                                                          |
+------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_9                   | 2.00    | root      |               | left outer join, equal:[eq(test.idt_26207.col1, test.idt_26207.col1)]                                                                                                                                                  |
| ├─Selection_14(Build)        | 1.60    | root      |               | not(isnull(test.idt_26207.col1)), or(eq(cast(test.idt_26207.col1, double UNSIGNED BINARY), 0), or(eq(cast(test.idt_26207.col1, double UNSIGNED BINARY), 0), eq(cast(test.idt_26207.col1, double UNSIGNED BINARY), 1))) |
| │ └─TableReader_16           | 2.00    | root      |               | data:TableFullScan_15                                                                                                                                                                                                  |
| │   └─TableFullScan_15       | 2.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                                                                                                                         |
| └─Selection_11(Probe)        | 1.60    | root      |               | eq(cast(test.idt_26207.col1, double UNSIGNED BINARY), 0)                                                                                                                                                               |
|   └─TableReader_13           | 2.00    | root      |               | data:TableFullScan_12                                                                                                                                                                                                  |
|     └─TableFullScan_12       | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                                                                                                         |
+------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> explain select t1.col1 from IDT_26207 as t1 left join IDT_26207 as t2 on t1.col1 = t2.col1 where t1.col1 in (0x00, 0x00, 0x01);
+------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------+
| id                           | estRows | task      | access object | operator info                                                         |
+------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------+
| HashJoin_9                   | 2.00    | root      |               | left outer join, equal:[eq(test.idt_26207.col1, test.idt_26207.col1)] |
| ├─Selection_14(Build)        | 1.60    | root      |               | in(test.idt_26207.col1, 0, 0, 1), not(isnull(test.idt_26207.col1))    |
| │ └─TableReader_16           | 2.00    | root      |               | data:TableFullScan_15                                                 |
| │   └─TableFullScan_15       | 2.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                        |
| └─Selection_11(Probe)        | 1.60    | root      |               | in(test.idt_26207.col1, 0, 0, 1)                                      |
|   └─TableReader_13           | 2.00    | root      |               | data:TableFullScan_12                                                 |
|     └─TableFullScan_12       | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                        |
+------------------------------+---------+-----------+---------------+-----------------------------------------------------------------------+
7 rows in set (0.01 sec)

[Reminiscent]

The root case is when we want to push the outer table's conditions into inner table(Use the example above, conditions t1.col1 in (0x00, 0x00, 0x01) and t1.col1 = t2.col1 can deduce t2.col1 in (0x00, 0x00, 0x01)). We will remove the duplicated expression. But that is not correct, it should consider the parameter.