I created a question in native query (for MongoDB). At a certain point, I calculate a new field "deadline".
Then I would like to filter on that field based on a variable in input, if provided. So basically I try to do this :
{
"$match" : {"deadline" : {{deadline}} }
}
The {{deadline}} variable is of type "text". I cannot choose "Field Filter", because the field "deadline" was computed in the query, but it didn't exist in the original collection. The text filter is associated to a dropdown list, with a custom list of values : "On time", and "Overdue".
When the filter is filled in with one of the 2 possible values, everything works fine. But when I let the filter empty, I have the following error :
I think it's because when we use a text filter, it is interpreted as something like : "deadline $in : [ ] ", but since there's no value provided in input of the $in, the error occurs. Actually, I would expect that when the filter is let empty by the user, no filtering is done. This is the case when using the filter is of type Field Filter (but as already said, I cannot use such type of filter).
Is it a known issue ?
Is there any workaround for this ?
Here under I put the complete log :
[528a3ed5-bf8c-4874-aeba-b042746c13aa] 2023-09-20T16:32:29+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: Cannot run query: missing required parameters: #{"deadline"}
{:database_id 2,
:started_at #t "2023-09-20T14:32:28.871400Z[GMT]",
:error_type :invalid-query,
:json_query
{:database 2,
:native
{:query
"[\n {\n \"$match\" : {{priority}}\n },\n {\n \"$match\" : {{type}}\n },\n {\n \"$project\" :{\n \"_id\" : 0,\n \"id\" : 1,\n \"type\" : 1,\n \"priority\" : 1,\n \"creationDateTime\" : {\n \"$dateTrunc\" : {\n \"date\" : \"$creationDateTime\",\n \"unit\" : \"day\"\n }\n },\n \"completionDateTime\" : {\n \"$dateTrunc\" : {\n \"date\" : \"$completionDateTime\",\n \"unit\" : \"day\"\n }\n }\n \"dueDate\" : {\n \"$ifNull\" : [\"$dueDate\", {\"$toDate\": \"29991231\"}]\n }\n }\n },\n {\n \"$addFields\" : {\n \"period\" : { \n \"$regexFindAll\": {\n \"input\": '{{dateStart}}',\n \"regex\": /(\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}Z)/\n }\n }\n }\n },\n {\n \"$addFields\": {\n \"lowerBound\" : { \n \"$toDate\" : {\n \"$arrayElemAt\" : [\"$period.match\", 0] \n }\n },\n \"upperBound\" : {\n \"$toDate\" :{\n \"$arrayElemAt\" : [\"$period.match\", 1] \n }\n }\n }\n },\n {\n \"$match\" :{\n \"$and\" :[\n { \"$expr\": { \"$lte\": [\"$creationDateTime\", \"$upperBound\" ] } },\n { \n \"$or\" :[\n { \"$expr\": { \"$eq\": [ {\"$ifNull\" : [\"$completionDateTime\", \"null\" ] }, \"null\" ] } },\n { \"$expr\": { \"$gte\": [\"$completionDateTime\", \"$lowerBound\" ] } },\n ]\n }\n ]\n }\n },\n {\n \"$addFields\" :{\n \"range_length\": {\n \"$dateDiff\": {\n \"startDate\": \"$lowerBound\",\n \"endDate\": \"$upperBound\",\n \"unit\": \"day\"\n }\n }\n }\n },\n {\n \"$addFields\" :{\n \"days_to_add\": {\n \"$range\": [0, \"$range_length\"]\n }\n }\n },\n {\n \"$addFields\" :{\n \"date\": {\n \"$map\": {\n \"input\": \"$days_to_add\",\n \"as\": \"number\",\n \"in\": {\n \"$dateAdd\": {\n \"startDate\": \"$lowerBound\"\n \"unit\": \"day\",\n \"amount\": \"$$number\"\n }\n }\n }\n }\n }\n },\n {\n \"$unset\" :[\"days_to_add\", \"range_length\", \"lowerBound\", \"upperBound\", \"period\"]\n },\n {\n \"$unwind\" : {\n \"path\": \"$date\"\n }\n },\n {\n \"$addFields\" :{\n \"status\": {\n \"$cond\": [\n {\n \"$and\": [\n {\"$lte\": [\"$creationDateTime\", \"$date\"]},\n {\n \"$or\" :[\n { \"$eq\": [ {\"$ifNull\" : [\"$completionDateTime\", \"null\" ] }, \"null\" ] },\n { \"$gt\": [\"$completionDateTime\", \"$date\"]}\n ]\n }\n ]\n },\n \"ACTIVE\",\n \"INACTIVE\"\n ]\n },\n }\n },\n {\n \"$addFields\" :{\n \"deadline\" :{\n \"$cond\": [\n {\"$eq\": [\"$status\", \"ACTIVE\"]},\n {\n \"$cond\" : [\n {\"$gte\": [\"$dueDate\", \"$date\"]},\n \"On time\",\n \"Overdue\"\n ]\n },\n \"\"\n ] \n }\n }\n },\n {\n \"$match\" : {\"status\" : \"ACTIVE\"}\n },\n\n {\n \"$match\" : {\"deadline\" : {{deadline}} }\n }\n\n\n]\n",
:template-tags
{:priority
{:id "0d7a384e-48e5-6dc0-997e-2140702deed3",
:name "priority",
:display-name "Priority",
:type "dimension",
:dimension ["field" 266408 nil],
:widget-type "string/="},
:type
{:id "533f06fc-428f-2f80-6932-9d69f44119f2",
:name "type",
:display-name "Type",
:type "dimension",
:dimension ["field" 266402 nil],
:widget-type "string/="},
:dateStart
{:id "0a531779-6cae-ed1a-f774-06a9ff8f4bc1",
:name "dateStart",
:display-name "Datestart",
:type "dimension",
:dimension ["field" 266409 nil],
:widget-type "date/all-options",
:required true,
:default "past30days"},
:deadline {:id "c985dea1-a8bd-671b-05f7-876adf62fba5", :name "deadline", :display-name "Deadline", :type "text"}},
:collection "tasks_raw"},
:type "native",
:parameters
[{:id "0a531779-6cae-ed1a-f774-06a9ff8f4bc1",
:type "date/all-options",
:value "past30days",
:target ["dimension" ["template-tag" "dateStart"]]}],
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
:status :failed,
:class clojure.lang.ExceptionInfo,
:stacktrace
["--> driver.mongo.parameters$substitute.invokeStatic(parameters.clj:204)"
"driver.mongo.parameters$substitute.invoke(parameters.clj:201)"
"driver.mongo.parameters$parse_and_substitute.invokeStatic(parameters.clj:212)"
"driver.mongo.parameters$parse_and_substitute.invoke(parameters.clj:209)"
"driver.mongo.parameters$substitute_native_parameters.invokeStatic(parameters.clj:220)"
"driver.mongo.parameters$substitute_native_parameters.invoke(parameters.clj:216)"
"driver.mongo$fn__120785.invokeStatic(mongo.clj:317)"
"driver.mongo$fn__120785.invoke(mongo.clj:315)"
"query_processor.middleware.parameters.native$expand_inner.invokeStatic(native.clj:40)"
"query_processor.middleware.parameters.native$expand_inner.invoke(native.clj:31)"
"query_processor.middleware.parameters$expand_one.invokeStatic(parameters.clj:50)"
"query_processor.middleware.parameters$expand_one.invoke(parameters.clj:41)"
"query_processor.middleware.parameters$expand_all$replace_66990__66991.invoke(parameters.clj:59)"
"mbql.util.match.impl$replace_in_collection$iter__27031__27035$fn__27036.invoke(impl.cljc:44)"
"mbql.util.match.impl$replace_in_collection.invokeStatic(impl.cljc:43)"
"mbql.util.match.impl$replace_in_collection.invoke(impl.cljc:38)"
"query_processor.middleware.parameters$expand_all$replace_66990__66991.invoke(parameters.clj:59)"
"query_processor.middleware.parameters$expand_all.invokeStatic(parameters.clj:59)"
"query_processor.middleware.parameters$expand_all.invoke(parameters.clj:53)"
"query_processor.middleware.parameters$expand_all.invokeStatic(parameters.clj:56)"
"query_processor.middleware.parameters$expand_all.invoke(parameters.clj:53)"
"query_processor.middleware.parameters$expand_parameters.invokeStatic(parameters.clj:77)"
"query_processor.middleware.parameters$expand_parameters.invoke(parameters.clj:73)"
"query_processor.middleware.parameters$fn__67007$substitute_parameters_STAR___67012$fn__67013.invoke(parameters.clj:82)"
"query_processor.middleware.parameters$fn__67007$substitute_parameters_STAR___67012.invoke(parameters.clj:79)"
"query_processor.middleware.parameters$substitute_parameters.invokeStatic(parameters.clj:109)"
"query_processor.middleware.parameters$substitute_parameters.invoke(parameters.clj:101)"
"query_processor$preprocess_STAR_$fn__70750.invoke(query_processor.clj:158)"
"query_processor$preprocess_STAR_.invokeStatic(query_processor.clj:156)"
"query_processor$preprocess_STAR_.invoke(query_processor.clj:151)"
"query_processor$fn__70758$combined_pre_process__70759$combined_pre_process_STAR___70760.invoke(query_processor.clj:240)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69150$fn__69155.invoke(resolve_database_and_driver.clj:36)"
"driver$do_with_driver.invokeStatic(driver.clj:92)"
"driver$do_with_driver.invoke(driver.clj:88)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69150.invoke(resolve_database_and_driver.clj:35)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__65021.invoke(fetch_source_query.clj:316)"
"query_processor.middleware.store$initialize_store$fn__65199$fn__65200.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__65199.invoke(store.clj:11)"
"query_processor.middleware.normalize_query$normalize$fn__69439.invoke(normalize_query.clj:25)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__66377.invoke(constraints.clj:54)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__69375.invoke(process_userland_query.clj:151)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__69752.invoke(catch_exceptions.clj:171)"
"query_processor.reducible$async_qp$qp_STAR___59520$thunk__59522.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___59520.invoke(reducible.clj:109)"
"query_processor.reducible$async_qp$qp_STAR___59520.invoke(reducible.clj:94)"
"query_processor.reducible$sync_qp$qp_STAR___59532.doInvoke(reducible.clj:129)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:366)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:362)"
"query_processor$fn__70807$process_query_and_save_execution_BANG___70816$fn__70819.invoke(query_processor.clj:377)"
"query_processor$fn__70807$process_query_and_save_execution_BANG___70816.invoke(query_processor.clj:370)"
"query_processor$fn__70852$process_query_and_save_with_max_results_constraints_BANG___70861$fn__70864.invoke(query_processor.clj:389)"
"query_processor$fn__70852$process_query_and_save_with_max_results_constraints_BANG___70861.invoke(query_processor.clj:382)"
"api.dataset$run_query_async$fn__86959.invoke(dataset.clj:73)"
"query_processor.streaming$streaming_response_STAR_$fn__54383$fn__54384.invoke(streaming.clj:166)"
"query_processor.streaming$streaming_response_STAR_$fn__54383.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__36968.invoke(streaming_response.clj:88)"],
:card_id nil,
:context :ad-hoc,
:error "Cannot run query: missing required parameters: #{\"deadline\"}",
:row_count 0,
:running_time 0,
:ex-data {:type :invalid-query},
:data {:rows [], :cols []}}
