Do not need to push down `ColumnRef <compare_operator> null` which is always false

[Lloyd-Pottiger]

Enhancement

mysql> create table t (a int, f float);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values (1, 0.1), (2, 0.2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain select * from t where f != null;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 0.00     | root      |               | data:Selection_6               |
| └─Selection_6           | 0.00     | cop[tikv] |               | ne(test.t.f, NULL)             |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where f > null;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 0.00     | root      |               | data:Selection_6               |
| └─Selection_6           | 0.00     | cop[tikv] |               | gt(test.t.f, NULL)             |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where f >= null;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 0.00     | root      |               | data:Selection_6               |
| └─Selection_6           | 0.00     | cop[tikv] |               | ge(test.t.f, NULL)             |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where f < null;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 0.00     | root      |               | data:Selection_6               |
| └─Selection_6           | 0.00     | cop[tikv] |               | lt(test.t.f, NULL)             |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where f <= null;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 0.00     | root      |               | data:Selection_6               |
| └─Selection_6           | 0.00     | cop[tikv] |               | le(test.t.f, NULL)             |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where f = null;
+-------------+---------+------+---------------+---------------+
| id          | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| TableDual_5 | 0.00    | root |               | rows:0        |
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)

[elsa0520]

Also , we have some predicate evaluate logic when build selection operator.

But the bad thing is that we cannot evaluate x!=null to false.

By the way, I think maybe the predicate evaluate feature should be a single rewrite rule in the optimizer to handle this kind of predicate (always false).

[King-Dylan]

/assign @King-Dylan