[Bug Report] Error when using coalesce expression in question with joins

[crisptrutski]

Description

I am trying to create a custom column using a brand new question I'm creating today and getting an error. I'll save the question and add it to the slack thread

Loom

Links

• Slack File
• Stats Question

[bshepherdson]

Repro

I was able to reproduce this locally with the following setup:

• Create MBQL models "Orders Model" and "Products Model", but trivial wrappers for those two tables.
• Create a new question on Products Model, explicitly join Orders Model on Orders.PRODUCT_ID.
• Add an implicitly joined field via the FK Orders.USER_ID:
  • eg. with an expression coalesce([User -> Birth Date], [Orders Model -> Created At])

[bshepherdson]

Analysis

The trouble is in metabase.query-processor.middleware.add-implicit-joins:

• The main source is a model, so the query has a :source-query for the raw Products table.
• When implicit join clauses are added, the LHS of the condition is always just [:field 123 nil].
  • That's incorrect if the implicit join FK came from an explicit join, as is the case here.
  • Call that ERROR 1
• When the query has a :source-query, this logic adds the (broken) [:field 123 nil] to the :fields of the :source-query.
  • Call that ERROR 2
• Both of these busted field refs get patched up by a later QP step which looks for the right :join-alias to slap on such under-specified :field refs.
  • It uses the correct alias for the outer query, which fixes the ref in the implicit join's :condition (ERROR 1 fixed).
  • But there's no such field nor join in the :source-query, which causes the QP to throw, surfacing the error in the UI.

Solution

The right fix is to stop doing the add-fields-to-source step if that's not where the field should have come from. But this code is quite vague about where the FK fields are coming from - it's all powered by field IDs and looking up fields and tables. (Note that it's not sufficient to check the :source-table of the main source or any joins; they can all be multi-stage or have nested joins.)

Possibly the right way to fix this is to make adding implicit joins powered by the MBQL lib? There doesn't appear to be a quick fix.

[bshepherdson]

I don't believe this is a regression.

There's a workaround: either use the original table as the source instead of a transparent model; OR add an explicit join and use its columns in your expression/filter/etc.

With a workaround, and this apparently not a regression, I'm lowering the priority to P2. This code is due to be ported to MBQL lib in any case, and it will not have this problem.

[appleby]

Still able to reproduce in master at 7d306e5884d. Query fails with error

Cannot determine the source table or query for Field clause [:field 11 {:join-alias "Orders Model"}]

[appleby]

stats repro: https://stats.metabase.com/question/26713-repro-for-56602

[ranquild]

Can be reproduced on master with the steps https://github.com/metabase/metabase/issues/56602#issuecomment-2797871680. Hash 0c7400c81eb29c27ad21d55d6c9486b4d0668206, pre-v56

[bshepherdson]

I'll take a look at this after the recent QP refactorings, and estimate the costs of fixing this.

[camsaul]

This might have been fixed by #61168

[camsaul]

So the stats repro question runs fine now https://stats.metabase.com/question/26713-repro-for-56602

[camsaul]

Totally working for me on master, 99% sure this has been fixed by #61168