Incorrect subquery expansion leads to wrong results

[King-Dylan]

Bug Report

Incorrect subquery expansion leads to wrong results
Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t1` (
  `data_status` tinyint(1) DEFAULT '0',
  `partition` tinyint(255) unsigned DEFAULT NULL);
insert into t1 values(1,1);
CREATE TABLE `t2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `routing_rule_switch` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
);
insert into t2 values(1,0);
CREATE TABLE `t3` (
  `fk_id` bigint(20) DEFAULT NULL,
  `offer_pbu_id` varchar(255) DEFAULT NULL ,
  `market_id` smallint(6) DEFAULT NULL ,
  `te_partition` tinyint(255) DEFAULT NULL ,
   UNIQUE KEY `t_pbu_partition_id` (`offer_pbu_id`,`market_id`,`te_partition`)
);
insert into t3 values(8,'a',3,6);

SELECT tt.market_id,tt.offer_pbu_id FROM t3 tt  Right JOIN 
(select pp.offer_pbu_id, pp.market_id, t.partition_no FROM 
(select p.offer_pbu_id, p.market_id from t3 p 
INNER JOIN t2 e ON p.fk_id = e.id AND e.routing_rule_switch = 1 ) pp ,(select `partition` as partition_no from t1) t) o 
ON tt.market_id = o.market_id 
AND tt.offer_pbu_id = o.offer_pbu_id 
AND tt.te_partition = o.partition_no;

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

Empty set (0.01 sec)

mysql> explain select pp.offer_pbu_id, pp.market_id, t.partition_no FROM  (select p.offer_pbu_id, p.market_id from t3 p  INNER JOIN t2 e ON p.fk_id = e.id AND e.routing_rule_switch = 1  GROUP BY p.offer_pbu_id, p.market_id) pp ,(select `partition` as partition_no from t1) t;
+----------------------------------------+---------+-----------+---------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows | task      | access object                                                             | operator info                                                                                                                                                                                                                                                                                                                           |
+----------------------------------------+---------+-----------+---------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_16                          | 0.00    | root      |                                                                           | test.t3.market_id, test.t3.offer_pbu_id                                                                                                                                                                                                                                                                                                 |
| └─IndexJoin_21                         | 0.00    | root      |                                                                           | right outer join, inner:IndexReader_20, outer key:test.t3.market_id, test.t3.offer_pbu_id, test.t1.partition, inner key:test.t3.market_id, test.t3.offer_pbu_id, test.t3.te_partition, equal cond:eq(test.t1.partition, test.t3.te_partition), eq(test.t3.market_id, test.t3.market_id), eq(test.t3.offer_pbu_id, test.t3.offer_pbu_id) |
|   ├─HashJoin_37(Build)                 | 0.00    | root      |                                                                           | CARTESIAN inner join                                                                                                                                                                                                                                                                                                                    |
|   │ ├─HashJoin_50(Build)               | 0.00    | root      |                                                                           | inner join, equal:[eq(test.t2.id, test.t3.fk_id)]                                                                                                                                                                                                                                                                                       |
|   │ │ ├─TableReader_56(Build)          | 0.00    | root      |                                                                           | data:Selection_55                                                                                                                                                                                                                                                                                                                       |
|   │ │ │ └─Selection_55                 | 0.00    | cop[tikv] |                                                                           | eq(test.t2.routing_rule_switch, 1)                                                                                                                                                                                                                                                                                                      |
|   │ │ │   └─TableFullScan_54           | 1.00    | cop[tikv] | table:e                                                                   | keep order:false                                                                                                                                                                                                                                                                                                                        |
|   │ │ └─TableReader_53(Probe)          | 1.00    | root      |                                                                           | data:Selection_52                                                                                                                                                                                                                                                                                                                       |
|   │ │   └─Selection_52                 | 1.00    | cop[tikv] |                                                                           | not(isnull(test.t3.fk_id))                                                                                                                                                                                                                                                                                                              |
|   │ │     └─TableFullScan_51           | 1.00    | cop[tikv] | table:p                                                                   | keep order:false                                                                                                                                                                                                                                                                                                                        |
|   │ └─TableReader_58(Probe)            | 1.00    | root      |                                                                           | data:TableFullScan_57                                                                                                                                                                                                                                                                                                                   |
|   │   └─TableFullScan_57               | 1.00    | cop[tikv] | table:t1                                                                  | keep order:false                                                                                                                                                                                                                                                                                                                        |
|   └─IndexReader_20(Probe)              | 1.00    | root      |                                                                           | index:Selection_19                                                                                                                                                                                                                                                                                                                      |
|     └─Selection_19                     | 1.00    | cop[tikv] |                                                                           | not(isnull(test.t3.market_id)), not(isnull(test.t3.offer_pbu_id)), not(isnull(test.t3.te_partition))                                                                                                                                                                                                                                    |
|       └─IndexRangeScan_18              | 1.00    | cop[tikv] | table:tt, index:t_pbu_partition_id(offer_pbu_id, market_id, te_partition) | range: decided by [eq(test.t3.offer_pbu_id, test.t3.offer_pbu_id) eq(test.t3.market_id, test.t3.market_id) eq(test.t3.te_partition, test.t1.partition)], keep order:false                                                                                                                                                               |
+----------------------------------------+---------+-----------+---------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)

3. What did you see instead (Required)

+-----------+--------------+
| market_id | offer_pbu_id |
+-----------+--------------+
|      NULL | NULL         |
+-----------+--------------+
1 row in set (0.00 sec)
+----------------------------------------+---------+-----------+---------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows | task      | access object                                                             | operator info                                                                                                                                                                                                                                     |
+----------------------------------------+---------+-----------+---------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_17                          | 1.00    | root      |                                                                           | test.t3.market_id, test.t3.offer_pbu_id                                                                                                                                                                                                           |
| └─HashJoin_19                      | 1.00    | root      |                                                                           | right outer join, equal:[eq(test.t3.te_partition, test.t1.partition)]                                                                                                                                                                             |
|   ├─IndexJoin_25(Build)            | 0.00    | root      |                                                                           | right outer join, inner:IndexReader_24, outer key:test.t3.market_id, test.t3.offer_pbu_id, inner key:test.t3.market_id, test.t3.offer_pbu_id, equal cond:eq(test.t3.market_id, test.t3.market_id), eq(test.t3.offer_pbu_id, test.t3.offer_pbu_id) |
|   │ ├─HashJoin_65(Build)         | 0.00    | root      |                                                                           | inner join, equal:[eq(test.t2.id, test.t3.fk_id)]                                                                                                                                                                                                 |
|   │ │ ├─TableReader_71(Build)  | 0.00    | root      |                                                                           | data:Selection_70                                                                                                                                                                                                                                 |
|   │ │ │ └─Selection_70       | 0.00    | cop[tikv] |                                                                           | eq(test.t2.routing_rule_switch, 1)                                                                                                                                                                                                                |
|   │ │ │   └─TableFullScan_69 | 1.00    | cop[tikv] | table:e                                                                   | keep order:false, stats:pseudo                                                                                                                                                                                                                    |
|   │ │ └─TableReader_68(Probe)  | 1.00    | root      |                                                                           | data:Selection_67                                                                                                                                                                                                                                 |
|   │ │   └─Selection_67         | 1.00    | cop[tikv] |                                                                           | not(isnull(test.t3.fk_id))                                                                                                                                                                                                                        |
|   │ │     └─TableFullScan_66   | 1.00    | cop[tikv] | table:p                                                                   | keep order:false, stats:pseudo                                                                                                                                                                                                                    |
|   │ └─IndexReader_24(Probe)      | 1.00    | root      |                                                                           | index:Selection_23                                                                                                                                                                                                                                |
|   │   └─Selection_23             | 1.00    | cop[tikv] |                                                                           | not(isnull(test.t3.market_id)), not(isnull(test.t3.offer_pbu_id))                                                                                                                                                                                 |
|   │     └─IndexRangeScan_22      | 1.00    | cop[tikv] | table:tt, index:t_pbu_partition_id(offer_pbu_id, market_id, te_partition) | range: decided by [eq(test.t3.offer_pbu_id, test.t3.offer_pbu_id) eq(test.t3.market_id, test.t3.market_id) not(isnull(test.t3.te_partition))], keep order:false, stats:pseudo                                                                     |
|   └─TableReader_76(Probe)          | 1.00    | root      |                                                                           | data:TableFullScan_75                                                                                                                                                                                                                             |
|     └─TableFullScan_75             | 1.00    | cop[tikv] | table:t1                                                                  | keep order:false, stats:pseudo                                                                                                                                                                                                                    |
+----------------------------------------+---------+-----------+---------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.1.0
Edition: Community
Git Commit Hash: 1a89dec
Git Branch: heads/refs/tags/v6.1.0
UTC Build Time: 2022-06-05 05:15:11
GoVersion: go1.18.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)