| -- SORT_QUERY_RESULTS |
| |
| create table facts (val string); |
| |
| insert into facts values ('abcdefghijklmnopqrstuvwxyz0123456789'); |
| |
| set hive.vectorized.execution.enabled=false; |
| drop table groupingsets32; |
| drop table groupingsets33; |
| drop table groupingsets32a; |
| drop table groupingsets33a; |
| |
| create table groupingsets32 as |
| select |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| ,count(*) as n from ( |
| select |
| substring(val,01,1) as c00, substring(val,02,1) as c01, substring(val,03,1) as c02,substring(val,04,1) as c03,substring(val,05,1) as c04,substring(val,06,1) as c05,substring(val,07,1) as c06, substring(val,08,1) as c07,substring(val,09,1) as c08,substring(val,10,1) as c09, |
| substring(val,11,1) as c10, substring(val,12,1) as c11, substring(val,13,1) as c12,substring(val,14,1) as c13,substring(val,15,1) as c14,substring(val,16,1) as c15,substring(val,17,1) as c16, substring(val,18,1) as c17,substring(val,19,1) as c18,substring(val,20,1) as c19, |
| substring(val,21,1) as c20, substring(val,22,1) as c21, substring(val,23,1) as c22,substring(val,24,1) as c23,substring(val,25,1) as c24,substring(val,26,1) as c25,substring(val,27,1) as c26, substring(val,28,1) as c27,substring(val,29,1) as c28,substring(val,30,1) as c29, |
| substring(val,31,1) as c30,substring(val,32,1) as c31 |
| from facts ) x |
| group by |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| grouping sets ( |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| ); |
| |
| select * from groupingsets32; |
| |
| create table groupingsets32a as |
| select |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31, |
| count(*) as n, |
| grouping__id, |
| grouping(c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31) |
| from ( |
| select |
| substring(val,01,1) as c00, substring(val,02,1) as c01, substring(val,03,1) as c02,substring(val,04,1) as c03,substring(val,05,1) as c04,substring(val,06,1) as c05,substring(val,07,1) as c06, substring(val,08,1) as c07,substring(val,09,1) as c08,substring(val,10,1) as c09, |
| substring(val,11,1) as c10, substring(val,12,1) as c11, substring(val,13,1) as c12,substring(val,14,1) as c13,substring(val,15,1) as c14,substring(val,16,1) as c15,substring(val,17,1) as c16, substring(val,18,1) as c17,substring(val,19,1) as c18,substring(val,20,1) as c19, |
| substring(val,21,1) as c20, substring(val,22,1) as c21, substring(val,23,1) as c22,substring(val,24,1) as c23,substring(val,25,1) as c24,substring(val,26,1) as c25,substring(val,27,1) as c26, substring(val,28,1) as c27,substring(val,29,1) as c28,substring(val,30,1) as c29, |
| substring(val,31,1) as c30,substring(val,32,1) as c31 |
| from facts ) x |
| group by |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| grouping sets ( |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| ); |
| |
| select * from groupingsets32a; |
| |
| create table groupingsets33 as |
| select |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| ,count(*) as n from ( |
| select |
| substring(val,01,1) as c00, substring(val,02,1) as c01, substring(val,03,1) as c02,substring(val,04,1) as c03,substring(val,05,1) as c04,substring(val,06,1) as c05,substring(val,07,1) as c06, substring(val,08,1) as c07,substring(val,09,1) as c08,substring(val,10,1) as c09, |
| substring(val,11,1) as c10, substring(val,12,1) as c11, substring(val,13,1) as c12,substring(val,14,1) as c13,substring(val,15,1) as c14,substring(val,16,1) as c15,substring(val,17,1) as c16, substring(val,18,1) as c17,substring(val,19,1) as c18,substring(val,20,1) as c19, |
| substring(val,21,1) as c20, substring(val,22,1) as c21, substring(val,23,1) as c22,substring(val,24,1) as c23,substring(val,25,1) as c24,substring(val,26,1) as c25,substring(val,27,1) as c26, substring(val,28,1) as c27,substring(val,29,1) as c28,substring(val,30,1) as c29, |
| substring(val,31,1) as c30,substring(val,32,1) as c31,substring(val,33,1) as c32 |
| from facts ) x |
| group by |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| grouping sets ( |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| ) ; |
| |
| select * from groupingsets33; |
| |
| create table groupingsets33a as |
| select |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| ,count(*) as n, |
| grouping__id, |
| grouping(c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32) |
| from ( |
| select |
| substring(val,01,1) as c00, substring(val,02,1) as c01, substring(val,03,1) as c02,substring(val,04,1) as c03,substring(val,05,1) as c04,substring(val,06,1) as c05,substring(val,07,1) as c06, substring(val,08,1) as c07,substring(val,09,1) as c08,substring(val,10,1) as c09, |
| substring(val,11,1) as c10, substring(val,12,1) as c11, substring(val,13,1) as c12,substring(val,14,1) as c13,substring(val,15,1) as c14,substring(val,16,1) as c15,substring(val,17,1) as c16, substring(val,18,1) as c17,substring(val,19,1) as c18,substring(val,20,1) as c19, |
| substring(val,21,1) as c20, substring(val,22,1) as c21, substring(val,23,1) as c22,substring(val,24,1) as c23,substring(val,25,1) as c24,substring(val,26,1) as c25,substring(val,27,1) as c26, substring(val,28,1) as c27,substring(val,29,1) as c28,substring(val,30,1) as c29, |
| substring(val,31,1) as c30,substring(val,32,1) as c31,substring(val,33,1) as c32 |
| from facts ) x |
| group by |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| grouping sets ( |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| ) ; |
| |
| select * from groupingsets33a; |
| |
| set hive.vectorized.execution.enabled=true; |
| |
| drop table groupingsets32; |
| drop table groupingsets33; |
| |
| drop table groupingsets32; |
| drop table groupingsets33; |
| drop table groupingsets32a; |
| drop table groupingsets33a; |
| |
| create table groupingsets32 as |
| select |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| ,count(*) as n from ( |
| select |
| substring(val,01,1) as c00, substring(val,02,1) as c01, substring(val,03,1) as c02,substring(val,04,1) as c03,substring(val,05,1) as c04,substring(val,06,1) as c05,substring(val,07,1) as c06, substring(val,08,1) as c07,substring(val,09,1) as c08,substring(val,10,1) as c09, |
| substring(val,11,1) as c10, substring(val,12,1) as c11, substring(val,13,1) as c12,substring(val,14,1) as c13,substring(val,15,1) as c14,substring(val,16,1) as c15,substring(val,17,1) as c16, substring(val,18,1) as c17,substring(val,19,1) as c18,substring(val,20,1) as c19, |
| substring(val,21,1) as c20, substring(val,22,1) as c21, substring(val,23,1) as c22,substring(val,24,1) as c23,substring(val,25,1) as c24,substring(val,26,1) as c25,substring(val,27,1) as c26, substring(val,28,1) as c27,substring(val,29,1) as c28,substring(val,30,1) as c29, |
| substring(val,31,1) as c30,substring(val,32,1) as c31 |
| from facts ) x |
| group by |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| grouping sets ( |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| ); |
| |
| select * from groupingsets32; |
| |
| create table groupingsets32a as |
| select |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31, |
| count(*) as n, |
| grouping__id, |
| grouping(c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31) |
| from ( |
| select |
| substring(val,01,1) as c00, substring(val,02,1) as c01, substring(val,03,1) as c02,substring(val,04,1) as c03,substring(val,05,1) as c04,substring(val,06,1) as c05,substring(val,07,1) as c06, substring(val,08,1) as c07,substring(val,09,1) as c08,substring(val,10,1) as c09, |
| substring(val,11,1) as c10, substring(val,12,1) as c11, substring(val,13,1) as c12,substring(val,14,1) as c13,substring(val,15,1) as c14,substring(val,16,1) as c15,substring(val,17,1) as c16, substring(val,18,1) as c17,substring(val,19,1) as c18,substring(val,20,1) as c19, |
| substring(val,21,1) as c20, substring(val,22,1) as c21, substring(val,23,1) as c22,substring(val,24,1) as c23,substring(val,25,1) as c24,substring(val,26,1) as c25,substring(val,27,1) as c26, substring(val,28,1) as c27,substring(val,29,1) as c28,substring(val,30,1) as c29, |
| substring(val,31,1) as c30,substring(val,32,1) as c31 |
| from facts ) x |
| group by |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| grouping sets ( |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31 |
| ); |
| |
| select * from groupingsets32a; |
| |
| create table groupingsets33 as |
| select |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| ,count(*) as n from ( |
| select |
| substring(val,01,1) as c00, substring(val,02,1) as c01, substring(val,03,1) as c02,substring(val,04,1) as c03,substring(val,05,1) as c04,substring(val,06,1) as c05,substring(val,07,1) as c06, substring(val,08,1) as c07,substring(val,09,1) as c08,substring(val,10,1) as c09, |
| substring(val,11,1) as c10, substring(val,12,1) as c11, substring(val,13,1) as c12,substring(val,14,1) as c13,substring(val,15,1) as c14,substring(val,16,1) as c15,substring(val,17,1) as c16, substring(val,18,1) as c17,substring(val,19,1) as c18,substring(val,20,1) as c19, |
| substring(val,21,1) as c20, substring(val,22,1) as c21, substring(val,23,1) as c22,substring(val,24,1) as c23,substring(val,25,1) as c24,substring(val,26,1) as c25,substring(val,27,1) as c26, substring(val,28,1) as c27,substring(val,29,1) as c28,substring(val,30,1) as c29, |
| substring(val,31,1) as c30,substring(val,32,1) as c31,substring(val,33,1) as c32 |
| from facts ) x |
| group by |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| grouping sets ( |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| ) ; |
| |
| select * from groupingsets33; |
| |
| create table groupingsets33a as |
| select |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| ,count(*) as n, |
| grouping__id, |
| grouping(c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32) |
| from ( |
| select |
| substring(val,01,1) as c00, substring(val,02,1) as c01, substring(val,03,1) as c02,substring(val,04,1) as c03,substring(val,05,1) as c04,substring(val,06,1) as c05,substring(val,07,1) as c06, substring(val,08,1) as c07,substring(val,09,1) as c08,substring(val,10,1) as c09, |
| substring(val,11,1) as c10, substring(val,12,1) as c11, substring(val,13,1) as c12,substring(val,14,1) as c13,substring(val,15,1) as c14,substring(val,16,1) as c15,substring(val,17,1) as c16, substring(val,18,1) as c17,substring(val,19,1) as c18,substring(val,20,1) as c19, |
| substring(val,21,1) as c20, substring(val,22,1) as c21, substring(val,23,1) as c22,substring(val,24,1) as c23,substring(val,25,1) as c24,substring(val,26,1) as c25,substring(val,27,1) as c26, substring(val,28,1) as c27,substring(val,29,1) as c28,substring(val,30,1) as c29, |
| substring(val,31,1) as c30,substring(val,32,1) as c31,substring(val,33,1) as c32 |
| from facts ) x |
| group by |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| grouping sets ( |
| c00,c01,c02,c03,c04,c05,c06,c07,c08,c09, |
| c10,c11,c12,c13,c14,c15,c16,c17,c18,c19, |
| c20,c21,c22,c23,c24,c25,c26,c27,c28,c29, |
| c30,c31,c32 |
| ) ; |
| |
| select * from groupingsets33a; |