Got it, thanks Julian! Regards!
Aron Tao Julian Hyde <[email protected]> 于2020年12月9日周三 下午2:26写道： > GROUPING_ID is problematic for both optimizers and humans, because if the > columns are permuted the value changes, and that causes problems. I think > GROUPING is working well for our purposes. > > Of course you can use whichever you like in your queries. > > Julian > > > On Dec 8, 2020, at 19:00, JiaTao Tao <[email protected]> wrote: > > > > 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 <[email protected]> 于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 <[email protected]> > 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 > >> >
