Field references inside and outside of the subquery should be distinguished

[metamben]

Describe the bug
References to the results of nested queries is incorrect when the result is produced by applying a non-idempotent function.

For example, the following (nonsensical) query

is sent as the following MBQL query to the backend:

{:database 353,
  :query
  {:source-query
   {:source-table 866, :aggregation [["count"]], :breakout [["field" 4906 {:temporal-unit "month-of-year"}]]},
   :aggregation [["count"]],
   :breakout [["field" "LAST_LOGIN" {:base-type "type/DateTime", :temporal-unit "month-of-year"}]]},
  :type "query",
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}}

The backend then tries to extract the month from the LAST_LOGIN result of the subquery which fails.

A more complicated example having the same problem can be found in #17769.

Potential solution

References inside the query should be distinguished from the references to the results of the subquery. For example,

;; for referring to the column at the same level of the query
field_ref => [:field 1 {:temporal-unit :month-of-year}]

;; for referring to the column if you nest the query
field_ref_source_query => [:field "my_field" {:base-type :type/Integer}]

Dependencies

Implementing the fix should be much easier after the completion of #proj-metabase-lib because then the changes will mostly be confined to the Clojure world and few additional frontend changes will be necessary.

[camsaul]

I think we can address this as part of #28689

[camsaul]

This is basically the same underlying issue as #29763

[ranquild]

The issue still exists. Steps with the sample DB:

[kamilmielnik]

Reproducible in master at 78cb288.