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

Notebook Editor filter breaks on custom column returning a date - (seems Bigquery specific) #47888

Open
Tony-metabase opened this issue Sep 12, 2024 · 1 comment
Open

Notebook Editor filter breaks on custom column returning a date - (seems Bigquery specific) #47888

Tony-metabase opened this issue Sep 12, 2024 · 1 comment
Assignees
@lbrdnk
Labels
.Backend Database/BigQuery Priority:P1 Security holes w/o exploit, crashing, setup/upgrade, login, broken common features, correctness Querying/MBQL .Regression/master Regression that is only present on master, or bug in new upcoming feature .Team/Querying Type:Bug Product defects

Comments

@Tony-metabase
Copy link
Contributor

Describe the bug

I created a custom column and when i attempt to filter on it the question breaks. I am able to query if I select the "include this xxx" option.

You will not be able to replicate this on stats due to #47887

To Reproduce

Make sure you are connected to a BigQuery DB (I tested on PG and MYSQL and none of them show this issue but others might)

  1. Go to New -> BigQuery Sample Test -> Make sure you select Table with a Date column -> People -> Add the following custom column:

case([Name] = "Won", datetimeAdd([Created At], 0, "month"), datetimeAdd([Birth Date], 0, "month"))

image
  1. Go to Filter on this column with previous 12 months and notice the error:
image

3 This works if you select the Include this month option:

image

Expected behavior

The Filter Works

Logs

ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: 400 Bad Request
POST https://bigquery.googleapis.com/bigquery/v2/projects/metabase-driver-dev/queries
{
  "code": 400,
  "errors": [
    {
      "domain": "global",
      "location": "q",
      "locationType": "parameter",
      "message": "No matching signature for operator < for argument types: DATETIME, TIMESTAMP. Supported signature: ANY < ANY at [2:1209]",
      "reason": "invalidQuery"
    }
  ],
  "message": "No matching signature for operator < for argument types: DATETIME, TIMESTAMP. Supported signature: ANY < ANY at [2:1209]",
  "status": "INVALID_ARGUMENT"
}
{:database_id 7,
 :started_at #t "2024-09-12T12:07:37.416435Z[GMT]",
 :via
 [{:status :failed,
   :class com.google.cloud.bigquery.BigQueryException,
   :error
   "No matching signature for operator < for argument types: DATETIME, TIMESTAMP. Supported signature: ANY < ANY at [2:1209]",
   :stacktrace
   ["com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.translate(HttpBigQueryRpc.java:115)"
    "com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.queryRpc(HttpBigQueryRpc.java:771)"
    "com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1365)"
    "com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1362)"
    "com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:103)"
    "com.google.cloud.bigquery.BigQueryRetryHelper.run(BigQueryRetryHelper.java:86)"
    "com.google.cloud.bigquery.BigQueryRetryHelper.runWithRetries(BigQueryRetryHelper.java:49)"
    "com.google.cloud.bigquery.BigQueryImpl.queryRpc(BigQueryImpl.java:1361)"
    "com.google.cloud.bigquery.BigQueryImpl.query(BigQueryImpl.java:1349)"
    "--> driver.bigquery_cloud_sdk$execute_bigquery_off_thread$fn__127900$fn__127901.invoke(bigquery_cloud_sdk.clj:421)"
    "driver.bigquery_cloud_sdk$execute_bigquery_off_thread$fn__127900.invoke(bigquery_cloud_sdk.clj:418)"]}
  {:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error
   "Error executing query: No matching signature for operator < for argument types: DATETIME, TIMESTAMP. Supported signature: ANY < ANY at [2:1209]",
   :stacktrace
   ["--> driver.bigquery_cloud_sdk$throw_invalid_query.invokeStatic(bigquery_cloud_sdk.clj:395)"
    "driver.bigquery_cloud_sdk$throw_invalid_query.invoke(bigquery_cloud_sdk.clj:394)"
    "driver.bigquery_cloud_sdk$handle_bigquery_exception.invokeStatic(bigquery_cloud_sdk.clj:449)"
    "driver.bigquery_cloud_sdk$handle_bigquery_exception.invoke(bigquery_cloud_sdk.clj:436)"
    "driver.bigquery_cloud_sdk$execute_bigquery.invokeStatic(bigquery_cloud_sdk.clj:471)"
    "driver.bigquery_cloud_sdk$execute_bigquery.invoke(bigquery_cloud_sdk.clj:453)"
    "driver.bigquery_cloud_sdk$execute_bigquery_on_db.invokeStatic(bigquery_cloud_sdk.clj:477)"
    "driver.bigquery_cloud_sdk$execute_bigquery_on_db.invoke(bigquery_cloud_sdk.clj:474)"
    "driver.bigquery_cloud_sdk$_STAR_process_native_STAR_$thunk__128038.invoke(bigquery_cloud_sdk.clj:517)"
    "driver.bigquery_cloud_sdk$_STAR_process_native_STAR_.invokeStatic(bigquery_cloud_sdk.clj:524)"
    "driver.bigquery_cloud_sdk$_STAR_process_native_STAR_.invoke(bigquery_cloud_sdk.clj:506)"
    "driver.bigquery_cloud_sdk$fn__128054.invokeStatic(bigquery_cloud_sdk.clj:545)"
    "driver.bigquery_cloud_sdk$fn__128054.invoke(bigquery_cloud_sdk.clj:537)"
    "query_processor.pipeline$_STAR_execute_STAR_.invokeStatic(pipeline.clj:47)"
    "query_processor.pipeline$_STAR_execute_STAR_.invoke(pipeline.clj:34)"
    "query_processor.pipeline$_STAR_run_STAR_.invokeStatic(pipeline.clj:97)"
    "query_processor.pipeline$_STAR_run_STAR_.invoke(pipeline.clj:90)"
    "query_processor.execute$run.invokeStatic(execute.clj:60)"
    "query_processor.execute$run.invoke(execute.clj:54)"
    "query_processor.middleware.update_used_cards$update_used_cards_BANG_$fn__71508.invoke(update_used_cards.clj:60)"
    "query_processor.execute$add_native_form_to_result_metadata$fn__71523.invoke(execute.clj:23)"
    "query_processor.execute$add_preprocessed_query_to_result_metadata_for_userland_query$fn__71528.invoke(execute.clj:34)"
    "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___71481.invoke(cache.clj:239)"
    "query_processor.middleware.permissions$check_query_permissions$fn__66122.invoke(permissions.clj:147)"
    "metabase_enterprise.advanced_permissions.query_processor.middleware.permissions$fn__108681$check_download_permissions__108682$fn__108683.invoke(permissions.clj:90)"
    "query_processor.middleware.enterprise$check_download_permissions_middleware$fn__66734.invoke(enterprise.clj:51)"
    "metabase_enterprise.sandbox.query_processor.middleware.column_level_perms_check$fn__110512$maybe_apply_column_level_perms_check__110513$fn__110514.invoke(column_level_perms_check.clj:38)"
    "query_processor.middleware.enterprise$maybe_apply_column_level_perms_check_middleware$fn__66744.invoke(enterprise.clj:64)"
    "query_processor.execute$execute$fn__71555.invoke(execute.clj:92)"
    "query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:225)"
    "query_processor.setup$do_with_qp_setup.invoke(setup.clj:216)"
    "query_processor.execute$execute.invokeStatic(execute.clj:91)"
    "query_processor.execute$execute.invoke(execute.clj:87)"
    "query_processor$process_query_STAR__STAR_.invokeStatic(query_processor.clj:47)"
    "query_processor$process_query_STAR__STAR_.invoke(query_processor.clj:43)"
    "metabase_enterprise.audit_app.query_processor.middleware.handle_audit_queries$fn__82876$handle_audit_app_internal_queries__82877$fn__82878.invoke(handle_audit_queries.clj:145)"
    "query_processor.middleware.enterprise$handle_audit_app_internal_queries_middleware$fn__66772.invoke(enterprise.clj:103)"
    "query_processor.middleware.process_userland_query$process_userland_query_middleware$fn__76744.invoke(process_userland_query.clj:198)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__76813.invoke(catch_exceptions.clj:128)"
    "query_processor$process_query$fn__76850.invoke(query_processor.clj:78)"
    "query_processor.setup$do_with_canceled_chan$fn__67176.invoke(setup.clj:187)"
    "query_processor.setup$do_with_database_local_settings$fn__67171.invoke(setup.clj:181)"
    "query_processor.setup$do_with_driver$fn__67166$fn__67167.invoke(setup.clj:166)"
    "driver$do_with_driver.invokeStatic(driver.clj:104)"
    "driver$do_with_driver.invoke(driver.clj:99)"
    "query_processor.setup$do_with_driver$fn__67166.invoke(setup.clj:165)"
    "query_processor.setup$do_with_metadata_provider$fn__67159$fn__67162.invoke(setup.clj:151)"
    "query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:170)"
    "query_processor.store$do_with_metadata_provider.invoke(store.clj:150)"
    "query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:159)"
    "query_processor.store$do_with_metadata_provider.invoke(store.clj:150)"
    "query_processor.setup$do_with_metadata_provider$fn__67159.invoke(setup.clj:150)"
    "query_processor.setup$do_with_resolved_database$fn__67153.invoke(setup.clj:128)"
    "query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:232)"
    "query_processor.setup$do_with_qp_setup.invoke(setup.clj:216)"
    "query_processor$process_query.invokeStatic(query_processor.clj:76)"
    "query_processor$process_query.invoke(query_processor.clj:69)"
    "api.dataset$run_streaming_query$fn__94750.invoke(dataset.clj:84)"
    "query_processor.streaming$_streaming_response$fn__70064$fn__70065$fn__70066.invoke(streaming.clj:176)"
    "query_processor.streaming$_streaming_response$fn__70064$fn__70065.invoke(streaming.clj:174)"
    "query_processor.streaming$do_with_streaming_rff.invokeStatic(streaming.clj:165)"
    "query_processor.streaming$do_with_streaming_rff.invoke(streaming.clj:152)"
    "query_processor.streaming$_streaming_response$fn__70064.invoke(streaming.clj:171)"
    "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:78)"
    "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:76)"
    "async.streaming_response$do_f_async$task__52029.invoke(streaming_response.clj:97)"],
   :error_type :invalid-query,
   :ex-data
   {:type :invalid-query,
    :sql
    "-- Metabase:: userID: 1 queryType: MBQL queryHash: fdb026a8ea5aa796591d94b04fde94b02e359c6dc7ac5cb00f3b2ef149aab50f\nSELECT `sample_database.people`.`person_id` AS `person_id`, `sample_database.people`.`address` AS `address`, `sample_database.people`.`email` AS `email`, `sample_database.people`.`password` AS `password`, `sample_database.people`.`name` AS `name`, `sample_database.people`.`city` AS `city`, `sample_database.people`.`longitude` AS `longitude`, `sample_database.people`.`state` AS `state`, `sample_database.people`.`source` AS `source`, `sample_database.people`.`birth_date` AS `birth_date`, `sample_database.people`.`zip` AS `zip`, `sample_database.people`.`latitude` AS `latitude`, `sample_database.people`.`created_at` AS `created_at`, CASE WHEN `sample_database.people`.`name` = ? THEN DATETIME_ADD(CAST(`sample_database.people`.`created_at` AS datetime), INTERVAL 0 month) ELSE DATE_ADD(`sample_database.people`.`birth_date`, INTERVAL 0 month) END AS `test` FROM `sample_database.people` WHERE (DATETIME(CASE WHEN `sample_database.people`.`name` = ? THEN DATETIME_ADD(CAST(`sample_database.people`.`created_at` AS datetime), INTERVAL 0 month) ELSE DATE_ADD(`sample_database.people`.`birth_date`, INTERVAL 0 month) END) >= DATETIME_TRUNC(DATETIME_ADD(CURRENT_DATETIME(), INTERVAL -12 month), month)) AND (CASE WHEN `sample_database.people`.`name` = ? THEN DATETIME_ADD(CAST(`sample_database.people`.`created_at` AS datetime), INTERVAL 0 month) ELSE DATE_ADD(`sample_database.people`.`birth_date`, INTERVAL 0 month) END < DATETIME_TRUNC(CURRENT_TIMESTAMP(), month)) LIMIT 10",
    :parameters ("Won" "Won" "Won")}}],
 :action_id nil,
 :error_type :invalid-query,
 :json_query
 {:database 7,
  :type "query",
  :query
  {:source-table 151,
   :expressions
   {:test
    ["case"
     [[["=" ["field" 1648 {:base-type "type/Text"}] "Won"]
       ["datetime-add" ["field" 1638 {:base-type "type/DateTimeWithLocalTZ"}] 0 "month"]]]
     {:default ["datetime-add" ["field" 1643 {:base-type "type/Date"}] 0 "month"]}]},
   :limit 10,
   :filter ["time-interval" ["expression" "test" {:base-type "type/Temporal"}] -12 "month" {:include-current false}]},
  :parameters [],
  :middleware {:js-int-to-string? true, :userland-query? true, :add-default-userland-constraints? true}},
 :native
 {:query
  "SELECT `sample_database.people`.`person_id` AS `person_id`, `sample_database.people`.`address` AS `address`, `sample_database.people`.`email` AS `email`, `sample_database.people`.`password` AS `password`, `sample_database.people`.`name` AS `name`, `sample_database.people`.`city` AS `city`, `sample_database.people`.`longitude` AS `longitude`, `sample_database.people`.`state` AS `state`, `sample_database.people`.`source` AS `source`, `sample_database.people`.`birth_date` AS `birth_date`, `sample_database.people`.`zip` AS `zip`, `sample_database.people`.`latitude` AS `latitude`, `sample_database.people`.`created_at` AS `created_at`, CASE WHEN `sample_database.people`.`name` = ? THEN DATETIME_ADD(CAST(`sample_database.people`.`created_at` AS datetime), INTERVAL 0 month) ELSE DATE_ADD(`sample_database.people`.`birth_date`, INTERVAL 0 month) END AS `test` FROM `sample_database.people` WHERE (DATETIME(CASE WHEN `sample_database.people`.`name` = ? THEN DATETIME_ADD(CAST(`sample_database.people`.`created_at` AS datetime), INTERVAL 0 month) ELSE DATE_ADD(`sample_database.people`.`birth_date`, INTERVAL 0 month) END) >= DATETIME_TRUNC(DATETIME_ADD(CURRENT_DATETIME(), INTERVAL -12 month), month)) AND (CASE WHEN `sample_database.people`.`name` = ? THEN DATETIME_ADD(CAST(`sample_database.people`.`created_at` AS datetime), INTERVAL 0 month) ELSE DATE_ADD(`sample_database.people`.`birth_date`, INTERVAL 0 month) END < DATETIME_TRUNC(CURRENT_TIMESTAMP(), month)) LIMIT 10",
  :params ("Won" "Won" "Won"),
  :table-name "people",
  :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$3.interceptResponse(AbstractGoogleClientRequest.java:466)"
  "com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1111)"
  "com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:552)"
  "com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:493)"
  "com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:603)"
  "com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.queryRpc(HttpBigQueryRpc.java:769)"
  "com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1365)"
  "com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1362)"
  "com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:103)"
  "com.google.cloud.bigquery.BigQueryRetryHelper.run(BigQueryRetryHelper.java:86)"
  "com.google.cloud.bigquery.BigQueryRetryHelper.runWithRetries(BigQueryRetryHelper.java:49)"
  "com.google.cloud.bigquery.BigQueryImpl.queryRpc(BigQueryImpl.java:1361)"
  "com.google.cloud.bigquery.BigQueryImpl.query(BigQueryImpl.java:1349)"
  "--> driver.bigquery_cloud_sdk$execute_bigquery_off_thread$fn__127900$fn__127901.invoke(bigquery_cloud_sdk.clj:421)"
  "driver.bigquery_cloud_sdk$execute_bigquery_off_thread$fn__127900.invoke(bigquery_cloud_sdk.clj:418)"],
 :card_id nil,
 :context :ad-hoc,
 :error
 "400 Bad Request\nPOST https://bigquery.googleapis.com/bigquery/v2/projects/metabase-driver-dev/queries\n{\n  \"code\": 400,\n  \"errors\": [\n    {\n      \"domain\": \"global\",\n      \"location\": \"q\",\n      \"locationType\": \"parameter\",\n      \"message\": \"No matching signature for operator < for argument types: DATETIME, TIMESTAMP. Supported signature: ANY < ANY at [2:1209]\",\n      \"reason\": \"invalidQuery\"\n    }\n  ],\n  \"message\": \"No matching signature for operator < for argument types: DATETIME, TIMESTAMP. Supported signature: ANY < ANY at [2:1209]\",\n  \"status\": \"INVALID_ARGUMENT\"\n}",
 :row_count 0,
 :running_time 0,
 :preprocessed
 {:database 7,
  :middleware {:js-int-to-string? true, :userland-query? true, :add-default-userland-constraints? true},
  :info {:executed-by 1, :context :ad-hoc},
  :constraints {:max-results 10000, :max-results-bare-rows 10000},
  :type :query,
  :query
  {:source-table 151,
   :expressions
   {"test"
    [:case
     [[[:=
        [:field 1648 {:base-type :type/Text}]
        [:value
         "Won"
         {:base_type :type/Text,
          :effective_type :type/Text,
          :coercion_strategy nil,
          :semantic_type :type/Name,
          :database_type "STRING",
          :name "name"}]]
       [:datetime-add [:field 1638 {:base-type :type/DateTimeWithLocalTZ, :temporal-unit :default}] 0 :month]]]
     {:default [:datetime-add [:field 1643 {:base-type :type/Date, :temporal-unit :default}] 0 :month]}]},
   :limit 10,
   :filter
   [:and
    [:>= [:expression "test" {:base-type :type/Temporal, :temporal-unit :default}] [:relative-datetime -12 :month]]
    [:< [:expression "test" {:base-type :type/Temporal, :temporal-unit :default}] [:relative-datetime 0 :month]]],
   :fields
   [[:field 1641 nil]
    [:field 1639 nil]
    [:field 1640 nil]
    [:field 1646 nil]
    [:field 1648 nil]
    [:field 1649 nil]
    [:field 1644 nil]
    [:field 1645 nil]
    [:field 1650 nil]
    [:field 1643 {:temporal-unit :default}]
    [:field 1647 nil]
    [:field 1642 nil]
    [:field 1638 {:temporal-unit :default}]
    [:expression "test"]]}},
 :data {:rows [], :cols []}}

Information about your Metabase installation

Tested on 50 but couldn't test it on master (due to bug 47887)

Severity

Breaks Bigquery question so it's a big problem

Additional context

No response
@Tony-metabase Tony-metabase added Type:Bug Product defects Priority:P1 Security holes w/o exploit, crashing, setup/upgrade, login, broken common features, correctness .Needs Triage labels Sep 12, 2024
@bshepherdson
Copy link
Contributor

There's some unusual logic for BigQuery around date filters; they don't use < etc.

I suspect this is just a symptom of #47887 and that this will start working with no extra effort once we fix that issue. (Because then the QP will see this column as a date again, and generate the right Bigquery syntax.)

@bshepherdson bshepherdson added Querying/MBQL Database/BigQuery .Backend .Regression/master Regression that is only present on master, or bug in new upcoming feature .Team/Querying and removed .Needs Triage labels Sep 12, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@lbrdnk lbrdnk

Labels
.Backend Database/BigQuery Priority:P1 Security holes w/o exploit, crashing, setup/upgrade, login, broken common features, correctness Querying/MBQL .Regression/master Regression that is only present on master, or bug in new upcoming feature .Team/Querying Type:Bug Product defects
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@bshepherdson @lbrdnk @Tony-metabase