Unrecognized table for BigQuery Field Filter

I am trying to create a "field filter" for a custom query on a BigQuery table.

SELECT *, value.string_value AS community_name
FROM `analytics_210183882.events_*` as a, UNNEST(event_params) as b
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND b.key = 'community_name'
)

SELECT count(DISTINCT user_pseudo_id) total_users 
FROM main
WHERE {{community_name}}

When creating the field filter that is linked to a different table in BigQuery I get the following error:

Unrecognized name: `analytics_210183882.community` at [11:7]

analytics_210183882.community is a table that exists in BigQuery but the Filed Filter does not seem to be able to find it.

The logs print the following:

:error_type :invalid-query,
   :ex-data
   {:type :invalid-query,
    :sql
    "-- Metabase:: userID: 1 queryType: native queryHash: 9a7ed86511d95bf5b7204b798901262221091931e4bd7030ecb12bbe3505f2d5\nwith main AS (\nSELECT *, value.string_value AS community_name\nFROM `analytics_210183882_*` as a, UNNEST(event_params) as b\nWHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())\nAND b.key = 'community_name'\n)\n\nSELECT count(DISTINCT user_pseudo_id) total_users \nFROM main\nWHERE `analytics_210183882.community`.`string_value` IN (?)",
    :parameters ["US-MA-Boston"]}}],
 :error_type :invalid-query,
 :json_query
 {:constraints {:max-results 10000, :max-results-bare-rows 2000},
  :type :native,
  :middleware {:js-int-to-string? true},
  :native
  {:template-tags
   {"community_name"
    {:id "1be6e69d-fd17-fd3d-f346-e565d0de2a4b",
     :name "community_name",
     :display-name "Community name",
     :type :dimension,
     :dimension [:field 10205 nil],
     :widget-type :category,
     :required true}},
   :query
   "\nwith main AS (\nSELECT *, value.string_value AS community_name\nFROM `analytics_210183882.events_*` as a, UNNEST(event_params) as b\nWHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())\nAND b.key = 'community_name'\n)\n\nSELECT count(DISTINCT user_pseudo_id) total_users \nFROM main\nWHERE {{community_name}}\n\n\n"},
  :database 2,
  :parameters
  [{:type "category",
    :value ["US-MA-Boston"],
    :target ["dimension" ["template-tag" "community_name"]]}],
  :async? true,
  :cache-ttl nil},
 :status :failed,
 :class clojure.lang.ExceptionInfo,
 :stacktrace
 ["--> driver.google$execute_no_auto_retry.invokeStatic(google.clj:41)"
  "driver.google$execute_no_auto_retry.invoke(google.clj:33)"
  "driver.google$execute.invokeStatic(google.clj:55)"
  "driver.google$execute.invoke(google.clj:45)"
  "driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:216)"
  "driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
  "driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:205)"
  "driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
  "driver.bigquery$process_native_STAR_$thunk__1576.invoke(bigquery.clj:277)"
  "driver.bigquery$process_native_STAR_.invokeStatic(bigquery.clj:279)"
  "driver.bigquery$process_native_STAR_.invoke(bigquery.clj:272)"
  "driver.bigquery$eval1580$fn__1582.invoke(bigquery.clj:299)"
  "query_processor.context$executef.invokeStatic(context.clj:59)"
  "query_processor.context$executef.invoke(context.clj:48)"
  "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
  "query_processor.context.default$default_runf.invoke(default.clj:66)"
  "query_processor.context$runf.invokeStatic(context.clj:45)"
  "query_processor.context$runf.invoke(context.clj:39)"
  "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
  "query_processor.reducible$pivot.invoke(reducible.clj:31)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47305.invoke(mbql_to_native.clj:25)"
  "query_processor.middleware.check_features$check_features$fn__46547.invoke(check_features.clj:39)"
  "query_processor.middleware.limit$limit$fn__47291.invoke(limit.clj:37)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__45999.invoke(cache.clj:211)"
  "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__47551.invoke(optimize_temporal_filters.clj:204)"
  "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__49481.invoke(validate_temporal_bucketing.clj:50)"
  "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45118.invoke(auto_parse_filter_values.clj:43)"
  "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__41435.invoke(wrap_value_literals.clj:161)"
  "query_processor.middleware.annotate$add_column_info$fn__41310.invoke(annotate.clj:605)"
  "query_processor.middleware.permissions$check_query_permissions$fn__46419.invoke(permissions.clj:81)"
  "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48409.invoke(pre_alias_aggregations.clj:40)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46620.invoke(cumulative_aggregations.clj:60)"
  "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48708.invoke(resolve_joined_fields.clj:102)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__49021.invoke(resolve_joins.clj:171)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44694.invoke(add_implicit_joins.clj:190)"
  "query_processor.middleware.large_int_id$convert_id_to_string$fn__47255.invoke(large_int_id.clj:59)"
  "query_processor.middleware.format_rows$format_rows$fn__47236.invoke(format_rows.clj:74)"
  "query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__43988.invoke(add_default_temporal_unit.clj:23)"
  "query_processor.middleware.desugar$desugar$fn__46686.invoke(desugar.clj:21)"
  "query_processor.middleware.binning$update_binning_strategy$fn__45505.invoke(binning.clj:227)"
  "query_processor.middleware.resolve_fields$resolve_fields$fn__46222.invoke(resolve_fields.clj:34)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__44343.invoke(add_dimension_projections.clj:312)"
  "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__44572.invoke(add_implicit_clauses.clj:147)"
  "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__49430.invoke(upgrade_field_literals.clj:40)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__44857.invoke(add_source_metadata.clj:123)"
  "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48583.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
  "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45065.invoke(auto_bucket_datetimes.clj:147)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46269.invoke(resolve_source_table.clj:45)"
  "query_processor.middleware.parameters$substitute_parameters$fn__48391.invoke(parameters.clj:111)"
  "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46321.invoke(resolve_referenced.clj:79)"
  "query_processor.middleware.expand_macros$expand_macros$fn__46942.invoke(expand_macros.clj:155)"
  "query_processor.middleware.add_timezone_info$add_timezone_info$fn__44866.invoke(add_timezone_info.clj:15)"
  "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49383.invoke(splice_params_in_response.clj:32)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48594$fn__48598.invoke(resolve_database_and_driver.clj:31)"
  "driver$do_with_driver.invokeStatic(driver.clj:60)"
  "driver$do_with_driver.invoke(driver.clj:56)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48594.invoke(resolve_database_and_driver.clj:25)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47182.invoke(fetch_source_query.clj:274)"
  "query_processor.middleware.store$initialize_store$fn__49392$fn__49393.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
  "query_processor.store$do_with_store.invoke(store.clj:38)"
  "query_processor.middleware.store$initialize_store$fn__49392.invoke(store.clj:10)"
  "query_processor.middleware.validate$validate_query$fn__49437.invoke(validate.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__47318.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44712.invoke(add_rows_truncated.clj:35)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49368.invoke(results_metadata.clj:147)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__46563.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__48480.invoke(process_userland_query.clj:135)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46503.invoke(catch_exceptions.clj:173)"
  "query_processor.reducible$async_qp$qp_STAR___37989$thunk__37990.invoke(reducible.clj:103)"
  "query_processor.reducible$async_qp$qp_STAR___37989$fn__37992.invoke(reducible.clj:108)"],
 :context :question,
 :error "Unrecognized name: `analytics_210183882.community` at [11:7]",
 :row_count 0,
 :running_time 0,
 :ex-data
 {"code" 400,
  "errors"
  [{"domain" "global", "location" "q", "locationType" "parameter", "message" "Unrecognized name: `analytics_210183882.community` at [11:7]", "reason" "invalidQuery"}],
  "message" "Unrecognized name: `analytics_210183882.community` at [11:7]",
  "status" "INVALID_ARGUMENT"},
 :data {:rows [], :cols []}}

Hi @dataichou
You have to add the Field Filter variable to the "main" query - and not use table aliases. See:
https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html

Hi @flamber, I have recreated the same query even simplifying it and not using Aliases and the same problem arises.

SELECT *
FROM `analytics_210183882.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND {{event_name}}

I still get the same error when I "map the field" to a different table in BigQuery. Moreover you see that the SQL that metabase produced is invalid for BigQuery.

"-- Metabase:: userID: 1 queryType: native queryHash: 9a7ed86511d95bf5b7204b798901262221091931e4bd7030ecb12bbe3505f2d5\nwith main AS (\nSELECT *, value.string_value AS community_name\nFROM `analytics_210183882_*` as a, UNNEST(event_params) as b\nWHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())\nAND b.key = 'community_name'\n)\n\nSELECT count(DISTINCT user_pseudo_id) total_users \nFROM main\nWHERE `analytics_210183882.community`.`string_value` IN (?)", :parameters ["US-MA-Boston"]}}]

There really is a bug here or a really severe usability issue

@dataichou

Post "Diagnostic Info" from Admin > Troubleshooting.

You need to reference the table (full reference, including dataset) that you are using with the Field Filter.

The query you're posting is different from the error, which says that the Field Filter is referencing the table `analytics_210183882.community`

@flamber

{
  "browser-info": {
    "language": "en-GB",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.212 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.11+9",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.11",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.11+9",
    "os.name": "Linux",
    "os.version": "4.15.0-1065-aws",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "bigquery",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "11.10"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.18"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-05-17",
      "tag": "v0.39.2",
      "branch": "release-x.39.x",
      "hash": "b0d9436"
    },
    "settings": {
      "report-timezone": "US/Eastern"
    }
  }
}

@flamber

I guess what it is not so clear is that the table used as field filter needs to be part of the query.

@dataichou That was why I linked to our article about Field Filters, which explains that part:
https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html#field-filter-gotchas