[ https://issues.apache.org/jira/browse/SPARK-29699?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17002122#comment-17002122 ]
Takeshi Yamamuro commented on SPARK-29699: ------------------------------------------ A root cause is the NULL order difference in order-by clauses; in Spark, NULLS FIRST is the default for an ascending order, but in PostgreSQL/Oracle, NULLS LAST is the default for that. So, if we explicitly set the order in the example query above, we can get the same answer with PostgreSQL; {code:java} sql("""select a, b, sum(c), sum(sum(c)) over (order by a asc nulls last, b asc nulls last) as rsum from gstest2 group by rollup (a,b) order by rsum asc nulls last, a asc nulls last, b asc nulls last""").show() +----+----+------+----+ | a| b|sum(c)|rsum| +----+----+------+----+ | 1| 1| 8| 8| | 1| 2| 2| 10| | 1|null| 10| 20| | 2| 2| 2| 22| | 2|null| 2| 24| |null|null| 12| 36| +----+----+------+----+ {code} Currently, it seems we follow the MySQL/SQL Server behaviour and they have NULLS FIRST by default. Any historical reason for our default NULL order? cc: [~smilegator] [~cloud_fan] [~viirya] Changing the default behaivour in Spark has some impacts on test output in SQLQueryTestSuite: [https://github.com/apache/spark/compare/master...maropu:NullLastByDefault] References: Cited from the PostgreSQL doc: [https://www.postgresql.org/docs/current/queries-order.html] {code:java} By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise. {code} Cited from the OracleDB doc: [https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702] {code:java} NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order. {code} Cited from the SQL server: [https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15] {code:java} ASC is the default sort order. Null values are treated as the lowest possible values. {code} Cited from the MySQL: [https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html] {code:java} When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC. {code} > Different answers in nested aggregates with window functions > ------------------------------------------------------------ > > Key: SPARK-29699 > URL: https://issues.apache.org/jira/browse/SPARK-29699 > Project: Spark > Issue Type: Sub-task > Components: SQL > Affects Versions: 3.0.0 > Reporter: Takeshi Yamamuro > Priority: Major > Labels: correctness > > A nested aggregate below with a window function seems to have different > answers in the `rsum` column between PgSQL and Spark; > {code:java} > postgres=# create table gstest2 (a integer, b integer, c integer, d integer, > e integer, f integer, g integer, h integer); > postgres=# insert into gstest2 values > postgres-# (1, 1, 1, 1, 1, 1, 1, 1), > postgres-# (1, 1, 1, 1, 1, 1, 1, 2), > postgres-# (1, 1, 1, 1, 1, 1, 2, 2), > postgres-# (1, 1, 1, 1, 1, 2, 2, 2), > postgres-# (1, 1, 1, 1, 2, 2, 2, 2), > postgres-# (1, 1, 1, 2, 2, 2, 2, 2), > postgres-# (1, 1, 2, 2, 2, 2, 2, 2), > postgres-# (1, 2, 2, 2, 2, 2, 2, 2), > postgres-# (2, 2, 2, 2, 2, 2, 2, 2); > INSERT 0 9 > postgres=# > postgres=# select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum > postgres-# from gstest2 group by rollup (a,b) order by rsum, a, b; > a | b | sum | rsum > ---+---+-----+------ > 1 | 1 | 8 | 8 > 1 | 2 | 2 | 10 > 1 | | 10 | 20 > 2 | 2 | 2 | 22 > 2 | | 2 | 24 > | | 12 | 36 > (6 rows) > {code} > {code:java} > scala> sql(""" > | select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum > | from gstest2 group by rollup (a,b) order by rsum, a, b > | """).show() > +----+----+------+----+ > > | a| b|sum(c)|rsum| > +----+----+------+----+ > |null|null| 12| 12| > | 1|null| 10| 22| > | 1| 1| 8| 30| > | 1| 2| 2| 32| > | 2|null| 2| 34| > | 2| 2| 2| 36| > +----+----+------+----+ > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org