executor: wrong results for outer join in a case

[fzhedu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql -h172.16.5.85 -P52324 -uroot -D subquery_agg10000

SELECT count(*), table1 . `pk` = 
    (SELECT avg( SUBQUERY5_t1 . `col_tinyint_not_null` ) AS SUBQUERY5_field1
    FROM `R` AS SUBQUERY5_t1 RIGHT OUTER
    JOIN `X` AS SUBQUERY5_t2
        ON (SUBQUERY5_t2 . `col_varchar_64` = SUBQUERY5_t1 . `col_varchar_key` ) ) AS field5
FROM `T` AS table1
RIGHT JOIN 
    (SELECT SUBQUERY6_t1 . *
    FROM `Q` AS SUBQUERY6_t1 LEFT OUTER
    JOIN `L` AS SUBQUERY6_t2
        ON (SUBQUERY6_t2 . `col_char_64` = SUBQUERY6_t1 . `col_varchar_key` ) ) AS table2
LEFT JOIN `W` AS table3
    ON (table3 . `pk` = table2 . `col_int_not_null_key` )
    ON (table3 . `col_int_key` = table2 . `col_int_not_null_key` )
GROUP BY  2
ORDER BY  2,1;

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

+----------+--------+
| count(*) | field5 |
+----------+--------+
|      174 |   NULL |
|      420 |      0 |
+----------+--------+

3. What did you see instead (Required)

+----------+--------+
| count(*) | field5 |
+----------+--------+
|      594 |      0 |
+----------+--------+

4. What is your TiDB version? (Required)

master of nightly

[wshwsh12]

/assign @XuHuaiyu

[XuHuaiyu]

minor case:

drop table t,s;
create table t(id int primary key, a int);
create table s(a int);
insert into s values(1),(2);
select t.id = 1.234 from t right join s on t.a = s.a;

[XuHuaiyu]

We can see from the explain result that t.id = 1.123 is rewritten to 0 wrongly.

+-------------------------------+----------+-----------+---------------+--------------------------------------------------+
| id                            | estRows  | task      | access object | operator info                                    |
+-------------------------------+----------+-----------+---------------+--------------------------------------------------+
| Projection_5                  | 2.50     | root      |               | 0->Column#5                                      |
| └─HashJoin_7                  | 2.50     | root      |               | right outer join, equal:[eq(test.t.a, test.s.a)] |
|   ├─TableReader_12(Build)     | 2.00     | root      |               | data:TableFullScan_11                            |
|   │ └─TableFullScan_11        | 2.00     | cop[tikv] | table:s       | keep order:false, stats:pseudo                   |
|   └─TableReader_10(Probe)     | 9990.00  | root      |               | data:Selection_9                                 |
|     └─Selection_9             | 9990.00  | cop[tikv] |               | not(isnull(test.t.a))                            |
|       └─TableFullScan_8       | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                   |
+-------------------------------+----------+-----------+---------------+--------------------------------------------------+
7 rows in set (0.00 sec)

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions