Date Variables are not working anymore after updating to 0.23

1

Hi Guys,

first of all thanks for the latest update, it’s great!

Unfortunately I’m running in a very annoying issue: all my variables that I previously used to filter through dates are now broken.
To be more specific, I have a few hundred questions where I used to successfully apply these conditions:
[[AND fbh.field_booking_insert_date between {{min_date}}::date AND ({{max_date}}::date + interval '1 day')]]
or
[[AND fbh.insert_date between timestamptz({{min_date}}) and timestamptz({{max_date}}) + interval '1 day']
depending on the type of data in my table.

After updating to 0.23 I’ve found that the between clause is no longer accepted, the query results in this error: syntax error at or near "{" Position: 1928
Whereas in my dashboards, if I set the date values, I can read from the logs that the error is {:errors {:description "value may be nil, or if non-nil, value must be a non-blank string."}}.

I’ve tried to fix it and so far I’ve found that:

  • avoiding the optionality makes the variables work again:
    WHERE fs.date between {{min_date}}::date and {{max_date}}::date + interval '1 day'
  • avoiding the use of between solves the issue temporarily, but then I can’t save the question because I get this message: value may be nil, or if non-nil, value must be a non-blank string.
2

To follow up, It seems it’s not setting anymore the timezone:

`Mar 24 14:10:46 DEBUG metabase.query-processor.middleware.log ::
QUERY: :sunglasses:
{:database 2,
:type “native”,
:native
{:query
“select\n u.id AS id_utente,\n lower(u.email) AS email,\n initcap(u.firstname) AS nome,\n initcap(u.lastname) AS cognome,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 1 THEN 1 ELSE 0 END) AS tot_prenotazioni,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) AS paga_al_campo,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 3 THEN 1 ELSE 0 END) AS annullate_al_campo,\n SUM(CASE WHEN fbh.payment_transaction > 0 THEN 1 ELSE 0 END) AS promo_acquistate,\n SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 3 THEN 1 ELSE 0 END) AS promo_cancellate,\n MIN(fbh.field_booking_insert_date) AS prima_prenotazione,\n MAX(fbh.field_booking_insert_date) AS ultima_prenotazione,\n MAX(fbh.date) AS ultima_da_giocare,\n u.active AS attivo,\n u.disabled AS disattivato,\n u.verified AS verificato,\n u.member_since AS membro_dal,\n u.first_activation_date AS attivo_dal,\n u.verified_by_mandrill AS verificato_da_mandrill,\n u.password,\n u.confirm_code\nfrom users as u\nleft join fields_bookings_histories as fbh on fbh.user_id = u.id\nLEFT JOIN fields AS fi ON fi.id = fbh.field_id\nLEFT JOIN facilities AS fa ON fa.id = fi.facility_id\nLEFT JOIN cities AS c ON c.id = fa.city_id\nLEFT JOIN province AS p ON p.id = c.province_id\nWHERE \n u.disabled = false\n and fbh.booked_online = TRUE\n and fbh.is_pending = FALSE\n and u.active = true\n and u.role_id = 1 /* creata da utente */\n and fbh.action_id != 2\n and (u.password != ‘’ OR u.facebook_id IS NOT NULL OR u.sportilia_id IS NOT NULL OR u.google_plus_id IS NOT NULL)\n and fa.id NOT IN (58, 671)\n [[AND fbh.field_booking_insert_date between {{min_date}}::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’]]\nGROUP BY u.id\nORDER BY tot_prenotazioni DESC;”,
:template_tags
{:min_date {:id “b2d5ce0d-4ebb-49d9-9893-2f39c0a4e7e9”, :name “min_date”, :display_name “Min date”, :type “date”, :required true, :default “2016-01-01”},
:max_date {:id “5858100d-8533-5360-090c-80e6d59162b0”, :name “max_date”, :display_name “Max date”, :type “date”, :required true, :default “2016-12-31”}}},
:parameters
[{:type “date/single”, :target [“variable” [“template-tag” “min_date”]], :value “2017-01-01”} {:type “date/single”, :target [“variable” [“template-tag” “max_date”]], :value “2017-05-31”}],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info
{:executed-by 1,
:context :ad-hoc,
:query-hash [-30, -11, -28, -71, -92, -94, -13, 14, -24, -95, 114, -7, 97, -15, -52, -11, 56, 15, 79, 106, -119, -99, -11, 71, -106, 1, 103, -13, -29, 92, -61, 110],
:query-type “native”}}

Mar 24 14:10:46 DEBUG metabase.query-processor.sql-parameters :: PARAM INFO:
({:param-key :min_date,
:original-snippet “[[AND fbh.field_booking_insert_date between {{min_date}}::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’]]”,
:variable-snippet “{{min_date}}”,
:optional-snippet “AND fbh.field_booking_insert_date between {{min_date}}::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’”,
:replacement-snippet “AND fbh.field_booking_insert_date between ?::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’”,
:prepared-statement-args (#inst “2017-01-01T00:00:00.000000000-00:00”)})

Mar 24 14:10:46 DEBUG metabase.query-processor.middleware.parameters ::

PARAMS/SUBSTITUTED: :heart_eyes_cat:
{:native
{:query
“select\n u.id AS id_utente,\n lower(u.email) AS email,\n initcap(u.firstname) AS nome,\n initcap(u.lastname) AS cognome,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 1 THEN 1 ELSE 0 END) AS tot_prenotazioni,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) AS paga_al_campo,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 3 THEN 1 ELSE 0 END) AS annullate_al_campo,\n SUM(CASE WHEN fbh.payment_transaction > 0 THEN 1 ELSE 0 END) AS promo_acquistate,\n SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 3 THEN 1 ELSE 0 END) AS promo_cancellate,\n MIN(fbh.field_booking_insert_date) AS prima_prenotazione,\n MAX(fbh.field_booking_insert_date) AS ultima_prenotazione,\n MAX(fbh.date) AS ultima_da_giocare,\n u.active AS attivo,\n u.disabled AS disattivato,\n u.verified AS verificato,\n u.member_since AS membro_dal,\n u.first_activation_date AS attivo_dal,\n u.verified_by_mandrill AS verificato_da_mandrill,\n u.password,\n u.confirm_code\nfrom users as u\nleft join fields_bookings_histories as fbh on fbh.user_id = u.id\nLEFT JOIN fields AS fi ON fi.id = fbh.field_id\nLEFT JOIN facilities AS fa ON fa.id = fi.facility_id\nLEFT JOIN cities AS c ON c.id = fa.city_id\nLEFT JOIN province AS p ON p.id = c.province_id\nWHERE \n u.disabled = false\n and fbh.booked_online = TRUE\n and fbh.is_pending = FALSE\n and u.active = true\n and u.role_id = 1 /* creata da utente */\n and fbh.action_id != 2\n and (u.password != ‘’ OR u.facebook_id IS NOT NULL OR u.sportilia_id IS NOT NULL OR u.google_plus_id IS NOT NULL)\n and fa.id NOT IN (58, 671)\n AND fbh.field_booking_insert_date between ?::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’\nGROUP BY u.id\nORDER BY tot_prenotazioni DESC;”,
:params (#inst “2017-01-01T00:00:00.000000000-00:00”)}}

Mar 24 14:10:46 DEBUG metabase.query-processor.permissions :: Permissions Check : Does user have permissions for /db/2/native/?
Mar 24 14:10:46 DEBUG metabase.query-processor.permissions :: Permissions Check : Yes because user has permissions for /.
Mar 24 14:10:46 DEBUG metabase.query-processor.middleware.mbql-to-native :: NATIVE FORM: :flushed:
{:query
“select\n u.id AS id_utente,\n lower(u.email) AS email,\n initcap(u.firstname) AS nome,\n initcap(u.lastname) AS cognome,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 1 THEN 1 ELSE 0 END) AS tot_prenotazioni,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) AS paga_al_campo,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 3 THEN 1 ELSE 0 END) AS annullate_al_campo,\n SUM(CASE WHEN fbh.payment_transaction > 0 THEN 1 ELSE 0 END) AS promo_acquistate,\n SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 3 THEN 1 ELSE 0 END) AS promo_cancellate,\n MIN(fbh.field_booking_insert_date) AS prima_prenotazione,\n MAX(fbh.field_booking_insert_date) AS ultima_prenotazione,\n MAX(fbh.date) AS ultima_da_giocare,\n u.active AS attivo,\n u.disabled AS disattivato,\n u.verified AS verificato,\n u.member_since AS membro_dal,\n u.first_activation_date AS attivo_dal,\n u.verified_by_mandrill AS verificato_da_mandrill,\n u.password,\n u.confirm_code\nfrom users as u\nleft join fields_bookings_histories as fbh on fbh.user_id = u.id\nLEFT JOIN fields AS fi ON fi.id = fbh.field_id\nLEFT JOIN facilities AS fa ON fa.id = fi.facility_id\nLEFT JOIN cities AS c ON c.id = fa.city_id\nLEFT JOIN province AS p ON p.id = c.province_id\nWHERE \n u.disabled = false\n and fbh.booked_online = TRUE\n and fbh.is_pending = FALSE\n and u.active = true\n and u.role_id = 1 /* creata da utente */\n and fbh.action_id != 2\n and (u.password != ‘’ OR u.facebook_id IS NOT NULL OR u.sportilia_id IS NOT NULL OR u.google_plus_id IS NOT NULL)\n and fa.id NOT IN (58, 671)\n AND fbh.field_booking_insert_date between ?::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’\nGROUP BY u.id\nORDER BY tot_prenotazioni DESC;”,
:template_tags
{:min_date {:id “b2d5ce0d-4ebb-49d9-9893-2f39c0a4e7e9”, :name “min_date”, :display_name “Min date”, :type “date”, :required true, :default “2016-01-01”},
:max_date {:id “5858100d-8533-5360-090c-80e6d59162b0”, :name “max_date”, :display_name “Max date”, :type “date”, :required true, :default “2016-12-31”}},
:params (#inst “2017-01-01T00:00:00.000000000-00:00”)}

Mar 24 14:10:46 DEBUG metabase.driver.generic-sql.query-processor :: [“UPDATE pg_settings SET setting = ? WHERE name ILIKE ‘timezone’;” “Europe/Berlin”]

Mar 24 14:10:46 ERROR metabase.driver.generic-sql.query-processor :: Failed to set timezone:
PSQLException:
Message: ERROR: syntax error at or near “{”
Position: 1935
SQLState: 42601
Error Code: 0

Mar 24 14:10:46 ERROR metabase.driver.generic-sql.query-processor :: nil
Mar 24 14:10:46 WARN metabase.query-processor :: {:status :failed,
:class java.lang.Exception,
:error “ERROR: syntax error at or near “{”\n Position: 1935”,
:stacktrace
[“driver.generic_sql.query_processor$do_with_try_catch.invokeStatic(query_processor.clj:323)”
“driver.generic_sql.query_processor$do_with_try_catch.invoke(query_processor.clj:319)”
“driver.generic_sql.query_processor$execute_query.invokeStatic(query_processor.clj:365)”
“driver.generic_sql.query_processor$execute_query.invoke(query_processor.clj:361)”
“driver$fn__19566$G__19559__19573.invoke(driver.clj:43)”
“query_processor$execute_query.invokeStatic(query_processor.clj:39)”
“query_processor$execute_query.invoke(query_processor.clj:33)”
“query_processor.middleware.mbql_to_native$mbql__GT_native$fn__23125.invoke(mbql_to_native.clj:28)”
“query_processor.middleware.annotate_and_sort$annotate_and_sort$fn__21793.invoke(annotate_and_sort.clj:37)”
“query_processor.middleware.limit$limit$fn__23097.invoke(limit.clj:14)”
“query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__22958.invoke(cumulative_aggregations.clj:46)”
“query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__22958.invoke(cumulative_aggregations.clj:46)”
“query_processor.middleware.format_rows$format_rows$fn__23088.invoke(format_rows.clj:21)”
“query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__21473.invoke(add_row_count_and_status.clj:14)”
“query_processor.middleware.driver_specific$process_query_in_context$fn__22978.invoke(driver_specific.clj:12)”
“query_processor.middleware.resolve_driver$resolve_driver$fn__24064.invoke(resolve_driver.clj:14)”
“query_processor.middleware.catch_exceptions$catch_exceptions$fn__22900.invoke(catch_exceptions.clj:51)”
“query_processor$process_query.invokeStatic(query_processor.clj:64)”
“query_processor$process_query.invoke(query_processor.clj:59)”
“query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:180)”
“query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:175)”
“query_processor$fn__24095$dataset_query__24100$fn__24101.invoke(query_processor.clj:212)”
“query_processor$fn__24095$dataset_query__24100.invoke(query_processor.clj:199)”
“api.dataset$fn__24866$fn__24869.invoke(dataset.clj:36)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:229)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:224)”
“api.dataset$fn__24866.invokeStatic(dataset.clj:30)”
“api.dataset$fn__24866.invoke(dataset.clj:30)”
“middleware$enforce_authentication$fn__34556.invoke(middleware.clj:119)”
“api.routes$fn__34682.invokeStatic(routes.clj:57)”
“api.routes$fn__34682.invoke(routes.clj:57)”
“routes$fn__35755.invokeStatic(routes.clj:44)”
“routes$fn__35755.invoke(routes.clj:44)”
“middleware$log_api_call$fn__34655$fn__34657.invoke(middleware.clj:331)”
“middleware$log_api_call$fn__34655.invoke(middleware.clj:330)”
“middleware$add_security_headers$fn__34605.invoke(middleware.clj:246)”
“middleware$bind_current_user$fn__34560.invoke(middleware.clj:139)”
“middleware$maybe_set_site_url$fn__34609.invoke(middleware.clj:268)”],
:query
{:type “native”,
:native
{:query
“select\n u.id AS id_utente,\n lower(u.email) AS email,\n initcap(u.firstname) AS nome,\n initcap(u.lastname) AS cognome,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 1 THEN 1 ELSE 0 END) AS tot_prenotazioni,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) AS paga_al_campo,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 3 THEN 1 ELSE 0 END) AS annullate_al_campo,\n SUM(CASE WHEN fbh.payment_transaction > 0 THEN 1 ELSE 0 END) AS promo_acquistate,\n SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 3 THEN 1 ELSE 0 END) AS promo_cancellate,\n MIN(fbh.field_booking_insert_date) AS prima_prenotazione,\n MAX(fbh.field_booking_insert_date) AS ultima_prenotazione,\n MAX(fbh.date) AS ultima_da_giocare,\n u.active AS attivo,\n u.disabled AS disattivato,\n u.verified AS verificato,\n u.member_since AS membro_dal,\n u.first_activation_date AS attivo_dal,\n u.verified_by_mandrill AS verificato_da_mandrill,\n u.password,\n u.confirm_code\nfrom users as u\nleft join fields_bookings_histories as fbh on fbh.user_id = u.id\nLEFT JOIN fields AS fi ON fi.id = fbh.field_id\nLEFT JOIN facilities AS fa ON fa.id = fi.facility_id\nLEFT JOIN cities AS c ON c.id = fa.city_id\nLEFT JOIN province AS p ON p.id = c.province_id\nWHERE \n u.disabled = false\n and fbh.booked_online = TRUE\n and fbh.is_pending = FALSE\n and u.active = true\n and u.role_id = 1 /* creata da utente */\n and fbh.action_id != 2\n and (u.password != ‘’ OR u.facebook_id IS NOT NULL OR u.sportilia_id IS NOT NULL OR u.google_plus_id IS NOT NULL)\n and fa.id NOT IN (58, 671)\n [[AND fbh.field_booking_insert_date between {{min_date}}::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’]]\nGROUP BY u.id\nORDER BY tot_prenotazioni DESC;”,
:template_tags
{:min_date {:id “b2d5ce0d-4ebb-49d9-9893-2f39c0a4e7e9”, :name “min_date”, :display_name “Min date”, :type “date”, :required true, :default “2016-01-01”},
:max_date {:id “5858100d-8533-5360-090c-80e6d59162b0”, :name “max_date”, :display_name “Max date”, :type “date”, :required true, :default “2016-12-31”}}},
:parameters
[{:type “date/single”, :target [“variable” [“template-tag” “min_date”]], :value “2017-01-01”} {:type “date/single”, :target [“variable” [“template-tag” “max_date”]], :value “2017-05-31”}],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info
{:executed-by 1,
:context :ad-hoc,
:query-hash [-30, -11, -28, -71, -92, -94, -13, 14, -24, -95, 114, -7, 97, -15, -52, -11, 56, 15, 79, 106, -119, -99, -11, 71, -106, 1, 103, -13, -29, 92, -61, 110],
:query-type “native”}},
:expanded-query nil}

Mar 24 14:10:47 WARN metabase.query-processor :: Query failure: ERROR: syntax error at or near “{”
Position: 1935
[“query_processor$assert_query_status_successful.invokeStatic(query_processor.clj:149)”
“query_processor$assert_query_status_successful.invoke(query_processor.clj:142)”
“query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:181)”
“query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:175)”
“query_processor$fn__24095$dataset_query__24100$fn__24101.invoke(query_processor.clj:212)”
“query_processor$fn__24095$dataset_query__24100.invoke(query_processor.clj:199)”
“api.dataset$fn__24866$fn__24869.invoke(dataset.clj:36)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:229)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:224)”
“api.dataset$fn__24866.invokeStatic(dataset.clj:30)”
“api.dataset$fn__24866.invoke(dataset.clj:30)”
“middleware$enforce_authentication$fn__34556.invoke(middleware.clj:119)”
“api.routes$fn__34682.invokeStatic(routes.clj:57)”
“api.routes$fn__34682.invoke(routes.clj:57)”
“routes$fn__35755.invokeStatic(routes.clj:44)”
“routes$fn__35755.invoke(routes.clj:44)”
“middleware$log_api_call$fn__34655$fn__34657.invoke(middleware.clj:331)”
“middleware$log_api_call$fn__34655.invoke(middleware.clj:330)”
“middleware$add_security_headers$fn__34605.invoke(middleware.clj:246)”
“middleware$bind_current_user$fn__34560.invoke(middleware.clj:139)”
“middleware$maybe_set_site_url$fn__34609.invoke(middleware.clj:268)”]

Mar 24 14:10:47 DEBUG metabase.middleware :: POST /api/dataset 200 (1 s) (5 DB calls)`

3

Unfortunately I believe you’re running into this issue. As of 0.23, we’re basically not letting you inject SQL tokens into templates any more. This was a huge security hole, and in closing it we’ve unfortunately limited what you can do with SQL templates.

1 Like
4

Any changes here? I think I have the same problem with bigquery. What’s a good workaround for version 0.24 ?

5

You can read the full discussion here, but the way people are getting around this now is that we included in 0.23.1 a way to let you use SQL field filters as filter widgets on SQL questions directly (previously, they were only meant to be used as a way to connect SQL questions to dashboard filters). Does this help you, @mcpeterson?

6

Not sure. We actually debugged the problem for us. A question mark in a string literal will cause the variable to be injected into the string literalI. Thought I logged a bug yesterday, but it appears to have disappearred.

On bigquery, version 0.22

breaks:
select ‘?a string’, {{somedate}}
from faketable
limit 1

works
select ‘?a string’, {{somedate}}
from faketable
limit 1

7

We had a regex that requires a ? which we’re trying to work around now.

8

Ah, I see, it's this issue:

9

Yeah, that’s an unfortunate one