Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Expression name with spaces causes incorrect aliasing when used together with Custom Column on BigQuery #25931

Closed
flamber opened this issue Oct 14, 2022 · 3 comments · Fixed by #39255
Closed

Expression name with spaces causes incorrect aliasing when used together with Custom Column on BigQuery #25931

flamber opened this issue Oct 14, 2022 · 3 comments · Fixed by #39255
Assignees
@bshepherdson
Labels
.Backend Database/BigQuery Priority:P2 Average run of the mill bug Querying/Nested Queries Questions based on other saved questions Querying/Notebook/Custom Column Querying/Notebook/Custom Expression .Team/QueryProcessor :hammer_and_wrench: Type:Bug Product defects .Wanted: MLv2 Issues that will be fixed (or easier to fix, or possible to fix) when we have MLv2
Milestone
0.49

Comments

@flamber
Copy link
Contributor

flamber commented Oct 14, 2022

Describe the bug
Expression name with spaces causes incorrect aliasing when used together with Custom Column on BigQuery.
Historically similar to #22310.

Workaround: Only use expression names without spaces.

To Reproduce

  1. Question > BigQuery > Products
  2. Summarize Custom Expression 1 * 1 as "expression name with spaces" grouped by Category
  3. Custom Column 1 * 1 as "CC"
    image
Full stacktrace 
2022-10-14 15:40:48,480 ERROR middleware.catch-exceptions :: Error processing query: 400 Bad Request
POST https://www.googleapis.com/bigquery/v2/projects/metabase-bigquery-driver/queries
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "location" : "q",
    "locationType" : "parameter",
    "message" : "Name expression name with spaces not found inside source at [2:195]",
    "reason" : "invalidQuery"
  } ],
  "message" : "Name expression name with spaces not found inside source at [2:195]",
  "status" : "INVALID_ARGUMENT"
}
{:database_id 43,
 :started_at #t "2022-10-14T15:40:47.389879+02:00[Europe/Copenhagen]",
 :via
 [{:status :failed,
   :class com.google.cloud.bigquery.BigQueryException,
   :error "Name expression name with spaces not found inside source at [2:195]",
   :stacktrace
   ["com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.translate(HttpBigQueryRpc.java:115)"
    "com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.queryRpc(HttpBigQueryRpc.java:652)"
    "com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1278)"
    "com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1275)"
    "com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:105)"
    "com.google.cloud.RetryHelper.run(RetryHelper.java:76)"
    "com.google.cloud.RetryHelper.runWithRetries(RetryHelper.java:50)"
    "com.google.cloud.bigquery.BigQueryImpl.queryRpc(BigQueryImpl.java:1274)"
    "com.google.cloud.bigquery.BigQueryImpl.query(BigQueryImpl.java:1262)"
    "--> driver.bigquery_cloud_sdk$execute_bigquery$fn__82495.invoke(bigquery_cloud_sdk.clj:193)"]}
  {:status :failed,
   :class java.util.concurrent.ExecutionException,
   :error
   "com.google.cloud.bigquery.BigQueryException: Name expression name with spaces not found inside source at [2:195]",
   :stacktrace
   ["java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)"
    "java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191)"
    "clojure.core$deref_future.invokeStatic(core.clj:2317)"
    "clojure.core$future_call$reify__8544.deref(core.clj:7041)"
    "clojure.core$deref.invokeStatic(core.clj:2337)"
    "clojure.core$deref.invoke(core.clj:2323)"
    "--> driver.bigquery_cloud_sdk$execute_bigquery.invokeStatic(bigquery_cloud_sdk.clj:181)"
    "driver.bigquery_cloud_sdk$execute_bigquery.invoke(bigquery_cloud_sdk.clj:177)"
    "driver.bigquery_cloud_sdk$execute_bigquery_on_db.invokeStatic(bigquery_cloud_sdk.clj:219)"
    "driver.bigquery_cloud_sdk$execute_bigquery_on_db.invoke(bigquery_cloud_sdk.clj:217)"
    "driver.bigquery_cloud_sdk$process_native_STAR_$thunk__82596.invoke(bigquery_cloud_sdk.clj:279)"
    "driver.bigquery_cloud_sdk$process_native_STAR_.invokeStatic(bigquery_cloud_sdk.clj:287)"
    "driver.bigquery_cloud_sdk$process_native_STAR_.invoke(bigquery_cloud_sdk.clj:271)"
    "driver.bigquery_cloud_sdk$fn__82602.invokeStatic(bigquery_cloud_sdk.clj:307)"
    "driver.bigquery_cloud_sdk$fn__82602.invoke(bigquery_cloud_sdk.clj:299)"
    "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___52619.invoke(cache.clj:220)"
    "query_processor.middleware.permissions$check_query_permissions$fn__48140.invoke(permissions.clj:109)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__51564.invoke(mbql_to_native.clj:23)"
    "query_processor$fn__54095$combined_post_process__54100$combined_post_process_STAR___54101.invoke(query_processor.clj:212)"
    "query_processor$fn__54095$combined_pre_process__54096$combined_pre_process_STAR___54097.invoke(query_processor.clj:209)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52540$fn__52545.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__52540.invoke(resolve_database_and_driver.clj:34)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48575.invoke(fetch_source_query.clj:353)"
    "query_processor.middleware.store$initialize_store$fn__48763$fn__48764.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__48763.invoke(store.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__52812.invoke(normalize_query.clj:22)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__49845.invoke(constraints.clj:53)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__52751.invoke(process_userland_query.clj:145)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__53123.invoke(catch_exceptions.clj:167)"
    "query_processor.reducible$async_qp$qp_STAR___44978$thunk__44980.invoke(reducible.clj:100)"
    "query_processor.reducible$async_qp$qp_STAR___44978.invoke(reducible.clj:106)"
    "query_processor.reducible$async_qp$qp_STAR___44978.invoke(reducible.clj:91)"
    "query_processor.reducible$sync_qp$qp_STAR___44989.doInvoke(reducible.clj:126)"
    "query_processor$process_userland_query.invokeStatic(query_processor.clj:331)"
    "query_processor$process_userland_query.doInvoke(query_processor.clj:327)"
    "query_processor$fn__54142$process_query_and_save_execution_BANG___54151$fn__54154.invoke(query_processor.clj:342)"
    "query_processor$fn__54142$process_query_and_save_execution_BANG___54151.invoke(query_processor.clj:335)"
    "query_processor$fn__54186$process_query_and_save_with_max_results_constraints_BANG___54195$fn__54198.invoke(query_processor.clj:354)"
    "query_processor$fn__54186$process_query_and_save_with_max_results_constraints_BANG___54195.invoke(query_processor.clj:347)"
    "api.dataset$run_query_async$fn__68893.invoke(dataset.clj:69)"
    "query_processor.streaming$streaming_response_STAR_$fn__40096$fn__40097.invoke(streaming.clj:162)"
    "query_processor.streaming$streaming_response_STAR_$fn__40096.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__19006.invoke(streaming_response.clj:84)"]}
  {:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error
   "Error executing query: com.google.cloud.bigquery.BigQueryException: Name expression name with spaces not found inside source at [2:195]",
   :stacktrace
   ["--> driver.bigquery_cloud_sdk$throw_invalid_query.invokeStatic(bigquery_cloud_sdk.clj:173)"
    "driver.bigquery_cloud_sdk$throw_invalid_query.invoke(bigquery_cloud_sdk.clj:172)"
    "driver.bigquery_cloud_sdk$execute_bigquery.invokeStatic(bigquery_cloud_sdk.clj:215)"
    "driver.bigquery_cloud_sdk$execute_bigquery.invoke(bigquery_cloud_sdk.clj:177)"
    "driver.bigquery_cloud_sdk$execute_bigquery_on_db.invokeStatic(bigquery_cloud_sdk.clj:219)"
    "driver.bigquery_cloud_sdk$execute_bigquery_on_db.invoke(bigquery_cloud_sdk.clj:217)"
    "driver.bigquery_cloud_sdk$process_native_STAR_$thunk__82596.invoke(bigquery_cloud_sdk.clj:279)"
    "driver.bigquery_cloud_sdk$process_native_STAR_.invokeStatic(bigquery_cloud_sdk.clj:287)"
    "driver.bigquery_cloud_sdk$process_native_STAR_.invoke(bigquery_cloud_sdk.clj:271)"
    "driver.bigquery_cloud_sdk$fn__82602.invokeStatic(bigquery_cloud_sdk.clj:307)"
    "driver.bigquery_cloud_sdk$fn__82602.invoke(bigquery_cloud_sdk.clj:299)"
    "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___52619.invoke(cache.clj:220)"
    "query_processor.middleware.permissions$check_query_permissions$fn__48140.invoke(permissions.clj:109)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__51564.invoke(mbql_to_native.clj:23)"
    "query_processor$fn__54095$combined_post_process__54100$combined_post_process_STAR___54101.invoke(query_processor.clj:212)"
    "query_processor$fn__54095$combined_pre_process__54096$combined_pre_process_STAR___54097.invoke(query_processor.clj:209)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52540$fn__52545.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__52540.invoke(resolve_database_and_driver.clj:34)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48575.invoke(fetch_source_query.clj:353)"
    "query_processor.middleware.store$initialize_store$fn__48763$fn__48764.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__48763.invoke(store.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__52812.invoke(normalize_query.clj:22)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__49845.invoke(constraints.clj:53)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__52751.invoke(process_userland_query.clj:145)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__53123.invoke(catch_exceptions.clj:167)"
    "query_processor.reducible$async_qp$qp_STAR___44978$thunk__44980.invoke(reducible.clj:100)"
    "query_processor.reducible$async_qp$qp_STAR___44978.invoke(reducible.clj:106)"
    "query_processor.reducible$async_qp$qp_STAR___44978.invoke(reducible.clj:91)"
    "query_processor.reducible$sync_qp$qp_STAR___44989.doInvoke(reducible.clj:126)"
    "query_processor$process_userland_query.invokeStatic(query_processor.clj:331)"
    "query_processor$process_userland_query.doInvoke(query_processor.clj:327)"
    "query_processor$fn__54142$process_query_and_save_execution_BANG___54151$fn__54154.invoke(query_processor.clj:342)"
    "query_processor$fn__54142$process_query_and_save_execution_BANG___54151.invoke(query_processor.clj:335)"
    "query_processor$fn__54186$process_query_and_save_with_max_results_constraints_BANG___54195$fn__54198.invoke(query_processor.clj:354)"
    "query_processor$fn__54186$process_query_and_save_with_max_results_constraints_BANG___54195.invoke(query_processor.clj:347)"
    "api.dataset$run_query_async$fn__68893.invoke(dataset.clj:69)"
    "query_processor.streaming$streaming_response_STAR_$fn__40096$fn__40097.invoke(streaming.clj:162)"
    "query_processor.streaming$streaming_response_STAR_$fn__40096.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__19006.invoke(streaming_response.clj:84)"],
   :error_type :invalid-query,
   :ex-data
   {:type :invalid-query,
    :sql
    "-- Metabase:: userID: 1 queryType: MBQL queryHash: 6e55ba9bb45e8854094984093b76c20d41fe3a2e0ef6c1532b5bc00c69b7d190\nSELECT `source`.`category` AS `category`, `source`.`expression_name_with_spaces` AS `expression_name_with_spaces`, `source`.`CC` AS `CC` FROM (SELECT `source`.`category` AS `category`, `source`.`expression name with spaces` AS `expression_name_with_spaces`, (1 * 1) AS `CC` FROM (SELECT `v6_sample_dataset.products`.`category` AS `category`, (1 * 1) AS `expression_name_with_spaces` FROM `v6_sample_dataset.products` GROUP BY `category` ORDER BY `category` ASC) `source`) `source` LIMIT 10",
    :parameters nil}}],
 :error_type :invalid-query,
 :json_query
 {:type "query",
  :query
  {:source-query
   {:source-table 6177,
    :aggregation
    [["aggregation-options"
      ["*" 1 1]
      {:name "expression name with spaces", :display-name "expression name with spaces"}]],
    :breakout [["field" 35280 nil]]},
   :expressions {:CC ["*" 1 1]},
   :limit 10},
  :database 43,
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :native
 {:query
  "SELECT `source`.`category` AS `category`, `source`.`expression_name_with_spaces` AS `expression_name_with_spaces`, `source`.`CC` AS `CC` FROM (SELECT `source`.`category` AS `category`, `source`.`expression name with spaces` AS `expression_name_with_spaces`, (1 * 1) AS `CC` FROM (SELECT `v6_sample_dataset.products`.`category` AS `category`, (1 * 1) AS `expression_name_with_spaces` FROM `v6_sample_dataset.products` GROUP BY `category` ORDER BY `category` ASC) `source`) `source` LIMIT 10",
  :params nil,
  :table-name "source",
  :mbql? true},
 :status :failed,
 :class com.google.api.client.googleapis.json.GoogleJsonResponseException,
 :stacktrace
 ["com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146)"
  "com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:118)"
  "com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:37)"
  "com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:428)"
  "com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1111)"
  "com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:514)"
  "com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:455)"
  "com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:565)"
  "com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.queryRpc(HttpBigQueryRpc.java:650)"
  "com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1278)"
  "com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1275)"
  "com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:105)"
  "com.google.cloud.RetryHelper.run(RetryHelper.java:76)"
  "com.google.cloud.RetryHelper.runWithRetries(RetryHelper.java:50)"
  "com.google.cloud.bigquery.BigQueryImpl.queryRpc(BigQueryImpl.java:1274)"
  "com.google.cloud.bigquery.BigQueryImpl.query(BigQueryImpl.java:1262)"
  "--> driver.bigquery_cloud_sdk$execute_bigquery$fn__82495.invoke(bigquery_cloud_sdk.clj:193)"],
 :card_id nil,
 :context :ad-hoc,
 :error
 "400 Bad Request\nPOST https://www.googleapis.com/bigquery/v2/projects/metabase-bigquery-driver/queries\n{\n  \"code\" : 400,\n  \"errors\" : [ {\n    \"domain\" : \"global\",\n    \"location\" : \"q\",\n    \"locationType\" : \"parameter\",\n    \"message\" : \"Name expression name with spaces not found inside source at [2:195]\",\n    \"reason\" : \"invalidQuery\"\n  } ],\n  \"message\" : \"Name expression name with spaces not found inside source at [2:195]\",\n  \"status\" : \"INVALID_ARGUMENT\"\n}",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:type :query,
  :query
  {:expressions {"CC" [:* 1 1]},
   :limit 10,
   :source-metadata
   [{:semantic_type :type/Category,
     :table_id 6177,
     :coercion_strategy nil,
     :name "category",
     :settings nil,
     :field_ref [:field 35280 nil],
     :effective_type :type/Text,
     :nfc_path nil,
     :parent_id nil,
     :id 35280,
     :display_name "Category",
     :fingerprint
     {:global {:distinct-count 4, :nil% 0.0},
      :type
      {:type/Text
       {:percent-json 0.0, :percent-url 0.0, :percent-email 0.0, :percent-state 0.0, :average-length 6.375}}},
     :base_type :type/Text}
    {:name "expression name with spaces",
     :display_name "expression name with spaces",
     :base_type :type/Float,
     :field_ref [:aggregation 0]}],
   :fields [[:field 35280 nil] [:field "expression name with spaces" {:base-type :type/Float}] [:expression "CC"]],
   :source-query
   {:source-table 6177,
    :aggregation
    [[:aggregation-options
      [:* 1 1]
      {:name "expression name with spaces", :display-name "expression name with spaces"}]],
    :breakout [[:field 35280 nil]],
    :order-by [[:asc [:field 35280 nil]]]}},
  :database 43,
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
  :info {:executed-by 1, :context :ad-hoc}},
 :data {:rows [], :cols []}}

2022-10-14 15:40:48,484 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 1.1 s (18 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (3 idle, 0 queued) (103 total active threads) Queries in flight: 0 (0 queued)

Information about your Metabase Installation:
Tested 0.44.4
@luizarakaki luizarakaki added the .Team/QueryProcessor :hammer_and_wrench: label Apr 10, 2023
@ranquild ranquild added the .Wanted: MLv2 Issues that will be fixed (or easier to fix, or possible to fix) when we have MLv2 label Oct 31, 2023
@bshepherdson
Copy link
Contributor

I can repro this on master. The BigQuery driver is not handling spaces in expression names.

Some more data points from my testing:

  • Deleting the CC column (and therefore the second stage) makes it work fine.
  • Putting the CC column back in the first stage also works.
  • Then changing the aggregation to Sum of CC works.
  • That still works with CC changed to expression name with spaces and it's SUM taken separately.
  • Filtering that query (bringing back the second stage) works fine.
  • Including CC = 1 * 1 in the second stage still works[

So this is a specific error only around custom aggregations with spaces in their names, with another stage after them.

@bshepherdson bshepherdson self-assigned this Feb 16, 2024
@bshepherdson
Copy link
Contributor

The broken SQL is:

SELECT
  `source`.`category` AS `category`,
  `source`.`expression name with spaces` AS `expression_name_with_spaces`,
  `source`.`CC` AS `CC`
FROM (
  SELECT
    `source`.`category` AS `category`,
    1 * 1 AS `CC`
  FROM (
    SELECT
      `sample_database.products`.`category` AS `category`,
      1 * 1 AS `expression_name_with_spaces`
    FROM `sample_database.products`
    GROUP BY `category`
    ORDER BY `category` ASC
  ) AS `source`
) AS `source`
LIMIT 2000

The same query on H2 yields SQL like this:

SELECT
  "source"."CATEGORY" AS "CATEGORY",
  "source"."expression name with spaces" AS "expression name with spaces",
  "source"."CC" AS "CC" FROM (
    SELECT
      "source"."CATEGORY" AS "CATEGORY",
      "source"."expression name with spaces" AS "expression name with spaces",
      1 * 1 AS "CC"
    FROM (
      SELECT
        "PUBLIC"."PRODUCTS"."CATEGORY" AS "CATEGORY",
        1 * 1 AS "expression name with spaces"
      FROM "PUBLIC"."PRODUCTS"
      GROUP BY "PUBLIC"."PRODUCTS"."CATEGORY"
      ORDER BY "PUBLIC"."PRODUCTS"."CATEGORY" ASC
    ) AS "source"
  ) AS "source"
  LIMIT 2000

@bshepherdson
Copy link
Contributor

I'll pick this up again next week. For now I want to note for posterity that there's actually two things going wrong in the bad SQL:

  1. The expression name with spaces is confused between that and expression_name_with_spaces.
  2. This field is not SELECTed from the middle nested query, only the inner and outer ones. It's in all three of the working SQL.

I suspect (1) is a symptom of (2). That is, if the field were getting returned from that middle query properly, the correct name for it would be used in the outer query. Since that field isn't found in the source-metadata it's just guessing the field by :name, which is expression name with spaces.

@bshepherdson bshepherdson mentioned this issue Feb 27, 2024
Closed
bshepherdson added a commit that referenced this issue Feb 28, 2024
@bshepherdson
[QP] Fix confusion of expressions with the same name as columns
d3d572e 
When determining column aliases in `add-alias-info`, reuse an existing
desired column alias if one is present.

Fixes #39059. Might fix #25931.
@bshepherdson bshepherdson mentioned this issue Feb 28, 2024
Merged
bshepherdson added a commit that referenced this issue Feb 28, 2024
@bshepherdson
[QP] Fix confusion of expressions with the same name as columns
1d62491 
When determining column aliases in `add-alias-info`, reuse an existing
desired column alias if one is present.

Fixes #39059. Might fix #25931.
bshepherdson added a commit that referenced this issue Feb 29, 2024
@bshepherdson
[QP] Fix confusion of expressions with the same name as columns
70c7c4d 
When determining column aliases in `add-alias-info`, reuse an existing
desired column alias if one is present.

Fixes #39059. Might fix #25931.
bshepherdson added a commit that referenced this issue Mar 1, 2024
@bshepherdson
[QP] Fix confusion of expressions with the same name as columns
30ccd18 
When determining column aliases in `add-alias-info`, reuse an existing
desired column alias if one is present.

Fixes #39059. Might fix #25931.
bshepherdson added a commit that referenced this issue Mar 4, 2024
@bshepherdson
[QP] Fix confusion of expressions with the same name as columns (#39255)
a60cd5e 
When determining column aliases in `add-alias-info`, reuse an existing
desired column alias if one is present.

Fixes #39059. Might fix #25931.
bshepherdson added a commit that referenced this issue Mar 4, 2024
@bshepherdson
[QP] Fix confusion of expressions with the same name as columns (#39255)
5350b69 
When determining column aliases in `add-alias-info`, reuse an existing
desired column alias if one is present.

Fixes #39059. Might fix #25931.
bshepherdson added a commit that referenced this issue Mar 4, 2024
@metabase-bot @bshepherdson
[QP] Fix confusion of expressions with the same name as columns (#39255…
800418b 
…) (#39526)

When determining column aliases in `add-alias-info`, reuse an existing
desired column alias if one is present.

Fixes #39059. Might fix #25931.

Co-authored-by: Braden Shepherdson <braden@metabase.com>
@darksciencebase darksciencebase added this to the 0.49 milestone Apr 10, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@bshepherdson bshepherdson

Labels
.Backend Database/BigQuery Priority:P2 Average run of the mill bug Querying/Nested Queries Questions based on other saved questions Querying/Notebook/Custom Column Querying/Notebook/Custom Expression .Team/QueryProcessor :hammer_and_wrench: Type:Bug Product defects .Wanted: MLv2 Issues that will be fixed (or easier to fix, or possible to fix) when we have MLv2
Projects
None yet
Milestone
0.49
Development

Successfully merging a pull request may close this issue.

[QP] Fix confusion of expressions with the same name as columns metabase/metabase
5 participants
@bshepherdson @flamber @ranquild @luizarakaki @darksciencebase