| # 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 |
