Teradata UNION结合CUBE查询报3848错误 SMALLINT COMPRESS问题

问题背景 #

在Teradata环境下对两个查询执行UNION操作时触发报错，错误信息如下：

错误码：3848
错误提示：The ORDER BY clause must contain only integer constants（ORDER BY子句仅允许包含整型常量）

经核查表结构，查询涉及的返回字段大多为定义完全一致的SMALLINT类型，仅1个字段配置了COMPRESS压缩属性，压缩值为从3开始的长序列连续数值。

涉及SQL代码 #

SELECT
    COALESCE (ContractType, 'InvalidType') AS "Contract",
    COALESCE (ContractStatus, 'InvalidStatus') AS "Status",
    COUNT(ContractType) AS "Contract_Type_Count",
    COUNT (ContractStatus) AS "Contract_Status_Count"
    NULL AS "negCodeErr_count"
    FROM fund_inventory_db.ContractDetail
    GROUP BY CUBE (ContractType, ContractStatus)
    UNION
    NULL,
    NULL,
    NULL,
    NULL,
    SELECT COUNT(*)
    FROM fund_inventory_db.ContractDetail
    WHERE ContractSource = -2
    ORDER BY ContractType, ContractStatus;

字段定义参考 #

• 常规SMALLINT字段定义格式：

[...columnName...] SMALLINT NOT NULL DEFAULT 0

• 带COMPRESS属性的特殊字段定义：

[...columnName...] SMALLINT NOT NULL DEFAULT 0 COMPRESS (3,4,5,6,7,8...)

待解答问题 #

1. 字段配置上述COMPRESS压缩规则是否会导致排序异常？若UNION两端对应字段的COMPRESS值范围不同（如一端为COMPRESS(3,4,5,6,7...)，另一端为COMPRESS(1,2,3,4,5...)或未配置COMPRESS属性），是否会触发本次3848报错？
2. UNION操作是否支持其中某一个子查询使用CUBE()分组函数？

问题解答 #

COMPRESS属性与3848报错的关联说明 #

• 字段配置的COMPRESS压缩规则不会导致排序异常，也和本次3848报错没有任何关系。COMPRESS属于Teradata物理存储层的优化属性，仅改变数据的底层存储压缩方式，不改变字段的数据类型、取值逻辑，对SQL层的排序、集合类操作完全透明。无论UNION两端对应字段的COMPRESS值范围是否存在差异、是否配置COMPRESS属性，都不会触发3848错误。
• 本次报错的根因有两点：
  1. UNION语法书写错误：UNION要求两侧必须是字段数量、对应位置数据类型完全匹配的完整SELECT语句，当前SQL中UNION后直接书写零散常量值、再拼接SELECT语句的写法属于非法语法，解析器无法识别最终结果集的列结构。
  2. UNION结果集的排序规则限制：Teradata中对UNION/UNION ALL的最终合并结果排序时，ORDER BY子句不允许直接引用某一侧子查询的源表字段名，只能引用合并结果集的最终输出列别名，或者填写对应列位置的整型常量——合并后的结果集不属于任意一个子查询，源字段名在排序阶段无法被解析，就会抛出3848错误。

UNION与CUBE分组函数的兼容性说明 #

• UNION操作完全支持单侧子查询使用CUBE()、ROLLUP()、GROUPING SETS()等扩展分组函数，唯一校验规则是UNION两侧SELECT返回的字段数量一致、对应位置字段的数据类型兼容，不会因为单侧子查询使用CUBE就触发报错。

修正后可正常执行的SQL参考 #

SELECT
    COALESCE (ContractType, 'InvalidType') AS "Contract",
    COALESCE (ContractStatus, 'InvalidStatus') AS "Status",
    COUNT(ContractType) AS "Contract_Type_Count",
    COUNT (ContractStatus) AS "Contract_Status_Count", -- 原SQL此处缺失逗号，已补全
    NULL AS "negCodeErr_count"
FROM fund_inventory_db.ContractDetail
GROUP BY CUBE (ContractType, ContractStatus)
UNION
SELECT
    NULL,
    NULL,
    NULL,
    NULL,
    COUNT(*)
FROM fund_inventory_db.ContractDetail
WHERE ContractSource = -2
-- 排序可直接使用列位置常量，即ORDER BY 1,2，效果一致
ORDER BY "Contract", "Status";

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