search

metabase / metabase

The simplest, fastest way to get business intelligence and analytics to everyone in your company :yum:
https://metabase.com
Other
37.46k stars 4.97k forks source link

Filtering a question between relative dates after summarizing by date leads to error #27486

Open BertrandToulas opened 1 year ago

BertrandToulas commented 1 year ago

Describe the bug

Using a relative date filter (not just specific date) after summarizing by date leads to the following error: 400 Bad Request POST https://www.googleapis.com/bigquery/v2/projects/moodwalk/queries { "code" : 400, "errors" : [ { "domain" : "global", "location" : "q", "locationType" : "parameter", "message" : "No matching signature for operator < for argument types: TIMESTAMP, DATETIME. Supported signature: ANY < ANY at [2:460]", "reason" : "invalidQuery" } ], "message" : "No matching signature for operator < for argument types: TIMESTAMP, DATETIME. Supported signature: ANY < ANY at [2:460]", "status" : "INVALID_ARGUMENT" }

This is different from #25994, where summarizing a min/max date, then filtering the result with a specific date leads to a different error.

Logs Server log :

[e3b5baaa-ef35-46e3-bd1d-510c2cd00afc] 2023-01-03T16:46:08+01:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: 400 Bad Request
POST https://www.googleapis.com/bigquery/v2/projects/moodwalk/queries
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "location" : "q",
    "locationType" : "parameter",
    "message" : "No matching signature for operator < for argument types: TIMESTAMP, DATETIME. Supported signature: ANY < ANY at [2:460]",
    "reason" : "invalidQuery"
  } ],
  "message" : "No matching signature for operator < for argument types: TIMESTAMP, DATETIME. Supported signature: ANY < ANY at [2:460]",
  "status" : "INVALID_ARGUMENT"
}
{:database_id 24,
 :started_at #t "2023-01-03T16:46:06.987306+01:00[Europe/Paris]",
 :via
 [{:status :failed,
   :class com.google.cloud.bigquery.BigQueryException,
   :error
   "No matching signature for operator < for argument types: TIMESTAMP, DATETIME. Supported signature: ANY < ANY at [2:460]",
   :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__82508.invoke(bigquery_cloud_sdk.clj:193)"]}
  {:status :failed,
   :class java.util.concurrent.ExecutionException,
   :error
   "com.google.cloud.bigquery.BigQueryException: No matching signature for operator < for argument types: TIMESTAMP, DATETIME. Supported signature: ANY < ANY at [2:460]",
   :stacktrace
   ["java.base/java.util.concurrent.FutureTask.report(Unknown Source)"
    "java.base/java.util.concurrent.FutureTask.get(Unknown Source)"
    "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__82609.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__82615.invokeStatic(bigquery_cloud_sdk.clj:307)"
    "driver.bigquery_cloud_sdk$fn__82615.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___52623.invoke(cache.clj:220)"
    "query_processor.middleware.permissions$check_query_permissions$fn__48144.invoke(permissions.clj:109)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__51568.invoke(mbql_to_native.clj:23)"
    "query_processor$fn__54099$combined_post_process__54104$combined_post_process_STAR___54105.invoke(query_processor.clj:212)"
    "query_processor$fn__54099$combined_pre_process__54100$combined_pre_process_STAR___54101.invoke(query_processor.clj:209)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52544$fn__52549.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__52544.invoke(resolve_database_and_driver.clj:34)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48579.invoke(fetch_source_query.clj:353)"
    "query_processor.middleware.store$initialize_store$fn__48767$fn__48768.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__48767.invoke(store.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__52816.invoke(normalize_query.clj:22)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__49849.invoke(constraints.clj:53)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__52755.invoke(process_userland_query.clj:145)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__53127.invoke(catch_exceptions.clj:167)"
    "query_processor.reducible$async_qp$qp_STAR___44982$thunk__44984.invoke(reducible.clj:100)"
    "query_processor.reducible$async_qp$qp_STAR___44982.invoke(reducible.clj:106)"
    "query_processor.reducible$async_qp$qp_STAR___44982.invoke(reducible.clj:91)"
    "query_processor.reducible$sync_qp$qp_STAR___44993.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__54146$process_query_and_save_execution_BANG___54155$fn__54158.invoke(query_processor.clj:342)"
    "query_processor$fn__54146$process_query_and_save_execution_BANG___54155.invoke(query_processor.clj:335)"
    "query_processor$fn__54190$process_query_and_save_with_max_results_constraints_BANG___54199$fn__54202.invoke(query_processor.clj:354)"
    "query_processor$fn__54190$process_query_and_save_with_max_results_constraints_BANG___54199.invoke(query_processor.clj:347)"
    "api.dataset$run_query_async$fn__68896.invoke(dataset.clj:69)"
    "query_processor.streaming$streaming_response_STAR_$fn__40100$fn__40101.invoke(streaming.clj:162)"
    "query_processor.streaming$streaming_response_STAR_$fn__40100.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: No matching signature for operator < for argument types: TIMESTAMP, DATETIME. Supported signature: ANY < ANY at [2:460]",
   :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__82609.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__82615.invokeStatic(bigquery_cloud_sdk.clj:307)"
    "driver.bigquery_cloud_sdk$fn__82615.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___52623.invoke(cache.clj:220)"
    "query_processor.middleware.permissions$check_query_permissions$fn__48144.invoke(permissions.clj:109)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__51568.invoke(mbql_to_native.clj:23)"
    "query_processor$fn__54099$combined_post_process__54104$combined_post_process_STAR___54105.invoke(query_processor.clj:212)"
    "query_processor$fn__54099$combined_pre_process__54100$combined_pre_process_STAR___54101.invoke(query_processor.clj:209)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52544$fn__52549.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__52544.invoke(resolve_database_and_driver.clj:34)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48579.invoke(fetch_source_query.clj:353)"
    "query_processor.middleware.store$initialize_store$fn__48767$fn__48768.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__48767.invoke(store.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__52816.invoke(normalize_query.clj:22)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__49849.invoke(constraints.clj:53)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__52755.invoke(process_userland_query.clj:145)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__53127.invoke(catch_exceptions.clj:167)"
    "query_processor.reducible$async_qp$qp_STAR___44982$thunk__44984.invoke(reducible.clj:100)"
    "query_processor.reducible$async_qp$qp_STAR___44982.invoke(reducible.clj:106)"
    "query_processor.reducible$async_qp$qp_STAR___44982.invoke(reducible.clj:91)"
    "query_processor.reducible$sync_qp$qp_STAR___44993.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__54146$process_query_and_save_execution_BANG___54155$fn__54158.invoke(query_processor.clj:342)"
    "query_processor$fn__54146$process_query_and_save_execution_BANG___54155.invoke(query_processor.clj:335)"
    "query_processor$fn__54190$process_query_and_save_with_max_results_constraints_BANG___54199$fn__54202.invoke(query_processor.clj:354)"
    "query_processor$fn__54190$process_query_and_save_with_max_results_constraints_BANG___54199.invoke(query_processor.clj:347)"
    "api.dataset$run_query_async$fn__68896.invoke(dataset.clj:69)"
    "query_processor.streaming$streaming_response_STAR_$fn__40100$fn__40101.invoke(streaming.clj:162)"
    "query_processor.streaming$streaming_response_STAR_$fn__40100.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: 33 queryType: MBQL queryHash: a77535fcdbdb4a75f334a79c37ccc92830ca8395b3834d174978fa834d3adf76\nSELECT `source`.`user_id` AS `user_id`, `source`.`min` AS `min` FROM (SELECT `production_moodwork_db.user_tests_view`.`user_id` AS `user_id`, min(timestamp_trunc(`production_moodwork_db.user_tests_view`.`test_created_at`, month)) AS `min` FROM `production_moodwork_db.user_tests_view` GROUP BY `user_id` ORDER BY `user_id` ASC) `source` WHERE (CAST(`source`.`min` AS datetime) >= datetime_trunc(datetime_add(current_datetime(), INTERVAL -2 month), month) AND `source`.`min` < datetime_trunc(current_datetime(), month)) LIMIT 2000",
    :parameters nil}}],
 :error_type :invalid-query,
 :json_query
 {:database 24,
  :query
  {:source-query
   {:source-table 2041,
    :aggregation [["min" ["field" 30214 {:temporal-unit "month"}]]],
    :breakout [["field" 29316 nil]]},
   :filter ["time-interval" ["field" "min" {:base-type "type/Float"}] -2 "month"]},
  :type "query",
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :native
 {:query
  "SELECT `source`.`user_id` AS `user_id`, `source`.`min` AS `min` FROM (SELECT `production_moodwork_db.user_tests_view`.`user_id` AS `user_id`, min(timestamp_trunc(`production_moodwork_db.user_tests_view`.`test_created_at`, month)) AS `min` FROM `production_moodwork_db.user_tests_view` GROUP BY `user_id` ORDER BY `user_id` ASC) `source` WHERE (CAST(`source`.`min` AS datetime) >= datetime_trunc(datetime_add(current_datetime(), INTERVAL -2 month), month) AND `source`.`min` < datetime_trunc(current_datetime(), month)) LIMIT 1048575",
  :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__82508.invoke(bigquery_cloud_sdk.clj:193)"],
 :card_id nil,
 :context :ad-hoc,
 :error
 "400 Bad Request\nPOST https://www.googleapis.com/bigquery/v2/projects/moodwalk/queries\n{\n  \"code\" : 400,\n  \"errors\" : [ {\n    \"domain\" : \"global\",\n    \"location\" : \"q\",\n    \"locationType\" : \"parameter\",\n    \"message\" : \"No matching signature for operator < for argument types: TIMESTAMP, DATETIME. Supported signature: ANY < ANY at [2:460]\",\n    \"reason\" : \"invalidQuery\"\n  } ],\n  \"message\" : \"No matching signature for operator < for argument types: TIMESTAMP, DATETIME. Supported signature: ANY < ANY at [2:460]\",\n  \"status\" : \"INVALID_ARGUMENT\"\n}",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:database 24,
  :query
  {:filter
   [:and
    [:>= [:field "min" {:base-type :type/Float, :temporal-unit :default}] [:relative-datetime -2 :month]]
    [:< [:field "min" {:base-type :type/Float, :temporal-unit :default}] [:relative-datetime 0 :month]]],
   :source-metadata
   [{:semantic_type nil,
     :table_id 2041,
     :coercion_strategy nil,
     :name "user_id",
     :settings nil,
     :field_ref [:field 29316 nil],
     :effective_type :type/Integer,
     :nfc_path nil,
     :parent_id nil,
     :id 29316,
     :display_name "user_id",
     :fingerprint
     {:global {:distinct-count 6944, :nil% 0.0},
      :type
      {:type/Number
       {:min 172.0,
        :q1 26907.024433847666,
        :q3 36280.299719380004,
        :max 41467.0,
        :sd 8674.829683989376,
        :avg 30140.4174}}},
     :base_type :type/Integer}
    {:name "min",
     :display_name "Min of test_created_at",
     :base_type :type/DateTimeWithLocalTZ,
     :semantic_type :type/CreationTimestamp,
     :settings nil,
     :field_ref [:aggregation 0]}],
   :fields [[:field 29316 nil] [:field "min" {:base-type :type/DateTimeWithLocalTZ, :temporal-unit :default}]],
   :source-query
   {:source-table 2041,
    :aggregation [[:aggregation-options [:min [:field 30214 {:temporal-unit :month}]] {:name "min"}]],
    :breakout [[:field 29316 nil]],
    :order-by [[:asc [:field 29316 nil]]]},
   :limit 1048575,
   :metabase.query-processor.middleware.limit/original-limit nil},
  :type :query,
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
  :info {:executed-by 33, :context :ad-hoc}},
 :data {:rows [], :cols []}}

To Reproduce Unable to reproduce with the Sample Database.

Expected behavior The question should return a result.

Screenshots An example of non-functional question, using one of our databases image image

Information about your Metabase Installation:

Severity Cannot remember what specific question I was trying to create with this one at the time, been a little while since I last attempted this...

Additional Context I initially thought the issue might be one of incorrect datatype on our end, but the same error occurs no matter which date variable or database I use (besides the Sample Database), so it's probably something else.

flamber commented 1 year ago

Seems more related to #22531 as it looks like it's somewhat specific to BigQuery.