Redshift中CTE搭配SELECT DISTINCT报错原因排查

构建了一个CTE，在子查询中使用SELECT DISTINCT时执行报错，但仅使用SELECT时查询可正常运行。该查询由正在调试的R包自动生成，需排查此场景下无法使用SELECT DISTINCT的原因。

SQL查询语句 #

with tab as (
  SELECT coh.cohort_definition_id, pr.person_id,
         'ADT' as codeset_tag, pr.procedure_date as drug_exposure_start_date,
         coh.cohort_start_date,
         coh.cohort_end_date
  FROM truven_ccmr_claims_actual_omop.PROCEDURE_OCCURRENCE pr
  JOIN sandbox_truven.PIONEER2023_US_MarketScan_stg coh
      ON pr.person_id = coh.subject_id
  WHERE procedure_concept_id in (
     4012324, 4304921, 4073141, 4071936, 4073142,
     4073143, 2103796, 2109975, 2109976,
     4512827, 4314682, 4286887, 4341536, 4145907) 
  LIMIT 10
)
SELECT distinct * 
FROM tab 
WHERE cohort_end_date >= drug_exposure_start_date
  AND cohort_start_date <= drug_exposure_start_date limit 10;

报错信息 #

An error occurred when executing the SQL command:
with tab as (SELECT coh.cohort_definition_id, pr.person_id,
         'ADT' as codeset_tag, pr.procedure_date as drug_exposure_start_date,
         coh...

[Amazon](500310) Invalid operation: failed to find conversion function from
"unknown" to text; [SQL State=XX000, DB Errorcode=500310]
1 statement failed.

原因分析 #

这是Amazon Redshift特有的类型解析问题：

• 普通SELECT场景下，Redshift会对未指定类型的字符串常量（如'ADT'）做隐式类型转换，默认识别为TEXT类型。
• 但使用SELECT DISTINCT时，由于需要对结果集做去重排序，Redshift会严格校验字段类型。此时未指定类型的'ADT'被标记为unknown类型，无法自动转换为TEXT，从而触发转换函数缺失的报错。

解决方案 #

显式指定codeset_tag字段的类型，修改CTE中的对应字段即可，有两种常用方式：

-- 方式1：使用CAST函数
CAST('ADT' AS TEXT) as codeset_tag

-- 方式2：使用PostgreSQL风格的类型转换语法
'ADT'::TEXT as codeset_tag

修改后的完整查询：

with tab as (
  SELECT coh.cohort_definition_id, pr.person_id,
         CAST('ADT' AS TEXT) as codeset_tag, pr.procedure_date as drug_exposure_start_date,
         coh.cohort_start_date,
         coh.cohort_end_date
  FROM truven_ccmr_claims_actual_omop.PROCEDURE_OCCURRENCE pr
  JOIN sandbox_truven.PIONEER2023_US_MarketScan_stg coh
      ON pr.person_id = coh.subject_id
  WHERE procedure_concept_id in (
     4012324, 4304921, 4073141, 4071936, 4073142,
     4073143, 2103796, 2109975, 2109976,
     4512827, 4314682, 4286887, 4341536, 4145907) 
  LIMIT 10
)
SELECT distinct * 
FROM tab 
WHERE cohort_end_date >= drug_exposure_start_date
  AND cohort_start_date <= drug_exposure_start_date limit 10;

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