Hi Julian I see, thanks, maybe use grouping id is better? Cuz seems not every engine has this grouping behavior, in the doc of oracle[ref1]:
The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. ref1: https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions064.htm#SQLRF00647 Regards! Aron Tao Julian Hyde <jh...@apache.org> 于2020年12月9日周三 上午9:28写道： > GROUPING is defined in the SQL standard. If it has N arguments, it > returns an integer bitmask with N bits. > > PostgreSQL has the same behavior: see example in > https://www.postgresql.org/docs/9.5/functions-aggregate.html. > > Julian > > On Tue, Dec 8, 2020 at 12:30 AM JiaTao Tao <taojia...@gmail.com> wrote: > > > > Hi > > After AggregateExpandDistinctAggregatesRule, I got a plan like this: > > The $10 in the project node is $g=[GROUPING($0, $1, $2, $3, $4, $5, $6, > > $7, $8)]) and we can see it is compared with value 1/2/3, but I check the > > def of grouping(), both pg and oracle, the value of grouping is 0 or 1. > > > > pg:https://www.postgresqltutorial.com/postgresql-grouping-sets/ > > oracle: > > > https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions064.htm#SQLRF00647 > > > > ``` > > EnumerableProject(xx=[$0], xx=[$1], xx=[$2], xx=[$3], xx=[$4], xx=[$5], > > xx=[$6], $f7=[$7], $f8=[$8], gid=[$9], $g_1=[=($10, 1)], $g_2=[=($10, > 2)], > > $g_3=[=($10, 3)]) > > EnumerableHashAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8}], > > groups=[[{0, 1, 2, 3, 4, 5, 6, 7}, {0, 1, 2, 3, 4, 5, 6, 8}, {0, 1, 2, 3, > > 4, 5, 6}]], dim_type=[grouping_id()], $g=[GROUPING($0, $1, $2, $3, $4, > $5, > > $6, $7, $8)]) > > ``` > > > > > > Regards! > > > > Aron Tao >
