Field Filter Category - Allow no default value for optional clause

[tobbbles]

Category Field Filters should allow no default option, so they can be used properly in optional clauses

Reproduce

Example clause:

[[ AND id IN (SELECT id FROM `dataset.with-id-and-name` WHERE {{name}} LIMIT 1) ]]

These field filter settings

This 'optional clause' is always included in the native query, even when the widget has no value set.

Desired Behaviour

There should be a "No Default" field, which is the default (default no default 🤔)

EG:

When "No Default" is used on this widget, then the clause should be optional and excluded from the native query that's constructed, similar to other, non-fieldfilter widgets.

[tobbbles]

Here's some handy debug logs, with some info redacted.

Query:

#standardSql
SELECT sum(wins) AS wins
FROM `data.secretSauce*`
WHERE {{dateRange}}
[[ AND id IN (SELECT id FROM `data.sauceMakers` WHERE {{name}} LIMIT 1) ]]
ORDER BY wins DESC

Debug:

{:query
 "#standardSql\nSELECT sum(wins) AS wins\nFROM `aggregate.data*`\nWHERE CAST(date(ts) AS timestamp) BETWEEN ? AND ?\n AND id IN (SELECT id FROM `aggregate.sauceMakers` WHERE 1 = 1 LIMIT 1) \n\nORDER BY wins DESC",
 :template_tags
 {:name {:id "14762412-b62d-DOOT-2f37-863ddad99b84", :name "name", :display_name "Sauce Maker", :type "dimension", :dimension ["field-id" 13330], :widget_type "category"},
  :dateRange
  {:id "333e696b-e258-1dd9-f8b5-e8e4354b9622", :name "dateRange", :display_name "Daterange", :type "dimension", :dimension ["field-id" 1050], :widget_type "date/relative", :default "yesterday"}},
 :params (#inst "2017-06-01T00:00:00.000000000-00:00" #inst "2017-06-30T00:00:00.000000000-00:00")}

The bit to focus on here is the subselect from [[ ... WHERE {{name}} .. ]], as you can see the parameter is substituted with WHERE 1 = 1 `, which shouldn't be the case; that whole clause should be emitted.

[tobbbles]

Looks like it's related to this area of code: https://github.com/metabase/metabase/blob/master/src/metabase/query_processor/middleware/parameters/sql.clj#L165

@camsaul As you were the author of this bit, wanna weigh into what you think's up? 😸

[tobbbles]

Digging into the frontend a little I can see that the ParameterValueWidget for the variable's default value for the Category dimension has value: null.

Whereas if I checkout the the ParameterValueWidget for say, DateRange, there is no value field.

This seems to add up to my comment ☝️ addressing what if value is specified but is 'nil'?

[maliayas]

I had another usecase that lead to this bug. See #8112

[Ucinorn]

I'd like to bump this bug, as I'm seeing the same behaviour, and I notice that in both cases the variable is within a subquery:

SELECT orig.year as year, orig.quarter as quarter, orig.month as month, orig.sum as current_sum, orig.count as current_count, orig.average as current_avg, FROM ( SELECT year(event_start_datetime) as year, quarter(event_start_datetime) as quarter, month(event_start_datetime) as month, sum(cost) AS sum, count(cost) AS count, sum(cost) / count(cost) AS average FROM _reports_booked_jumpers WHERE 1 = 1 [[AND _reports_booked_jumpers.venue = {{venue}}]] [[AND _reports_booked_jumpers.sales_category = {{sales_category}}]] GROUP BY year, quarter, month ORDER BY year DESC, quarter DESC, month DESC ) as orig

And the query i get in the returned is below, not the use of AND venue = 1 = 1 instead of just omitting the whole line:

  orig.year as year, 
  orig.quarter as quarter, 
  orig.month as month, 
  orig.sum as current_sum, 
  orig.count as current_count, 
  orig.average as current_avg, 
  prevyear.sum as prevyear_sum, 
  prevyear.count as prevyear_count, 
  prevyear.average as prevyear_avg
FROM 
(
  SELECT
    year(event_start_datetime) as year, 
    quarter(event_start_datetime) as quarter, 
    month(event_start_datetime) as month, 
    sum(cost) AS sum, 
    count(cost) AS count, 
    sum(cost) / count(cost) AS average 
  FROM 
    _reports_booked_jumpers 
    WHERE 1=1
      AND venue = 1 = 1
  GROUP BY 
    year, 
    quarter, 
    month 
  ORDER BY 
    year DESC, 
    quarter DESC, 
    month DESC
) as orig

[lp-lima]

Community post about the same issue:

https://discourse.metabase.com/t/option-clause-for-field-filter-in-native-sql-parameter/6193

This would allow us dinamically hide or show columns according to the user selection

[camsaul]

Fixed by #10947