Unexpected result when using string comparison `AND` `IS NULL` about crate

[jeeminso]

Thanks again for reporting @suyZhong , this is fixed by #15401.

[matriv]

Thank you for handling the revert @matriv. For the outer join scenario, the arg to is null was actually core.users_roles.user_id which I think could been some kind of a ScopedSymbol(or AliasSymbol?) such that it would be prevented from is null normalization. WDYT? If sounds feasible, this could be something to look into during feature freeze.

We should not optimize/normalize the is null/is not null of a where clause, when it acts on a column of the table on the "outer" side of the join, I don't think AliasSymbol or so is enough to catch that.

[matriv]

Also exists in 5.5.2.

[jeeminso]

Thanks a lot for reporting, @suyZhong!

SELECT * FROM t1 WHERE NOT ((t1.c0 != '1' )AND (t1.c0 IS NULL)); is equivalent to SELECT * FROM t1 WHERE NOT (t1.c0 IS NULL); but the former uses get (invalid optimization from collect attempting to utilize the primary key in the where clause) while latter uses collect operator:

cr> EXPLAIN SELECT * FROM t1 WHERE NOT ((t1.c0 != '1' )AND (t1.c0 IS NULL)); 
                                                                                                                 
+--------------------------------------------------------------------------------------+
| QUERY PLAN                                                                           |
+--------------------------------------------------------------------------------------+
| Get[doc.t1 | c0 | DocKeys{'1'} | (NOT ((NOT (c0 = '1')) AND (c0 IS NULL)))] (rows=1) |
+--------------------------------------------------------------------------------------+

cr> EXPLAIN SELECT * FROM t1 WHERE NOT ((t1.c0 IS NULL)); 
                                                                                                                 
+------------------------------------------------------------+
| QUERY PLAN                                                 |
+------------------------------------------------------------+
| Collect[doc.t1 | [c0] | (NOT (c0 IS NULL))] (rows=unknown) |
+------------------------------------------------------------+

[matriv]

The fix causes issues with outer joins and it's being reverted with: #15468

[jeeminso]

The fix causes issues with outer joins and it's being reverted with: #15468

Thank you for handling the revert @matriv. For the outer join scenario, the arg to is null was actually core.users_roles.user_id which I think could been some kind of a ScopedSymbol(or AliasSymbol?) such that it would be prevented from is null normalization. WDYT? If sounds feasible, this could be something to look into during feature freeze.

[jeeminso]

Fixed by #15487.