planner: predicates that should not be pushed down are pushed down, causing semantic changes in query results

[Reminiscent]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1(a int, b int);
create table t2(a int, b int);
explain select * from t1 join t2 on t1.a = t2.a where rand() > 0.5;

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

In postgres, the filter will be done after the join finshed.

yanchengpeng=# create table t1(a int, b int);
CREATE TABLE
yanchengpeng=# create table t2(a int, b int);
CREATE TABLE
yanchengpeng=# explain select * from t1 join t2 on t1.a = t2.a where random() > 0.5;
                            QUERY PLAN                            
------------------------------------------------------------------
 Merge Join  (cost=317.01..839.07 rows=8513 width=16)
   Merge Cond: (t1.a = t2.a)
   Join Filter: (random() > '0.5'::double precision)
   ->  Sort  (cost=158.51..164.16 rows=2260 width=8)
         Sort Key: t1.a
         ->  Seq Scan on t1  (cost=0.00..32.60 rows=2260 width=8)
   ->  Sort  (cost=158.51..164.16 rows=2260 width=8)
         Sort Key: t2.a
         ->  Seq Scan on t2  (cost=0.00..32.60 rows=2260 width=8)
(9 行记录)

3. What did you see instead (Required)

mysql> create table t1(a int, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(a int, b int);
Query OK, 0 rows affected (0.02 sec)

mysql> explain select * from t1 join t2 on t1.a = t2.a where rand() > 0.5;
+--------------------------------+----------+-----------+---------------+----------------------------------------------+
| id                             | estRows  | task      | access object | operator info                                |
+--------------------------------+----------+-----------+---------------+----------------------------------------------+
| HashJoin_25                    | 9990.00  | root      |               | inner join, equal:[eq(test.t1.a, test.t2.a)] |
| ├─Selection_37(Build)          | 7992.00  | root      |               | gt(rand(), 0.5)                              |
| │ └─TableReader_40             | 9990.00  | root      |               | data:Selection_39                            |
| │   └─Selection_39             | 9990.00  | cop[tikv] |               | not(isnull(test.t2.a))                       |
| │     └─TableFullScan_38       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo               |
| └─Selection_29(Probe)          | 7992.00  | root      |               | gt(rand(), 0.5)                              |
|   └─TableReader_32             | 9990.00  | root      |               | data:Selection_31                            |
|     └─Selection_31             | 9990.00  | cop[tikv] |               | not(isnull(test.t1.a))                       |
|       └─TableFullScan_30       | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo               |
+--------------------------------+----------+-----------+---------------+----------------------------------------------+
9 rows in set, 10 warnings (0.00 sec)

some predicates are not safe for pushdown, but are currently not handled and still get pushed down, which can change evaluation semantics and lead to incorrect results; this has been observed with non-deterministic functions like RAND(), and similar issues may also affect side-effect functions (e.g. sequence functions) and therefore require special handling.

4. What is your TiDB version? (Required)

lastest master