sql-tests/inputs/order-by-nulls-ordering.sql

0001 -- Test sort operator with codegen on and off.
0002 --CONFIG_DIM1 spark.sql.codegen.wholeStage=true
0003 --CONFIG_DIM1 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=CODEGEN_ONLY
0004 --CONFIG_DIM1 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=NO_CODEGEN
0005
0006 -- Q1. testing window functions with order by
0007 create table spark_10747(col1 int, col2 int, col3 int) using parquet;
0008
0009 -- Q2. insert to tables
0010 INSERT INTO spark_10747 VALUES (6, 12, 10), (6, 11, 4), (6, 9, 10), (6, 15, 8),
0011 (6, 15, 8), (6, 7, 4), (6, 7, 8), (6, 13, null), (6, 10, null);
0012
0013 -- Q3. windowing with order by DESC NULLS LAST
0014 select col1, col2, col3, sum(col2)
0015     over (partition by col1
0016        order by col3 desc nulls last, col2
0017        rows between 2 preceding and 2 following ) as sum_col2
0018 from spark_10747 where col1 = 6 order by sum_col2;
0019
0020 -- Q4. windowing with order by DESC NULLS FIRST
0021 select col1, col2, col3, sum(col2)
0022     over (partition by col1
0023        order by col3 desc nulls first, col2
0024        rows between 2 preceding and 2 following ) as sum_col2
0025 from spark_10747 where col1 = 6 order by sum_col2;
0026
0027 -- Q5. windowing with order by ASC NULLS LAST
0028 select col1, col2, col3, sum(col2)
0029     over (partition by col1
0030        order by col3 asc nulls last, col2
0031        rows between 2 preceding and 2 following ) as sum_col2
0032 from spark_10747 where col1 = 6 order by sum_col2;
0033
0034 -- Q6. windowing with order by ASC NULLS FIRST
0035 select col1, col2, col3, sum(col2)
0036     over (partition by col1
0037        order by col3 asc nulls first, col2
0038        rows between 2 preceding and 2 following ) as sum_col2
0039 from spark_10747 where col1 = 6 order by sum_col2;
0040
0041 -- Q7. Regular query with ORDER BY ASC NULLS FIRST
0042 SELECT COL1, COL2, COL3 FROM spark_10747 ORDER BY COL3 ASC NULLS FIRST, COL2;
0043
0044 -- Q8. Regular query with ORDER BY ASC NULLS LAST
0045 SELECT COL1, COL2, COL3 FROM spark_10747 ORDER BY COL3 NULLS LAST, COL2;
0046
0047 -- Q9. Regular query with ORDER BY DESC NULLS FIRST
0048 SELECT COL1, COL2, COL3 FROM spark_10747 ORDER BY COL3 DESC NULLS FIRST, COL2;
0049
0050 -- Q10. Regular query with ORDER BY DESC NULLS LAST
0051 SELECT COL1, COL2, COL3 FROM spark_10747 ORDER BY COL3 DESC NULLS LAST, COL2;
0052
0053 -- drop the test table
0054 drop table spark_10747;
0055
0056 -- Q11. mix datatype for ORDER BY NULLS FIRST|LAST
0057 create table spark_10747_mix(
0058 col1 string,
0059 col2 int,
0060 col3 double,
0061 col4 decimal(10,2),
0062 col5 decimal(20,1))
0063 using parquet;
0064
0065 -- Q12. Insert to the table
0066 INSERT INTO spark_10747_mix VALUES
0067 ('b', 2, 1.0, 1.00, 10.0),
0068 ('d', 3, 2.0, 3.00, 0.0),
0069 ('c', 3, 2.0, 2.00, 15.1),
0070 ('d', 3, 0.0, 3.00, 1.0),
0071 (null, 3, 0.0, 3.00, 1.0),
0072 ('d', 3, null, 4.00, 1.0),
0073 ('a', 1, 1.0, 1.00, null),
0074 ('c', 3, 2.0, 2.00, null);
0075
0076 -- Q13. Regular query with 2 NULLS LAST columns
0077 select * from spark_10747_mix order by col1 nulls last, col5 nulls last;
0078
0079 -- Q14. Regular query with 2 NULLS FIRST columns
0080 select * from spark_10747_mix order by col1 desc nulls first, col5 desc nulls first;
0081
0082 -- Q15. Regular query with mixed NULLS FIRST|LAST
0083 select * from spark_10747_mix order by col5 desc nulls first, col3 desc nulls last;
0084
0085 -- drop the test table
0086 drop table spark_10747_mix;
0087
0088