Metabase报无效引用错误：含日期字段过滤器的SQL查询异常

编写了如下SQL查询，其中merchantname和createdat是Metabase变量，多次遇到无效引用错误：

ERROR: invalid reference to FROM-clause entry for table "ra_orders"
Hint: Perhaps you meant to reference the table alias "a"

且多数使用日期字段过滤器的查询都会出现该问题，尝试多种方法仍未解决。

SELECT
  count(
    distinct "public"."cnpy_portfolio_current_snapshot"."userid"
  ) AS "count"
FROM
  "public"."cnpy_portfolio_current_snapshot"
  INNER JOIN (
    SELECT
      tenderneutralledgerid as ledgerid,
      userid,
      tnl.merchantprogramid,
      tm.merchantid
    FROM
      tdym_tender_neutral_ledgers as tnl
      INNER JOIN tdym_merchant_programs as tm ON tnl.merchantprogramid = tm.merchantprogramid
      where {{createdat}} 
    UNION
    SELECT
      rewardledgerid as ledgerid,
      userid,
      tm.merchantprogramid,
      tm.merchantid
    FROM
      tdym_reward_ledgers as tl
      INNER JOIN tdym_merchant_programs as tm ON tl.merchantid = tm.merchantid
      where {{createdat}} 
  ) AS "Join all Reward Ledgers - Userid" ON "public"."cnpy_portfolio_current_snapshot"."userid" = "Join all Reward Ledgers - Userid"."userid"
  INNER JOIN "public"."tdym_merchants" ON "Join all Reward Ledgers - Userid"."merchantid" = "public"."tdym_merchants"."merchantid"
 

WHERE {{merchantname}}

• 排查变量关联错误：当前SQL里没有ra_orders表，但错误提示引用了它，先检查createdat和merchantname变量的定义，看是否不小心关联了未在当前查询中出现的ra_orders表。
• 明确变量过滤字段：子查询中的where {{createdat}}要指定所属表的日期字段，比如第一个子查询改为where tnl.created_at {{createdat}}，第二个改为where tl.created_at {{createdat}}，避免Metabase解析时匹配到错误表。
• 简化子查询别名：把长别名"Join all Reward Ledgers - Userid"改成短别名（比如rl），减少解析出错概率，修改后的JOIN示例：

  INNER JOIN (
    -- 子查询内容不变
  ) AS rl ON "public"."cnpy_portfolio_current_snapshot"."userid" = rl."userid"
  INNER JOIN "public"."tdym_merchants" ON rl."merchantid" = "public"."tdym_merchants"."merchantid"
  

• 检查变量类型设置：确保createdat设为「日期/时间」类型，merchantname关联public.tdym_merchants表的对应字段，变量定义时不要误引用其他表。
• 清理Metabase缓存：如果之前有涉及ra_orders表的查询，清理查询缓存，避免旧缓存干扰当前查询解析。

内容的提问来源于stack exchange，提问作者Roshni