eliminate uncessary projection if order by items of topn refer to projection items for some cases

[fzhedu]

Feature Request

the extra projection for topN are unnecessary.

mysql> set @@tidb_allow_mpp=2;
Query OK, 0 rows affected (0.00 sec)

mysql> desc SELECT table2 . col_int AS field1 , table2 . col_int_not_null AS field2 , MAX( table2 . col_int_not_null ) AS field3 , SUM( table1 . pk ) AS field4 , table2 . pk AS field5 , COUNT( table1 . col_int ) AS field6 FROM J AS table1 RIGHT JOIN B AS table2 ON table1 . pk = table2 . pk WHERE table1 . col_int_not_null IS NOT NULL GROUP BY field1, field2, field5 ORDER BY field1, field2, field3, field4, field5, field6 ASC LIMIT 10;
+--------------------------------------------+----------+-------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                         | estRows  | task              | access object | operator info                                                                                                                                                                                  |
+--------------------------------------------+----------+-------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_12                              | 10.00    | root              |               | fzh.b.col_int, fzh.b.col_int_not_null, cast(fzh.b.col_int_not_null, int(11))->Column#23, cast(fzh.j.pk, decimal(32,0) BINARY)->Column#24, fzh.b.pk, if(isnull(fzh.j.col_int), 0, 1)->Column#25 |
| └─TopN_16                                  | 10.00    | root              |               | fzh.b.col_int, fzh.b.col_int_not_null, cast(fzh.b.col_int_not_null), cast(fzh.j.pk), fzh.b.pk, if(isnull(fzh.j.col_int), 0, 1), offset:0, count:10                                             |
|   └─TableReader_75                         | 10.00    | root              |               | data:ExchangeSender_74                                                                                                                                                                         |
|     └─ExchangeSender_74                    | 10.00    | batchCop[tiflash] |               | ExchangeType: PassThrough                                                                                                                                                                      |
|       └─TopN_73                            | 10.00    | batchCop[tiflash] |               | fzh.b.col_int, fzh.b.col_int_not_null, cast(fzh.b.col_int_not_null), cast(fzh.j.pk), fzh.b.pk, if(isnull(fzh.j.col_int), 0, 1), offset:0, count:10                                             |
|         └─HashJoin_72                      | 12487.50 | batchCop[tiflash] |               | inner join, equal:[eq(fzh.j.pk, fzh.b.pk)]                                                                                                                                                     |
|           ├─ExchangeReceiver_46(Build)     | 9990.00  | batchCop[tiflash] |               |                                                                                                                                                                                                |
|           │ └─ExchangeSender_45            | 9990.00  | batchCop[tiflash] |               | ExchangeType: HashPartition, Hash Cols: fzh.j.pk                                                                                                                                               |
|           │   └─Selection_44               | 9990.00  | batchCop[tiflash] |               | not(isnull(fzh.j.col_int_not_null))                                                                                                                                                            |
|           │     └─TableFullScan_43         | 10000.00 | batchCop[tiflash] | table:table1  | keep order:false, stats:pseudo                                                                                                                                                                 |
|           └─ExchangeReceiver_49(Probe)     | 10000.00 | batchCop[tiflash] |               |                                                                                                                                                                                                |
|             └─ExchangeSender_48            | 10000.00 | batchCop[tiflash] |               | ExchangeType: HashPartition, Hash Cols: fzh.b.pk                                                                                                                                               |
|               └─TableFullScan_47           | 10000.00 | batchCop[tiflash] | table:table2  | keep order:false, stats:pseudo                                                                                                                                                                 |
+--------------------------------------------+----------+-------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)

mysql> show create table J;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| J     | CREATE TABLE `J` (
  `col_varchar_10` varchar(10) DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_varchar_10_not_null` varchar(10) NOT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_datetime` datetime DEFAULT NULL,
  `col_int_not_null` int(11) NOT NULL,
  `col_decimal` decimal(10,0) DEFAULT NULL,
  `col_datetime_not_null` datetime NOT NULL,
  `col_varchar_1024_not_null` varchar(1024) NOT NULL,
  `col_varchar_1024` varchar(1024) DEFAULT NULL,
  `col_decimal_not_null` decimal(10,0) NOT NULL,
  PRIMARY KEY (`pk`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=21 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table B;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| B     | CREATE TABLE `B` (
  `col_datetime_not_null` datetime NOT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_varchar_1024_not_null` varchar(1024) NOT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_decimal_not_null` decimal(10,0) NOT NULL,
  `col_decimal` decimal(10,0) DEFAULT NULL,
  `col_datetime` datetime DEFAULT NULL,
  `col_int_not_null` int(11) NOT NULL,
  `col_varchar_10_not_null` varchar(10) NOT NULL,
  `col_varchar_1024` varchar(1024) DEFAULT NULL,
  `col_varchar_10` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`pk`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=2 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> 

Is your feature request related to a problem? Please describe:

Describe the feature you'd like:

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[winoros]

For this case, we are not going to eliminate the topn, we should move the projection down to the topn to remove the redundant calculation here.

Take another simple case as an example. Project{a+1}->TopN{by a+1, count 10}->Scan(t). Notice that the top-n is just a calculator, it won't change the output of its child. So its output is a instead of a+1 since its child Scan(t)'s output is a. @fzhedu If you get me, you can change the issue title to a more correct one.