南大通用GBase 8s_GROUP BY支持标量函数和表达式问题详解

Group By支持标量函数和表达式问题详解

1概述

为与主流数据库兼容，GBase 8s在很多国产化替代项目中做了很多努力，其中，Group By语句对标量函数及表达式的支持问题较为典型，这篇文章会详细阐述一下。

2问题现状

2.1现状描述

在某国产化替代项目现场出现如下场景：Group By子句需要支持to_char函数分组（包含不在投影列中的字段）。在这种场景下，GBase 8s会报错“SQL错误【ErrorCode：-201】【SQLState：42000】：A syntax error has occurred.”。如下所示：

源SQL：

select

count(distinct(t.col4)) as psr_num,

1 as defaulVal,

t.col2

from test t

where t.col1 > 10

group by t.col2,to_char(t.col5,'YYYY-MM-DD') ;

2.2问题分析

在某主流数据库中对Group By语句对标量函数及表达式支持情况进行测试分析，过程如下。

a.测试表table1结构:

col1col2col3col4col5col6

12312019-10-10 12:12:122019-10-11 12:12:12 b.测试过程：

⚫标量函数

➢测试SQL：

标量函数：

SQL1: select col2, to_char(col5,'YYYY-MM-DD

HH:MM:SS'),count(distinct(col4)) from table1 group by col2, to_char(col5,'YYYY-MM-DD HH:MM:SS');

SQL2: select abs(col2), count(distinct(col4)) from table1 group by abs(col2), col1;

SQL3: select to_char(col5,'YYYY') from table1 group by to_char(col5,'YYYY-MM');

SQL4: select to_char(col2) from table1 group by abs(col2);

SQL5: select col3+abs(col2), count(distinct(col4)) from table1 group by abs(col2), col3;

嵌套函数：

SQL6: select sum(avg(abs(col2))) from table1 group by nvl(col2,col3);

SQL7: select avg(sum(avg(abs(col2)))) from table1 group by nvl(col2,col3);

SQL8: select to_char(abs(abs(col2))) from table1 group by

to_char(abs(abs(col2)));

➢测试结果：

SQL1-2、SQL5语句执行成功，SQL3-4语句执行失败，说明标量函数在select 投影列中单独存在时需与group by语句中出现的标量函数保持一致，包括函数名称及参数。

SQL6、SQL8语句执行成功，SQL7语句执行失败，说明select投影列中聚合函数最多嵌套两层，而标量函数可无限嵌套，但投影列与group by语句中嵌套的标量函数必须保持一致。

⚫表达式

➢测试SQL：

算术表达式

SQL1: select col1+col2, count(distinct(col4)) from table1 group by col2+col1, col3;

SQL2: select 1+col2, count(distinct(col4)) from table1 group by col2+1, col3;

SQL3: select col2+1+col3, count(distinct(col4)) from table1 group by col2+1, col3;

SQL4: select col2+col3+1, count(distinct(col4)) from table1 group by col2+1, col3;

比较表达式：

SQL5: select count(col2) from table1 group by col2>1;

逻辑表达式：

SQL6: select count(col2) from table1 group by col2>1 and col2<10;

按位表达式：

SQL7: select count(col2) from table1 group by col2 | col3;

➢测试结果：

SQL1-3:主流数据库均支持。

SQL4-7:均不支持。

说明group by语句后的表达式仅支持算术表达式且遵循交换律，对比较表达式、逻辑表达式和按位表达式并不提供支持。

⚫标量函数+表达式

➢测试SQL：

SQL1: select 1+abs(col2), count(distinct(col4)) from table1 group by abs(col2)+1, col3;

SQL2: select 1+abs(col2)+col3, count(distinct(col4)) from table1 group by abs(col2)+1, col3;