X-Ray failure with message "Cannot determine source table..."

[markbastian]

To reproduce:

• X-Ray the Reviews table in the sample database
• Note that "Product → Price over time" is broken
• Click the "Product → Price over time" to focus on the query to see the same behavior

See @albertoperdomo's related examples in this Slack thread.

Visual repro:

When this happens, you will see a message like so in the Metabase logs:

 :error "Cannot determine the source table or query for Field clause [:field 59 {:base-type :type/Float}]",

Here's an example broken query:

(qp/process-query
  {:database 1,
   :query {:aggregation [["sum" ["field" 59 {:base-type "type/Float"}]] 
                                        ["avg" ["field" 59 {:base-type "type/Float"}]]],
           :breakout [["field" 69 {:base-type "type/DateTime", :temporal-unit "month"}]],
           :source-table 8},
   :type "query"})
2024-02-09 16:02:29,815 WARN middleware.fix-bad-references :: Bad :field clause [:field 59 {:base-type :type/Float}] for field "PRODUCTS.PRICE" at [:aggregation :sum]: clause should have a :join-alias. Unable to infer an appropriate join. Query may not work as expected.
2024-02-09 16:02:29,816 WARN middleware.fix-bad-references :: Bad :field clause [:field 59 {:base-type :type/Float}] for field "PRODUCTS.PRICE" at [:aggregation :avg]: clause should have a :join-alias. Unable to infer an appropriate join. Query may not work as expected.
Execution error (ExceptionInfo) at metabase.query-processor.util.add-alias-info/fn$&f (add_alias_info.clj:193).
Cannot determine the source table or query for Field clause [:field 59 {:base-type :type/Float}]

The issue seems to be that when the breakout is added to the query in the x-ray code, it does not add in a :source-field or other join information since field 59 (PRICE) is from the products table, but we're querying from table 8 (REVIEWS).

When manually creating the question, the right query is:

{:database 1,
 :type     "query",
 :query    {:source-table 8,
            :aggregation  [["sum" ["field" 59 {:base-type "type/Float", :source-field 71}]]
                           ["avg" ["field" 59 {:base-type "type/Float", :source-field 71}]]],
            :breakout     [["field" 69 {:base-type "type/DateTime", :temporal-unit "month"}]]}}

Field 71 is product id.