errors in results with 4 cartesian products + only true filter

[bajinsheng]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT , c2 INT DEFAULT 1);
INSERT INTO t0(c0) VALUES (2), (3), (4);
CREATE VIEW v0(c0) AS SELECT 5 FROM t0;
CREATE VIEW v1(c0) AS SELECT 6 FROM t0;
CREATE VIEW v2(c0) AS SELECT false FROM v0, t0;

SELECT SUM(v2.c0) FROM v2, t0, v0, v1; -- {0}
SELECT SUM(v2.c0) FROM v2, t0, v0, v1 WHERE INET6_ATON(7) IS NULL; -- {486}

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

The second query should not return more results than the first query.

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

| Release Version: v7.6.0-alpha-347-g598ccada40-dirty
Edition: Community
Git Commit Hash: 598ccada4065e4871bc5bce53a039cbb598a6d82
Git Branch: master
UTC Build Time: 2023-12-01 01:44:42
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: unistore |

[SeaRise]

Minimum case

use test;
drop table if exists t0;
CREATE TABLE t0(c INT);
INSERT INTO t0 VALUES (0);
drop view if exists v0;
CREATE VIEW v0(cc0) AS SELECT 0 FROM t0;
SELECT v1.cc0 FROM t0, t0 as t1, v0, v0 as v1 WHERE cast(null as json) IS NULL;
+-----+
| cc0 |
+-----+
|   1 |
+-----+

expect 0 instead of 1, and tidb/tiflash both return 1.

[SeaRise]

Unrelated to INET6_ATON, related to multiple cartesian join.

[SeaRise]

SQL with incorrect result 1 :
SELECT v1.cc0 FROM t0, t0 as t1, v0, v0 as v1 WHERE cast(null as json) IS NULL;
plan:

 [HashJoin_21 4096000000000000.00 root  CARTESIAN inner join]
[├─HashJoin_22(Build) 64000000.00 root  CARTESIAN inner join]
[│ ├─TableReader_29(Build) 8000.00 root  data:Selection_28]
[│ │ └─Selection_28 8000.00 cop[tikv]  isnull(cast(cast(NULL, json BINARY), var_string(4194304)))]
[│ │   └─TableFullScan_27 10000.00 cop[tikv] table:t1 keep order:false, stats:pseudo]
[│ └─TableReader_26(Probe) 8000.00 root  data:Selection_25]
[│   └─Selection_25 8000.00 cop[tikv]  isnull(cast(cast(NULL, json BINARY), var_string(4194304)))]
[│     └─TableFullScan_24 10000.00 cop[tikv] table:t0 keep order:false, stats:pseudo]
[└─HashJoin_31(Probe) 64000000.00 root  CARTESIAN inner join]
[  ├─Selection_32(Build) 8000.00 root  isnull(cast(cast(NULL, json BINARY), var_string(4194304)))]
[  │ └─TableReader_35 10000.00 root  data:TableFullScan_34]
[  │   └─TableFullScan_34 10000.00 cop[tikv] table:t0 keep order:false, stats:pseudo]
[  └─Selection_36(Probe) 8000.00 root  isnull(cast(cast(NULL, json BINARY), var_string(4194304)))]
[    └─Projection_37 10000.00 root  0->Column#10]
[      └─TableReader_39 10000.00 root  data:TableFullScan_38]
[        └─TableFullScan_38 10000.00 cop[tikv] table:t0 keep order:false, stats:pseudo]

HashJoin_31 in the plan outputs 1 instead of 0.
Because innerJoiner.tryToMatchInners generates the wrong join_result_chunk.
innerJoiner.tryToMatchInners-> outerIsRight: true probe side rows: 0 build side rows: 1 lUsed: [0] rUsed: []
_tidb_rowid in the build side to be output incorrectly and 0 from view v0 to be discarded.

SQL with correct result 0 :
SELECT v1.c FROM t0, t0 as t1, v0, v0 as v1;
plan:

 [HashJoin_18 10000000000000000.00 root  CARTESIAN inner join]
[├─HashJoin_19(Build) 100000000.00 root  CARTESIAN inner join]
[│ ├─TableReader_24(Build) 10000.00 root  data:TableFullScan_23]
[│ │ └─TableFullScan_23 10000.00 cop[tikv] table:t1 keep order:false, stats:pseudo]
[│ └─TableReader_22(Probe) 10000.00 root  data:TableFullScan_21]
[│   └─TableFullScan_21 10000.00 cop[tikv] table:t0 keep order:false, stats:pseudo]
[└─HashJoin_25(Probe) 100000000.00 root  CARTESIAN inner join]
[  ├─Projection_30(Build) 10000.00 root  0->Column#10]
[  │ └─TableReader_32 10000.00 root  data:TableFullScan_31]
[  │   └─TableFullScan_31 10000.00 cop[tikv] table:t0 keep order:false, stats:pseudo]
[  └─Projection_27(Probe) 10000.00 root  1->Column#15]
[    └─TableReader_29 10000.00 root  data:TableFullScan_28]
[      └─TableFullScan_28 10000.00 cop[tikv] table:t0 keep order:false, stats:pseudo]

The HashJoin_25 in plan outputs 0, which is the correct result.
innerJoiner.tryToMatchInners generates the wrong join_result_chunk.
innerJoiner.tryToMatchInners-> outerIsRight: false probe side rows: 1 build side rows: 0 lUsed: rUsed: 0

So the parameters of joiner are set incorrectly, maybe is outerIsRight.

[XuHuaiyu]

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