When creating a new question from a model that includes this postgres syntax case t1.field1 ? 'val' then val1 else val2 end
, if I apply a filter on a text field (let's say t1.field2), it returns me an error No value specified for parameter 2.
After looking deeper, I found thanks the conversation to raw SQL that the ?
inside the case
is wrongly detected by Metabase by the placeholder to replace by the filter value
To be more concrete, here's the tmp
model I created
select
t1. title->>'fr' as title,
(case when t2.name ? 'fr' then t2.name->>'fr' else 'No name' end) as name
from t1
left join t2 on t2.id = t1.link_to_t2
Now I create a filtered question from this model:
... and it returns me an error
... and if I look into converted sql
SELECT "source"."title" AS "title", "source"."name" AS "name"
FROM (select
title->>'fr' as title,
(case when name '%b%' 'fr' then name->>'fr' else 'No name' end) as name
from t1
left join t2 on t2.id = t1.link_to_t2) "source"
WHERE (lower("source"."title") like ?)
LIMIT 1048575
As you see, the ?
from the postgres syntax is wrongly detected has the placeholder to fill, whereas the ? in the WHERE clause isn't replaced