sql-tests/inputs/order-by-ordinal.sql

0001 -- order by and sort by ordinal positions
0002
0003 create temporary view data as select * from values
0004   (1, 1),
0005   (1, 2),
0006   (2, 1),
0007   (2, 2),
0008   (3, 1),
0009   (3, 2)
0010   as data(a, b);
0011
0012 select * from data order by 1 desc;
0013
0014 -- mix ordinal and column name
0015 select * from data order by 1 desc, b desc;
0016
0017 -- order by multiple ordinals
0018 select * from data order by 1 desc, 2 desc;
0019
0020 -- 1 + 0 is considered a constant (not an ordinal) and thus ignored
0021 select * from data order by 1 + 0 desc, b desc;
0022
0023 -- negative cases: ordinal position out of range
0024 select * from data order by 0;
0025 select * from data order by -1;
0026 select * from data order by 3;
0027
0028 -- sort by ordinal
0029 select * from data sort by 1 desc;
0030
0031 -- turn off order by ordinal
0032 set spark.sql.orderByOrdinal=false;
0033
0034 -- 0 is now a valid literal
0035 select * from data order by 0;
0036 select * from data sort by 0;