pingcap / pingcap/tidb

View support projection pull up

Open
#37,510 0 comments 0 reactions 0 assignees View on GitHub
sig/planner type/enhancement type/question
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
```
CREATE TABLE `test1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`cust_no` varchar(30) NOT NULL COMMENT,
`cust_name` varchar(120) DEFAULT NULL,
`id1` varchar(3) DEFAULT NULL ,
`id2` varchar(50) DEFAULT NULL,
`id3` varchar(50) DEFAULT NULL,
`id4` varchar(3) NOT NULL DEFAULT '0',
`id5` varchar(3) DEFAULT NULL ,
`id6` varchar(3) DEFAULT NULL,
`id6_end_date` date DEFAULT NULL,
`id7` varchar(40) DEFAULT NULL,
`id8` varchar(40) DEFAULT NULL ,
`id9` varchar(100) DEFAULT NULL,
`id10` varchar(100) DEFAULT NULL ,
`id11` varchar(100) DEFAULT NULL,
`id12` varchar(2) NOT NULL DEFAULT '0',
`id13` varchar(5) NOT NULL DEFAULT '0',
`id14` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`id15` varchar(12) NOT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */,
UNIQUE KEY `idx_I0` (`cust_no`),
KEY `test1_I1` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

CREATE TABLE `test2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`risk_id` varchar(32) NOT NULL ,
`cust_no` varchar(100) DEFAULT NULL ,
`id1` varchar(2) DEFAULT NULL,
`id2` varchar(100) DEFAULT NULL ,
`cust_name` varchar(160) DEFAULT NULL,
`ud1` varchar(2) DEFAULT NULL,
`ud2` varchar(8) DEFAULT NULL,
`ud3` varchar(100) DEFAULT NULL,
`ud4` varchar(200) DEFAULT NULL,
`ud5` varchar(20) NOT NULL,
`ud6` varchar(2) NOT NULL,
`ud7` varchar(2) NOT NULL ,
`ud8` datetime NOT NULL COMMENT,
`ud9` datetime DEFAULT NULL,
`ud10` datetime DEFAULT NULL,
`ud11` varchar(10) DEFAULT NULL,
`ud12` varchar(3000) DEFAULT NULL,
`ud13` varchar(300) DEFAULT NULL,
`ud14` varchar(3000) DEFAULT NULL,
`ud15` varchar(2) DEFAULT NULL ,
`id7` varchar(32) DEFAULT NULL,
`oper_no` varchar(20) DEFAULT NULL,
`node_no` varchar(20) DEFAULT NULL ,
`node_no2` varchar(20) DEFAULT NULL,
`node_no3` varchar(20) DEFAULT NULL,
`check_oper` varchar(20) DEFAULT NULL,
`check_date` datetime DEFAULT NULL,
`id15` varchar(20) DEFAULT NULL,
`id14` datetime DEFAULT NULL,
`reg_node` varchar(20) DEFAULT NULL,
`deal_flag` varchar(2) NOT NULL DEFAULT '00',
`update_oper` varchar(20) DEFAULT NULL,
`publish_type` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */,
UNIQUE KEY `test2_OO_I0` (`risk_id`),
KEY `test2_OO_I1` (`cust_no`,`ud5`,`ud7`,`ud13`),
KEY `bsg_risk_warn_alertno_ud7` (`ud5`,`id`,`ud7`),
KEY `test2_I2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE VIEW `vw_test1` (`cust_no`, `id1`, `id2`, `id3`, `id4`, `id5`, `id6`, `id6_end_date`, `outer_lvl`, `outer_full_lvl`) AS SELECT `l`.`cust_no` AS `cust_no`,`l`.`id1`,`l`.`id2`,`l`.`id3`,`id4` AS `id4`,`id5` AS `id5`,`id6` AS `id6`,`id6_end_date` AS `id6_end_date`,CASE WHEN ((`id6` IS NOT NULL) AND (`id6_end_date` IS NOT NULL) AND (`id6_end_date`>=CURDATE())) THEN `id6` ELSE GREATEST(`id4`, IFNULL(`id5`, _UTF8MB4'0')) END AS `outer_lvl`,CASE WHEN ((`id6` IS NOT NULL) AND (`id6_end_date` IS NOT NULL) AND (`id6_end_date`>=CURDATE())) THEN `id6` ELSE GREATEST(`id4`, IFNULL(`id5`, _UTF8MB4'0')) END AS `outer_full_lvl` FROM `riskwarngldb`.`test1` AS `l`;

explain select /* from test2 t1 left join vw_test1 t2 on t1.cust_no = t2.cust_no where t1.ud5 in ('YJ230202007');
```

### 2. What did you expect to see? (Required)
```
+--------------------------------------+---------+-----------+------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+---------+-----------+------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_6 | 3.00 | root | | riskwarngldb.test2.id, riskwarngldb.test2.risk_id, riskwarngldb.test2.cust_no, riskwarngldb.test2.id1, riskwarngldb.test2.id2, riskwarngldb.test2.cust_name, riskwarngldb.test2.ud1, riskwarngldb.test2.ud2, riskwarngldb.test2.ud3, riskwarngldb.test2.ud4, riskwarngldb.test2.ud5, riskwarngldb.test2.ud6, riskwarngldb.test2.ud7, riskwarngldb.test2.ud8, riskwarngldb.test2.ud9, riskwarngldb.test2.ud10, riskwarngldb.test2.ud11, riskwarngldb.test2.ud12, riskwarngldb.test2.ud13, riskwarngldb.test2.ud14, riskwarngldb.test2.ud15, riskwarngldb.test2.id7, riskwarngldb.test2.oper_no, riskwarngldb.test2.node_no, riskwarngldb.test2.node_no2, riskwarngldb.test2.node_no3, riskwarngldb.test2.check_oper, riskwarngldb.test2.check_date, riskwarngldb.test2.id15, riskwarngldb.test2.id14, riskwarngldb.test2.reg_node, riskwarngldb.test2.deal_flag, riskwarngldb.test2.update_oper, riskwarngldb.test2.publish_type, riskwarngldb.test1.cust_no, riskwarngldb.test1.id1, riskwarngldb.test1.id2, riskwarngldb.test1.id3, riskwarngldb.test1.id4, riskwarngldb.test1.id5, riskwarngldb.test1.id6, riskwarngldb.test1.id6_end_date, case(and(and(not(isnull(riskwarngldb.test1.id6)), not(isnull(riskwarngldb.test1.id6_end_date))), ge(riskwarngldb.test1.id6_end_date, 2022-08-31)), riskwarngldb.test1.id6, greatest(riskwarngldb.test1.id4, ifnull(riskwarngldb.test1.id5, 0)))->Column#56, case(and(and(not(isnull(riskwarngldb.test1.id6)), not(isnull(riskwarngldb.test1.id6_end_date))), ge(riskwarngldb.test1.id6_end_date, 2022-08-31)), riskwarngldb.test1.id6, greatest(riskwarngldb.test1.id4, ifnull(riskwarngldb.test1.id5, 0)))->Column#57 |
| └─IndexJoin_11 | 3.00 | root | | left outer join, inner:IndexLookUp_10, outer key:riskwarngldb.test2.cust_no, inner key:riskwarngldb.test1.cust_no, equal cond:eq(riskwarngldb.test2.cust_no, riskwarngldb.test1.cust_no) |
| ├─IndexLookUp_36(Build) | 3.00 | root | | |
| │ ├─IndexRangeScan_34(Build) | 3.00 | cop[tikv] | table:t1, index:bsg_risk_warn_alertno_ud7(ud5, id, ud7) | range:["YJ230202007","YJ230202007"], keep order:false |
| │ └─TableRowIDScan_35(Probe) | 3.00 | cop[tikv] | table:t1 | keep order:false |
| └─IndexLookUp_10(Probe) | 1.00 | root | | |
| ├─IndexRangeScan_8(Build) | 1.00 | cop[tikv] | table:l, index:idx_I0(cust_no) | range: decided by [eq(riskwarngldb.test1.cust_no, riskwarngldb.test2.cust_no)], keep order:false |
| └─TableRowIDScan_9(Probe) | 1.00 | cop[tikv] | table:l | keep order:false |
+--------------------------------------+---------+-----------+------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
```
### 3. What did you see instead (Required)
```
+------------------------------------+------------+-----------+------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+------------+-----------+------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_11 | 3.00 | root | | left outer join, equal:[eq(riskwarngldb.test2.cust_no, Column#37)] |
| ├─IndexLookUp_27(Build) | 3.00 | root | | |
| │ ├─IndexRangeScan_25(Build) | 3.00 | cop[tikv] | table:t1, index:bsg_risk_warn_alertno_ud7(ud5, id, ud7) | range:["YJ230202007","YJ230202007"], keep order:false |
| │ └─TableRowIDScan_26(Probe) | 3.00 | cop[tikv] | table:t1 | keep order:false |
| └─Projection_28(Probe) | 4805712.00 | root | | riskwarngldb.test1.cust_no, riskwarngldb.test1.id1, riskwarngldb.test1.id2, riskwarngldb.test1.id3, riskwarngldb.test1.id4, riskwarngldb.test1.id5, riskwarngldb.test1.id6, riskwarngldb.test1.id6_end_date, case(and(and(not(isnull(riskwarngldb.test1.id6)), not(isnull(riskwarngldb.test1.id6_end_date))), ge(riskwarngldb.test1.id6_end_date, 2022-08-31)), riskwarngldb.test1.id6, greatest(riskwarngldb.test1.id4, ifnull(riskwarngldb.test1.id5, 0)))->Column#56, case(and(and(not(isnull(riskwarngldb.test1.id6)), not(isnull(riskwarngldb.test1.id6_end_date))), ge(riskwarngldb.test1.id6_end_date, 2022-08-31)), riskwarngldb.test1.id6, greatest(riskwarngldb.test1.id4, ifnull(riskwarngldb.test1.id5, 0)))->Column#57 |
| └─TableReader_30 | 4805712.00 | root | | data:TableFullScan_29 |
| └─TableFullScan_29 | 4805712.00 | cop[tikv] | table:l | keep order:false |
+------------------------------------+------------+-----------+------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

```
### 4. What is your TiDB version? (Required)
v6.2.0

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.