[BUG]: Variable Field Filter not working

Bug reports
1

I have a slightly complicated SQL query that keeps failing on me because of (I assume) small bug associated with "variables". I would love to debug it myself but I can't find a way to access the actual query that gets run once the variables are substituted (something bad must be happening there).

I am pasting here below both the query with the variables and without. The one without runs just fine, the one with variables gives me this error.

ERROR: invalid reference to FROM-clause entry for table "dbmanagment_right" Hint: Perhaps you meant to reference the table alias "u0". Position: 1301

All the variables are set-up as "Field Filter"

--With Variable--

SELECT 
  "dbmanagment_license"."beitrag_id", 
  "dbmanagment_license"."license_id", 
  "dbmanagment_license"."vertragsabschlussjahr", 
  "dbmanagment_license"."vertragsnummer", 
  "dbmanagment_license"."lizenzgeber_id", 
  "dbmanagment_license"."lizenznehmer_id", 
  "dbmanagment_license"."lizenzbeginn", 
  "dbmanagment_license"."lizenzende", 
  "dbmanagment_license"."lizenzpreis_euro", 
  "dbmanagment_license"."lizenzstatus", 
  "dbmanagment_license"."lizenzpaket", 
  "dbmanagment_license"."ek_vk", 
  "dbmanagment_license"."territory_set_id", 
  "dbmanagment_license"."right_set_id" 
FROM 
  "dbmanagment_license" 
WHERE 
  (
    "dbmanagment_license"."right_set_id" IN (
      SELECT 
        V0."id" 
      FROM 
        "dbmanagment_rightset" V0 
        INNER JOIN "dbmanagment_rightset_exclude" V1 ON (V0."id" = V1."rightset_id") 
        INNER JOIN "dbmanagment_rightset_include" V3 ON (V0."id" = V3."rightset_id") 
      WHERE 
        (
          V1."right_id" IN (
            SELECT 
              U0."id" 
            FROM 
              "dbmanagment_right" U0 
            WHERE 
                {{ exclude_rights}}
          ) 
          AND V3."right_id" IN (
            SELECT 
              U0."id" 
            FROM 
              "dbmanagment_right" U0 
            WHERE 
                {{ include_rights}}
              
          )
        )
    ) 
    AND "dbmanagment_license"."territory_set_id" IN (
      SELECT 
        V0."id" 
      FROM 
        "dbmanagment_territoryset" V0 
        INNER JOIN "dbmanagment_territoryset_exclude" V1 ON (V0."id" = V1."territoryset_id") 
        INNER JOIN "dbmanagment_territoryset_include" V3 ON (V0."id" = V3."territoryset_id") 
      WHERE 
        (
          V1."territory_id" IN (
            SELECT 
              U0."id" 
            FROM 
              "dbmanagment_territory" U0 
            WHERE 
              {{ exclude_territory}}
          ) 
          AND V3."territory_id" IN (
            SELECT 
              U0."id" 
            FROM 
              "dbmanagment_territory" U0 
            WHERE 
              {{ include_territory}}
          )
        )
    ) 
    AND "dbmanagment_license"."lizenzende" < '2022-08-15' 
    AND NOT (
      "dbmanagment_license"."beitrag_id" IN (
        SELECT 
          W0."beitrag_id" 
        FROM 
          "dbmanagment_license" W0 
        WHERE 
          (
            W0."right_set_id" IN (
              SELECT 
                V0."id" 
              FROM 
                "dbmanagment_rightset" V0 
                INNER JOIN "dbmanagment_rightset_include" V1 ON (V0."id" = V1."rightset_id") 
              WHERE 
                V1."right_id" IN (
                  SELECT 
                    U0."id" 
                  FROM 
                    "dbmanagment_right" U0 
                  WHERE 
                   {{include_territory}}
                )
            ) 
            AND W0."territory_set_id" IN (
              SELECT 
                V0."id" 
              FROM 
                "dbmanagment_territoryset" V0 
                INNER JOIN "dbmanagment_territoryset_include" V1 ON (V0."id" = V1."territoryset_id") 
              WHERE 
                V1."territory_id" IN (
                  SELECT 
                    U0."id" 
                  FROM 
                    "dbmanagment_territory" U0 
                  WHERE 
                    {{ include_territory }}
                )
            ) 
            AND W0."lizenzende" > '2022-08-15'
          )
      )
    )
  )

---without variable ---

SELECT 
  "dbmanagment_license"."beitrag_id", 
  "dbmanagment_license"."license_id", 
  "dbmanagment_license"."vertragsabschlussjahr", 
  "dbmanagment_license"."vertragsnummer", 
  "dbmanagment_license"."lizenzgeber_id", 
  "dbmanagment_license"."lizenznehmer_id", 
  "dbmanagment_license"."lizenzbeginn", 
  "dbmanagment_license"."lizenzende", 
  "dbmanagment_license"."lizenzpreis_euro", 
  "dbmanagment_license"."lizenzstatus", 
  "dbmanagment_license"."lizenzpaket", 
  "dbmanagment_license"."ek_vk", 
  "dbmanagment_license"."territory_set_id", 
  "dbmanagment_license"."right_set_id" 
FROM 
  "dbmanagment_license" 
WHERE 
  (
    "dbmanagment_license"."right_set_id" IN (
      SELECT 
        V0."id" 
      FROM 
        "dbmanagment_rightset" V0 
        INNER JOIN "dbmanagment_rightset_exclude" V1 ON (V0."id" = V1."rightset_id") 
        INNER JOIN "dbmanagment_rightset_include" V3 ON (V0."id" = V3."rightset_id") 
      WHERE 
        (
          V1."right_id" IN (
            SELECT 
              U0."id" 
            FROM 
              "dbmanagment_right" U0 
            WHERE 
              U0."name" IN ('TVoDE')
          ) 
          AND V3."right_id" IN (
            SELECT 
              U0."id" 
            FROM 
              "dbmanagment_right" U0 
            WHERE 
              U0."name" IN (
                'alleVoD', 'SVoDE', 'FTVEPTVETHHV', 'ALLE'
              )
          )
        )
    ) 
    AND "dbmanagment_license"."territory_set_id" IN (
      SELECT 
        V0."id" 
      FROM 
        "dbmanagment_territoryset" V0 
        INNER JOIN "dbmanagment_territoryset_exclude" V1 ON (V0."id" = V1."territoryset_id") 
        INNER JOIN "dbmanagment_territoryset_include" V3 ON (V0."id" = V3."territoryset_id") 
      WHERE 
        (
          V1."territory_id" IN (
            SELECT 
              U0."id" 
            FROM 
              "dbmanagment_territory" U0 
            WHERE 
              U0."name" IN ('AA')
          ) 
          AND V3."territory_id" IN (
            SELECT 
              U0."id" 
            FROM 
              "dbmanagment_territory" U0 
            WHERE 
              U0."name" IN ('I', 'IST', 'W', 'DGneu')
          )
        )
    ) 
    AND "dbmanagment_license"."lizenzende" < '2022-08-15' 
    AND NOT (
      "dbmanagment_license"."beitrag_id" IN (
        SELECT 
          W0."beitrag_id" 
        FROM 
          "dbmanagment_license" W0 
        WHERE 
          (
            W0."right_set_id" IN (
              SELECT 
                V0."id" 
              FROM 
                "dbmanagment_rightset" V0 
                INNER JOIN "dbmanagment_rightset_include" V1 ON (V0."id" = V1."rightset_id") 
              WHERE 
                V1."right_id" IN (
                  SELECT 
                    U0."id" 
                  FROM 
                    "dbmanagment_right" U0 
                  WHERE 
                    U0."name" IN ('alleVoD')
                )
            ) 
            AND W0."territory_set_id" IN (
              SELECT 
                V0."id" 
              FROM 
                "dbmanagment_territoryset" V0 
                INNER JOIN "dbmanagment_territoryset_include" V1 ON (V0."id" = V1."territoryset_id") 
              WHERE 
                V1."territory_id" IN (
                  SELECT 
                    U0."id" 
                  FROM 
                    "dbmanagment_territory" U0 
                  WHERE 
                    U0."name" IN ('I')
                )
            ) 
            AND W0."lizenzende" > '2022-08-15'
          )
      )
    )
  )
2

Hi @nmacchitella
Field Filter does not support table aliases:
https://www.metabase.com/learn/sql-questions/field-filters#field-filters-are-incompatible-with-aliasing

3

Thanks! I fixed it!

Side note: do you know if there is a way to see the final query being submitted after variables are put in place

4

@nmacchitella Only by looking in the database query log, or viewing the response data, when submitting a query.
https://github.com/metabase/metabase/issues/6096 - upvote by clicking :+1: on the first post