Uncertain Results by MERGE_JOIN

[bajinsheng]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 BOOL, c1 BOOL);
INSERT INTO t1 VALUES (false, true);
INSERT INTO t1 VALUES (true, true);
CREATE VIEW v0(c0, c1, c2) AS SELECT t1.c0, LOG10(t0.c0), t1.c0 FROM t0, t1;
INSERT INTO t0(c0) VALUES (3);

SELECT /*+ MERGE_JOIN(t1, t0, v0)*/v0.c2, t1.c0 FROM v0,  t0 CROSS JOIN t1 ORDER BY -v0.c1;

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

3. What did you see instead (Required)

Executing the above test case multiple times and the results are not the same.
Sometimes it returns:

c2      c0
1       0
0       0
1       1
0       1

Sometimes it returns empty: {}.

4. What is your TiDB version? (Required)

| Release Version: v7.4.0-alpha-239-g4f2f5e1061
Edition: Community
Git Commit Hash: 4f2f5e1
Git Branch: master
UTC Build Time: 2023-08-30 12:06:00
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: unistore |

[bajinsheng]

/label fuzz/sqlancer

[qw4990]

That may be caused by some bug in the execution engine. A projection operator swallows some rows, which is unexpected:

[XuHuaiyu]

After this modification, the issue is fixed

diff --git a/planner/core/rule_inject_extra_projection.go b/planner/core/rule_inject_extra_projection.go
index edd972426f..8aad3c58ea 100644
--- a/planner/core/rule_inject_extra_projection.go
+++ b/planner/core/rule_inject_extra_projection.go
@@ -322,6 +322,7 @@ func TurnNominalSortIntoProj(p PhysicalPlan, onlyColumn bool, orderByItems []*ut
        topProj.SetSchema(childPlan.Schema().Clone())
        topProj.SetChildren(bottomProj)
 
+       refine4NeighbourProj(topProj, bottomProj)
        if origChildProj, isChildProj := childPlan.(*PhysicalProjection); isChildProj {
                refine4NeighbourProj(bottomProj, origChildProj)
        }

[XuHuaiyu]

The column.Index of Projection_40 is not as expected.
Column#3.Index is 0, Column#3.Index is 2. But both of them should be 0.
Projection_40 and Projection_39 are built in TurnNominalSortIntoProj

tidb:4111 [test]> explain  SELECT /*+ MERGE_JOIN(t1, t0, v0)*/v0.c2, t1.c0 FROM v0,  t0 CROSS JOIN t1 ORDER BY -v0.c1;
+---------------------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------+
| id                                          | estRows | task      | access object | operator info                                                                                |
+---------------------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------+
| Projection_17                               | 4.00    | root      |               | Column#3->Column#12, test.t1.c0                                                              |
| └─Projection_40                             | 4.00    | root      |               | Column#3, Column#6, Column#3, test.t1.c0                                                     |
|   └─Projection_39                           | 4.00    | root      |               | Column#3, Column#6, Column#3, test.t1.c0, unaryminus(Column#6)->Column#15                    |
|     └─Projection_36                         | 4.00    | root      |               | Column#3, Column#6, Column#3, test.t1.c0                                                     |
|       └─Sort_38                             | 4.00    | root      |               | Column#6:desc                                                                                |
|         └─MergeJoin_37                      | 4.00    | root      |               | inner join                                                                                   |
|           ├─Projection_28(Build)            | 2.00    | root      |               | test.t1.c0->Column#3, log10(cast(test.t0.c0, double BINARY))->Column#6, test.t1.c0->Column#3 |
|           │ └─HashJoin_31                   | 2.00    | root      |               | CARTESIAN inner join                                                                         |
|           │   ├─TableReader_33(Build)       | 1.00    | root      |               | data:TableFullScan_32                                                                        |
|           │   │ └─TableFullScan_32          | 1.00    | cop[tikv] | table:t0      | keep order:false, stats:pseudo                                                               |
|           │   └─TableReader_35(Probe)       | 2.00    | root      |               | data:TableFullScan_34                                                                        |
|           │     └─TableFullScan_34          | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                               |
|           └─MergeJoin_22(Probe)             | 2.00    | root      |               | inner join                                                                                   |
|             ├─TableReader_27(Build)         | 2.00    | root      |               | data:TableFullScan_26                                                                        |
|             │ └─TableFullScan_26            | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                               |
|             └─TableReader_25(Probe)         | 1.00    | root      |               | data:TableFullScan_24                                                                        |
|               └─TableFullScan_24            | 1.00    | cop[tikv] | table:t0      | keep order:false, stats:pseudo                                                               |
+---------------------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)

[XuHuaiyu]

planner-related issue, I'll change the label to sig/planner

[qw4990]

I'll fix it this week.