DBeaver查询Redshift时LEFT OUTER JOIN结果等同INNER JOIN原因

问题背景 #

• 表t1的uuid字段共630000个去重值，表t2的uuid字段共300000个去重值
• 已知t2所有uuid均能在t1中找到匹配项，t1存在约330000个uuid无法在t2中匹配，与两表数据量差值吻合
• 执行以下左连接查询未匹配记录的SQL时，无任何结果返回，最初怀疑是Redshift与DBeaver的通信异常：

SELECT 
    t1.uuid
    , t2.uuid
FROM 
    t1 --630,000 uuids
    LEFT OUTER JOIN t2 -- 300,000 uuids
        ON t1.uuid = t2.uuid 
WHERE
    t2.uuid IS NULL

样例逻辑参考：t1.uuid包含ufo123、abc456、def789三个值，t2.uuid包含ufo123、def789两个值，预期上述SQL应返回未匹配的abc456记录。

• 执行以下全连接统计SQL时，仅返回300000条结果（与t2数据量一致），完全不符合预期：

SELECT 
    COUNT(DISTINCT t1.uuid)
FROM 
    t1 --630,000 uuids
    FULL JOIN t2 -- 300,000 uuids
        ON t1.uuid = t2.uuid 

根因与排查方案 #

该问题和客户端通信故障无关，按优先级从高到低排查以下三类常见原因即可：

• 不可见字符导致匹配失败
  这是这类uuid匹配问题的最高发原因：两表uuid字段存在肉眼不可见的字符差异，常见为尾随空格、换行符、零宽空格，比如t1中存储的是abc456 （末尾带空格），t2中存储的是abc456（无多余字符），等值判断时会被判定为不相等，但手动查询预览时看不到多余字符，会造成“值应该匹配”的错觉。
  直接执行以下语句验证，如果返回值约为330000即可确认是该问题：

  SELECT COUNT(DISTINCT TRIM(t1.uuid))
  FROM t1
  LEFT JOIN t2 ON TRIM(t1.uuid) = TRIM(t2.uuid)
  WHERE t2.uuid IS NULL
  

  修复方式：join时统一对字段做TRIM()去除首尾空白，或提前清洗表字段，删除所有不可见特殊字符。
• 查询的表对象不符合预期
  当前会话下查询的t1并非你认知中存储了63w去重uuid的表：常见场景为schema优先级问题命中了同名表、t1实际是带行级权限控制的视图、t1为分区表触发了隐式分区裁剪仅扫描到30w行数据。
  先单独执行基础统计确认表的实际数据量：

  -- 统计当前会话下t1的去重uuid量
  SELECT COUNT(DISTINCT uuid) FROM t1;
  -- 统计当前会话下t2的去重uuid量
  SELECT COUNT(DISTINCT uuid) FROM t2;
  

  如果t1的统计结果不是630000，检查表的schema路径、权限配置、分区过滤规则即可。
• 字段类型不匹配触发隐式转换异常
  若两表uuid字段数据类型不一致，比如t1是定长CHAR(36)类型（长度不足会自动补尾部空格），t2是变长VARCHAR(36)类型，部分Redshift版本的隐式转换规则会导致等值匹配结果不符合预期。
  修复方式：join时统一将字段转为相同的变长类型即可：

  ON t1.uuid::VARCHAR(36) = t2.uuid::VARCHAR(36)
  

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