Error when adding filter to GUI question when Postgres syntax includes a?

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:

image

... 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

Hi @dynnammo
Use ?? to avoid the parameter replacement. This is a strange quirk in the Postgres JDBC driver.
For history https://github.com/metabase/metabase/issues/1964

1 Like