BigQuery driver fails to execute queries with two of the same aggregation

[tlrobinson]

e.x. with two "sum" aggregations, you get 2.55 - 2.97: Ambiguous column name sum.

01-05 22:59:17 DEBUG metabase.query-processor ::
QUERY: 😎
{:database 89,
 :type "query",
 :query {:source_table 803, :aggregation [["sum" ["field-id" 27662]] ["sum" ["field-id" 27663]]]},
 :parameters [],
 :constraints {:max-results 10000, :max-results-bare-rows 2000},
 :info {:executed-by 1, :card-id nil, :uuid "f3ec2a9c-85bd-4e8c-b43f-40fb9ea89886", :query-hash 1590943132, :query-type "MBQL"}}

01-05 22:59:17 DEBUG metabase.query-processor ::

MACRO/SUBSTITUTED: 😻
{:database 89,
 :type "query",
 :query {:source_table 803, :aggregation [["sum" ["field-id" 27662]] ["sum" ["field-id" 27663]]], :filter []},
 :parameters [],
 :constraints {:max-results 10000, :max-results-bare-rows 2000},
 :info {:executed-by 1, :card-id nil, :uuid "f3ec2a9c-85bd-4e8c-b43f-40fb9ea89886", :query-hash 1590943132, :query-type "MBQL"},
 :driver {},
 :settings {}}

01-05 22:59:17 DEBUG metabase.query-processor ::

PARAMS/SUBSTITUTED: 😻
{:database 89,
 :type "query",
 :query {:source_table 803, :aggregation [["sum" ["field-id" 27662]] ["sum" ["field-id" 27663]]], :filter []},
 :constraints {:max-results 10000, :max-results-bare-rows 2000},
 :info {:executed-by 1, :card-id nil, :uuid "f3ec2a9c-85bd-4e8c-b43f-40fb9ea89886", :query-hash 1590943132, :query-type "MBQL"},
 :driver {},
 :settings {}}

01-05 22:59:17 DEBUG metabase.query-processor ::
PREPROCESSED/EXPANDED: 😻
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
 :fk-field-ids #{},
 :table-ids #{803},
 :settings {},
 :type "query",
 :info {:executed-by 1, :uuid "f3ec2a9c-85bd-4e8c-b43f-40fb9ea89886", :query-hash 1590943132, :query-type "MBQL"},
 :database {:name "BigQuery (EU)", :id 89, :engine :bigquery, :details "😋 ", :features (:basic-aggregations :standard-deviation-aggregations :foreign-keys :native-parameters)},
 :driver "BigQuery",
 :query
 {:source-table {:name "blocks", :id 803},
  :aggregation
  [{:aggregation-type :sum, :field {:base-type :type/Integer, :table-id 803, :field-name "gasLimit", :field-display-name "Gas Limit", :visibility-type :normal, :field-id 27662, :table-name "blocks"}}
   {:aggregation-type :sum, :field {:base-type :type/Integer, :table-id 803, :field-name "gasUsed", :field-display-name "Gas Used", :visibility-type :normal, :field-id 27663, :table-name "blocks"}}]}}

Permissions Check 🔒 : Can User 1 access Table 803 (blocks)?
Permissions Check 🔓 : Yes ✅  because User 1 is a member of Group 1 (All Users) which has permissions for '/db/89/'
01-05 22:59:17 DEBUG generic-sql.query-processor :: HoneySQL Form: 🍯
 {:from (:ethereum2.blocks), :select ([{:name :sum, :args (:ethereum2.blocks.gasLimit)} "sum"] [{:name :sum, :args (:ethereum2.blocks.gasUsed)} "sum"])}

01-05 22:59:17 DEBUG metabase.query-processor :: NATIVE FORM: 😳
{:query "SELECT sum([ethereum2.blocks.gasLimit]) AS [sum], sum([ethereum2.blocks.gasUsed]) AS [sum] FROM [ethereum2.blocks]", :table-name "blocks", :mbql? true}


01-05 22:59:17 WARN metabase.util :: auto-retry metabase.driver.google$execute$fn__38384@2fe7ae4c: 2.55 - 2.97: Ambiguous column name sum.
01-05 22:59:18 WARN metabase.util :: auto-retry metabase.driver.google$execute$fn__38384@2fe7ae4c: 2.55 - 2.97: Ambiguous column name sum.
01-05 22:59:19 WARN metabase.util :: auto-retry metabase.driver.bigquery$process_native_STAR_$fn__39428@54e97677: 2.55 - 2.97: Ambiguous column name sum.
01-05 22:59:19 WARN metabase.util :: auto-retry metabase.driver.google$execute$fn__38384@2517c92c: 2.55 - 2.97: Ambiguous column name sum.
01-05 22:59:19 WARN metabase.util :: auto-retry metabase.driver.google$execute$fn__38384@2517c92c: 2.55 - 2.97: Ambiguous column name sum.
01-05 22:59:19 ERROR metabase.query-processor :: {:status :failed,
 :class clojure.lang.ExceptionInfo,
 :error "2.55 - 2.97: Ambiguous column name sum.",
 :stacktrace
 ["driver.google$execute_no_auto_retry.invokeStatic(google.clj:34)"
  "driver.google$execute_no_auto_retry.invoke(google.clj:27)"
  "driver.google$execute$fn__38384.invoke(google.clj:45)"
  "util$do_with_auto_retries.invokeStatic(util.clj:692)"
  "util$do_with_auto_retries.invoke(util.clj:684)"
  "util$do_with_auto_retries.invokeStatic(util.clj:696)"
  "util$do_with_auto_retries.invoke(util.clj:684)"
  "util$do_with_auto_retries.invokeStatic(util.clj:696)"
  "util$do_with_auto_retries.invoke(util.clj:684)"
  "driver.google$execute.invokeStatic(google.clj:44)"
  "driver.google$execute.invoke(google.clj:38)"
  "driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:123)"
  "driver.bigquery$execute_bigquery.invoke(bigquery.clj:114)"
  "driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:116)"
  "driver.bigquery$execute_bigquery.invoke(bigquery.clj:114)"
  "driver.bigquery$process_native_STAR_$fn__39428.invoke(bigquery.clj:183)"
  "util$do_with_auto_retries.invokeStatic(util.clj:692)"
  "util$do_with_auto_retries.invoke(util.clj:684)"
  "util$do_with_auto_retries.invokeStatic(util.clj:696)"
  "util$do_with_auto_retries.invoke(util.clj:684)"
  "driver.bigquery$process_native_STAR_.invokeStatic(bigquery.clj:182)"
  "driver.bigquery$process_native_STAR_.invoke(bigquery.clj:179)"
  "driver.bigquery$execute_query.invokeStatic(bigquery.clj:301)"
  "driver.bigquery$execute_query.invoke(bigquery.clj:299)"
  "driver$eval20449$fn__20450$G__20438__20457.invoke(driver.clj:43)"
  "query_processor$run_query.invokeStatic(query_processor.clj:431)"
  "query_processor$run_query.invoke(query_processor.clj:415)"
  "query_processor$pre_check_query_permissions$fn__33985.invoke(query_processor.clj:370)"
  "query_processor$limit$fn__33970.invoke(query_processor.clj:342)"
  "query_processor$cumulative_aggregation$fn__33964.invoke(query_processor.clj:322)"
  "query_processor$cumulative_aggregation$fn__33964.invoke(query_processor.clj:322)"
  "query_processor$post_format_rows$fn__33841.invoke(query_processor.clj:216)"
  "query_processor$post_add_row_count_and_status$fn__33786.invoke(query_processor.clj:192)"
  "query_processor$wrap_catch_exceptions$fn__33764.invoke(query_processor.clj:122)"
  "query_processor$process_query.invokeStatic(query_processor.clj:479)"
  "query_processor$process_query.invoke(query_processor.clj:470)"
  "query_processor$dataset_query.invokeStatic(query_processor.clj:562)"
  "query_processor$dataset_query.invoke(query_processor.clj:520)"
  "api.dataset$fn__35443$fn__35446.invoke(dataset.clj:35)"
  "api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:222)"
  "api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:219)"
  "api.dataset$fn__35443.invokeStatic(dataset.clj:29)"
  "api.dataset$fn__35443.invoke(dataset.clj:29)"
  "middleware$enforce_authentication$fn__34466.invoke(middleware.clj:84)"
  "api.routes$fn__37929.invokeStatic(routes.clj:45)"
  "api.routes$fn__37929.invoke(routes.clj:45)"
  "routes$fn__37985.invokeStatic(routes.clj:24)"
  "routes$fn__37985.invoke(routes.clj:24)"
  "middleware$log_api_call$fn__34570$fn__34572.invoke(middleware.clj:275)"
  "db$_do_with_call_counting.invokeStatic(db.clj:461)"
  "db$_do_with_call_counting.invoke(db.clj:455)"
  "middleware$log_api_call$fn__34570.invoke(middleware.clj:274)"
  "middleware$add_security_headers$fn__34513.invoke(middleware.clj:209)"
  "middleware$bind_current_user$fn__34470.invoke(middleware.clj:104)"],
 :query
 {:type "query",
  :query {:source_table 803, :aggregation [["sum" ["field-id" 27662]] ["sum" ["field-id" 27663]]]},
  :parameters [],
  :constraints {:max-results 10000, :max-results-bare-rows 2000},
  :info {:executed-by 1, :card-id nil, :uuid "f3ec2a9c-85bd-4e8c-b43f-40fb9ea89886", :query-hash 1590943132, :query-type "MBQL"}},
 :expanded-query
 {:constraints {:max-results 10000, :max-results-bare-rows 2000},
  :fk-field-ids #{},
  :table-ids #{803},
  :type "query",
  :info {:executed-by 1, :card-id nil, :uuid "f3ec2a9c-85bd-4e8c-b43f-40fb9ea89886", :query-hash 1590943132, :query-type "MBQL"},
  :query
  {:source-table {:schema nil, :name "blocks", :id 803},
   :aggregation
   [{:aggregation-type :sum,
     :field
     {:field-id 27662,
      :field-name "gasLimit",
      :field-display-name "Gas Limit",
      :base-type :type/Integer,
      :special-type nil,
      :visibility-type :normal,
      :table-id 803,
      :schema-name nil,
      :table-name "blocks",
      :position nil,
      :fk-field-id nil,
      :description nil,
      :parent-id nil,
      :parent nil},
     :custom-name nil}
    {:aggregation-type :sum,
     :field
     {:field-id 27663,
      :field-name "gasUsed",
      :field-display-name "Gas Used",
      :base-type :type/Integer,
      :special-type nil,
      :visibility-type :normal,
      :table-id 803,
      :schema-name nil,
      :table-name "blocks",
      :position nil,
      :fk-field-id nil,
      :description nil,
      :parent-id nil,
      :parent nil},
     :custom-name nil}],
   :join-tables nil},
  :parameters []},
 :ex-data
 {"code" 400,
  "errors" [{"domain" "global", "location" "query", "locationType" "other", "message" "2.55 - 2.97: Ambiguous column name sum.", "reason" "invalidQuery"}],
  "message" "2.55 - 2.97: Ambiguous column name sum."}}

01-05 22:59:19 ERROR metabase.query-processor :: Query failure: 2.55 - 2.97: Ambiguous column name sum.
["query_processor$assert_valid_query_result.invokeStatic(query_processor.clj:518)"
 "query_processor$assert_valid_query_result.invoke(query_processor.clj:513)"
 "query_processor$dataset_query.invokeStatic(query_processor.clj:563)"
 "query_processor$dataset_query.invoke(query_processor.clj:520)"
 "api.dataset$fn__35443$fn__35446.invoke(dataset.clj:35)"
 "api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:222)"
 "api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:219)"
 "api.dataset$fn__35443.invokeStatic(dataset.clj:29)"
 "api.dataset$fn__35443.invoke(dataset.clj:29)"
 "middleware$enforce_authentication$fn__34466.invoke(middleware.clj:84)"
 "api.routes$fn__37929.invokeStatic(routes.clj:45)"
 "api.routes$fn__37929.invoke(routes.clj:45)"
 "routes$fn__37985.invokeStatic(routes.clj:24)"
 "routes$fn__37985.invoke(routes.clj:24)"
 "middleware$log_api_call$fn__34570$fn__34572.invoke(middleware.clj:275)"
 "db$_do_with_call_counting.invokeStatic(db.clj:461)"
 "db$_do_with_call_counting.invoke(db.clj:455)"
 "middleware$log_api_call$fn__34570.invoke(middleware.clj:274)"
 "middleware$add_security_headers$fn__34513.invoke(middleware.clj:209)"
 "middleware$bind_current_user$fn__34470.invoke(middleware.clj:104)"]

01-05 22:59:19 DEBUG metabase.middleware :: POST /api/dataset 200 (2 s) (13 DB calls)

[camsaul]

Like #4087 this is a BigQuery limitation. We could either automatically add suffixes to column names here (e.g. count_2) or enforce this on the frontend and so people can give the columns more meaningful names

[mazameli]

Seems like we should do the automatic suffix adding, and then file a separate issue to add column heading renaming to table viz settings (like we do for axis labels on other chart types). In the UI, count_2 could be shown as Count 2