Serialization load-process does not update source-table in joins, leading to broken questions

[flamber]

Describe the bug
Serialization load-process does not update source-table in questions with explicit joins, leading to broken questions.

To Reproduce

1. Custom question > Sample Dataset > Reviews
2. Join Products
3. Save question "Q1"
4. Do the serialization process (dump and load) and login to the target instance
5. Go to the question "Q1", which fails with Value does not match schema: {:query {:joins [{:source-table (not (matches-some-precondition? a-java.lang.String))}]}} and joined table is empty in Notebook (with a couple of browser console errors too):
   

Full stacktrace

06-25 16:17:15 ERROR middleware.catch-exceptions :: Error processing query: null
{:database_id 1,
 :started_at (t/zoned-date-time "2020-06-25T16:17:14.911972+02:00[Europe/Copenhagen]"),
 :error_type :invalid-query,
 :json_query
 {:constraints {:max-results 10000, :max-results-bare-rows 2000},
  :type :query,
  :middleware nil,
  :database 1,
  :query
  {:source-table 4,
   :joins [{:fields :all, :source-table "/databases/Sample Dataset/schemas/PUBLIC/tables/PRODUCTS", :condition [:= [:field-id 36] [:joined-field "Product" [:field-id 2]]], :alias "Product"}]},
  :parameters [],
  :async? true,
  :cache-ttl nil},
 :native nil,
 :status :failed,
 :class clojure.lang.ExceptionInfo,
 :stacktrace
 ["--> util.schema$schema_core_validator$fn__20652.invoke(schema.clj:26)"
  "query_processor.middleware.validate$validate_query$fn__50166.invoke(validate.clj:9)"
  "query_processor.middleware.normalize_query$normalize$fn__46144.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__43923.invoke(add_rows_truncated.clj:36)"
  "metabase_enterprise.audit.query_processor.middleware.handle_audit_queries$handle_internal_queries$fn__31258.invoke(handle_audit_queries.clj:165)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__50133.invoke(results_metadata.clj:128)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__45461.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__46866.invoke(process_userland_query.clj:136)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__45404.invoke(catch_exceptions.clj:174)"
  "query_processor.reducible$async_qp$qp_STAR___32688$thunk__32689.invoke(reducible.clj:103)"
  "query_processor.reducible$async_qp$qp_STAR___32688$fn__32691.invoke(reducible.clj:108)"],
 :context :question,
 :error "Value does not match schema: {:query {:joins [{:source-table (not (matches-some-precondition? a-java.lang.String))}]}}",
 :row_count 0,
 :running_time 0,
 :preprocessed nil,
 :ex-data
 {:type :schema.core/error,
  :value
  {:constraints {:max-results 10000, :max-results-bare-rows 2000},
   :type :query,
   :info
   {:executed-by 1,
    :context :question,
    :card-id 7,
    :query-hash [23, 83, 115, -28, 3, 92, -70, 99, -105, 93, 36, -38, -6, 56, 71, -11, 115, -61, 93, -67, -103, -15, -93, 77, 125, -86, 5, 111, 88, -123, 71, -14]},
   :database 1,
   :query
   {:source-table 4,
    :joins [{:fields :all, :source-table "/databases/Sample Dataset/schemas/PUBLIC/tables/PRODUCTS", :condition [:= [:field-id 36] [:joined-field "Product" [:field-id 2]]], :alias "Product"}]},
   :async? true},
  :error {:query {:joins [{:source-table (not (matches-some-precondition? a-java.lang.String))}]}}},
 :data {:rows [], :cols []}}

06-25 16:17:15 DEBUG middleware.log :: POST /api/card/7/query 202 [ASYNC: completed] 789.7 ms (3 DB calls) App DB connections: 0/4 Jetty threads: 3/50 (3 idle, 0 queued) (50 total active threads) Queries in flight: 1 (0 queued)

Information about your Metabase Installation:
Tested 1.35.4.1 thru 1.38.1

Additional context
Originally EE487 https://github.com/metabase/metabase-enterprise/issues/487
Workaround is to manually modify report_card.dataset_query references from the full path string to ID reference on the target.

---

Workaround for Postgres - make sure to backup before running query - this should only be executed on the target appdb:

UPDATE report_card
SET dataset_query=REGEXP_REPLACE(dataset_query, lookup.findstr, lookup.replacestr)
FROM (
    WITH wrongref AS (
        SELECT rc.id, REGEXP_MATCH(rc.dataset_query, '"source-table":("/databases/([^/]+)/schemas/([^/]+)/tables/([^"]+)")') AS findstr
        FROM report_card AS rc WHERE rc.dataset_query LIKE '%"source-table":"/databases/%'
    )
    SELECT
      wrongref.id,
      concat('"source-table":', wrongref.findstr[1]) AS findstr,
      concat('"source-table":', (
        SELECT mt.id::varchar FROM metabase_database AS md LEFT JOIN metabase_table AS mt ON mt.db_id=md.id WHERE md."name"=wrongref.findstr[2] AND mt."schema"=wrongref.findstr[3] AND mt."name"=wrongref.findstr[4])
      ) AS replacestr
    FROM wrongref
) AS lookup
WHERE report_card.id=lookup.id

Note - you have to run the query multiple times until no more rows are updated.
This is a limitation of Postgres' "UPDATE ... FROM": https://www.postgresql.org/docs/current/sql-update.html#id-1.9.3.182.8

[jeff303]

Should be fixed by commit 13 under #15858

[jeff303]

Added commit 17 to fix joining to other cards, under #15858

[flamber]

Fixed by #15858 - will be part of 1.39.3