[multistage] physical optimizer lookup join support

[dang-stripe]

While testing the physical optimizer, we get this error when trying to invoke a lookup join:

Error log:

"message": "Received 1 error from stage N on Server_1234: Right input must be leaf operator",

Query:

WITH "source_1" AS (
  SELECT
    'v1' AS "column_1",
    1234567890 AS "column_2"
  FROM "table_3"
  LIMIT 1
)
SELECT
  /*+ "joinOptions"("join_strategy" = 'lookup') */
  a."column_1",
  a."column_2",
  b."column_3"
FROM "source_1" a
INNER JOIN "table_2" b
  ON b."column_1" = a."column_1"
 AND b."column_2" = a."column_2"

Claude's analysis:

The V2 physical optimizer (usePhysicalOptimizer=true) does not correctly handle lookup joins. When a query uses /*+ joinOptions(join_strategy='lookup') */, V2 inserts a BROADCAST exchange on the right side (dim table), which splits the dim table scan into a separate fragment from the join. At runtime, LookupJoinOperator requires rightInput instanceof LeafOperator but receives a MailboxReceiveOperator instead, throwing "Right input must be leaf operator"

cc @ankitsultana @suvodeep-pyne

[ankitsultana]

yeah I hadn't added lookup join. If you folks are considering this for production we can definitely add it.

[dang-stripe]

I'm working on adding support now and will tag you in the PR. We're testing out the optimizer right now on one our more complicated prod query patterns.

[ankitsultana]

sounds good. just a heads up that the change was fairly complicated because you want to do it in a way that will allow other optimizations to also work in conjunction with lookup joins. I skipped it at the time because I wanted to understand the exact set of requirements from a prod use-case to tune the implementation accordingly.