Apache Superset SQL Lab中SQL查询报错求助（Microsoft SQL Server）

在Microsoft SQL Server环境中，以下SQL语句可在数据库管理器中正常运行，但在Apache Superset的SQL Lab执行时报错：

SELECT cast(I04_OP_DATA as date), I04_OP_RUSIS, SUM(I05_SUMA)
FROM I04_ATH
LEFT JOIN I05_ATD 
ON I04_ATH.I04_KODAS_CH  = I05_ATD.I05_KODAS_CH
GROUP BY cast(I04_OP_DATA as date), I04_OP_RUSIS;

报错信息 #

Azure Synapse Error
mssql error: All your SQL functions need to have an alias on MSSQL. For example: SELECT COUNT(*) AS C1 FROM TABLE1
This may be triggered by:
Issue 1002 - The database returned an unexpected error.

原因分析 #

报错提示明确指出：Microsoft SQL Server要求所有经函数处理的列、聚合函数的结果必须指定别名。原查询中cast(I04_OP_DATA as date)（类型转换函数）和SUM(I05_SUMA)（聚合函数）均未设置别名，虽然数据库原生支持无别名查询，但Superset的SQL解析层会严格校验这一点，从而触发报错。

解决方案 #

为所有函数处理后的列添加别名即可解决问题，修改后的SQL如下：

SELECT 
    cast(I04_OP_DATA as date) AS OP_DATE, 
    I04_OP_RUSIS, 
    SUM(I05_SUMA) AS TOTAL_SUMA
FROM I04_ATH
LEFT JOIN I05_ATD 
ON I04_ATH.I04_KODAS_CH  = I05_ATD.I05_KODAS_CH
GROUP BY cast(I04_OP_DATA as date), I04_OP_RUSIS;

补充说明 #

如果使用MS SQL Server 2012及以上版本，GROUP BY子句也可以直接使用SELECT中定义的别名，简化后的写法如下：

SELECT 
    cast(I04_OP_DATA as date) AS OP_DATE, 
    I04_OP_RUSIS, 
    SUM(I05_SUMA) AS TOTAL_SUMA
FROM I04_ATH
LEFT JOIN I05_ATD 
ON I04_ATH.I04_KODAS_CH  = I05_ATD.I05_KODAS_CH
GROUP BY OP_DATE, I04_OP_RUSIS;

内容的提问来源于stack exchange，提问作者Lukas Kondrusevičius