Skip to content
This repository was archived by the owner on Apr 11, 2024. It is now read-only.

Unable to run query with any dates #8

Open
vsreekanti opened this issue Jan 13, 2023 · 3 comments
Open

Unable to run query with any dates #8

vsreekanti opened this issue Jan 13, 2023 · 3 comments

Comments

@vsreekanti
Copy link

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
Copy link
Owner

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

@cannstandard
Copy link

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 []}}```

@cannstandard cannstandard mentioned this issue Mar 17, 2023
Closed
2 tasks
@nfire11
Copy link
Contributor

nfire11 commented Nov 8, 2023

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.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@vsreekanti @nfire11 @AlexR2D2 @cannstandard