Help with dashboard filters

[chucksense]

Hi there,
I’m running into some trouble getting a dashboard date filter to work.

Setup:

• Metabase 0.32.9
• Postgres db for Metabase
• Postgres db I’m querying
• Native SQL query that returns 3 columns: date, text (that has 2 possible values), integer
• Nested query that groups by date::week, text, and does a sum(integer) and applies a visualization of a stacked bar chart (date::week as x, sum(integer) as y)

When I then add this to a dashboard, I can successfully choose the “date” column for the filter (I’m using the “all options” date picker, but none of them work), however as long as a date filter is applied, the chart stops working entirely. I then see the following exception in the logs:

Jul 26 21:33:18 WARN metabase.query-processor.middleware.process-userland-query :: Query failure {:status :failed, :class clojure.lang.ExceptionInfo, :error (not (integer? "Date")), :stacktrace ("--> models.params$fn__25070$field_form__GT_id__25075.invoke(params.clj:21)" "query_processor.middleware.parameters.mbql$fn__36381$build_filter_clause__36386$fn__36396.invoke(mbql.clj:50)" "query_processor.middleware.parameters.mbql$fn__36381$build_filter_clause__36386.invoke(mbql.clj:39)" "query_processor.middleware.parameters.mbql$expand.invokeStatic(mbql.clj:72)" "query_processor.middleware.parameters.mbql$expand.invoke(mbql.clj:59)" "query_processor.middleware.parameters$expand_parameters_STAR_.invokeStatic(parameters.clj:20)" "query_processor.middleware.parameters$expand_parameters_STAR_.invoke(parameters.clj:14)" "query_processor.middleware.parameters$expand_parameters.invokeStatic(parameters.clj:44)" "query_processor.middleware.parameters$expand_parameters.invoke(parameters.clj:40)" "query_processor.middleware.parameters$substitute_parameters_STAR_.invokeStatic(parameters.clj:50)" "query_processor.middleware.parameters$substitute_parameters_STAR_.invoke(parameters.clj:47)" "query_processor.middleware.driver_specific$process_query_in_context$fn__35460.invoke(driver_specific.clj:12)" "query_processor.middleware.resolve_driver$resolve_driver$fn__38788.invoke(resolve_driver.clj:23)" "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__34649$fn__34650.invoke(bind_effective_timezone.clj:9)" "util.date$call_with_effective_timezone.invokeStatic(date.clj:88)" "util.date$call_with_effective_timezone.invoke(date.clj:77)" "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__34649.invoke(bind_effective_timezone.clj:8)" "query_processor.middleware.store$initialize_store$fn__41910$fn__41911.invoke(store.clj:11)" "query_processor.store$do_with_new_store.invokeStatic(store.clj:41)" "query_processor.store$do_with_new_store.invoke(store.clj:37)" "query_processor.middleware.store$initialize_store$fn__41910.invoke(store.clj:10)" "query_processor.middleware.async$async__GT_sync$fn__34250.invoke(async.clj:23)" "query_processor.middleware.async_wait$runnable$fn__34375.invoke(async_wait.clj:89)"), :query {:constraints {:max-results 10000, :max-results-bare-rows 2000}, :type :query, :middleware {:userland-query? true}, :info {:executed-by 20, :context :question, :card-id 502, :dashboard-id nil, :query-hash [37, -2, -14, 124, 42, 122, -26, 64, -42, -42, -55, -70, 23, 1, -41, -101, -33, -15, -16, -39, 114, 61, 17, -4, 8, 37, -46, -65, -9, -10, 82, -10]}, :query {:source-table "card__254", :breakout [[:datetime-field [:field-literal "Date" :type/DateTime] :week] [:field-literal "Customer Type" :type/Text]], :aggregation [[:sum [:field-literal "Kits Sold" :type/Integer]]]}, :parameters [{:type "date/all-options", :target ["dimension" ["field-literal" "Date" "type/DateTime"]], :value "past30days"}], :async? true, :cache-ttl nil}, :preprocessed nil, :native nil, :ex-data {:type :schema.core/error, :value "Date", :error (not (integer? "Date"))}}

Am I running into a bug I should report, is this simply an unsupported usecase, or have I done something wrong?

Much thanks in advance!

[flamber]

Hi @chucksense
The Zen of Metabase would probably be that you never do anything wrong.
Question, why are you using a Nested Query? Is it just so you can reuse the same query for different questions?
I think your problem is that the dashboard is expecting the “Date” column as a Date, but the Nested Query is returning a Week, and the underlying SQL is a Date.
Can you post the SQL query, then I’ll try to see if I can reproduce this?

[chucksense]

Hi flamber,
To reproduce, you can use the sample data set.

First, save a question using this query:

WITH tmp_user_order_dates as (
  SELECT 
    o.USER_ID,
    o.CREATED_AT,
    o.QUANTITY
  FROM 
    ORDERS o
),

tmp_prior_orders_by_date as (
  select
      tbod.USER_ID,
      tbod.CREATED_AT,
      tbod.QUANTITY,
      (select count(*) from tmp_user_order_dates tbod2 where tbod2.USER_ID = tbod.USER_ID and tbod2.CREATED_AT < tbod.CREATED_AT ) as PRIOR_ORDERS
  from tmp_user_order_dates tbod
)

select
  date_trunc('day', tpobd.CREATED_AT) as "Date",
  case when tpobd.PRIOR_ORDERS > 0 then 'Return' else 'New' end as "Customer Type",
  sum(QUANTITY) as "Items Sold"
from tmp_prior_orders_by_date tpobd
where true 
    [[and tpobd.CREATED_AT >= {{start_date}}]]
    [[and tpod.CREATED_AT <= {{end_date}}]]
group by date_trunc('day', tpobd.CREATED_AT), "Customer Type"
order by date_trunc('day', tpobd.CREATED_AT) asc

Then configure the second question referencing the first, like this:

Then when you add it to a dashboard and configure a date filter, once a filter is applied, you can see the error (see next post since I can apparently only do one image per post).

Thank you,
-Chuck

[chucksense]

Error:

[flamber]

@chucksense
So freaking awesome, that you provided an extensive way to reproduce the issue with Sample Dataset!
I’m almost certain that you’re seeing issue 9802, which is really badly explained. Have a look at 9848 for a much better explanation.
You should post your steps-to-reproduce, since they’re really good.
(one tiny note, [[and tpod.CREATED_AT <= {{end_date}}]] should be [[and tpobd.CREATED_AT <= {{end_date}}]]

[chucksense]

Thank you! Subscribed to both those issues (and thumbs upped them). I also added the steps to #9802 as requested.