Gábor E. Gévay

A customer ran into this recently: https://materializeinc.slack.com/archives/C02PPB50ZHS/p1691659716977619 (which Seth also mentioned here: https://github.com/MaterializeInc/materialize/issues/21170)

I dare say we should consider adding an HIR transform as a quick fix.

If we had efficient broadcast joins, then we could look for the ``` Distinct group_by=[#0] + Project (#0) + Filter ((#0) IS NULL OR (#1) IS NULL OR (#0 =...

Workarounds ([copied from Slack](https://materializeinc.slack.com/archives/C02PPB50ZHS/p1692030115059499?thread_ts=1692024654.158569&cid=C02PPB50ZHS)): An other thing that makes the CrossJoin disappear is to filter out nulls before feeding into the NOT IN. E.g. instead of ``` CREATE TABLE t1...

One more situation where a NOT IN leads to a cross join: ``` WITH leaves AS ( SELECT * FROM mz_internal.mz_dataflow_addresses AS outer WHERE outer.address NOT IN ( SELECT inner.address[:list_length(outer.address)]...

This is also happening with the `NOT IN` in our [quickstart](https://materialize.com/docs/get-started/quickstart/#step-3-see-results-change): ``` explain SELECT buyer, count(*) FROM winning_bids WHERE buyer NOT IN (SELECT id FROM fraud_accounts) GROUP BY buyer ORDER...

I think the next step here is to check exactly how the `null` checks in `((#0) IS NULL OR (#1) IS NULL OR (#0 = #1))` end up there. Instead...

We should also check the `all_errors` stuff in `PredicatePushdown`.

Also, check https://materializeinc.slack.com/archives/C02PPB50ZHS/p1675864117612589

The relevant Postgres docs: https://www.postgresql.org/docs/9.0/sql-expressions.html#SYNTAX-EXPRESS-EVAL This is a really thorough explanation of all these issues in Postgres. (And a relevant Postgres mailing list thread: https://www.postgresql.org/message-id/flat/18400-1358382426-292278%40sneakemail.com#5094932246b593dbb7679a47b1b2c3b0 )