fanfuxiaoran commented on issue #594:
URL: https://github.com/apache/cloudberry/issues/594#issuecomment-2489978902
Found the root cause:
for the below query
```
create table t1(a int, b int not null);
create table t2(like t1);
select t1.*, t2.* from t1 full join t2 on false where (t1.b < t1.b) is null;
```
The original Algebrized query is as below
```
Algebrized query:
+--CLogicalSelect
|--CLogicalFullOuterJoin
| |--CLogicalGet "t1" ("t1"), Columns: ["a" (0), "b" (1), "ctid" (2),
"xmin" (3), "cmin" (4), "xmax" (5), "cmax" (6), "tableoid" (7), "gp_segment_id"
(8), "gp_foreign_server" (9)] Key sets: {[2,8]}
| |--CLogicalGet "t2" ("t2"), Columns: ["a" (10), "b" (11), "ctid" (12),
"xmin" (13), "cmin" (14), "xmax" (15), "cmax" (16), "tableoid" (17),
"gp_segment_id" (18), "gp_foreign_server" (19)] Key sets: {[2,8]}
| +--CScalarConst (0)
+--CScalarNullTest
+--CScalarCmp (<)
|--CScalarIdent "b" (1)
+--CScalarIdent "b" (1)
```
After `PexprEliminateSelfComparison` called (used to eliminate self
comparisons)
the Algebrized query is :
```
--CLogicalSelect
|--CLogicalFullOuterJoin
| |--CLogicalGet "t1" ("t1"), Columns: ["a" (0), "b" (1), "ctid" (2),
"xmin" (3), "cmin" (4), "xmax" (5), "cmax" (6), "tableoid" (7), "gp_segment_id"
(8), "gp_foreign_server" (9)] Key sets: {[2,8]}
| |--CLogicalGet "t2" ("t2"), Columns: ["a" (10), "b" (11), "ctid" (12),
"xmin" (13), "cmin" (14), "xmax" (15), "cmax" (16), "tableoid" (17),
"gp_segment_id" (18), "gp_foreign_server" (19)] Key sets: {[2,8]}
| +--CScalarConst (0)
+--CScalarNullTest
+--CScalarConst (0)
```
as we can see that
```
+--CScalarCmp (<)
|--CScalarIdent "b" (1)
+--CScalarIdent "b" (1)
```
has been transformed into `CScalarConst (0)`, this step is wrong.
The reason is that when checking if the `selfcomparison` can be simplified
in function `FSelfComparison`, it checks the `CColRef` IsNullable only from the
column definition. Not checking if the column is from outer join.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
