eliminate ifnull projection on not null column

[lysu]

Feature Request

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

some framework no-brain generate ifnull(col, x) for all return column even column is not null

create table ttt (id int not null);

explain select ifnull(id, 0) from ttt;
+---------------------+----------+------+--------------------------------------------------------------+
| id                  | count    | task | operator info                                                |
+---------------------+----------+------+--------------------------------------------------------------+
| Projection_3        | 10000.00 | root | ifnull(test.ttt.id, 0)                                       |
| └─TableReader_5     | 10000.00 | root | data:TableScan_4                                             |
|   └─TableScan_4     | 10000.00 | cop  | table:ttt, range:[-inf,+inf], keep order:false, stats:pseudo |
+---------------------+----------+------+--------------------------------------------------------------+
3 rows in set (0.00 sec)

Describe the feature you'd like:

eliminate ifnull() for not null column.

+-------------------+----------+------+--------------------------------------------------------------+
| id                | count    | task | operator info                                                |
+-------------------+----------+------+--------------------------------------------------------------+
| TableReader_5     | 10000.00 | root | data:TableScan_4                                             |
| └─TableScan_4     | 10000.00 | cop  | table:ttt, range:[-inf,+inf], keep order:false, stats:pseudo |
+-------------------+----------+------+--------------------------------------------------------------+

Describe alternatives you've considered:

it's better eliminate impl can reduce plan node allocation- -?

Teachability, Documentation, Adoption, Migration Strategy:

mainly useful for some no-brain generate code framework's user.

[winoros]

Actually, the thing you described contains two step:

• Simplify ifnull function since it's arg is always not null.
• Eliminate project since it's arg is all column.

[zz-jason]

@lzmhhh123 PTAL

[zhexuany]

@lysu I believe the correct plan should be the following:

mysql> explain select ifnull(id, 0), ifnull(id, 1) from ttt;
+---------------------+----------+------+--------------------------------------------------------------+
| id                  | count    | task | operator info                                                |
+---------------------+----------+------+--------------------------------------------------------------+
| Projection_3        | 10000.00 | root | test.ttt.id                                   |
| └─TableReader_5     | 10000.00 | root | data:TableScan_4                                             |
|   └─TableScan_4     | 10000.00 | cop  | table:ttt, range:[-inf,+inf], keep order:false, stats:pseudo |
+---------------------+----------+------+--------------------------------------------------------------+
3 rows in set (0.00 sec)

Proj can be eliminated later.