# IMPALA-4166: insert into tables with sort.columns property adds sort node. Clustering
# columns are added to the sort columns.
insert into table test_sort_by_zorder.t partition(year, month) /*+ shuffle */
select id, int_col, bool_col, year, month from functional.alltypes
---- PLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(year,month)]
|  partitions=24
|
01:SORT
|  order by: ZORDER: year, month, int_col, bool_col
|  row-size=17B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=17B cardinality=7.30K
---- DISTRIBUTEDPLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(year,month)]
|  partitions=24
|
02:SORT
|  order by: ZORDER: year, month, int_col, bool_col
|  row-size=17B cardinality=7.30K
|
01:EXCHANGE [HASH(`year`,`month`)]
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=17B cardinality=7.30K
====
# IMPALA-4166: insert with noshuffle hint into tables with sort.columns property adds
# sort node.
insert into table test_sort_by_zorder.t partition(year, month) /*+ noshuffle */
select id, int_col, bool_col, year, month from functional.alltypes
---- PLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(year,month)]
|  partitions=24
|
01:SORT
|  order by: ZORDER: year, month, int_col, bool_col
|  row-size=17B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=17B cardinality=7.30K
---- DISTRIBUTEDPLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(year,month)]
|  partitions=24
|
01:SORT
|  order by: ZORDER: year, month, int_col, bool_col
|  row-size=17B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=17B cardinality=7.30K
====
# IMPALA-4166: insert into tables with sort.columns property adds sort node. Clustering
# columns are added to the sort columns. noclustered hint is ignored when sort.columns
# are specified.
insert into table test_sort_by_zorder.t partition(year, month) /*+ noclustered */
select id, int_col, bool_col, year, month from functional.alltypes
---- PLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(year,month)]
|  partitions=24
|
01:SORT
|  order by: ZORDER: year, month, int_col, bool_col
|  row-size=17B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=17B cardinality=7.30K
---- DISTRIBUTEDPLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(year,month)]
|  partitions=24
|
02:SORT
|  order by: ZORDER: year, month, int_col, bool_col
|  row-size=17B cardinality=7.30K
|
01:EXCHANGE [HASH(`year`,`month`)]
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=17B cardinality=7.30K
====
# IMPALA-4166: insert into tables with sort.columns property adds sort node.
insert into table test_sort_by_zorder.t_nopart /*+ shuffle */
select id, int_col, bool_col from functional.alltypes
---- PLAN
WRITE TO HDFS [test_sort_by_zorder.t_nopart, OVERWRITE=false]
|  partitions=1
|
01:SORT
|  order by: ZORDER: int_col, bool_col
|  row-size=9B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=9B cardinality=7.30K
---- DISTRIBUTEDPLAN
WRITE TO HDFS [test_sort_by_zorder.t_nopart, OVERWRITE=false]
|  partitions=1
|
02:SORT
|  order by: ZORDER: int_col, bool_col
|  row-size=9B cardinality=7.30K
|
01:EXCHANGE [UNPARTITIONED]
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=9B cardinality=7.30K
====
# IMPALA-4166: insert with noshuffle hint into tables with sort.columns property adds
# sort node.
insert into table test_sort_by_zorder.t_nopart /*+ noshuffle */
select id, int_col, bool_col from functional.alltypes
---- PLAN
WRITE TO HDFS [test_sort_by_zorder.t_nopart, OVERWRITE=false]
|  partitions=1
|
01:SORT
|  order by: ZORDER: int_col, bool_col
|  row-size=9B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=9B cardinality=7.30K
---- DISTRIBUTEDPLAN
WRITE TO HDFS [test_sort_by_zorder.t_nopart, OVERWRITE=false]
|  partitions=1
|
01:SORT
|  order by: ZORDER: int_col, bool_col
|  row-size=9B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=9B cardinality=7.30K
====
# IMPALA-4166: sort columns are correct when using an identity column permutation.
insert into table test_sort_by_zorder.t_nopart (id, int_col, bool_col) /*+ shuffle */
select id, int_col, bool_col from functional.alltypes
---- PLAN
WRITE TO HDFS [test_sort_by_zorder.t_nopart, OVERWRITE=false]
|  partitions=1
|
01:SORT
|  order by: ZORDER: int_col, bool_col
|  row-size=9B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=9B cardinality=7.30K
---- DISTRIBUTEDPLAN
WRITE TO HDFS [test_sort_by_zorder.t_nopart, OVERWRITE=false]
|  partitions=1
|
02:SORT
|  order by: ZORDER: int_col, bool_col
|  row-size=9B cardinality=7.30K
|
01:EXCHANGE [UNPARTITIONED]
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=9B cardinality=7.30K
====
# IMPALA-4166: sort columns are correct when using a non-trivial column permutation.
insert into table test_sort_by_zorder.t_nopart (bool_col, id, int_col) /*+ shuffle */
select bool_col, id, int_col from functional.alltypes
---- PLAN
WRITE TO HDFS [test_sort_by_zorder.t_nopart, OVERWRITE=false]
|  partitions=1
|
01:SORT
|  order by: ZORDER: int_col, bool_col
|  row-size=9B cardinality=7.30K
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=9B cardinality=7.30K
---- DISTRIBUTEDPLAN
WRITE TO HDFS [test_sort_by_zorder.t_nopart, OVERWRITE=false]
|  partitions=1
|
02:SORT
|  order by: ZORDER: int_col, bool_col
|  row-size=9B cardinality=7.30K
|
01:EXCHANGE [UNPARTITIONED]
|
00:SCAN HDFS [functional.alltypes]
   HDFS partitions=24/24 files=24 size=478.45KB
   row-size=9B cardinality=7.30K
====
# IMPALA-4166: sort columns with a join
insert into table test_sort_by_zorder.t partition(year, month) /*+ noclustered */
select a.id, b.int_col, a.bool_col, b.year, a.month
from functional.alltypes a join functional.alltypes b on a.id = b.id order by b.string_col
limit 10
---- PLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(year,month)]
|  partitions=24
|
04:SORT
|  order by: ZORDER: year, month, int_col, bool_col
|  row-size=17B cardinality=10
|
03:TOP-N [LIMIT=10]
|  order by: string_col ASC
|  row-size=30B cardinality=10
|
02:HASH JOIN [INNER JOIN]
|  hash predicates: b.id = a.id
|  runtime filters: RF000 <- a.id
|  row-size=34B cardinality=7.30K
|
|--00:SCAN HDFS [functional.alltypes a]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     row-size=9B cardinality=7.30K
|
01:SCAN HDFS [functional.alltypes b]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> b.id
   row-size=25B cardinality=7.30K
---- DISTRIBUTEDPLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(year,month)]
|  partitions=24
|
07:SORT
|  order by: ZORDER: year, month, int_col, bool_col
|  row-size=17B cardinality=10
|
06:MERGING-EXCHANGE [UNPARTITIONED]
|  order by: string_col ASC
|  limit: 10
|
03:TOP-N [LIMIT=10]
|  order by: string_col ASC
|  row-size=30B cardinality=10
|
02:HASH JOIN [INNER JOIN, PARTITIONED]
|  hash predicates: b.id = a.id
|  runtime filters: RF000 <- a.id
|  row-size=34B cardinality=7.30K
|
|--05:EXCHANGE [HASH(a.id)]
|  |
|  00:SCAN HDFS [functional.alltypes a]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     row-size=9B cardinality=7.30K
|
04:EXCHANGE [HASH(b.id)]
|
01:SCAN HDFS [functional.alltypes b]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> b.id
   row-size=25B cardinality=7.30K
====
# IMPALA-4166: sort columns with a join and agg
insert into table test_sort_by_zorder.t partition(year, month) /*+ noclustered */
select a.id, max(b.int_col), min(a.bool_col), b.year, a.month
from functional.alltypes a join functional.alltypes b on a.id = b.id
group by a.id, b.year, a.month
---- PLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(b.`year`,a.`month`)]
|  partitions=24
|
04:SORT
|  order by: ZORDER: b.`year`, a.`month`, max(b.int_col), min(a.bool_col)
|  row-size=17B cardinality=7.30K
|
03:AGGREGATE [FINALIZE]
|  output: max(b.int_col), min(a.bool_col)
|  group by: a.id, b.`year`, a.`month`
|  row-size=17B cardinality=7.30K
|
02:HASH JOIN [INNER JOIN]
|  hash predicates: b.id = a.id
|  runtime filters: RF000 <- a.id
|  row-size=21B cardinality=7.30K
|
|--00:SCAN HDFS [functional.alltypes a]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     row-size=9B cardinality=7.30K
|
01:SCAN HDFS [functional.alltypes b]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> b.id
   row-size=12B cardinality=7.30K
---- DISTRIBUTEDPLAN
WRITE TO HDFS [test_sort_by_zorder.t, OVERWRITE=false, PARTITION-KEYS=(b.`year`,a.`month`)]
|  partitions=24
|
09:SORT
|  order by: ZORDER: b.`year`, a.`month`, max(b.int_col), min(a.bool_col)
|  row-size=17B cardinality=7.30K
|
08:EXCHANGE [HASH(b.`year`,a.`month`)]
|
07:AGGREGATE [FINALIZE]
|  output: max:merge(b.int_col), min:merge(a.bool_col)
|  group by: a.id, b.`year`, a.`month`
|  row-size=17B cardinality=7.30K
|
06:EXCHANGE [HASH(a.id,b.`year`,a.`month`)]
|
03:AGGREGATE [STREAMING]
|  output: max(b.int_col), min(a.bool_col)
|  group by: a.id, b.`year`, a.`month`
|  row-size=17B cardinality=7.30K
|
02:HASH JOIN [INNER JOIN, PARTITIONED]
|  hash predicates: b.id = a.id
|  runtime filters: RF000 <- a.id
|  row-size=21B cardinality=7.30K
|
|--05:EXCHANGE [HASH(a.id)]
|  |
|  00:SCAN HDFS [functional.alltypes a]
|     HDFS partitions=24/24 files=24 size=478.45KB
|     row-size=9B cardinality=7.30K
|
04:EXCHANGE [HASH(b.id)]
|
01:SCAN HDFS [functional.alltypes b]
   HDFS partitions=24/24 files=24 size=478.45KB
   runtime filters: RF000 -> b.id
   row-size=12B cardinality=7.30K