search

AlexR2D2 / metabase_duckdb_driver

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

Null/NA dates in duckdb causes error in Metabase #20

Closed dvictori closed 1 year ago

dvictori commented 1 year ago

I have a duckdb database linked to a metabase instance. This data has a date column where some values are NA (empty).

Querying the database using duckdb cli works as expected:

image

However, Metabase cannot query this table and the following message appears in the logs:

2023-10-24 13:24:39,188 ERROR middleware.catch-exceptions :: Erro ao processar a consulta: Erro ao executar consulta
{:database_id 4,
 :started_at #t "2023-10-24T13:24:38.992218189Z[Etc/UTC]",
 :via
 [{:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error "Erro reduzindo linhas de resultados: null",
   :stacktrace
   ["--> query_processor.context.default$default_reducef$fn__53329.invoke(default.clj:60)"
    "query_processor.context.default$default_reducef.invokeStatic(default.clj:57)"
    "query_processor.context.default$default_reducef.invoke(default.clj:49)"
    "query_processor.context$reducef.invokeStatic(context.clj:70)"
    "query_processor.context$reducef.invoke(context.clj:63)"
    "query_processor.context.default$default_runf$respond_STAR___53333.invoke(default.clj:69)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:513)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
    "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:497)"
    "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
    "driver.sql_jdbc$fn__105083.invokeStatic(sql_jdbc.clj:63)"
    "driver.sql_jdbc$fn__105083.invoke(sql_jdbc.clj:61)"
    "query_processor.context$executef.invokeStatic(context.clj:60)"
    "query_processor.context$executef.invoke(context.clj:49)"
    "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
    "query_processor.context.default$default_runf.invoke(default.clj:66)"
    "query_processor.context$runf.invokeStatic(context.clj:46)"
    "query_processor.context$runf.invoke(context.clj:40)"
    "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
    "query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
    "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___69172.invoke(cache.clj:224)"
    "query_processor.middleware.permissions$check_query_permissions$fn__64644.invoke(permissions.clj:126)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__68063.invoke(mbql_to_native.clj:24)"
    "query_processor$fn__70691$combined_post_process__70696$combined_post_process_STAR___70697.invoke(query_processor.clj:243)"
    "query_processor$fn__70691$combined_pre_process__70692$combined_pre_process_STAR___70693.invoke(query_processor.clj:240)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69083$fn__69088.invoke(resolve_database_and_driver.clj:36)"
    "driver$do_with_driver.invokeStatic(driver.clj:90)"
    "driver$do_with_driver.invoke(driver.clj:86)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69083.invoke(resolve_database_and_driver.clj:35)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__64953.invoke(fetch_source_query.clj:310)"
    "query_processor.middleware.store$initialize_store$fn__65131$fn__65132.invoke(store.clj:12)"
    "query_processor.store$do_with_store.invokeStatic(store.clj:47)"
    "query_processor.store$do_with_store.invoke(store.clj:41)"
    "query_processor.middleware.store$initialize_store$fn__65131.invoke(store.clj:11)"
    "query_processor.middleware.normalize_query$normalize$fn__69372.invoke(normalize_query.clj:25)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__66309.invoke(constraints.clj:54)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__69308.invoke(process_userland_query.clj:150)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__69685.invoke(catch_exceptions.clj:171)"
    "query_processor.reducible$async_qp$qp_STAR___59455$thunk__59457.invoke(reducible.clj:103)"
    "query_processor.reducible$async_qp$qp_STAR___59455.invoke(reducible.clj:109)"
    "query_processor.reducible$async_qp$qp_STAR___59455.invoke(reducible.clj:94)"
    "query_processor.reducible$sync_qp$qp_STAR___59467.doInvoke(reducible.clj:129)"
    "query_processor$process_userland_query.invokeStatic(query_processor.clj:362)"
    "query_processor$process_userland_query.doInvoke(query_processor.clj:358)"
    "query_processor$fn__70739$process_query_and_save_execution_BANG___70748$fn__70751.invoke(query_processor.clj:373)"
    "query_processor$fn__70739$process_query_and_save_execution_BANG___70748.invoke(query_processor.clj:366)"
    "query_processor$fn__70784$process_query_and_save_with_max_results_constraints_BANG___70793$fn__70796.invoke(query_processor.clj:385)"
    "query_processor$fn__70784$process_query_and_save_with_max_results_constraints_BANG___70793.invoke(query_processor.clj:378)"
    "api.dataset$run_query_async$fn__86545.invoke(dataset.clj:73)"
    "query_processor.streaming$streaming_response_STAR_$fn__54305$fn__54306.invoke(streaming.clj:166)"
    "query_processor.streaming$streaming_response_STAR_$fn__54305.invoke(streaming.clj:165)"
    "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
    "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
    "async.streaming_response$do_f_async$task__36922.invoke(streaming_response.clj:88)"],
   :error_type :qp,
   :ex-data {:type :qp}}],
 :error_type :qp,
 :json_query
 {:type "native",
  :native
  {:query
   "SELECT \"main\".\"dados\".\"id\" AS \"id\", \"main\".\"dados\".\"data\" AS \"data\"\nFROM \"main\".\"dados\"\n--where data is not null\nLIMIT 1048575",
   :template-tags {}},
  :database 4,
  :parameters [],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :status :failed,
 :class java.lang.NullPointerException,
 :stacktrace [nil],
 :card_id nil,
 :context :ad-hoc,
 :error nil,
 :row_count 0,
 :running_time 0,
 :data {:rows [], :cols []}}

2023-10-24 13:24:39,191 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 213.8 ms (6 chamadas ao banco de dados) Conexões de banco de dados do aplicativo: 0 / 15 Threads do Jetty: 4 / 50 (3 ocioso, 0 na fila) (122 total de threads ativas) Consultas ativas: 0 (0 na fila); duckdb DB 4 connections: 0/2 (0 threads blocked)

If I exclude the NULL dates from the query, it works!

image

I'm creating my teste database in R, using the following code:

library(DBI)
library(duckdb)

con <- dbConnect(duckdb::duckdb(), dbdir = '/dados/teste_data_na.duckdb')

dados <- data.frame(id = 1:10,
    data = seq.Date(from = as.Date('2020-01-01'),
        to = as.Date('2020-01-10'),
        by = 'day'))

dados$data[5] <- NA

dbWriteTable(con, 'dados', dados)
dbDisconnect(con)
dvictori commented 1 year ago

Forgot to add:

dvictori commented 1 year ago

Upgraded everything here and now things work. Sorry for the noise.

Here are the software versions I'm using now:

image