Presto TYPE_MISMATCH错误：SQL联合查询GROUP BY子句类型不匹配问题

错误1：TYPE_MISMATCH:Left side of logical expression must evaluate to a boolean (actual: varchar) 修复 #

• 错误根因：第二个SELECT查询的SQL语法顺序不符合规范，SQL执行逻辑要求WHERE子句的所有过滤条件必须放在GROUP BY之前，你把exists、not exists的过滤条件直接写在了GROUP BY的字段列表后面，导致Presto把c.shipping_address_country（varchar类型）和后面的and exists逻辑判断拼接成了非法表达式，触发类型不匹配报错。
• 额外注意：UNION ALL要求上下两个查询的列数量、列类型完全对齐，你第一个SELECT查询没有输出status字段，第二个SELECT多了一个常量status列，会额外触发UNION ALL列不匹配错误，需要同步修复。
• 修复方式：
  1. 把第二个SELECT的exists和not exists条件挪到WHERE子句中，也就是放在where sub.status in ('trialing')后面、GROUP BY前面
  2. 给第一个SELECT补上对应的status常量字段，比如'active' as status

错误2：缩减GROUP BY字段触发聚合类错误修复 #

• 错误根因：Presto严格遵循SQL标准聚合规则：SELECT子句中所有非聚合函数的列，必须全部出现在GROUP BY子句中。你如果只保留c.email、c.id作为GROUP BY字段，但SELECT里还查了shipping_name、shipping_address_city等多个非聚合字段，这些字段不在GROUP BY列表里就会触发聚合规则错误。
• 修复方式：二选一即可
  1. 继续保留原有的全量非聚合字段在GROUP BY列表里，只要保证SELECT里的非聚合字段都在GROUP BY中就不会报错
  2. 对非聚合的字段加聚合函数包裹，比如用max(c.shipping_name) as shipping_name，因为同一个customer_id对应的用户配送信息是唯一的，max/min都不会改变结果，这种写法可以简化GROUP BY的字段数量

修正后的完整SQL参考 #

active_customer_plans as (

  select c.email, c.id as customer_id, c.shipping_name, c.name, c.shipping_address_line1, c.shipping_address_line2, 
    c.shipping_address_city, c.shipping_address_state, c.shipping_address_postal_code, c.shipping_address_country, sum(charges.amount) as total_charges, 
    sum(charges.amount_refunded) as total_refunds, 'active' as status -- 补上status字段匹配union all规则
  from subscriptions sub
    left join charges on sub.customer_id = charges.customer_id
    left join customers c on sub.customer_id = c.id
  where sub.status in ('active', 'past_due')
  group by c.email, c.id, c.shipping_name, c.name, c.shipping_address_line1, c.shipping_address_line2, 
    c.shipping_address_city, c.shipping_address_state, c.shipping_address_postal_code, c.shipping_address_country
  

  union all

  -- including subscriptions migrated from vhx existing as trialing
  -- and excluding paused subscriptions
  select c.email, c.id as customer_id, c.shipping_name, c.name, c.shipping_address_line1, c.shipping_address_line2, 
    c.shipping_address_city, c.shipping_address_state, c.shipping_address_postal_code, c.shipping_address_country, sum(charges.amount) as total_charges,
    sum(charges.amount_refunded) as total_refunds, 'active (migrated_trialing)' as status
  from subscriptions sub
    left join charges on sub.customer_id = charges.customer_id
    left join customers c on sub.customer_id = c.id
  where sub.status in ('trialing')
  -- 过滤条件挪到GROUP BY之前
    and exists (
      select sub_meta.subscription_id
      from subscriptions_metadata sub_meta
      where sub.id = sub_meta.subscription_id
        and sub_meta.key = 'zype_migration'
        and sub_meta.value = 'true'
    )
    and not exists (
      select sub_meta.subscription_id
      from subscriptions_metadata sub_meta
      where sub.id = sub_meta.subscription_id
        and sub_meta.key = 'zype_status'
        and sub_meta.value = 'paused'
    )
  group by c.email, c.id, c.shipping_name, c.name, c.shipping_address_line1, c.shipping_address_line2, 
     c.shipping_address_city, c.shipping_address_state, c.shipping_address_postal_code, c.shipping_address_country
)
select status as subscription_status,
    email as customer_email,
  customer_id,
  shipping_name,
  name,
  shipping_address_line1,
  shipping_address_line2,
  shipping_address_city,
  shipping_address_state,
  shipping_address_postal_code,
  shipping_address_country,
  total_charges,
  total_refunds
from active_customer_plans

order by 1, 2

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