search

AlexR2D2 / metabase_duckdb_driver

Metabase DuckDB Driver shipped as 3rd party plugin
Apache License 2.0
72 stars 24 forks source link

Unable to run query with any dates #8

Open vsreekanti opened 1 year ago

vsreekanti commented 1 year ago

Hi there! I've tried running multiple queries with dates, and none of them seem to work. They all result in "Unknown target type 91" regardless of what I do. I've tried using Date variables in my queries and also tried using a date ranger Filter Field, and both result in the same error message. I suspect it has something to do with how the date field is being parsed/inserted in the driver and what DuckDB is expecting. Please let me know if I'm doing something wrong or if there's a way to resolve this.

Thanks for building the library!


{:database_id 2,
 :started_at #t "2023-01-13T00:49:03.727107Z[Etc/UTC]",
 :state nil,
 :json_query
 {:database 2,
  :native
  {:template-tags
   {:date_range
    {:id "3fe6e2fe-5ffa-f98b-3667-fccb581f7820",
     :name "date_range",
     :display-name "Date Range",
     :type "dimension",
     :dimension ["field" 83 nil],
     :widget-type "date/range",
     :required false}},
   :query "select * from website_pageviews where {{date_range}};"},
  :type "native",
  :parameters
  [{:type "date/range",
    :value "2023-01-01~2023-01-12",
    :target ["dimension" ["template-tag" "date_range"]],
    :id "3fe6e2fe-5ffa-f98b-3667-fccb581f7820"}],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :status :failed,
 :class java.sql.SQLException,
 :stacktrace
 ["org.duckdb.DuckDBPreparedStatement.setObject(DuckDBPreparedStatement.java:687)"
  "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:769)"
  "--> driver.sql_jdbc.execute$set_object.invokeStatic(execute.clj:226)"
  "driver.sql_jdbc.execute$set_object.invoke(execute.clj:218)"
  "driver.sql_jdbc.execute$fn__55842.invokeStatic(execute.clj:234)"
  "driver.sql_jdbc.execute$fn__55842.invoke(execute.clj:232)"
  "driver.sql_jdbc.execute$set_parameters_BANG_$fn__55858.invoke(execute.clj:274)"
  "driver.sql_jdbc.execute$set_parameters_BANG_.invokeStatic(execute.clj:270)"
  "driver.sql_jdbc.execute$set_parameters_BANG_.invoke(execute.clj:266)"
  "driver.sql_jdbc.execute$fn__55865.invokeStatic(execute.clj:301)"
  "driver.sql_jdbc.execute$fn__55865.invoke(execute.clj:284)"
  "driver.sql_jdbc.execute$prepared_statement_STAR_.invokeStatic(execute.clj:345)"
  "driver.sql_jdbc.execute$prepared_statement_STAR_.invoke(execute.clj:343)"
  "driver.sql_jdbc.execute$statement_or_prepared_statement.invokeStatic(execute.clj:358)"
  "driver.sql_jdbc.execute$statement_or_prepared_statement.invoke(execute.clj:355)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:497)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:485)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:493)"
  "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:485)"
  "driver.sql_jdbc$fn__88395.invokeStatic(sql_jdbc.clj:58)"
  "driver.sql_jdbc$fn__88395.invoke(sql_jdbc.clj:56)"
  "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___53529.invoke(cache.clj:220)"
  "query_processor.middleware.permissions$check_query_permissions$fn__49184.invoke(permissions.clj:109)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__52474.invoke(mbql_to_native.clj:23)"
  "query_processor$fn__55005$combined_post_process__55010$combined_post_process_STAR___55011.invoke(query_processor.clj:212)"
  "query_processor$fn__55005$combined_pre_process__55006$combined_pre_process_STAR___55007.invoke(query_processor.clj:209)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__53450$fn__53455.invoke(resolve_database_and_driver.clj:35)"
  "driver$do_with_driver.invokeStatic(driver.clj:76)"
  "driver$do_with_driver.invoke(driver.clj:72)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__53450.invoke(resolve_database_and_driver.clj:34)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__49450.invoke(fetch_source_query.clj:314)"
  "query_processor.middleware.store$initialize_store$fn__49640$fn__49641.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:45)"
  "query_processor.store$do_with_store.invoke(store.clj:39)"
  "query_processor.middleware.store$initialize_store$fn__49640.invoke(store.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__53722.invoke(normalize_query.clj:22)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__50728.invoke(constraints.clj:53)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__53661.invoke(process_userland_query.clj:145)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__54033.invoke(catch_exceptions.clj:167)"
  "query_processor.reducible$async_qp$qp_STAR___45482$thunk__45484.invoke(reducible.clj:100)"
  "query_processor.reducible$async_qp$qp_STAR___45482.invoke(reducible.clj:106)"
  "query_processor.reducible$async_qp$qp_STAR___45482.invoke(reducible.clj:91)"
  "query_processor.reducible$sync_qp$qp_STAR___45493.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__55052$process_query_and_save_execution_BANG___55061$fn__55064.invoke(query_processor.clj:342)"
  "query_processor$fn__55052$process_query_and_save_execution_BANG___55061.invoke(query_processor.clj:335)"
  "query_processor$fn__55096$process_query_and_save_with_max_results_constraints_BANG___55105$fn__55108.invoke(query_processor.clj:354)"
  "query_processor$fn__55096$process_query_and_save_with_max_results_constraints_BANG___55105.invoke(query_processor.clj:347)"
  "api.dataset$run_query_async$fn__70403.invoke(dataset.clj:69)"
  "query_processor.streaming$streaming_response_STAR_$fn__40578$fn__40579.invoke(streaming.clj:162)"
  "query_processor.streaming$streaming_response_STAR_$fn__40578.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__20017.invoke(streaming_response.clj:84)"],
 :card_id nil,
 :context :ad-hoc,
 :error "Unknown target type 91",
 :row_count 0,
 :running_time 0,
 :data {:rows [], :cols []}}```
AlexR2D2 commented 1 year ago

Hi, could you give examples of broken requests with Date(s)? Also, the DuckDB doc could help you.

cannstandard commented 1 year ago

Tripping over this one as well, here is an example query that requires no data :

SELECT {{start_day}} AS test

Define start_day as a Date variable and give it any value to get Unknown targe type 91 error:


{:database_id 2,
 :started_at #t "2023-03-16T20:01:22.196300-04:00[US/Eastern]",
 :state nil,
 :json_query
 {:database 2,
  :native
  {:template-tags
   {:start_day
    {:id "74ff9233-727f-ecc7-f04b-7d3990a09ffd",
     :name "start_day",
     :display-name "Start day",
     :type "date",
     :default nil}},
   :query "SELECT {{start_day}} AS test"},
  :type "native",
  :parameters
  [{:type "date/single",
    :value "2023-02-26",
    :target ["variable" ["template-tag" "start_day"]],
    :id "74ff9233-727f-ecc7-f04b-7d3990a09ffd"}],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :status :failed,
 :class java.sql.SQLException,
 :stacktrace
 ["org.duckdb.DuckDBPreparedStatement.setObject(DuckDBPreparedStatement.java:687)"
  "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:769)"
  "--> driver.sql_jdbc.execute$set_object.invokeStatic(execute.clj:225)"
  "driver.sql_jdbc.execute$set_object.invoke(execute.clj:217)"
  "driver.sql_jdbc.execute$fn__54966.invokeStatic(execute.clj:233)"
  "driver.sql_jdbc.execute$fn__54966.invoke(execute.clj:231)"
  "driver.sql_jdbc.execute$set_parameters_BANG_$fn__54982.invoke(execute.clj:273)"
  "driver.sql_jdbc.execute$set_parameters_BANG_.invokeStatic(execute.clj:269)"
  "driver.sql_jdbc.execute$set_parameters_BANG_.invoke(execute.clj:265)"
  "driver.sql_jdbc.execute$fn__54989.invokeStatic(execute.clj:300)"
  "driver.sql_jdbc.execute$fn__54989.invoke(execute.clj:283)"
  "driver.sql_jdbc.execute$prepared_statement_STAR_.invokeStatic(execute.clj:335)"
  "driver.sql_jdbc.execute$prepared_statement_STAR_.invoke(execute.clj:332)"
  "driver.sql_jdbc.execute$statement_or_prepared_statement.invokeStatic(execute.clj:359)"
  "driver.sql_jdbc.execute$statement_or_prepared_statement.invoke(execute.clj:356)"
  "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:498)"
  "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__86182.invokeStatic(sql_jdbc.clj:54)"
  "driver.sql_jdbc$fn__86182.invoke(sql_jdbc.clj:52)"
  "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___52597.invoke(cache.clj:220)"
  "query_processor.middleware.permissions$check_query_permissions$fn__48118.invoke(permissions.clj:109)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__51542.invoke(mbql_to_native.clj:23)"
  "query_processor$fn__54073$combined_post_process__54078$combined_post_process_STAR___54079.invoke(query_processor.clj:212)"
  "query_processor$fn__54073$combined_pre_process__54074$combined_pre_process_STAR___54075.invoke(query_processor.clj:209)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__52518$fn__52523.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__52518.invoke(resolve_database_and_driver.clj:34)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48553.invoke(fetch_source_query.clj:353)"
  "query_processor.middleware.store$initialize_store$fn__48741$fn__48742.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__48741.invoke(store.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__52790.invoke(normalize_query.clj:22)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__49823.invoke(constraints.clj:53)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__52729.invoke(process_userland_query.clj:145)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__53101.invoke(catch_exceptions.clj:167)"
  "query_processor.reducible$async_qp$qp_STAR___44956$thunk__44958.invoke(reducible.clj:100)"
  "query_processor.reducible$async_qp$qp_STAR___44956.invoke(reducible.clj:106)"
  "query_processor.reducible$async_qp$qp_STAR___44956.invoke(reducible.clj:91)"
  "query_processor.reducible$sync_qp$qp_STAR___44967.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__54120$process_query_and_save_execution_BANG___54129$fn__54132.invoke(query_processor.clj:342)"
  "query_processor$fn__54120$process_query_and_save_execution_BANG___54129.invoke(query_processor.clj:335)"
  "query_processor$fn__54164$process_query_and_save_with_max_results_constraints_BANG___54173$fn__54176.invoke(query_processor.clj:354)"
  "query_processor$fn__54164$process_query_and_save_with_max_results_constraints_BANG___54173.invoke(query_processor.clj:347)"
  "api.dataset$run_query_async$fn__68870.invoke(dataset.clj:69)"
  "query_processor.streaming$streaming_response_STAR_$fn__40074$fn__40075.invoke(streaming.clj:162)"
  "query_processor.streaming$streaming_response_STAR_$fn__40074.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__18980.invoke(streaming_response.clj:84)"],
 :card_id nil,
 :context :ad-hoc,
 :error "Unknown target type 91",
 :row_count 0,
 :running_time 0,
 :data {:rows [], :cols []}}```
nfire11 commented 11 months ago

I encountered the same issue in 0.9.1. When creating SQL query with varirables via date picker or field filter(mapped to a datetime field), It would return Unknown targe type 91 . However, datetime filtering works fine in queries which directly generated by visualised panel.