Models query fails when a field is remapped with Custom value #23449

flamber · 2022-06-20T21:14:23Z

Describe the bug
When a field is using custom remapping via Data Model, then creating Models will make the query fail.

Workaround: Use a database view, or try using SQL with manual remapping (case when ...)

To Reproduce

Admin > Data Model > Sample > Reviews > Rating ⚙️ > set "Filtering on this field"="A list of all values", set "Display values"="Custom mapping" and change the values and click Save
Question > Sample > Reviews
Save question and turn into Model
Errors with Error executing query: Column \"source.Rating\" not found;, which is the second rating field in the query, but it is using the display name as identifier instead of the underlying column name.

SELECT "source"."ID" AS "ID",
       "source"."PRODUCT_ID" AS "PRODUCT_ID",
       "source"."REVIEWER" AS "REVIEWER",
       "source"."RATING" AS "RATING",
       "source"."CREATED_AT" AS "CREATED_AT",
       "source"."Rating" AS "Rating_2"
FROM
  (SELECT "PUBLIC"."REVIEWS"."ID" AS "ID",
          "PUBLIC"."REVIEWS"."PRODUCT_ID" AS "PRODUCT_ID",
          "PUBLIC"."REVIEWS"."REVIEWER" AS "REVIEWER",
          "PUBLIC"."REVIEWS"."RATING" AS "RATING",
          "PUBLIC"."REVIEWS"."CREATED_AT" AS "CREATED_AT"
   FROM "PUBLIC"."REVIEWS") "source"

Full stacktrace

2022-06-20 23:03:28,650 INFO api.dataset :: Source query for this query is Card 1,046
2022-06-20 23:03:29,573 ERROR middleware.catch-exceptions :: Error processing query: null
{:database_id 4,
 :started_at #t "2022-06-20T23:03:28.695664+02:00[Europe/Copenhagen]",
 :via
 [{:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error
   "Error executing query: Column \"source.Rating\" not found; SQL statement:\n-- Metabase:: userID: 1 queryType: MBQL queryHash: 6bc1cc23e99b42e1ec279eb20a81ee51c3ba194e6191ce2312cef0b7c49434ec\nSELECT \"source\".\"ID\" AS \"ID\", \"source\".\"PRODUCT_ID\" AS \"PRODUCT_ID\", \"source\".\"REVIEWER\" AS \"REVIEWER\", \"source\".\"RATING\" AS \"RATING\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", \"source\".\"Rating\" AS \"Rating_2\" FROM (SELECT \"PUBLIC\".\"REVIEWS\".\"ID\" AS \"ID\", \"PUBLIC\".\"REVIEWS\".\"PRODUCT_ID\" AS \"PRODUCT_ID\", \"PUBLIC\".\"REVIEWS\".\"REVIEWER\" AS \"REVIEWER\", \"PUBLIC\".\"REVIEWS\".\"RATING\" AS \"RATING\", \"PUBLIC\".\"REVIEWS\".\"CREATED_AT\" AS \"CREATED_AT\" FROM \"PUBLIC\".\"REVIEWS\") \"source\" LIMIT 2000 [42122-197]",
   :stacktrace
   ["--> driver.sql_jdbc.execute$execute_reducible_query$fn__54058.invoke(execute.clj:502)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:499)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:494)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
    "driver.sql_jdbc$fn__83571.invokeStatic(sql_jdbc.clj:54)"
    "driver.sql_jdbc$fn__83571.invoke(sql_jdbc.clj:52)"
    "driver.h2$fn__80660.invokeStatic(h2.clj:90)"
    "driver.h2$fn__80660.invoke(h2.clj:87)"
    "query_processor.context$executef.invokeStatic(context.clj:59)"
    "query_processor.context$executef.invoke(context.clj:48)"
    "query_processor.context.default$default_runf.invokeStatic(default.clj:67)"
    "query_processor.context.default$default_runf.invoke(default.clj:65)"
    "query_processor.context$runf.invokeStatic(context.clj:45)"
    "query_processor.context$runf.invoke(context.clj:39)"
    "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:12)"
    "query_processor.reducible$identity_qp.invoke(reducible.clj:9)"
    "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___51446.invoke(cache.clj:220)"
    "query_processor.middleware.permissions$check_query_permissions$fn__47229.invoke(permissions.clj:109)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__50387.invoke(mbql_to_native.clj:23)"
    "query_processor$fn__52919$combined_post_process__52924$combined_post_process_STAR___52925.invoke(query_processor.clj:207)"
    "query_processor$fn__52919$combined_pre_process__52920$combined_pre_process_STAR___52921.invoke(query_processor.clj:204)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__51367$fn__51372.invoke(resolve_database_and_driver.clj:35)"
    "driver$do_with_driver.invokeStatic(driver.clj:75)"
    "driver$do_with_driver.invoke(driver.clj:71)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__51367.invoke(resolve_database_and_driver.clj:34)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47472.invoke(fetch_source_query.clj:277)"
    "query_processor.middleware.store$initialize_store$fn__47660$fn__47661.invoke(store.clj:11)"
    "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
    "query_processor.store$do_with_store.invoke(store.clj:38)"
    "query_processor.middleware.store$initialize_store$fn__47660.invoke(store.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__51639.invoke(normalize_query.clj:22)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__48729.invoke(constraints.clj:53)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__51578.invoke(process_userland_query.clj:145)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__51950.invoke(catch_exceptions.clj:162)"
    "query_processor.reducible$async_qp$qp_STAR___44199$thunk__44201.invoke(reducible.clj:100)"
    "query_processor.reducible$async_qp$qp_STAR___44199.invoke(reducible.clj:106)"
    "query_processor.reducible$async_qp$qp_STAR___44199.invoke(reducible.clj:91)"
    "query_processor.reducible$sync_qp$qp_STAR___44210.doInvoke(reducible.clj:126)"
    "query_processor$process_userland_query.invokeStatic(query_processor.clj:326)"
    "query_processor$process_userland_query.doInvoke(query_processor.clj:322)"
    "query_processor$fn__52966$process_query_and_save_execution_BANG___52975$fn__52978.invoke(query_processor.clj:337)"
    "query_processor$fn__52966$process_query_and_save_execution_BANG___52975.invoke(query_processor.clj:330)"
    "query_processor$fn__53010$process_query_and_save_with_max_results_constraints_BANG___53019$fn__53022.invoke(query_processor.clj:349)"
    "query_processor$fn__53010$process_query_and_save_with_max_results_constraints_BANG___53019.invoke(query_processor.clj:342)"
    "api.dataset$run_query_async$fn__66700.invoke(dataset.clj:68)"
    "query_processor.streaming$streaming_response_STAR_$fn__39322$fn__39323.invoke(streaming.clj:162)"
    "query_processor.streaming$streaming_response_STAR_$fn__39322.invoke(streaming.clj:161)"
    "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
    "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
    "async.streaming_response$do_f_async$task__18948.invoke(streaming_response.clj:84)"],
   :error_type :invalid-query,
   :ex-data
   {:sql
    "-- Metabase:: userID: 1 queryType: MBQL queryHash: 6bc1cc23e99b42e1ec279eb20a81ee51c3ba194e6191ce2312cef0b7c49434ec\nSELECT \"source\".\"ID\" AS \"ID\", \"source\".\"PRODUCT_ID\" AS \"PRODUCT_ID\", \"source\".\"REVIEWER\" AS \"REVIEWER\", \"source\".\"RATING\" AS \"RATING\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", \"source\".\"Rating\" AS \"Rating_2\" FROM (SELECT \"PUBLIC\".\"REVIEWS\".\"ID\" AS \"ID\", \"PUBLIC\".\"REVIEWS\".\"PRODUCT_ID\" AS \"PRODUCT_ID\", \"PUBLIC\".\"REVIEWS\".\"REVIEWER\" AS \"REVIEWER\", \"PUBLIC\".\"REVIEWS\".\"RATING\" AS \"RATING\", \"PUBLIC\".\"REVIEWS\".\"CREATED_AT\" AS \"CREATED_AT\" FROM \"PUBLIC\".\"REVIEWS\") \"source\" LIMIT 2000",
    :params nil,
    :type :invalid-query}}],
 :state "42S22",
 :error_type :invalid-query,
 :json_query
 {:type "query",
  :database 4,
  :query {:source-table "card__1046"},
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :native
 {:query
  "SELECT \"source\".\"ID\" AS \"ID\", \"source\".\"PRODUCT_ID\" AS \"PRODUCT_ID\", \"source\".\"REVIEWER\" AS \"REVIEWER\", \"source\".\"RATING\" AS \"RATING\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", \"source\".\"Rating\" AS \"Rating_2\" FROM (SELECT \"PUBLIC\".\"REVIEWS\".\"ID\" AS \"ID\", \"PUBLIC\".\"REVIEWS\".\"PRODUCT_ID\" AS \"PRODUCT_ID\", \"PUBLIC\".\"REVIEWS\".\"REVIEWER\" AS \"REVIEWER\", \"PUBLIC\".\"REVIEWS\".\"RATING\" AS \"RATING\", \"PUBLIC\".\"REVIEWS\".\"CREATED_AT\" AS \"CREATED_AT\" FROM \"PUBLIC\".\"REVIEWS\") \"source\" LIMIT 1048575",
  :params nil},
 :status :failed,
 :class org.h2.jdbc.JdbcSQLException,
 :stacktrace
 ["org.h2.message.DbException.getJdbcSQLException(DbException.java:357)"
  "org.h2.message.DbException.get(DbException.java:179)"
  "org.h2.message.DbException.get(DbException.java:155)"
  "org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:150)"
  "org.h2.expression.Alias.optimize(Alias.java:51)"
  "org.h2.command.dml.Select.prepare(Select.java:858)"
  "org.h2.command.Parser.prepareCommand(Parser.java:283)"
  "org.h2.engine.Session.prepareLocal(Session.java:611)"
  "org.h2.engine.Session.prepareCommand(Session.java:549)"
  "org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)"
  "org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217)"
  "org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)"
  "com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)"
  "--> driver.sql_jdbc.execute$fn__53978.invokeStatic(execute.clj:367)"
  "driver.sql_jdbc.execute$fn__53978.invoke(execute.clj:365)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:375)"
  "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:372)"
  "driver.sql_jdbc.execute$execute_reducible_query$fn__54058.invoke(execute.clj:500)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:499)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:494)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:486)"
  "driver.sql_jdbc$fn__83571.invokeStatic(sql_jdbc.clj:54)"
  "driver.sql_jdbc$fn__83571.invoke(sql_jdbc.clj:52)"
  "driver.h2$fn__80660.invokeStatic(h2.clj:90)"
  "driver.h2$fn__80660.invoke(h2.clj:87)"
  "query_processor.context$executef.invokeStatic(context.clj:59)"
  "query_processor.context$executef.invoke(context.clj:48)"
  "query_processor.context.default$default_runf.invokeStatic(default.clj:67)"
  "query_processor.context.default$default_runf.invoke(default.clj:65)"
  "query_processor.context$runf.invokeStatic(context.clj:45)"
  "query_processor.context$runf.invoke(context.clj:39)"
  "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:12)"
  "query_processor.reducible$identity_qp.invoke(reducible.clj:9)"
  "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___51446.invoke(cache.clj:220)"
  "query_processor.middleware.permissions$check_query_permissions$fn__47229.invoke(permissions.clj:109)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__50387.invoke(mbql_to_native.clj:23)"
  "query_processor$fn__52919$combined_post_process__52924$combined_post_process_STAR___52925.invoke(query_processor.clj:207)"
  "query_processor$fn__52919$combined_pre_process__52920$combined_pre_process_STAR___52921.invoke(query_processor.clj:204)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__51367$fn__51372.invoke(resolve_database_and_driver.clj:35)"
  "driver$do_with_driver.invokeStatic(driver.clj:75)"
  "driver$do_with_driver.invoke(driver.clj:71)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__51367.invoke(resolve_database_and_driver.clj:34)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47472.invoke(fetch_source_query.clj:277)"
  "query_processor.middleware.store$initialize_store$fn__47660$fn__47661.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
  "query_processor.store$do_with_store.invoke(store.clj:38)"
  "query_processor.middleware.store$initialize_store$fn__47660.invoke(store.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__51639.invoke(normalize_query.clj:22)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__48729.invoke(constraints.clj:53)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__51578.invoke(process_userland_query.clj:145)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__51950.invoke(catch_exceptions.clj:162)"
  "query_processor.reducible$async_qp$qp_STAR___44199$thunk__44201.invoke(reducible.clj:100)"
  "query_processor.reducible$async_qp$qp_STAR___44199.invoke(reducible.clj:106)"
  "query_processor.reducible$async_qp$qp_STAR___44199.invoke(reducible.clj:91)"
  "query_processor.reducible$sync_qp$qp_STAR___44210.doInvoke(reducible.clj:126)"
  "query_processor$process_userland_query.invokeStatic(query_processor.clj:326)"
  "query_processor$process_userland_query.doInvoke(query_processor.clj:322)"
  "query_processor$fn__52966$process_query_and_save_execution_BANG___52975$fn__52978.invoke(query_processor.clj:337)"
  "query_processor$fn__52966$process_query_and_save_execution_BANG___52975.invoke(query_processor.clj:330)"
  "query_processor$fn__53010$process_query_and_save_with_max_results_constraints_BANG___53019$fn__53022.invoke(query_processor.clj:349)"
  "query_processor$fn__53010$process_query_and_save_with_max_results_constraints_BANG___53019.invoke(query_processor.clj:342)"
  "api.dataset$run_query_async$fn__66700.invoke(dataset.clj:68)"
  "query_processor.streaming$streaming_response_STAR_$fn__39322$fn__39323.invoke(streaming.clj:162)"
  "query_processor.streaming$streaming_response_STAR_$fn__39322.invoke(streaming.clj:161)"
  "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
  "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
  "async.streaming_response$do_f_async$task__18948.invoke(streaming_response.clj:84)"],
 :card_id 1046,
 :context :ad-hoc,
 :error
 "Column \"source.Rating\" not found; SQL statement:\n-- Metabase:: userID: 1 queryType: MBQL queryHash: 6bc1cc23e99b42e1ec279eb20a81ee51c3ba194e6191ce2312cef0b7c49434ec\nSELECT \"source\".\"ID\" AS \"ID\", \"source\".\"PRODUCT_ID\" AS \"PRODUCT_ID\", \"source\".\"REVIEWER\" AS \"REVIEWER\", \"source\".\"RATING\" AS \"RATING\", \"source\".\"CREATED_AT\" AS \"CREATED_AT\", \"source\".\"Rating\" AS \"Rating_2\" FROM (SELECT \"PUBLIC\".\"REVIEWS\".\"ID\" AS \"ID\", \"PUBLIC\".\"REVIEWS\".\"PRODUCT_ID\" AS \"PRODUCT_ID\", \"PUBLIC\".\"REVIEWS\".\"REVIEWER\" AS \"REVIEWER\", \"PUBLIC\".\"REVIEWS\".\"RATING\" AS \"RATING\", \"PUBLIC\".\"REVIEWS\".\"CREATED_AT\" AS \"CREATED_AT\" FROM \"PUBLIC\".\"REVIEWS\") \"source\" LIMIT 2000 [42122-197]",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:type :query,
  :database 4,
  :query
  {:source-card-id 1046,
   :source-metadata
   [{:description "A unique internal identifier for the review. Should not be used externally.",
     :semantic_type :type/PK,
     :coercion_strategy nil,
     :name "ID",
     :settings nil,
     :field_ref [:field 111 nil],
     :effective_type :type/BigInteger,
     :id 111,
     :display_name "ID",
     :fingerprint nil,
     :base_type :type/BigInteger}
    {:description "The product the review was for",
     :semantic_type :type/FK,
     :coercion_strategy nil,
     :name "PRODUCT_ID",
     :settings nil,
     :field_ref [:field 110 nil],
     :effective_type :type/Integer,
     :id 110,
     :display_name "Product ID",
     :fingerprint {:global {:distinct-count 176, :nil% 0}},
     :base_type :type/Integer}
    {:description "The user who left the review",
     :semantic_type :type/Name,
     :coercion_strategy nil,
     :name "REVIEWER",
     :settings nil,
     :field_ref [:field 113 nil],
     :effective_type :type/Text,
     :id 113,
     :display_name "Reviewer",
     :fingerprint
     {:global {:distinct-count 1076, :nil% 0},
      :type
      {:type/Text
       {:percent-json 0,
        :percent-url 0,
        :percent-email 0,
        :percent-state 0.001798561151079137,
        :average-length 9.972122302158274}}},
     :base_type :type/Text}
    {:description "The rating (on a scale of 1-5) the user left.",
     :semantic_type :type/Score,
     :coercion_strategy nil,
     :name "RATING",
     :settings nil,
     :field_ref [:field 115 nil],
     :effective_type :type/Integer,
     :id 115,
     :display_name "Rating",
     :fingerprint
     {:global {:distinct-count 5, :nil% 0},
      :type
      {:type/Number
       {:min 1, :q1 3.54744353181696, :q3 4.764807071650455, :max 5, :sd 1.0443899855660577, :avg 3.987410071942446}}},
     :base_type :type/Integer}
    {:description "The day and time a review was written by a user.",
     :semantic_type :type/CreationTimestamp,
     :coercion_strategy nil,
     :unit :default,
     :name "CREATED_AT",
     :settings nil,
     :field_ref [:field 114 {:temporal-unit :default}],
     :effective_type :type/DateTime,
     :id 114,
     :display_name "Created At",
     :fingerprint
     {:global {:distinct-count 1112, :nil% 0},
      :type {:type/DateTime {:earliest "2016-06-03T00:37:05.818Z", :latest "2020-04-19T14:15:25.677Z"}}},
     :base_type :type/DateTime}
    {:id nil,
     :description nil,
     :display_name "Rating",
     :semantic_type nil,
     :name "Rating",
     :base_type :type/Text,
     :fingerprint
     {:global {:distinct-count 5, :nil% 0},
      :type {:type/Text {:percent-json 0, :percent-url 0, :percent-email 0, :percent-state 0, :average-length 1}}}}],
   :source-query/dataset? true,
   :fields
   [[:field 111 nil]
    [:field 110 nil]
    [:field 113 nil]
    [:field 115 nil]
    [:field 114 {:temporal-unit :default}]
    [:field "Rating" {:base-type :type/Text}]],
   :source-query
   {:source-table 13,
    :fields
    [[:field 111 nil] [:field 110 nil] [:field 113 nil] [:field 115 nil] [:field 114 {:temporal-unit :default}]]},
   :limit 1048575,
   :metabase.query-processor.middleware.limit/original-limit nil},
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
  :info
  {:executed-by 1,
   :context :ad-hoc,
   :card-id 1046,
   :metadata/dataset-metadata
   [{:description "A unique internal identifier for the review. Should not be used externally.",
     :semantic_type :type/PK,
     :name "ID",
     :field_ref [:field 111 nil],
     :effective_type :type/BigInteger,
     :id 111,
     :display_name "ID",
     :base_type :type/BigInteger}
    {:description "The product the review was for",
     :semantic_type :type/FK,
     :name "PRODUCT_ID",
     :field_ref [:field 110 nil],
     :effective_type :type/Integer,
     :id 110,
     :display_name "Product ID",
     :fingerprint {:global {:distinct-count 176, :nil% 0}},
     :base_type :type/Integer}
    {:description "The user who left the review",
     :semantic_type :type/Name,
     :name "REVIEWER",
     :field_ref [:field 113 nil],
     :effective_type :type/Text,
     :id 113,
     :display_name "Reviewer",
     :fingerprint
     {:global {:distinct-count 1076, :nil% 0},
      :type
      {:type/Text
       {:percent-json 0,
        :percent-url 0,
        :percent-email 0,
        :percent-state 0.001798561151079137,
        :average-length 9.972122302158274}}},
     :base_type :type/Text}
    {:description "The rating (on a scale of 1-5) the user left.",
     :semantic_type :type/Score,
     :name "RATING",
     :field_ref [:field 115 nil],
     :effective_type :type/Integer,
     :id 115,
     :display_name "Rating",
     :fingerprint
     {:global {:distinct-count 5, :nil% 0},
      :type
      {:type/Number
       {:min 1, :q1 3.54744353181696, :q3 4.764807071650455, :max 5, :sd 1.0443899855660577, :avg 3.987410071942446}}},
     :base_type :type/Integer}
    {:description "The day and time a review was written by a user.",
     :semantic_type :type/CreationTimestamp,
     :unit :default,
     :name "CREATED_AT",
     :field_ref [:field 114 {:temporal-unit :default}],
     :effective_type :type/DateTime,
     :id 114,
     :display_name "Created At",
     :fingerprint
     {:global {:distinct-count 1112, :nil% 0},
      :type {:type/DateTime {:earliest "2016-06-03T00:37:05.818Z", :latest "2020-04-19T14:15:25.677Z"}}},
     :base_type :type/DateTime}
    {:display_name "Rating",
     :name "Rating",
     :base_type :type/Text,
     :fingerprint
     {:global {:distinct-count 5, :nil% 0},
      :type {:type/Text {:percent-json 0, :percent-url 0, :percent-email 0, :percent-state 0, :average-length 1}}}}]}},
 :data {:rows [], :cols []}}

2022-06-20 23:03:29,576 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 928.5 ms (33 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (2 idle, 0 queued) (109 total active threads) Queries in flight: 1 (0 queued); h2 DB 4 connections: 0/1 (0 threads blocked)

Information about your Metabase Installation:
Tested 0.43.3

Additional context
Feels related to #22519, though remapping and casting are two different things.

The text was updated successfully, but these errors were encountered:

…) (#23892) Co-authored-by: Nemanja Glumac <31325167+nemanjaglumac@users.noreply.github.com>

qnkhuat · 2022-08-04T10:03:09Z

This bug is not unique to models, I can reproduce it with questions as well.

Admin > Data Model > Sample > Reviews > Rating ⚙️ > set "Filtering on this field"="A list of all values", set "Display values"="Custom mapping" and change the values and click Save
Questions > Sample > Review > Save
Change the question name and save it (just any updates to the question is okay)
Create a new question with the source is the question created above
The same error will appear.

I also notice we that there are 2 Rating columns when we click into the select column section.

This is because when a column has a remapping, we add a new remapped field to the metadata => the metadata of this question will have one field for the original field, and one for the remapped field.

The reason we have to make the edit in step 3 above is somehow when we first the question, FE sends metadata without the remapped field.
But it sends a full metadata (including both original and remapped field) when editting.

qnkhuat · 2022-08-31T07:59:47Z

I did some debugging and I think this happens because when creating a question that has a custom-mapping, the result-metadata for those questions will have an additional field for the "remapped columns", this is a virtual column that is added by remap-results middleware and it does not exist in DB.

This virtual field is problematic because some of our middleware assumes columns inside results_metadata are real columns. For example add-implicit-clauses will automatically bring all fields in result_metadata of a source table and select it.
And since the virtual column does not exist in the DB, the generated native query will be invalid.

Also note that this problem happens with joins too, as demonstrated in here

daltojohnso · 2022-09-08T20:00:38Z

Might be fixed by #25109 as I no longer rerun the query when converting the question into a model: 4dd7fd2

…rtual" fields by the associated query's `table` method (#25109) * Add merge method to Field * add new virtual table utils + tests * Use new utils in implementation of Structured/NativeQuery table methods + remove unneeded Dimension logic * let all Questions return dependentMetadata * Fix types related to query.table() potentially returning null * Fix updateQuestion tests * Remove unused Dimension imports * Fix issue resolving rootTable on queries based on virtual tables * Fallback to the nested card table stored in the Metadata instance * Make the nested card Table the main driver in returning fields for nested cards + other misc fixes * more tweaks related to cloned Tables, source queries * Don't clobber metadata if something is already there (like a method named 'metadata') * Fix for nested native query * Don't use real table for source query * Update table code to use the actual nested card table fields instead of doing merge logic * Skip an e2e test * Workaround for getting Dimensions from joins * Update arg name * Try removing the Table fetch from Question's dependentMetadata method * Don't rerun query when converting question to model * Fix for source query not showing 'previous results' * Fix unit tests * Update models e2e tests not to wait on query * Unskip repro for #23449 * make sure a join's joinedQuery is not a dataset/model * use createQuestion in e2e test * don't use Question redux dispatch method for updating * split test cases * Add comment

bshepherdson · 2024-02-15T17:55:11Z

Since this is a symptom of my favourite millstone #36185, it can't really be fixed directly. I'm triaging this as "Hard" even though this individual issue would probably be fixed in passing by that deeper change.

nemanjaglumac added a commit that referenced this issue Jul 13, 2022

Add repro for #23449

f645dc1

nemanjaglumac mentioned this issue Jul 13, 2022

Repro #23449: Models fail on fields with custom remapped values #23891

Merged

nemanjaglumac added a commit that referenced this issue Jul 13, 2022

Repro #23449: Models fail on fields with custom remapped values (#23891)

7ea59cd

nemanjaglumac added the .Reproduced Issues reproduced in test (usually Cypress) label Jul 13, 2022

github-actions bot pushed a commit that referenced this issue Jul 13, 2022

Repro #23449: Models fail on fields with custom remapped values (#23891)

661f3c7

nemanjaglumac added a commit that referenced this issue Jul 13, 2022

Repro #23449: Models fail on fields with custom remapped values (#23891…

51b1cff

…) (#23892) Co-authored-by: Nemanja Glumac <31325167+nemanjaglumac@users.noreply.github.com>

qnkhuat added Difficulty:Medium Querying/GUI Query builder catch-all, including simple mode labels Aug 4, 2022

flamber added Querying/Nested Queries Questions based on other saved questions and removed Querying/GUI Query builder catch-all, including simple mode labels Aug 4, 2022

qnkhuat mentioned this issue Aug 4, 2022

Don't save metadata for remapped fields using custom mapping #24589

Closed

iethree assigned iethree and unassigned iethree Aug 10, 2022

qnkhuat self-assigned this Aug 11, 2022

gusaiani mentioned this issue Aug 16, 2022

[Epic] Models bugfixes and improvements #24815

Closed

daltojohnso mentioned this issue Sep 8, 2022

Move complex FieldDimension field logic to the instantiation of "virtual" fields by the associated query's table method #25109

Merged

daltojohnso added a commit that referenced this issue Sep 8, 2022

Unskip repro for #23449

0710c82

NevRA added .Team/Querying and removed .Team/QueryProcessor(deprecated) Use .Team/Querying instead labels Aug 16, 2024

camsaul removed the .Wanted: MLv2 Issues that will be fixed (or easier to fix, or possible to fix) when we have MLv2 label Sep 17, 2024

escherize added the Camber label Sep 17, 2024

camsaul mentioned this issue Sep 17, 2024

Display issues affecting native models referencing columns with mapped values #43390

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Models query fails when a field is remapped with Custom value #23449

Models query fails when a field is remapped with Custom value #23449

flamber commented Jun 20, 2022

qnkhuat commented Aug 4, 2022

qnkhuat commented Aug 31, 2022 •

edited

Loading

daltojohnso commented Sep 8, 2022

bshepherdson commented Feb 15, 2024

Models query fails when a field is remapped with Custom value #23449

Models query fails when a field is remapped with Custom value #23449

Comments

flamber commented Jun 20, 2022

qnkhuat commented Aug 4, 2022

qnkhuat commented Aug 31, 2022 • edited Loading

daltojohnso commented Sep 8, 2022

bshepherdson commented Feb 15, 2024

qnkhuat commented Aug 31, 2022 •

edited

Loading