http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q71.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q71.test b/testdata/workloads/tpcds/queries/tpcds-q71.test new file mode 100644 index 0000000..6aa374e --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q71.test @@ -0,0 +1,1061 @@ +==== +---- QUERY: TPCDS-Q71 +select i_brand_id brand_id, i_brand brand,t_hour,t_minute, + sum(ext_price) ext_price + from item, (select ws_ext_sales_price as ext_price, + ws_sold_date_sk as sold_date_sk, + ws_item_sk as sold_item_sk, + ws_sold_time_sk as time_sk + from web_sales,date_dim + where d_date_sk = ws_sold_date_sk + and d_moy=11 + and d_year=1999 + union all + select cs_ext_sales_price as ext_price, + cs_sold_date_sk as sold_date_sk, + cs_item_sk as sold_item_sk, + cs_sold_time_sk as time_sk + from catalog_sales,date_dim + where d_date_sk = cs_sold_date_sk + and d_moy=11 + and d_year=1999 + union all + select ss_ext_sales_price as ext_price, + ss_sold_date_sk as sold_date_sk, + ss_item_sk as sold_item_sk, + ss_sold_time_sk as time_sk + from store_sales,date_dim + where d_date_sk = ss_sold_date_sk + and d_moy=11 + and d_year=1999 + ) tmp,time_dim + where + sold_item_sk = i_item_sk + and i_manager_id=1 + and time_sk = t_time_sk + and (t_meal_time = 'breakfast' or t_meal_time = 'dinner') + group by i_brand, i_brand_id,t_hour,t_minute + order by ext_price desc, i_brand_id + ; +---- RESULTS +2002001,'importoimporto #1',9,58,NULL +2003002,'exportiimporto #2',9,27,NULL +2004002,'edu packimporto #2',9,7,NULL +5003002,'exportischolar #2',9,20,NULL +5004002,'edu packscholar #2',19,31,20021.76 +7012010,'importonameless #10',7,2,17142.78 +4004001,'edu packedu pack #1',9,23,16687.92 +3003001,'exportiexporti #1',19,37,16531.50 +6012008,'importobrand #8',19,41,16055.65 +2001002,'amalgimporto #2',17,32,15801.28 +3001001,'amalgexporti #1',17,25,15765.36 +9014011,'edu packunivamalg #11',18,32,15347.28 +4003001,'exportiedu pack #1',8,34,14784.50 +1002002,'importoamalg #2',8,13,13246.16 +10004001,'edu packunivamalg #1',19,56,13160.28 +2002001,'importoimporto #1',17,24,12932.04 +9008002,'namelessmaxi #2',19,23,12759.38 +6002003,'importocorp #3',19,52,12649.84 +1001002,'amalgamalg #2',19,15,12395.76 +6005001,'scholarcorp #1',6,58,12395.37 +6008005,'namelesscorp #5',17,47,12252.44 +5001001,'amalgscholar #1',9,28,12171.40 +5003001,'exportischolar #1',17,45,12101.22 +7010004,'univnameless #4',17,51,12082.56 +6016003,'corpbrand #3',8,6,11902.80 +5003001,'exportischolar #1',19,35,11523.60 +6002005,'importocorp #5',7,54,11026.50 +6005001,'scholarcorp #1',17,13,10941.60 +3002001,'importoexporti #1',19,43,10914.60 +6005001,'scholarcorp #1',7,39,10787.84 +8013009,'exportimaxi #9',19,26,10683.66 +1001001,'amalgamalg #1',17,48,10597.73 +8005005,'scholarnameless #5',19,51,10488.40 +7015007,'scholarnameless #7',17,8,10338.06 +3003001,'exportiexporti #1',19,24,10307.88 +2003002,'exportiimporto #2',6,53,10190.88 +7012010,'importonameless #10',18,40,10119.62 +1003002,'exportiamalg #2',8,58,10023.00 +4001001,'amalgedu pack #1',19,59,9881.19 +10003004,'exportiunivamalg #4',17,27,9719.37 +7014001,'edu packnameless #1',17,56,9606.96 +9016003,'corpunivamalg #3',9,42,9576.26 +6016003,'corpbrand #3',8,35,9497.90 +7013007,'exportinameless #7',18,20,9426.12 +5001001,'amalgscholar #1',17,44,9369.40 +7016001,'corpnameless #1',9,8,9319.91 +1004002,'edu packamalg #2',19,26,9298.01 +7015007,'scholarnameless #7',18,47,9254.90 +3003001,'exportiexporti #1',17,21,9190.80 +2002001,'importoimporto #1',6,34,9043.24 +10015004,'scholaramalgamalg #4',17,46,9033.72 +5003002,'exportischolar #2',8,54,9007.28 +7012010,'importonameless #10',19,42,8888.32 +10013015,'exportiamalgamalg #15',9,18,8828.12 +4004001,'edu packedu pack #1',18,50,8728.56 +3002001,'importoexporti #1',19,4,8664.04 +7001005,'amalgbrand #5',8,59,8656.83 +2003002,'exportiimporto #2',9,31,8513.40 +3002001,'importoexporti #1',19,45,8497.28 +1001001,'amalgamalg #1',19,3,8431.69 +9016003,'corpunivamalg #3',18,8,8380.89 +10002012,'importounivamalg #12',17,1,8360.00 +8016004,'corpmaxi #4',17,34,8241.06 +2004002,'edu packimporto #2',19,14,8165.73 +2001002,'amalgimporto #2',17,34,8160.75 +5002001,'importoscholar #1',9,22,8143.74 +2003001,'exportiimporto #1',9,59,8113.05 +6007003,'brandcorp #3',6,9,8053.44 +10015004,'scholaramalgamalg #4',17,58,7966.40 +5002001,'importoscholar #1',19,59,7964.88 +2003001,'exportiimporto #1',9,17,7900.62 +8002009,'importonameless #9',18,28,7891.65 +7013007,'exportinameless #7',19,49,7855.88 +3003001,'exportiexporti #1',19,49,7845.76 +3001001,'amalgexporti #1',7,26,7840.00 +3004001,'edu packexporti #1',18,0,7698.24 +6011005,'amalgbrand #5',17,13,7649.00 +8013007,'exportimaxi #7',18,32,7629.60 +2003002,'exportiimporto #2',18,26,7621.35 +1003002,'exportiamalg #2',18,35,7620.51 +2004001,'edu packimporto #1',17,3,7568.00 +3001001,'amalgexporti #1',19,1,7502.67 +7010009,'univnameless #9',9,5,7459.82 +3002001,'importoexporti #1',19,3,7400.40 +5002002,'importoscholar #2',19,19,7349.90 +1003002,'exportiamalg #2',9,41,7345.80 +6002005,'importocorp #5',19,43,7341.75 +2003001,'exportiimporto #1',8,42,7326.20 +6012008,'importobrand #8',9,53,7286.37 +10003016,'exportiunivamalg #16',19,43,7272.65 +3003002,'exportiexporti #2',7,31,7195.50 +8016004,'corpmaxi #4',18,55,7186.20 +6011001,'amalgbrand #1',9,18,7129.56 +6005001,'scholarcorp #1',9,44,7129.00 +5003001,'exportischolar #1',17,28,7029.12 +3002001,'importoexporti #1',9,59,6964.25 +6008007,'namelesscorp #7',18,47,6953.36 +7001005,'amalgbrand #5',8,12,6940.96 +1002002,'importoamalg #2',18,51,6924.69 +5001001,'amalgscholar #1',19,57,6841.12 +6015006,'scholarbrand #6',18,23,6755.76 +5002002,'importoscholar #2',19,31,6745.90 +2004002,'edu packimporto #2',17,2,6744.98 +5003001,'exportischolar #1',9,24,6694.80 +10002012,'importounivamalg #12',18,16,6634.95 +5004001,'edu packscholar #1',8,19,6527.50 +5003001,'exportischolar #1',9,53,6525.33 +1004002,'edu packamalg #2',17,32,6524.70 +1002001,'importoamalg #1',17,22,6511.08 +3002002,'importoexporti #2',19,13,6487.20 +10002012,'importounivamalg #12',18,3,6471.90 +3003001,'exportiexporti #1',17,9,6450.60 +4004002,'edu packedu pack #2',17,3,6447.42 +1002001,'importoamalg #1',6,6,6334.24 +10009015,'maxiunivamalg #15',8,32,6322.29 +10012004,'importoamalgamalg #4',6,49,6234.24 +10012011,'importoamalgamalg #11',19,27,6218.47 +9008002,'namelessmaxi #2',17,41,6207.45 +3002001,'importoexporti #1',18,3,6203.20 +7004007,'edu packbrand #7',9,23,6173.10 +1004002,'edu packamalg #2',17,2,6118.65 +5001002,'amalgscholar #2',18,15,6118.20 +6012005,'importobrand #5',17,30,6063.57 +1003002,'exportiamalg #2',18,52,6013.26 +4003001,'exportiedu pack #1',9,54,5994.50 +3003001,'exportiexporti #1',19,38,5990.76 +7010003,'univnameless #3',19,4,5952.32 +3002001,'importoexporti #1',18,50,5950.48 +9012003,'importounivamalg #3',8,30,5922.09 +1004001,'edu packamalg #1',17,57,5860.74 +8013009,'exportimaxi #9',9,53,5808.00 +10010013,'univamalgamalg #13',7,11,5799.04 +10003004,'exportiunivamalg #4',18,18,5692.22 +8002009,'importonameless #9',19,33,5633.00 +1003001,'exportiamalg #1',7,57,5617.50 +10010013,'univamalgamalg #13',17,7,5590.62 +5003001,'exportischolar #1',7,44,5589.90 +6006007,'corpcorp #7',6,1,5578.24 +9008002,'namelessmaxi #2',17,33,5547.78 +10003016,'exportiunivamalg #16',17,55,5519.20 +5002001,'importoscholar #1',18,2,5471.54 +10012017,'importoamalgamalg #17',18,54,5415.90 +10012011,'importoamalgamalg #11',18,17,5384.79 +1004002,'edu packamalg #2',9,53,5382.72 +7016001,'corpnameless #1',17,32,5382.09 +9008005,'namelessmaxi #5',6,9,5382.09 +2001001,'amalgimporto #1',6,40,5295.40 +3002001,'importoexporti #1',6,40,5245.80 +1002002,'importoamalg #2',18,15,5240.76 +3004001,'edu packexporti #1',9,36,5218.56 +5003001,'exportischolar #1',17,14,5214.72 +3003002,'exportiexporti #2',19,5,5201.25 +2004002,'edu packimporto #2',9,41,5165.58 +9013009,'exportiunivamalg #9',7,31,5155.29 +2003001,'exportiimporto #1',18,28,5107.41 +3003001,'exportiexporti #1',17,56,5104.62 +6015006,'scholarbrand #6',19,58,5080.60 +3003001,'exportiexporti #1',8,32,5008.64 +10004001,'edu packunivamalg #1',9,20,5002.40 +8013007,'exportimaxi #7',19,3,4989.95 +8016004,'corpmaxi #4',19,8,4979.25 +6003008,'exporticorp #8',18,38,4955.40 +3002002,'importoexporti #2',8,51,4938.16 +8014005,'edu packmaxi #5',19,38,4893.48 +5003001,'exportischolar #1',18,1,4889.22 +6003008,'exporticorp #8',9,18,4794.00 +5002001,'importoscholar #1',19,9,4787.43 +4003001,'exportiedu pack #1',9,51,4781.14 +9015009,'scholarunivamalg #9',8,52,4774.34 +5002002,'importoscholar #2',6,3,4774.25 +1001002,'amalgamalg #2',19,31,4758.75 +5002002,'importoscholar #2',19,15,4754.40 +2003001,'exportiimporto #1',9,32,4723.95 +3003001,'exportiexporti #1',19,40,4711.07 +10003004,'exportiunivamalg #4',19,43,4701.06 +2001002,'amalgimporto #2',19,9,4681.05 +7014001,'edu packnameless #1',6,35,4646.40 +5001001,'amalgscholar #1',17,56,4638.84 +6010005,'univbrand #5',19,23,4606.83 +1004002,'edu packamalg #2',7,0,4599.14 +6016003,'corpbrand #3',17,33,4592.04 +4001001,'amalgedu pack #1',17,8,4586.22 +9012005,'importounivamalg #5',19,59,4581.56 +3002001,'importoexporti #1',17,5,4561.20 +10012011,'importoamalgamalg #11',18,29,4531.45 +6008005,'namelesscorp #5',18,27,4498.44 +1003001,'exportiamalg #1',19,4,4483.98 +5003001,'exportischolar #1',18,31,4452.80 +3002002,'importoexporti #2',18,52,4436.80 +1003002,'exportiamalg #2',9,38,4428.98 +1004002,'edu packamalg #2',9,55,4427.52 +5003001,'exportischolar #1',19,2,4412.78 +1003002,'exportiamalg #2',18,53,4374.72 +6005001,'scholarcorp #1',17,55,4367.72 +9012008,'importounivamalg #8',18,11,4354.35 +5002001,'importoscholar #1',18,32,4324.52 +7013007,'exportinameless #7',18,47,4323.55 +9013009,'exportiunivamalg #9',17,42,4321.92 +4004001,'edu packedu pack #1',8,22,4316.62 +8004003,'edu packnameless #3',17,23,4282.17 +2004002,'edu packimporto #2',7,56,4267.90 +1003002,'exportiamalg #2',9,49,4255.80 +7013007,'exportinameless #7',19,42,4252.78 +9015011,'scholarunivamalg #11',8,55,4235.00 +1002002,'importoamalg #2',17,0,4183.34 +6010005,'univbrand #5',9,27,4172.35 +6007003,'brandcorp #3',19,33,4146.75 +2004001,'edu packimporto #1',19,33,4138.56 +6005001,'scholarcorp #1',19,55,4137.88 +6016003,'corpbrand #3',9,41,4100.88 +9014011,'edu packunivamalg #11',17,50,4100.40 +5003001,'exportischolar #1',18,17,4099.20 +5003001,'exportischolar #1',9,28,4064.76 +1004002,'edu packamalg #2',17,16,4061.68 +10012017,'importoamalgamalg #17',8,53,4045.00 +4004001,'edu packedu pack #1',17,15,4028.96 +6002005,'importocorp #5',17,22,4011.30 +4003001,'exportiedu pack #1',9,42,3952.59 +7004007,'edu packbrand #7',7,19,3943.82 +10002012,'importounivamalg #12',19,19,3925.48 +10009015,'maxiunivamalg #15',7,51,3912.99 +4001001,'amalgedu pack #1',17,55,3890.08 +10002012,'importounivamalg #12',18,10,3870.00 +10004001,'edu packunivamalg #1',18,13,3864.00 +5004002,'edu packscholar #2',18,50,3863.88 +6008002,'namelesscorp #2',18,38,3841.04 +1001001,'amalgamalg #1',19,44,3818.40 +10002012,'importounivamalg #12',17,51,3766.14 +2004001,'edu packimporto #1',18,4,3757.34 +10003004,'exportiunivamalg #4',7,34,3756.20 +9012008,'importounivamalg #8',18,56,3735.84 +5001001,'amalgscholar #1',19,12,3735.76 +7015007,'scholarnameless #7',17,55,3734.28 +2001002,'amalgimporto #2',8,51,3725.90 +5002002,'importoscholar #2',17,38,3709.44 +7016007,'corpnameless #7',19,53,3701.95 +8002009,'importonameless #9',9,18,3686.90 +8014005,'edu packmaxi #5',18,43,3649.00 +9008002,'namelessmaxi #2',17,52,3635.28 +5001001,'amalgscholar #1',19,19,3617.97 +4003001,'exportiedu pack #1',17,43,3598.98 +6009003,'maxicorp #3',17,6,3596.58 +7012003,'importonameless #3',9,29,3573.75 +9012003,'importounivamalg #3',17,2,3568.96 +1001002,'amalgamalg #2',19,59,3542.94 +1001002,'amalgamalg #2',18,37,3522.98 +8011009,'amalgmaxi #9',19,35,3485.72 +1003002,'exportiamalg #2',19,58,3423.80 +1001002,'amalgamalg #2',18,25,3408.48 +4004002,'edu packedu pack #2',17,2,3400.05 +6008007,'namelesscorp #7',18,16,3395.62 +5002002,'importoscholar #2',6,23,3383.10 +1002002,'importoamalg #2',9,48,3374.00 +4003001,'exportiedu pack #1',8,33,3358.72 +5001001,'amalgscholar #1',18,27,3326.04 +3002002,'importoexporti #2',17,18,3304.55 +7010004,'univnameless #4',18,7,3301.28 +9016003,'corpunivamalg #3',8,42,3279.18 +6016003,'corpbrand #3',9,9,3262.08 +7015007,'scholarnameless #7',9,48,3249.72 +8004003,'edu packnameless #3',18,44,3232.48 +5003002,'exportischolar #2',9,47,3222.66 +1002002,'importoamalg #2',8,37,3220.80 +3004001,'edu packexporti #1',7,56,3218.22 +3001001,'amalgexporti #1',17,10,3217.50 +7010009,'univnameless #9',18,26,3215.11 +1002001,'importoamalg #1',17,46,3195.24 +2004002,'edu packimporto #2',18,46,3194.31 +5001001,'amalgscholar #1',17,54,3180.02 +7008009,'namelessbrand #9',17,52,3146.96 +2004002,'edu packimporto #2',17,46,3144.40 +3004001,'edu packexporti #1',19,14,3127.20 +7010004,'univnameless #4',9,16,3126.90 +3001001,'amalgexporti #1',8,38,3125.32 +1003001,'exportiamalg #1',19,10,3110.49 +6012008,'importobrand #8',19,13,3101.32 +1003002,'exportiamalg #2',8,56,3097.12 +6005001,'scholarcorp #1',8,14,3096.90 +6016003,'corpbrand #3',19,31,3094.50 +7010004,'univnameless #4',19,4,3093.35 +10002012,'importounivamalg #12',18,0,3063.20 +2004001,'edu packimporto #1',19,4,3051.40 +8014005,'edu packmaxi #5',17,40,3049.76 +1001001,'amalgamalg #1',18,49,3045.12 +3004001,'edu packexporti #1',7,50,3029.00 +10012017,'importoamalgamalg #17',18,26,3027.72 +1003001,'exportiamalg #1',8,35,3007.26 +8005005,'scholarnameless #5',6,50,2972.97 +1003002,'exportiamalg #2',8,25,2957.35 +4001001,'amalgedu pack #1',9,12,2930.95 +1002002,'importoamalg #2',9,9,2926.95 +6008005,'namelesscorp #5',9,1,2902.58 +3002002,'importoexporti #2',17,15,2902.20 +2001002,'amalgimporto #2',19,47,2894.46 +1003001,'exportiamalg #1',9,58,2871.44 +9012005,'importounivamalg #5',18,34,2864.88 +3003002,'exportiexporti #2',18,8,2844.00 +10013013,'exportiamalgamalg #13',8,0,2831.12 +5002001,'importoscholar #1',17,38,2822.47 +7016007,'corpnameless #7',17,56,2817.48 +9015011,'scholarunivamalg #11',8,2,2811.48 +1001001,'amalgamalg #1',9,18,2793.44 +4004001,'edu packedu pack #1',9,54,2787.20 +1003001,'exportiamalg #1',7,23,2784.84 +1003002,'exportiamalg #2',18,21,2783.00 +1003001,'exportiamalg #1',17,37,2779.50 +2004002,'edu packimporto #2',19,26,2776.36 +6009003,'maxicorp #3',19,38,2763.09 +1003001,'exportiamalg #1',17,41,2751.84 +2001002,'amalgimporto #2',18,24,2749.95 +10015013,'scholaramalgamalg #13',8,25,2746.86 +3002001,'importoexporti #1',17,13,2741.54 +4001001,'amalgedu pack #1',9,3,2738.34 +9012003,'importounivamalg #3',9,18,2732.40 +5001001,'amalgscholar #1',17,43,2731.37 +2001002,'amalgimporto #2',9,36,2715.18 +3003001,'exportiexporti #1',19,25,2713.30 +5001001,'amalgscholar #1',17,52,2707.54 +3002001,'importoexporti #1',18,20,2694.50 +6008002,'namelesscorp #2',18,48,2683.20 +1003002,'exportiamalg #2',17,59,2670.50 +1002002,'importoamalg #2',9,36,2668.77 +1003001,'exportiamalg #1',9,26,2633.15 +4003001,'exportiedu pack #1',9,38,2631.20 +9015011,'scholarunivamalg #11',18,16,2621.71 +7010009,'univnameless #9',18,48,2609.88 +8005005,'scholarnameless #5',19,55,2606.45 +10012011,'importoamalgamalg #11',17,9,2589.95 +1002001,'importoamalg #1',19,21,2579.48 +4002001,'importoedu pack #1',18,51,2567.40 +5003001,'exportischolar #1',8,50,2555.28 +1004002,'edu packamalg #2',18,24,2551.16 +2003001,'exportiimporto #1',19,58,2528.40 +6005001,'scholarcorp #1',17,45,2522.52 +6007003,'brandcorp #3',17,49,2522.15 +1002001,'importoamalg #1',19,14,2513.30 +10010013,'univamalgamalg #13',8,30,2513.25 +4001001,'amalgedu pack #1',19,9,2512.00 +7010004,'univnameless #4',17,44,2509.78 +8003010,'exportinameless #10',18,27,2507.44 +6011008,'amalgbrand #8',18,5,2505.00 +7010004,'univnameless #4',8,21,2502.36 +6012008,'importobrand #8',8,33,2495.65 +8004003,'edu packnameless #3',18,52,2495.22 +1001002,'amalgamalg #2',18,11,2491.16 +7010003,'univnameless #3',7,5,2485.08 +4004002,'edu packedu pack #2',18,23,2483.01 +8016004,'corpmaxi #4',18,43,2482.62 +10004001,'edu packunivamalg #1',8,39,2472.93 +2003002,'exportiimporto #2',9,41,2471.90 +6011001,'amalgbrand #1',7,21,2458.17 +8011009,'amalgmaxi #9',19,5,2457.99 +6002005,'importocorp #5',19,0,2454.03 +1002002,'importoamalg #2',19,57,2451.06 +5001002,'amalgscholar #2',19,42,2444.80 +9014011,'edu packunivamalg #11',17,44,2436.40 +10003004,'exportiunivamalg #4',19,46,2423.18 +6016003,'corpbrand #3',19,19,2421.65 +8004009,'edu packnameless #9',19,44,2421.51 +2002001,'importoimporto #1',8,8,2420.34 +6012005,'importobrand #5',7,20,2412.30 +3003001,'exportiexporti #1',7,29,2408.70 +3002001,'importoexporti #1',18,21,2403.96 +3002002,'importoexporti #2',8,58,2399.13 +2001002,'amalgimporto #2',17,12,2396.61 +5003002,'exportischolar #2',17,37,2388.24 +4004001,'edu packedu pack #1',17,5,2386.20 +5003002,'exportischolar #2',9,29,2378.69 +3004001,'edu packexporti #1',17,46,2375.45 +9014011,'edu packunivamalg #11',19,18,2363.04 +1003001,'exportiamalg #1',8,5,2360.81 +1003001,'exportiamalg #1',19,19,2355.12 +6002005,'importocorp #5',8,27,2351.04 +2002001,'importoimporto #1',19,47,2338.56 +6007003,'brandcorp #3',9,4,2335.10 +8004003,'edu packnameless #3',9,44,2314.40 +5003001,'exportischolar #1',19,58,2294.62 +8002009,'importonameless #9',18,12,2266.60 +3004001,'edu packexporti #1',19,9,2261.52 +7010003,'univnameless #3',18,35,2234.40 +4004001,'edu packedu pack #1',9,25,2215.21 +3003001,'exportiexporti #1',8,9,2206.47 +1003002,'exportiamalg #2',17,41,2197.60 +6008007,'namelesscorp #7',18,0,2190.24 +3002001,'importoexporti #1',17,51,2181.96 +8013007,'exportimaxi #7',7,11,2180.76 +6005001,'scholarcorp #1',18,43,2174.48 +6006007,'corpcorp #7',7,24,2158.40 +6002003,'importocorp #3',19,26,2152.17 +1001001,'amalgamalg #1',19,53,2149.70 +5003001,'exportischolar #1',8,59,2144.73 +6005001,'scholarcorp #1',9,25,2143.10 +7008009,'namelessbrand #9',19,39,2132.41 +3003001,'exportiexporti #1',19,53,2118.88 +9015009,'scholarunivamalg #9',19,3,2114.64 +10013013,'exportiamalgamalg #13',7,33,2103.93 +8003010,'exportinameless #10',17,28,2084.72 +8003010,'exportinameless #10',17,42,2082.86 +8002009,'importonameless #9',9,37,2079.84 +4001001,'amalgedu pack #1',19,43,2067.52 +5004002,'edu packscholar #2',19,2,2061.30 +5004002,'edu packscholar #2',18,7,2049.67 +10004001,'edu packunivamalg #1',18,49,2031.20 +1003001,'exportiamalg #1',18,21,2028.31 +4001002,'amalgedu pack #2',19,19,2018.40 +5002002,'importoscholar #2',19,18,2016.96 +4001002,'amalgedu pack #2',6,3,2012.80 +6011005,'amalgbrand #5',9,53,1997.52 +6011008,'amalgbrand #8',8,28,1995.60 +6007003,'brandcorp #3',17,33,1995.20 +3003001,'exportiexporti #1',9,22,1982.73 +5002002,'importoscholar #2',17,49,1980.88 +2001002,'amalgimporto #2',17,27,1955.20 +7007004,'brandbrand #4',9,31,1954.11 +2001002,'amalgimporto #2',17,11,1951.95 +2004001,'edu packimporto #1',19,50,1934.46 +4004001,'edu packedu pack #1',9,11,1930.50 +7010003,'univnameless #3',19,20,1926.24 +1004002,'edu packamalg #2',8,13,1922.68 +9013009,'exportiunivamalg #9',19,55,1906.92 +8004003,'edu packnameless #3',19,58,1905.78 +6008007,'namelesscorp #7',17,2,1905.12 +1002001,'importoamalg #1',9,28,1904.10 +1002002,'importoamalg #2',9,42,1893.54 +4001002,'amalgedu pack #2',17,55,1880.97 +1004001,'edu packamalg #1',19,42,1878.76 +1001001,'amalgamalg #1',17,19,1877.46 +2003002,'exportiimporto #2',18,9,1874.40 +9012008,'importounivamalg #8',18,22,1873.02 +1002002,'importoamalg #2',18,42,1852.20 +6002003,'importocorp #3',17,39,1840.98 +5002002,'importoscholar #2',18,14,1817.00 +1001001,'amalgamalg #1',17,59,1813.11 +7012003,'importonameless #3',18,18,1807.85 +7016001,'corpnameless #1',19,5,1806.68 +8005005,'scholarnameless #5',17,31,1800.48 +5002001,'importoscholar #1',6,7,1789.20 +9015009,'scholarunivamalg #9',6,44,1788.93 +3002001,'importoexporti #1',8,52,1770.00 +3002002,'importoexporti #2',6,41,1760.58 +1001001,'amalgamalg #1',9,14,1745.24 +1004002,'edu packamalg #2',18,32,1742.52 +9013009,'exportiunivamalg #9',9,36,1737.57 +10015004,'scholaramalgamalg #4',9,2,1736.72 +7010003,'univnameless #3',7,58,1735.12 +6016003,'corpbrand #3',8,54,1733.08 +7008009,'namelessbrand #9',19,27,1726.56 +5002001,'importoscholar #1',8,24,1725.50 +9013009,'exportiunivamalg #9',18,43,1722.16 +2002001,'importoimporto #1',9,43,1715.64 +5003001,'exportischolar #1',17,32,1714.80 +5001001,'amalgscholar #1',18,35,1713.96 +7004007,'edu packbrand #7',17,57,1713.28 +4001001,'amalgedu pack #1',19,13,1708.26 +6007003,'brandcorp #3',17,51,1707.48 +4001002,'amalgedu pack #2',19,51,1706.46 +1003001,'exportiamalg #1',18,3,1704.75 +10010013,'univamalgamalg #13',18,51,1677.20 +2004002,'edu packimporto #2',17,59,1676.84 +2001002,'amalgimporto #2',18,21,1659.84 +5001002,'amalgscholar #2',18,35,1654.44 +2002001,'importoimporto #1',17,4,1649.28 +5004001,'edu packscholar #1',6,36,1646.50 +3002001,'importoexporti #1',17,14,1644.96 +4004001,'edu packedu pack #1',17,18,1644.00 +4001001,'amalgedu pack #1',17,28,1643.76 +5001001,'amalgscholar #1',19,21,1643.22 +4001001,'amalgedu pack #1',6,45,1639.44 +1004001,'edu packamalg #1',8,16,1629.81 +1001002,'amalgamalg #2',9,48,1624.59 +9008005,'namelessmaxi #5',17,4,1621.62 +1002002,'importoamalg #2',9,56,1600.82 +9013009,'exportiunivamalg #9',18,51,1590.00 +10012017,'importoamalgamalg #17',17,16,1565.38 +5002001,'importoscholar #1',19,55,1563.38 +10015013,'scholaramalgamalg #13',19,51,1553.20 +3004001,'edu packexporti #1',9,31,1541.43 +5003002,'exportischolar #2',17,2,1540.54 +8013007,'exportimaxi #7',18,51,1537.90 +5004002,'edu packscholar #2',19,24,1514.74 +4003001,'exportiedu pack #1',8,3,1514.43 +2001002,'amalgimporto #2',18,48,1511.30 +1001002,'amalgamalg #2',17,55,1505.25 +2004001,'edu packimporto #1',19,27,1490.13 +4001001,'amalgedu pack #1',17,15,1488.84 +6005001,'scholarcorp #1',17,14,1488.52 +9015011,'scholarunivamalg #11',18,55,1487.52 +5003001,'exportischolar #1',9,26,1482.30 +5004002,'edu packscholar #2',18,47,1481.61 +10009015,'maxiunivamalg #15',19,55,1476.75 +2004002,'edu packimporto #2',9,3,1475.84 +7016007,'corpnameless #7',17,42,1470.52 +5003001,'exportischolar #1',17,59,1466.25 +5004002,'edu packscholar #2',18,35,1462.00 +5003001,'exportischolar #1',18,10,1461.98 +1003001,'exportiamalg #1',19,50,1455.83 +5003001,'exportischolar #1',8,25,1446.36 +1004001,'edu packamalg #1',19,26,1439.04 +6011005,'amalgbrand #5',18,33,1435.50 +5002001,'importoscholar #1',19,33,1434.24 +3004001,'edu packexporti #1',18,49,1433.88 +4001002,'amalgedu pack #2',7,40,1431.54 +4004001,'edu packedu pack #1',17,37,1430.37 +1003002,'exportiamalg #2',7,40,1418.18 +1002002,'importoamalg #2',17,44,1415.25 +2001002,'amalgimporto #2',7,8,1414.14 +5002002,'importoscholar #2',17,30,1404.00 +5003001,'exportischolar #1',19,10,1403.22 +7012003,'importonameless #3',9,22,1399.96 +3003001,'exportiexporti #1',18,33,1386.35 +3002001,'importoexporti #1',17,40,1379.04 +9014011,'edu packunivamalg #11',17,3,1364.52 +5003001,'exportischolar #1',9,46,1361.57 +5001002,'amalgscholar #2',19,51,1360.00 +2004002,'edu packimporto #2',9,28,1353.15 +9015009,'scholarunivamalg #9',18,9,1352.61 +5002002,'importoscholar #2',17,52,1345.02 +1002002,'importoamalg #2',18,27,1343.93 +6011008,'amalgbrand #8',9,39,1341.93 +1004002,'edu packamalg #2',17,11,1340.64 +5003001,'exportischolar #1',17,11,1328.00 +6002003,'importocorp #3',17,20,1327.58 +6012008,'importobrand #8',9,10,1327.19 +7010003,'univnameless #3',19,49,1319.57 +2003001,'exportiimporto #1',9,23,1310.16 +3002002,'importoexporti #2',17,45,1301.90 +3003001,'exportiexporti #1',8,19,1301.37 +6011008,'amalgbrand #8',9,33,1278.35 +10003004,'exportiunivamalg #4',9,23,1272.43 +3004001,'edu packexporti #1',18,21,1270.66 +2001002,'amalgimporto #2',17,22,1269.07 +4004002,'edu packedu pack #2',19,7,1264.56 +1004002,'edu packamalg #2',19,0,1261.65 +10015013,'scholaramalgamalg #13',9,34,1257.30 +1001001,'amalgamalg #1',17,56,1250.08 +3003002,'exportiexporti #2',18,26,1248.48 +9012005,'importounivamalg #5',7,0,1230.20 +9015009,'scholarunivamalg #9',18,40,1222.64 +3003002,'exportiexporti #2',9,42,1217.28 +2001002,'amalgimporto #2',7,46,1213.44 +10004001,'edu packunivamalg #1',18,58,1212.96 +3001001,'amalgexporti #1',9,41,1211.50 +6006007,'corpcorp #7',18,59,1210.32 +5003001,'exportischolar #1',18,14,1210.20 +5001001,'amalgscholar #1',8,31,1205.50 +9016003,'corpunivamalg #3',17,53,1200.00 +10013015,'exportiamalgamalg #15',17,14,1192.50 +1002002,'importoamalg #2',19,16,1182.37 +1002002,'importoamalg #2',19,42,1178.88 +10012011,'importoamalgamalg #11',17,31,1173.28 +3003001,'exportiexporti #1',8,48,1167.05 +5002002,'importoscholar #2',18,40,1159.40 +2001002,'amalgimporto #2',9,3,1157.20 +5003002,'exportischolar #2',19,6,1150.92 +5003001,'exportischolar #1',9,29,1150.77 +10009015,'maxiunivamalg #15',18,2,1150.73 +1004001,'edu packamalg #1',19,2,1148.16 +3003001,'exportiexporti #1',9,14,1139.60 +2001001,'amalgimporto #1',17,28,1135.06 +4003001,'exportiedu pack #1',6,50,1129.20 +8016004,'corpmaxi #4',17,32,1127.61 +9015009,'scholarunivamalg #9',18,18,1124.16 +5002001,'importoscholar #1',8,48,1123.47 +8003010,'exportinameless #10',9,0,1123.08 +1004002,'edu packamalg #2',19,25,1117.39 +7012010,'importonameless #10',19,35,1114.76 +1001002,'amalgamalg #2',9,46,1114.40 +10012017,'importoamalgamalg #17',17,7,1107.54 +10009015,'maxiunivamalg #15',17,29,1104.61 +6010005,'univbrand #5',17,24,1102.20 +6011001,'amalgbrand #1',19,21,1098.30 +3002001,'importoexporti #1',18,39,1094.10 +10004001,'edu packunivamalg #1',17,26,1081.80 +9016003,'corpunivamalg #3',19,8,1079.54 +6011005,'amalgbrand #5',17,31,1071.00 +9015009,'scholarunivamalg #9',18,30,1062.06 +6008007,'namelesscorp #7',19,54,1058.93 +8014005,'edu packmaxi #5',9,34,1058.00 +10013015,'exportiamalgamalg #15',8,30,1057.80 +6006007,'corpcorp #7',19,54,1055.43 +2004002,'edu packimporto #2',18,28,1052.64 +1002002,'importoamalg #2',9,27,1040.43 +7008009,'namelessbrand #9',19,45,1037.51 +2002001,'importoimporto #1',7,32,1023.75 +7012010,'importonameless #10',7,19,1016.80 +5004002,'edu packscholar #2',17,10,1016.60 +6015006,'scholarbrand #6',19,0,1010.91 +1003001,'exportiamalg #1',9,36,1007.81 +7016007,'corpnameless #7',18,45,992.74 +6005001,'scholarcorp #1',19,16,991.98 +5002002,'importoscholar #2',18,27,986.24 +8002009,'importonameless #9',9,23,983.40 +1002001,'importoamalg #1',19,26,977.60 +10002012,'importounivamalg #12',18,4,963.80 +1003001,'exportiamalg #1',17,40,953.55 +8011009,'amalgmaxi #9',19,42,951.60 +4004002,'edu packedu pack #2',9,34,951.00 +4004002,'edu packedu pack #2',18,18,943.66 +2002001,'importoimporto #1',9,20,943.56 +8014005,'edu packmaxi #5',18,24,943.00 +3002002,'importoexporti #2',18,24,935.00 +8003010,'exportinameless #10',7,24,931.32 +7016007,'corpnameless #7',17,28,924.66 +5003002,'exportischolar #2',9,14,920.46 +5001001,'amalgscholar #1',19,54,920.16 +5001001,'amalgscholar #1',18,23,909.48 +6002005,'importocorp #5',18,4,904.12 +1002001,'importoamalg #1',19,40,901.60 +5003001,'exportischolar #1',6,18,901.42 +9014011,'edu packunivamalg #11',8,40,896.49 +7016007,'corpnameless #7',19,16,892.64 +4001002,'amalgedu pack #2',17,14,886.48 +7010003,'univnameless #3',18,37,883.34 +10013013,'exportiamalgamalg #13',7,42,879.36 +1002001,'importoamalg #1',17,32,876.30 +2003001,'exportiimporto #1',18,54,864.36 +10004004,'edu packunivamalg #4',19,59,861.10 +7004009,'edu packbrand #9',9,36,858.73 +8014005,'edu packmaxi #5',9,22,850.23 +7004009,'edu packbrand #9',6,11,847.26 +4001001,'amalgedu pack #1',19,58,844.90 +2003002,'exportiimporto #2',6,17,842.40 +7010009,'univnameless #9',17,37,841.96 +1003002,'exportiamalg #2',19,33,839.90 +8004009,'edu packnameless #9',17,8,829.76 +9012003,'importounivamalg #3',8,59,829.20 +7012003,'importonameless #3',8,51,826.89 +3002001,'importoexporti #1',19,49,826.74 +9012005,'importounivamalg #5',17,45,821.48 +2001002,'amalgimporto #2',17,56,820.26 +2002001,'importoimporto #1',19,10,818.40 +5001001,'amalgscholar #1',19,18,818.09 +7016001,'corpnameless #1',19,50,817.11 +10013013,'exportiamalgamalg #13',17,44,814.80 +7010003,'univnameless #3',18,12,811.50 +6008007,'namelesscorp #7',17,20,810.46 +7016007,'corpnameless #7',9,54,810.24 +9012008,'importounivamalg #8',9,43,803.64 +4001001,'amalgedu pack #1',17,38,796.38 +4001002,'amalgedu pack #2',17,57,792.74 +5003002,'exportischolar #2',19,10,790.72 +7004009,'edu packbrand #9',9,8,789.88 +5001001,'amalgscholar #1',19,51,778.40 +4001001,'amalgedu pack #1',17,7,777.12 +3004001,'edu packexporti #1',19,17,763.00 +5001002,'amalgscholar #2',19,24,762.96 +9008005,'namelessmaxi #5',9,52,756.42 +3002001,'importoexporti #1',9,41,755.15 +5002002,'importoscholar #2',18,26,755.09 +6011001,'amalgbrand #1',6,46,752.80 +3002002,'importoexporti #2',19,19,748.41 +5002002,'importoscholar #2',19,27,745.92 +4003001,'exportiedu pack #1',18,35,745.20 +6002003,'importocorp #3',7,2,744.12 +3002001,'importoexporti #1',19,27,738.14 +1004002,'edu packamalg #2',19,5,736.92 +2002001,'importoimporto #1',9,32,735.13 +4001002,'amalgedu pack #2',17,6,724.80 +10012004,'importoamalgamalg #4',18,33,717.21 +2004002,'edu packimporto #2',9,46,716.38 +9012008,'importounivamalg #8',7,51,715.44 +2002001,'importoimporto #1',19,31,709.32 +8014005,'edu packmaxi #5',17,11,695.64 +2001002,'amalgimporto #2',8,54,688.71 +1001001,'amalgamalg #1',7,31,682.08 +1003002,'exportiamalg #2',8,19,681.56 +7010003,'univnameless #3',19,28,672.51 +3003002,'exportiexporti #2',18,29,665.01 +9008002,'namelessmaxi #2',17,4,660.04 +5003002,'exportischolar #2',9,57,650.43 +2003002,'exportiimporto #2',8,25,650.25 +2004002,'edu packimporto #2',18,57,646.34 +10003016,'exportiunivamalg #16',19,34,641.39 +1001002,'amalgamalg #2',19,54,634.68 +5001002,'amalgscholar #2',8,59,633.36 +3004001,'edu packexporti #1',18,4,633.06 +10010013,'univamalgamalg #13',18,32,630.00 +1002002,'importoamalg #2',19,21,628.67 +10012017,'importoamalgamalg #17',17,19,625.56 +1001002,'amalgamalg #2',18,45,622.21 +5002001,'importoscholar #1',19,32,615.12 +4004001,'edu packedu pack #1',19,50,610.50 +1004002,'edu packamalg #2',8,26,608.52 +6010005,'univbrand #5',7,17,598.91 +6005001,'scholarcorp #1',9,9,597.30 +10012017,'importoamalgamalg #17',19,11,595.65 +6011005,'amalgbrand #5',19,3,594.59 +3002001,'importoexporti #1',18,37,589.28 +6007003,'brandcorp #3',17,26,588.80 +1002001,'importoamalg #1',19,46,583.47 +2004002,'edu packimporto #2',19,16,581.28 +1004002,'edu packamalg #2',17,39,580.32 +6008002,'namelesscorp #2',9,3,578.97 +10015013,'scholaramalgamalg #13',18,20,574.24 +9015009,'scholarunivamalg #9',18,31,573.93 +2003002,'exportiimporto #2',19,52,568.92 +9008005,'namelessmaxi #5',17,42,567.64 +7004007,'edu packbrand #7',18,15,563.80 +5002002,'importoscholar #2',19,0,558.98 +8004003,'edu packnameless #3',18,21,558.90 +10012017,'importoamalgamalg #17',8,4,558.69 +9015011,'scholarunivamalg #11',19,54,555.36 +1002001,'importoamalg #1',19,20,554.88 +2004002,'edu packimporto #2',19,20,553.76 +1001001,'amalgamalg #1',19,13,549.43 +6015006,'scholarbrand #6',19,50,548.68 +10012011,'importoamalgamalg #11',9,19,541.25 +3004001,'edu packexporti #1',17,51,539.95 +1003001,'exportiamalg #1',6,53,528.90 +10015004,'scholaramalgamalg #4',19,42,525.80 +4004001,'edu packedu pack #1',17,35,524.59 +8002009,'importonameless #9',6,46,522.06 +5003002,'exportischolar #2',17,23,520.96 +4004001,'edu packedu pack #1',17,30,517.65 +7008009,'namelessbrand #9',17,29,514.25 +9012005,'importounivamalg #5',8,34,504.60 +10012004,'importoamalgamalg #4',8,8,503.10 +3002002,'importoexporti #2',18,21,490.82 +1002002,'importoamalg #2',18,31,489.72 +10012011,'importoamalgamalg #11',8,53,486.50 +9013009,'exportiunivamalg #9',17,24,478.00 +6008002,'namelesscorp #2',8,59,471.68 +10003016,'exportiunivamalg #16',18,20,471.66 +8016004,'corpmaxi #4',7,53,470.55 +4001001,'amalgedu pack #1',17,26,469.58 +1002001,'importoamalg #1',18,59,464.52 +9013009,'exportiunivamalg #9',9,8,462.80 +9008002,'namelessmaxi #2',9,51,458.82 +6009003,'maxicorp #3',17,44,457.74 +3002001,'importoexporti #1',8,5,454.77 +10012004,'importoamalgamalg #4',17,24,450.90 +4004001,'edu packedu pack #1',9,37,449.90 +6011008,'amalgbrand #8',17,4,445.12 +10015013,'scholaramalgamalg #13',8,32,438.48 +4003001,'exportiedu pack #1',9,31,424.26 +3002002,'importoexporti #2',8,35,421.26 +5004001,'edu packscholar #1',8,30,420.28 +1002001,'importoamalg #1',8,24,418.92 +1002002,'importoamalg #2',17,33,416.67 +5002002,'importoscholar #2',6,34,416.52 +2003002,'exportiimporto #2',18,18,414.12 +3002002,'importoexporti #2',9,28,414.12 +3003002,'exportiexporti #2',19,24,410.00 +5003001,'exportischolar #1',19,29,407.34 +5001001,'amalgscholar #1',18,40,405.15 +10009015,'maxiunivamalg #15',9,58,402.38 +3002001,'importoexporti #1',19,35,397.71 +3003001,'exportiexporti #1',17,28,389.88 +1001002,'amalgamalg #2',8,38,388.35 +8003010,'exportinameless #10',19,26,387.92 +7010003,'univnameless #3',17,11,387.00 +6011001,'amalgbrand #1',8,8,386.54 +1003001,'exportiamalg #1',6,10,386.40 +5001001,'amalgscholar #1',17,1,384.75 +5004002,'edu packscholar #2',18,15,379.68 +5001002,'amalgscholar #2',9,39,378.56 +3002002,'importoexporti #2',9,12,378.08 +5002001,'importoscholar #1',8,1,377.57 +6006007,'corpcorp #7',18,53,375.36 +1003002,'exportiamalg #2',18,0,374.99 +5003001,'exportischolar #1',18,43,374.68 +8002009,'importonameless #9',8,46,371.42 +3002001,'importoexporti #1',17,33,369.75 +3003002,'exportiexporti #2',9,20,367.90 +5002001,'importoscholar #1',18,53,365.10 +7015007,'scholarnameless #7',18,44,357.84 +8014005,'edu packmaxi #5',17,2,352.36 +10004004,'edu packunivamalg #4',19,3,344.64 +4001002,'amalgedu pack #2',8,50,341.33 +6010005,'univbrand #5',9,8,339.83 +9012005,'importounivamalg #5',8,3,339.57 +2001002,'amalgimporto #2',18,49,333.66 +1002002,'importoamalg #2',17,4,330.01 +8011009,'amalgmaxi #9',19,26,328.35 +5002002,'importoscholar #2',9,7,328.26 +9008002,'namelessmaxi #2',18,6,327.95 +5001001,'amalgscholar #1',17,19,323.90 +5003002,'exportischolar #2',17,26,320.31 +3002001,'importoexporti #1',18,31,319.90 +8013007,'exportimaxi #7',17,49,318.20 +6011008,'amalgbrand #8',8,13,315.10 +3003002,'exportiexporti #2',7,37,313.24 +7014001,'edu packnameless #1',18,38,312.80 +1003002,'exportiamalg #2',19,42,304.64 +2004002,'edu packimporto #2',9,24,304.36 +3003001,'exportiexporti #1',8,58,302.40 +4004001,'edu packedu pack #1',19,11,300.48 +6012005,'importobrand #5',19,25,299.52 +4004001,'edu packedu pack #1',19,48,276.92 +2001002,'amalgimporto #2',18,20,276.77 +2002001,'importoimporto #1',18,1,273.60 +1004002,'edu packamalg #2',8,49,264.60 +8003010,'exportinameless #10',19,57,263.19 +4003001,'exportiedu pack #1',18,37,260.23 +3003001,'exportiexporti #1',9,48,258.39 +7012003,'importonameless #3',9,2,258.24 +5002001,'importoscholar #1',18,38,257.53 +6005001,'scholarcorp #1',17,43,256.48 +5001002,'amalgscholar #2',17,44,255.00 +4001002,'amalgedu pack #2',7,6,253.80 +2001002,'amalgimporto #2',19,25,253.50 +1001001,'amalgamalg #1',8,16,253.34 +5002002,'importoscholar #2',17,51,252.56 +1002002,'importoamalg #2',18,29,250.80 +9015009,'scholarunivamalg #9',8,28,248.27 +3001001,'amalgexporti #1',19,57,247.68 +6003008,'exporticorp #8',18,47,244.44 +1004002,'edu packamalg #2',19,9,242.25 +2004001,'edu packimporto #1',17,48,241.50 +3003002,'exportiexporti #2',9,27,234.60 +1001001,'amalgamalg #1',19,54,234.00 +2002001,'importoimporto #1',18,33,232.56 +4003001,'exportiedu pack #1',19,20,231.99 +5003001,'exportischolar #1',19,27,231.84 +2001002,'amalgimporto #2',18,4,229.32 +4001001,'amalgedu pack #1',19,45,227.84 +5002002,'importoscholar #2',17,27,227.52 +5004001,'edu packscholar #1',18,30,225.21 +2002001,'importoimporto #1',17,51,222.80 +7004009,'edu packbrand #9',19,36,221.85 +3003002,'exportiexporti #2',17,21,221.20 +8005008,'scholarnameless #8',18,20,220.83 +6003008,'exporticorp #8',17,16,218.16 +6008005,'namelesscorp #5',18,51,216.69 +9008002,'namelessmaxi #2',17,28,215.76 +2001002,'amalgimporto #2',9,27,213.64 +6015006,'scholarbrand #6',6,14,212.28 +6015006,'scholarbrand #6',19,26,211.85 +4001001,'amalgedu pack #1',9,48,211.32 +1004002,'edu packamalg #2',7,2,210.14 +3002001,'importoexporti #1',9,26,209.95 +6010005,'univbrand #5',17,3,208.32 +7004009,'edu packbrand #9',17,25,208.25 +7010004,'univnameless #4',19,23,208.20 +9012005,'importounivamalg #5',18,4,205.70 +3003001,'exportiexporti #1',7,23,203.61 +1001002,'amalgamalg #2',19,48,203.28 +4003001,'exportiedu pack #1',8,1,202.30 +2004001,'edu packimporto #1',19,10,202.12 +4001002,'amalgedu pack #2',17,53,198.33 +6010005,'univbrand #5',8,18,197.37 +3003001,'exportiexporti #1',17,6,196.38 +1004001,'edu packamalg #1',17,26,194.18 +7004009,'edu packbrand #9',17,42,193.39 +3001001,'amalgexporti #1',17,33,191.98 +1001002,'amalgamalg #2',17,26,190.50 +3002001,'importoexporti #1',8,1,186.93 +9015009,'scholarunivamalg #9',19,9,186.30 +2003001,'exportiimporto #1',19,11,184.90 +6003008,'exporticorp #8',6,32,182.90 +4003001,'exportiedu pack #1',17,45,182.28 +9015009,'scholarunivamalg #9',17,32,181.60 +6011008,'amalgbrand #8',9,14,178.22 +2002001,'importoimporto #1',18,34,177.79 +9012005,'importounivamalg #5',19,25,176.67 +6008002,'namelesscorp #2',17,31,176.19 +8013009,'exportimaxi #9',19,25,174.96 +2004001,'edu packimporto #1',18,42,172.48 +8013009,'exportimaxi #9',19,31,168.82 +3003001,'exportiexporti #1',9,1,156.98 +7010009,'univnameless #9',9,22,155.40 +4004001,'edu packedu pack #1',9,32,154.79 +3003002,'exportiexporti #2',19,36,154.38 +3003001,'exportiexporti #1',9,6,151.92 +1001001,'amalgamalg #1',9,41,148.05 +9015009,'scholarunivamalg #9',8,38,147.92 +5003001,'exportischolar #1',19,28,147.14 +2001001,'amalgimporto #1',18,47,142.29 +5002001,'importoscholar #1',8,52,141.15 +6002003,'importocorp #3',17,53,140.40 +2003002,'exportiimporto #2',9,49,137.40 +10003016,'exportiunivamalg #16',19,4,137.40 +4001001,'amalgedu pack #1',7,58,135.78 +7010004,'univnameless #4',17,26,129.50 +5003001,'exportischolar #1',9,43,129.24 +1003002,'exportiamalg #2',7,42,127.68 +6008002,'namelesscorp #2',19,53,127.32 +5001001,'amalgscholar #1',9,38,126.56 +2002001,'importoimporto #1',17,40,126.49 +4004001,'edu packedu pack #1',18,52,122.67 +5001001,'amalgscholar #1',17,10,118.36 +2002001,'importoimporto #1',17,38,116.16 +5003001,'exportischolar #1',8,1,116.10 +8005008,'scholarnameless #8',8,19,111.68 +8004009,'edu packnameless #9',8,59,110.90 +9014011,'edu packunivamalg #11',19,9,110.12 +1002002,'importoamalg #2',19,52,109.44 +2001002,'amalgimporto #2',18,31,108.78 +3002001,'importoexporti #1',17,1,108.45 +2002001,'importoimporto #1',19,15,107.64 +3004001,'edu packexporti #1',18,16,106.60 +1004002,'edu packamalg #2',9,12,103.87 +3003001,'exportiexporti #1',19,16,101.58 +3004001,'edu packexporti #1',17,1,100.80 +6002005,'importocorp #5',9,41,99.00 +4003001,'exportiedu pack #1',9,47,98.25 +7010009,'univnameless #9',8,48,95.36 +3002001,'importoexporti #1',6,50,95.22 +7016001,'corpnameless #1',9,37,93.22 +3004001,'edu packexporti #1',9,40,91.77 +9012005,'importounivamalg #5',6,45,91.35 +5002001,'importoscholar #1',19,31,91.20 +8004003,'edu packnameless #3',8,39,90.64 +8004003,'edu packnameless #3',8,11,87.12 +5003001,'exportischolar #1',17,9,87.10 +6009003,'maxicorp #3',6,4,87.00 +9014011,'edu packunivamalg #11',7,58,86.57 +2002001,'importoimporto #1',17,0,86.10 +5001001,'amalgscholar #1',19,27,85.65 +8003010,'exportinameless #10',17,7,85.50 +6011008,'amalgbrand #8',18,29,83.68 +10013013,'exportiamalgamalg #13',19,39,81.96 +9015009,'scholarunivamalg #9',9,32,81.84 +1004002,'edu packamalg #2',7,41,81.59 +4004001,'edu packedu pack #1',19,13,80.24 +5001002,'amalgscholar #2',9,46,78.68 +10012017,'importoamalgamalg #17',17,29,76.50 +2001002,'amalgimporto #2',19,45,76.36 +7008009,'namelessbrand #9',17,43,75.36 +1003001,'exportiamalg #1',9,44,73.92 +3001001,'amalgexporti #1',17,38,72.96 +2001001,'amalgimporto #1',19,48,72.03 +2004001,'edu packimporto #1',19,45,70.40 +7010009,'univnameless #9',17,54,69.56 +1002001,'importoamalg #1',18,10,68.37 +7009010,'maxibrand #10',9,38,66.30 +2001002,'amalgimporto #2',18,1,65.00 +9012003,'importounivamalg #3',8,13,64.64 +1002001,'importoamalg #1',8,1,64.48 +10015013,'scholaramalgamalg #13',17,10,64.26 +8005005,'scholarnameless #5',17,19,63.70 +4001002,'amalgedu pack #2',7,45,63.47 +6016003,'corpbrand #3',7,0,63.20 +4004001,'edu packedu pack #1',7,34,62.68 +4003001,'exportiedu pack #1',9,12,62.26 +2004002,'edu packimporto #2',18,37,56.75 +5002002,'importoscholar #2',19,10,55.68 +10010013,'univamalgamalg #13',9,26,54.80 +9015009,'scholarunivamalg #9',8,47,54.20 +7004007,'edu packbrand #7',17,8,53.20 +2004002,'edu packimporto #2',8,39,51.68 +10015013,'scholaramalgamalg #13',8,18,51.48 +10012011,'importoamalgamalg #11',19,25,48.45 +1003002,'exportiamalg #2',19,10,46.80 +5003001,'exportischolar #1',17,25,46.72 +1002001,'importoamalg #1',18,21,45.32 +5003002,'exportischolar #2',8,21,43.89 +6011008,'amalgbrand #8',9,50,42.50 +3003001,'exportiexporti #1',17,42,42.08 +6011005,'amalgbrand #5',18,40,41.54 +1001002,'amalgamalg #2',19,41,41.52 +7010004,'univnameless #4',18,21,41.46 +9015011,'scholarunivamalg #11',17,40,40.70 +5004002,'edu packscholar #2',17,22,39.36 +6016003,'corpbrand #3',19,50,39.20 +3003002,'exportiexporti #2',19,37,38.80 +3002001,'importoexporti #1',17,0,38.34 +5002001,'importoscholar #1',17,58,38.27 +8014005,'edu packmaxi #5',18,14,37.80 +6015006,'scholarbrand #6',17,2,37.29 +9013009,'exportiunivamalg #9',9,16,34.78 +4001001,'amalgedu pack #1',9,29,34.76 +10013013,'exportiamalgamalg #13',18,27,34.23 +7012010,'importonameless #10',18,37,33.83 +2001002,'amalgimporto #2',19,19,33.20 +7016001,'corpnameless #1',18,53,32.24 +6003008,'exporticorp #8',17,56,31.08 +5004001,'edu packscholar #1',18,27,31.02 +7010004,'univnameless #4',19,51,30.16 +8002009,'importonameless #9',19,8,29.84 +8005005,'scholarnameless #5',9,15,29.20 +3002002,'importoexporti #2',9,53,27.74 +5003001,'exportischolar #1',9,3,26.76 +4004001,'edu packedu pack #1',18,22,26.00 +9015009,'scholarunivamalg #9',18,26,25.96 +6016003,'corpbrand #3',19,59,23.73 +4001001,'amalgedu pack #1',17,54,22.38 +1002001,'importoamalg #1',19,53,22.28 +6003008,'exporticorp #8',18,36,22.08 +5003001,'exportischolar #1',17,4,21.70 +10012004,'importoamalgamalg #4',19,4,20.91 +5003001,'exportischolar #1',17,46,17.63 +4004001,'edu packedu pack #1',18,30,17.50 +3004001,'edu packexporti #1',7,45,17.11 +1003001,'exportiamalg #1',19,52,15.81 +6011001,'amalgbrand #1',8,42,15.81 +6010005,'univbrand #5',8,8,15.47 +2003001,'exportiimporto #1',17,18,15.17 +2001002,'amalgimporto #2',19,6,14.72 +8011009,'amalgmaxi #9',19,15,14.04 +4003001,'exportiedu pack #1',19,39,13.49 +3003001,'exportiexporti #1',19,20,13.26 +5002002,'importoscholar #2',9,16,13.13 +8011009,'amalgmaxi #9',8,23,12.88 +6016003,'corpbrand #3',19,57,12.48 +1001002,'amalgamalg #2',9,28,12.47 +6016003,'corpbrand #3',17,58,12.40 +6008005,'namelesscorp #5',19,2,10.59 +3004001,'edu packexporti #1',19,47,10.00 +9015011,'scholarunivamalg #11',17,17,8.60 +6008007,'namelesscorp #7',19,15,7.36 +4004001,'edu packedu pack #1',9,41,7.14 +4003001,'exportiedu pack #1',17,4,6.84 +1002001,'importoamalg #1',9,56,6.44 +6003008,'exporticorp #8',18,8,5.15 +3001001,'amalgexporti #1',17,18,3.96 +6008005,'namelesscorp #5',9,19,3.22 +6016003,'corpbrand #3',17,8,2.65 +5001001,'amalgscholar #1',8,41,0.30 +3003001,'exportiexporti #1',18,15,0.00 +3003002,'exportiexporti #2',6,35,0.00 +4003001,'exportiedu pack #1',18,33,0.00 +4004002,'edu packedu pack #2',17,57,0.00 +5001002,'amalgscholar #2',8,25,0.00 +5003001,'exportischolar #1',17,36,0.00 +6012008,'importobrand #8',9,0,0.00 +7012010,'importonameless #10',18,50,0.00 +9012005,'importounivamalg #5',9,12,0.00 +---- TYPES +INT, STRING, INT, INT, DECIMAL +====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q72.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q72.test b/testdata/workloads/tpcds/queries/tpcds-q72.test new file mode 100644 index 0000000..ba96064 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q72.test @@ -0,0 +1,132 @@ +==== +---- QUERY: TPCDS-Q72 +select i_item_desc + ,w_warehouse_name + ,d1.d_week_seq + ,sum(case when p_promo_sk is null then 1 else 0 end) no_promo + ,sum(case when p_promo_sk is not null then 1 else 0 end) promo + ,count(*) total_cnt +from catalog_sales +join inventory on (cs_item_sk = inv_item_sk) +join warehouse on (w_warehouse_sk=inv_warehouse_sk) +join item on (i_item_sk = cs_item_sk) +join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk) +join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk) +join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk) +join date_dim d2 on (inv_date_sk = d2.d_date_sk) +join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk) +left outer join promotion on (cs_promo_sk=p_promo_sk) +left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number) +where d1.d_week_seq = d2.d_week_seq + and inv_quantity_on_hand < cs_quantity + and cast(d3.d_date as timestamp) > (cast(d1.d_date as timestamp) + interval 5 days) + and hd_buy_potential = '>10000' + and d1.d_year = 1999 + and cd_marital_status = 'D' +group by i_item_desc,w_warehouse_name,d1.d_week_seq +order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq +limit 100; +---- RESULTS +'Best possible ages tell together new, st','Conventional childr',5213,0,2,2 +'Closed, good condition','Doors canno',5169,0,2,2 +'Departments make once again police. Very acceptable results call still extended, known ends; relationships shoot strangely. Acids shall discharge in order ethnic, ric','NULL',5168,0,2,2 +'Futures should enjoy able galleries. Late blue tickets pass longer urgently dead types. Shoulders will see rigidly institutions. Other con','Conventional childr',5210,0,2,2 +'Good, open studies dream more; industrial, social organisations could understand recently quick tall theories. Lines must answer functions; subtle factor','Important issues liv',5214,0,2,2 +'Hard sudden aspects shall not commemorate about a functions. Western, british cases see here churches. Stairs a','Doors canno',5212,0,2,2 +'High, essential groups should not weigh more other years; there different papers could announce; large departments c','Important issues liv',5198,0,2,2 +'Just young partie','Important issues liv',5213,0,2,2 +'Levels undermine unfortunately efficient weeks','NULL',5210,0,2,2 +'New, full rises drive however. Legs retain often in a women. Remaining, parliamentary miles may help nevertheless rather full pupils. Glad crimes might','Bad cards must make.',5217,0,2,2 +'Provisions go too. Sad others contain italian branches. Keys k','Bad cards must make.',5213,0,2,2 +'Provisions go too. Sad others contain italian branches. Keys k','Doors canno',5213,0,2,2 +'Shortly desperate nat','Conventional childr',5210,0,2,2 +'True tears continue currently pale, close men. Soon medical numbers might not take other, ','Important issues liv',5199,0,2,2 +'White employees name; figures feed sure. Speeches can achieve. Extremely final seats may exist too all religious studies. Medite','Bad cards must make.',5168,0,2,2 +'Words use up a documents. Collections may','Conventional childr',5168,0,2,2 +'Words use up a documents. Collections may','Important issues liv',5168,0,2,2 +'A little average flames ought to break old, unique men. Things select often red, economic others. Hands will lift sufficiently; german, proper sections worry perhaps for the po','NULL',5212,0,1,1 +'Abilities would not require almost; local','Bad cards must make.',5197,0,1,1 +'Able','Conventional childr',5177,0,1,1 +'Able hands help however inevitable policemen. Far true matters will not forgive never hands. Absolutely french events stop now. Well able procedures unde','Conventional childr',5195,0,1,1 +'Able hands help however inevitable policemen. Far true matters will not forgive never hands. Absolutely french events stop now. Well able procedures unde','Doors canno',5195,0,1,1 +'Able, alternative police shall not give so other complaints. There complex ','NULL',5212,0,1,1 +'Able, long mammals can want new, serious years. Questions would not cope again mainly unable contributions. Less responsible shelves lose records; leading, similar ','Important issues liv',5170,0,1,1 +'Able, main parties think really. Resources arrive only independent, old representations. Small, double advantages ','Doors canno',5213,0,1,1 +'Able, main parties think really. Resources arrive only independent, old representations. Small, double advantages ','Important issues liv',5180,0,1,1 +'About international concentrations could avoid then alone apparent activities; inadequate, mediterranean days get eve','Important issues liv',5215,0,1,1 +'About natural economie','Bad cards must make.',5202,0,1,1 +'About other levels should proceed certainly fine, severe facts.','Important issues liv',5210,0,1,1 +'About working feelings could produce only types. Electoral, new visitors will not make more afraid, large tr','NULL',5197,0,1,1 +'Absolutely','Bad cards must make.',5203,0,1,1 +'Absolutely','Doors canno',5203,0,1,1 +'Absolutely ','NULL',5206,0,1,1 +'Absolutely front men turn spatial hours. Good, free sales used to marry outside appropriate ships. Noble men sa','NULL',5207,0,1,1 +'Absolutely old payments will b','Doors canno',5216,0,1,1 +'Accused men cannot increase e','Important issues liv',5207,0,1,1 +'Acids grab below previous standards. Ever large metals will come on a articles. Underlying stories protect at last. Reasonable directions believe rather due to a','Bad cards must make.',5169,0,1,1 +'Active plants need necessary, widespread roads. Best back visits hold regularly fresh friend','Conventional childr',5209,0,1,1 +'Active plants need necessary, widespread roads. Best back visits hold regularly fresh friend','NULL',5209,0,1,1 +'Active windows shall not find small, relig','Conventional childr',5203,0,1,1 +'Actively different proceedings light yet so similar houses. Good circumstances shall not take only levels. Then moral pounds will clean very only national organisations. ','NULL',5183,0,1,1 +'Activities say. Right lips resort current techniques. Regional, possible daughters might not present changes; students can notice ridiculous, l','Conventional childr',5192,0,1,1 +'Acts','Bad cards must make.',5218,0,1,1 +'Acts','Conventional childr',5218,0,1,1 +'Actual, grey hands giv','NULL',5214,0,1,1 +'Acute, important performances afford. New, nuclear men used to assess again small results.','NULL',5187,0,1,1 +'Added, similar grounds spend also concrete terms. Fellow, mass','Important issues liv',5206,0,1,1 +'Additional, terrible characters shall examine. Ago lexical conditions get into a weeks. Barely trying results perform still hot men. Great kinds end also committees. Police should live only on the ','Doors canno',5216,0,1,1 +'Additional, terrible characters shall examine. Ago lexical conditions get into a weeks. Barely trying results perform still hot men. Great kinds end also committees. Police should live only on the ','Important issues liv',5214,0,1,1 +'Adequate things reassure unknown legs. Old, possible bishops shall locate else during a companies; bitter, alone ','Bad cards must make.',5206,0,1,1 +'Adequately unemployed aspects ought to keep on a years. Years get somewhere sometimes late examples; laws must shape determined stones. Recently real decisions may cost now other female thousands. ','Conventional childr',5214,0,1,1 +'Adequately unemployed aspects ought to keep on a years. Years get somewhere sometimes late examples; laws must shape determined stones. Recently real decisions may cost now other female thousands. ','Important issues liv',5214,0,1,1 +'Adults throw close recent women. Orange, guilty libraries let earnings. Initiatives ought to walk. Simple, successful states might work eventually full orders. Formerly very ','Bad cards must make.',5206,0,1,1 +'Adults throw close recent women. Orange, guilty libraries let earnings. Initiatives ought to walk. Simple, successful states might work eventually full orders. Formerly very ','NULL',5206,0,1,1 +'Advanced, certain fields miss electronically for the books. Open measures match therefore s','Doors canno',5196,0,1,1 +'Advanced, certain fields miss electronically for the books. Open measures match therefore s','Important issues liv',5210,0,1,1 +'Advantages go small. Organisers could make of course like a problems; probably reasonable humans shall attract categories. Agencies will enable much heavy matters. Stair','Doors canno',5186,0,1,1 +'Advantages go small. Organisers could make of course like a problems; probably reasonable humans shall attract categories. Agencies will enable much heavy matters. Stair','Important issues liv',5186,0,1,1 +'Advantages go small. Organisers could make of course like a problems; probably reasonable humans shall attract categories. Agencies will enable much heavy matters. Stair','NULL',5206,0,1,1 +'Afraid years suspend much building','NULL',5171,0,1,1 +'Afraid, grey officers mean costly institutions. Societi','Conventional childr',5199,0,1,1 +'Afraid, old meals will get chronic, strong applicants. Arms could look with a needs. Hence wor','Important issues liv',5210,0,1,1 +'Afraid, southern problems need according to a dec','Conventional childr',5207,0,1,1 +'Afraid, southern problems need according to a dec','Important issues liv',5207,0,1,1 +'Again available bags breathe good circumstances. Thus final cases must ','Bad cards must make.',5214,0,1,1 +'Again available bags breathe good circumstances. Thus final cases must ','Conventional childr',5218,0,1,1 +'Again judicial colours may blame fully british strange groups. Rules shall cover probably participants. W','NULL',5214,0,1,1 +'Again small deaths could flou','Bad cards must make.',5175,0,1,1 +'Agencies shall not consider false in a others. Obviously interesting authorities come anyway men. Small, ','Important issues liv',5206,0,1,1 +'Agents see companies. Weekly clergy might not enable always mere studies. Men throw possible relations. Then static rights wr','Conventional childr',5209,0,1,1 +'Ago foreign writings leave; even considerable artists let fully then minimal workers. Clear ministers keep. Specifically dry men increase central tests. Living, alternative meanings ought','Important issues liv',5180,0,1,1 +'Ago regional objects finish courts. Large, serio','Bad cards must make.',5187,0,1,1 +'Agricultural, difficult engines marry according to the things; instances shall not go however quietly statutory images. Still sharp patients work no doubt producers. Magazines ','Doors canno',5210,0,1,1 +'Agricultural, selective groups follow much worthwhile panels. Fully sim','Bad cards must make.',5207,0,1,1 +'Aims play already','Doors canno',5198,0,1,1 +'Al','NULL',5167,0,1,1 +'All ','Bad cards must make.',5175,0,1,1 +'All ','Important issues liv',5210,0,1,1 +'All numerous reasons explain upper teachers; necessary, inte','Bad cards must make.',5181,0,1,1 +'All realistic employees should attempt all only expert parties. Complete days cannot come as possible rules. Normal candidates would not pay there improved, o','Doors canno',5204,0,1,1 +'All right deliberate difficulties wait still between a seats; final, actual jobs may mee','Bad cards must make.',5212,0,1,1 +'All right deliberate difficulties wait still between a seats; final, actual jobs may mee','Conventional childr',5174,0,1,1 +'All right used men must demand. Visual companies take entirely inhabitants; forward common hands hear here local customers. So traditional questions shal','Doors canno',5188,0,1,1 +'Alm','Important issues liv',5204,0,1,1 +'Almost armed animals will maintain always pure, professional days; differe','Conventional childr',5175,0,1,1 +'Almost armed animals will maintain always pure, professional days; differe','Doors canno',5175,0,1,1 +'Almost armed animals will maintain always pure, professional days; differe','Important issues liv',5175,0,1,1 +'Almost low provisions suggest to','Conventional childr',5211,0,1,1 +'Almost separate f','Bad cards must make.',5215,0,1,1 +'Almost separate f','NULL',5215,0,1,1 +'Alone days must undertake children; pages will face cases. Days fit especially black standards. Certain, involved numbers may not intend high, ','Conventional childr',5176,0,1,1 +'Alone relevant nights pretend so complete years. Currently new unions make horizontally bills. Most political troops could give most possible, australian elements; flowers shall recall most pop','Important issues liv',5212,0,1,1 +'Alone working-class dates open from a issues. Most european concessions will not tell personal areas; central, poor officials might not act ','Doors canno',5197,0,1,1 +'Alone, small conditions get either likely companies. Inner, long-term patients make hot rebels. Procedures see fi','Important issues liv',5215,0,1,1 +'Already european mothers ought to impose big ever fixed parents. Dominant groups say even. Here basic weeks set as winners. Modern, young prayers release very environ','Doors canno',5215,0,1,1 +'Also f','Doors canno',5192,0,1,1 +'Also good subjects cannot clean then then difficult gaps. Good, sorry methods divert perhaps. Whit','Bad cards must make.',5211,0,1,1 +'Also public waves will not bomb yesterday methods. Eyes ','NULL',5183,0,1,1 +'Also real details used to travel for the articles. A bit anonymous factors aim there early, clean standards. Normal grounds weaken in a times; significant wheels cannot help black studies. Right l','Important issues liv',5199,0,1,1 +'Also silent nurses find also fully mental priorities. Savings shall bring naturally silent e','Conventional childr',5168,0,1,1 +---- TYPES +STRING, STRING, INT, BIGINT, BIGINT, BIGINT +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q74.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q74.test b/testdata/workloads/tpcds/queries/tpcds-q74.test new file mode 100644 index 0000000..b2ac875 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q74.test @@ -0,0 +1,156 @@ +==== +---- QUERY: TPCDS-Q74 +with year_total as ( + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,d_year as year + ,sum(ss_net_paid) year_total + ,'s' sale_type + from customer + ,store_sales + ,date_dim + where c_customer_sk = ss_customer_sk + and ss_sold_date_sk = d_date_sk + and d_year in (2001,2001+1) + group by c_customer_id + ,c_first_name + ,c_last_name + ,d_year + union all + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,d_year as year + ,sum(ws_net_paid) year_total + ,'w' sale_type + from customer + ,web_sales + ,date_dim + where c_customer_sk = ws_bill_customer_sk + and ws_sold_date_sk = d_date_sk + and d_year in (2001,2001+1) + group by c_customer_id + ,c_first_name + ,c_last_name + ,d_year + ) + select + t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name + from year_total t_s_firstyear + ,year_total t_s_secyear + ,year_total t_w_firstyear + ,year_total t_w_secyear + where t_s_secyear.customer_id = t_s_firstyear.customer_id + and t_s_firstyear.customer_id = t_w_secyear.customer_id + and t_s_firstyear.customer_id = t_w_firstyear.customer_id + and t_s_firstyear.sale_type = 's' + and t_w_firstyear.sale_type = 'w' + and t_s_secyear.sale_type = 's' + and t_w_secyear.sale_type = 'w' + and t_s_firstyear.year = 2001 + and t_s_secyear.year = 2001+1 + and t_w_firstyear.year = 2001 + and t_w_secyear.year = 2001+1 + and t_s_firstyear.year_total > 0 + and t_w_firstyear.year_total > 0 + and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end + > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end + order by 1,1,1 +limit 100; +---- RESULTS +'AAAAAAAAAMGDAAAA','Kenneth','Harlan' +'AAAAAAAAANFAAAAA','Philip','Banks' +'AAAAAAAAAOPFBAAA','Jerry','Fields' +'AAAAAAAABLEIBAAA','Paula','Wakefield' +'AAAAAAAABNBBAAAA','Irma','Smith' +'AAAAAAAACADPAAAA','Cristobal','Thomas' +'AAAAAAAACFCGBAAA','Marcus','Sanders' +'AAAAAAAACFENAAAA','Christopher','Dawson' +'AAAAAAAACIJMAAAA','Elizabeth','Thomas' +'AAAAAAAACJDIAAAA','James','Kerr' +'AAAAAAAACNAGBAAA','Virginia','May' +'AAAAAAAADBEFBAAA','Bennie','Bowers' +'AAAAAAAADCKOAAAA','Robert','Gonzalez' +'AAAAAAAADFIEBAAA','John','Gray' +'AAAAAAAADFKABAAA','Latoya','Craft' +'AAAAAAAADIIOAAAA','David','Carroll' +'AAAAAAAADIJGBAAA','Ruth','Sanders' +'AAAAAAAADLHBBAAA','Henry','Bertrand' +'AAAAAAAAEADJAAAA','Ruth','Carroll' +'AAAAAAAAEJDLAAAA','Alice','Wright' +'AAAAAAAAEKFPAAAA','Annika','Chin' +'AAAAAAAAEKJLAAAA','Aisha','Carlson' +'AAAAAAAAEOAKAAAA','Molly','Benjamin' +'AAAAAAAAEPOGAAAA','Felisha','Mendes' +'AAAAAAAAFACEAAAA','Priscilla','Miller' +'AAAAAAAAFBAHAAAA','Michael','Williams' +'AAAAAAAAFGIGAAAA','Eduardo','Miller' +'AAAAAAAAFGPGAAAA','Albert','Wadsworth' +'AAAAAAAAFHACBAAA','NULL','NULL' +'AAAAAAAAFJHFAAAA','Larissa','Roy' +'AAAAAAAAFMHIAAAA','Emilio','Darling' +'AAAAAAAAFOGIAAAA','Michelle','Greene' +'AAAAAAAAFOJAAAAA','Don','Castillo' +'AAAAAAAAGEHIAAAA','Tyler','Miller' +'AAAAAAAAGFMDBAAA','Kathleen','Gibson' +'AAAAAAAAGHPBBAAA','Nick','Mendez' +'AAAAAAAAGNDAAAAA','Terry','Mcdowell' +'AAAAAAAAHGOABAAA','Sonia','White' +'AAAAAAAAHHCABAAA','William','Stewart' +'AAAAAAAAHJLAAAAA','Audrey','Beltran' +'AAAAAAAAHMJNAAAA','Ryan','Baptiste' +'AAAAAAAAHMOIAAAA','Grace','Henderson' +'AAAAAAAAHNFHAAAA','Rebecca','Wilson' +'AAAAAAAAIADEBAAA','Diane','Aldridge' +'AAAAAAAAIBAEBAAA','Sandra','Wilson' +'AAAAAAAAIBFCBAAA','Ruth','Grantham' +'AAAAAAAAIBHHAAAA','Jennifer','Ballard' +'AAAAAAAAICHFAAAA','Linda','Mccoy' +'AAAAAAAAIDKFAAAA','Michael','Mack' +'AAAAAAAAIJEMAAAA','Charlie','Cummings' +'AAAAAAAAIMHBAAAA','Kathy','Knowles' +'AAAAAAAAIMHHBAAA','Lillian','Davidson' +'AAAAAAAAJEKFBAAA','Norma','Burkholder' +'AAAAAAAAJGMMAAAA','Richard','Larson' +'AAAAAAAAJIALAAAA','Santos','Gutierrez' +'AAAAAAAAJKBNAAAA','Julie','Kern' +'AAAAAAAAJONHBAAA','Warren','Orozco' +'AAAAAAAAKAECAAAA','Milton','Mackey' +'AAAAAAAAKBCABAAA','Debra','Bell' +'AAAAAAAAKJBKAAAA','Georgia','Scott' +'AAAAAAAAKJBLAAAA','Kerry','Davis' +'AAAAAAAAKKGEAAAA','Katie','Dunbar' +'AAAAAAAAKLHHBAAA','Manuel','Castaneda' +'AAAAAAAAKNAKAAAA','Gladys','Banks' +'AAAAAAAAKOJJAAAA','Gracie','Mendoza' +'AAAAAAAALFKKAAAA','Ignacio','Miller' +'AAAAAAAALHMCAAAA','Brooke','Nelson' +'AAAAAAAALIOPAAAA','Derek','Allen' +'AAAAAAAALJNCBAAA','George','Gamez' +'AAAAAAAAMDCAAAAA','Louann','Hamel' +'AAAAAAAAMFFLAAAA','Margret','Gray' +'AAAAAAAAMMOBBAAA','Margaret','Smith' +'AAAAAAAANFBDBAAA','Vernice','Fernandez' +'AAAAAAAANGDBBAAA','Carlos','Jewell' +'AAAAAAAANIPLAAAA','Eric','Lawrence' +'AAAAAAAANJAGAAAA','Allen','Hood' +'AAAAAAAANJHCBAAA','Christopher','Schreiber' +'AAAAAAAAOBADBAAA','Elizabeth','Burnham' +'AAAAAAAAOCAJAAAA','Jenna','Staton' +'AAAAAAAAOCDJAAAA','Nina','Sanchez' +'AAAAAAAAOCICAAAA','Zachary','Pennington' +'AAAAAAAAOCLBBAAA','NULL','NULL' +'AAAAAAAAOFLCAAAA','James','Taylor' +'AAAAAAAAOPDLAAAA','Ann','Pence' +'AAAAAAAAPDFBAAAA','Terrance','Banks' +'AAAAAAAAPEHEBAAA','Edith','Molina' +'AAAAAAAAPFCLAAAA','Felicia','Neville' +'AAAAAAAAPJENAAAA','Ashley','Norton' +'AAAAAAAAPKBCBAAA','Andrea','White' +'AAAAAAAAPKIKAAAA','Wendy','Horvath' +'AAAAAAAAPMMBBAAA','Paul','Jordan' +'AAAAAAAAPPIBBAAA','Candice','Lee' +---- TYPES +STRING, STRING, STRING +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q75.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q75.test b/testdata/workloads/tpcds/queries/tpcds-q75.test new file mode 100644 index 0000000..bbedb4c --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q75.test @@ -0,0 +1,173 @@ +==== +---- QUERY: TPCDS-Q75 +WITH all_sales AS ( + SELECT d_year + ,i_brand_id + ,i_class_id + ,i_category_id + ,i_manufact_id + ,SUM(sales_cnt) AS sales_cnt + ,SUM(sales_amt) AS sales_amt + FROM (SELECT d_year + ,i_brand_id + ,i_class_id + ,i_category_id + ,i_manufact_id + ,cs_quantity - COALESCE(cr_return_quantity,0) AS sales_cnt + ,cs_ext_sales_price - COALESCE(cr_return_amount,0.0) AS sales_amt + FROM catalog_sales JOIN item ON i_item_sk=cs_item_sk + JOIN date_dim ON d_date_sk=cs_sold_date_sk + LEFT JOIN catalog_returns ON (cs_order_number=cr_order_number + AND cs_item_sk=cr_item_sk) + WHERE i_category='Books' + UNION + SELECT d_year + ,i_brand_id + ,i_class_id + ,i_category_id + ,i_manufact_id + ,ss_quantity - COALESCE(sr_return_quantity,0) AS sales_cnt + ,ss_ext_sales_price - COALESCE(sr_return_amt,0.0) AS sales_amt + FROM store_sales JOIN item ON i_item_sk=ss_item_sk + JOIN date_dim ON d_date_sk=ss_sold_date_sk + LEFT JOIN store_returns ON (ss_ticket_number=sr_ticket_number + AND ss_item_sk=sr_item_sk) + WHERE i_category='Books' + UNION + SELECT d_year + ,i_brand_id + ,i_class_id + ,i_category_id + ,i_manufact_id + ,ws_quantity - COALESCE(wr_return_quantity,0) AS sales_cnt + ,ws_ext_sales_price - COALESCE(wr_return_amt,0.0) AS sales_amt + FROM web_sales JOIN item ON i_item_sk=ws_item_sk + JOIN date_dim ON d_date_sk=ws_sold_date_sk + LEFT JOIN web_returns ON (ws_order_number=wr_order_number + AND ws_item_sk=wr_item_sk) + WHERE i_category='Books') sales_detail + GROUP BY d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id) + SELECT prev_yr.d_year AS prev_year + ,curr_yr.d_year AS year + ,curr_yr.i_brand_id + ,curr_yr.i_class_id + ,curr_yr.i_category_id + ,curr_yr.i_manufact_id + ,prev_yr.sales_cnt AS prev_yr_cnt + ,curr_yr.sales_cnt AS curr_yr_cnt + ,curr_yr.sales_cnt-prev_yr.sales_cnt AS sales_cnt_diff + ,curr_yr.sales_amt-prev_yr.sales_amt AS sales_amt_diff + FROM all_sales curr_yr, all_sales prev_yr + WHERE curr_yr.i_brand_id=prev_yr.i_brand_id + AND curr_yr.i_class_id=prev_yr.i_class_id + AND curr_yr.i_category_id=prev_yr.i_category_id + AND curr_yr.i_manufact_id=prev_yr.i_manufact_id + AND curr_yr.d_year=2002 + AND prev_yr.d_year=2002-1 + AND CAST(curr_yr.sales_cnt AS DECIMAL(17,2))/CAST(prev_yr.sales_cnt AS DECIMAL(17,2))<0.9 + ORDER BY sales_cnt_diff, sales_amt_diff + limit 100; +---- RESULTS +2001,2002,9011004,11,9,730,6136,3927,-2209,-64592.86 +2001,2002,9004008,4,9,454,6130,4012,-2118,-137401.99 +2001,2002,9006010,6,9,373,6584,4510,-2074,-56824.27 +2001,2002,9003002,3,9,331,6314,4249,-2065,-162310.87 +2001,2002,9016002,16,9,761,6098,4109,-1989,-75674.23 +2001,2002,2001001,7,9,85,5689,3750,-1939,-79485.07 +2001,2002,9011010,11,9,254,6635,4698,-1937,-108841.59 +2001,2002,9003002,3,9,1000,6152,4220,-1932,-51341.52 +2001,2002,9009010,1,9,311,6980,5065,-1915,-166624.10 +2001,2002,9003004,3,9,25,6382,4612,-1770,-109080.80 +2001,2002,9008008,8,9,322,5391,3664,-1727,-58283.91 +2001,2002,3004001,1,9,89,6307,4616,-1691,-105539.10 +2001,2002,9014004,12,9,423,6427,4742,-1685,-66528.45 +2001,2002,1001001,5,9,172,5642,3998,-1644,-65808.52 +2001,2002,8007009,9,9,322,5493,3906,-1587,-31458.78 +2001,2002,9015002,15,9,303,5722,4211,-1511,-81847.95 +2001,2002,8015003,4,9,289,6413,4905,-1508,-140884.23 +2001,2002,9002008,2,9,202,5758,4287,-1471,-27415.18 +2001,2002,9004002,4,9,55,5596,4196,-1400,-277.92 +2001,2002,9010002,10,9,432,5153,3767,-1386,-56643.31 +2001,2002,9015003,15,9,278,5120,3745,-1375,-61503.40 +2001,2002,5001001,10,9,136,5271,3918,-1353,-52691.74 +2001,2002,9007002,7,9,207,6416,5088,-1328,-28312.29 +2001,2002,9016004,8,9,114,5560,4263,-1297,-65169.90 +2001,2002,9007004,7,9,968,5625,4330,-1295,-86120.30 +2001,2002,9008008,8,9,535,6155,4861,-1294,-66647.47 +2001,2002,3003001,1,9,175,6170,4892,-1278,-72260.64 +2001,2002,9010004,3,9,948,5640,4387,-1253,-34985.51 +2001,2002,9012008,12,9,249,5254,4002,-1252,-15484.88 +2001,2002,9015008,15,9,281,5558,4315,-1243,10022.91 +2001,2002,9006004,6,9,620,4822,3594,-1228,-19309.88 +2001,2002,9010008,10,9,171,6500,5279,-1221,-73654.23 +2001,2002,9004008,4,9,256,5366,4164,-1202,-93628.46 +2001,2002,9008008,8,9,82,6042,4852,-1190,-78558.05 +2001,2002,9014010,2,9,727,6579,5396,-1183,-45368.94 +2001,2002,10002014,1,9,12,5662,4515,-1147,-47170.70 +2001,2002,9005002,5,9,105,5650,4508,-1142,-75927.61 +2001,2002,1001001,1,9,183,5538,4417,-1121,-34086.49 +2001,2002,8015005,11,9,898,5677,4557,-1120,-49916.13 +2001,2002,9016010,16,9,662,5306,4197,-1109,-29664.55 +2001,2002,2001001,1,9,151,6309,5200,-1109,-13881.98 +2001,2002,7007001,12,9,221,6250,5147,-1103,8445.22 +2001,2002,9010008,10,9,106,5739,4637,-1102,-39967.26 +2001,2002,5001001,6,9,201,5714,4615,-1099,-2073.61 +2001,2002,9002004,2,9,578,5976,4881,-1095,-26222.70 +2001,2002,9013010,12,9,28,5672,4579,-1093,-61365.10 +2001,2002,9003002,3,9,238,5784,4694,-1090,-52094.93 +2001,2002,9012010,12,9,4,6214,5128,-1086,-41244.90 +2001,2002,4003001,6,9,356,5890,4815,-1075,-19594.68 +2001,2002,9009002,9,9,633,5839,4786,-1053,-40163.90 +2001,2002,9006010,6,9,100,5322,4271,-1051,-65656.49 +2001,2002,9003002,3,9,130,5708,4658,-1050,-121435.05 +2001,2002,9011004,3,9,115,5101,4064,-1037,-38404.07 +2001,2002,9006008,6,9,156,5824,4793,-1031,-16295.15 +2001,2002,4004001,10,9,105,5560,4533,-1027,-29705.36 +2001,2002,9009008,9,9,3,5731,4718,-1013,-62660.48 +2001,2002,10005001,5,9,65,5890,4879,-1011,-57577.01 +2001,2002,9007008,7,9,122,6085,5077,-1008,-108804.86 +2001,2002,9001008,1,9,258,5714,4706,-1008,-73311.07 +2001,2002,9003008,3,9,382,5145,4143,-1002,-23005.48 +2001,2002,2001001,1,9,177,5711,4718,-993,-45422.40 +2001,2002,8002001,13,9,268,5521,4545,-976,-50681.49 +2001,2002,9010008,10,9,9,5549,4578,-971,45104.79 +2001,2002,5002001,2,9,230,5856,4887,-969,-25537.87 +2001,2002,10015001,1,9,399,6188,5225,-963,-76365.39 +2001,2002,9010004,10,9,110,5035,4082,-953,-81282.12 +2001,2002,9013010,3,9,235,5771,4821,-950,-81516.67 +2001,2002,9003004,3,9,311,5414,4467,-947,-19498.98 +2001,2002,9007008,7,9,766,5412,4470,-942,-43150.76 +2001,2002,3003001,9,9,210,5703,4761,-942,-22794.72 +2001,2002,9010008,10,9,621,5897,4955,-942,-1466.75 +2001,2002,4001001,15,9,42,5385,4444,-941,-77081.27 +2001,2002,9012010,12,9,268,5363,4425,-938,-13775.61 +2001,2002,4002001,2,9,891,6168,5233,-935,-54473.40 +2001,2002,10011013,11,9,123,6141,5207,-934,-77290.63 +2001,2002,9005002,5,9,379,5673,4742,-931,-66550.24 +2001,2002,9005002,5,9,35,6085,5166,-919,-23898.75 +2001,2002,9014008,14,9,904,5637,4725,-912,-54696.64 +2001,2002,9010002,10,9,351,5553,4668,-885,-58399.99 +2001,2002,9002008,2,9,603,5399,4518,-881,-8944.74 +2001,2002,9005002,5,9,226,6438,5559,-879,-78309.56 +2001,2002,9016008,16,9,134,5482,4617,-865,-93343.25 +2001,2002,7004007,2,9,248,5385,4527,-858,-59997.87 +2001,2002,9002002,2,9,556,5690,4832,-858,2965.47 +2001,2002,6012003,9,9,48,5468,4620,-848,17912.88 +2001,2002,9013010,13,9,288,5473,4631,-842,22917.88 +2001,2002,9009008,9,9,958,5837,5002,-835,-63092.62 +2001,2002,9015008,15,9,384,5348,4519,-829,-29937.40 +2001,2002,1003001,3,9,242,5504,4697,-807,-10853.56 +2001,2002,9011002,11,9,372,5085,4279,-806,-78551.75 +2001,2002,9015002,15,9,224,5915,5112,-803,-46136.85 +2001,2002,9015002,15,9,531,5831,5029,-802,-10846.84 +2001,2002,2004001,16,9,443,5269,4475,-794,-28869.68 +2001,2002,9003010,3,9,269,6297,5505,-792,-53827.19 +2001,2002,9007002,7,9,306,5398,4608,-790,-67953.64 +2001,2002,9012004,12,9,808,5796,5006,-790,-43025.36 +2001,2002,6015001,15,9,543,5041,4252,-789,3631.40 +2001,2002,9004012,4,9,621,5974,5186,-788,52156.56 +2001,2002,9010008,10,9,284,5382,4597,-785,22162.92 +2001,2002,9011002,11,9,42,5750,4968,-782,-87125.33 +---- TYPES +INT, INT, INT, INT, INT, INT, BIGINT, BIGINT, BIGINT, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q76.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q76.test b/testdata/workloads/tpcds/queries/tpcds-q76.test new file mode 100644 index 0000000..7631f23 --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q76.test @@ -0,0 +1,127 @@ +==== +---- QUERY: TPCDS-Q76 +select channel, col_name, d_year, d_qoy, i_category, COUNT(*) sales_cnt, SUM(ext_sales_price) sales_amt FROM ( + SELECT 'store' as channel, 'ss_store_sk' col_name, d_year, d_qoy, i_category, ss_ext_sales_price ext_sales_price + FROM store_sales, item, date_dim + WHERE ss_store_sk IS NULL + AND ss_sold_date_sk=d_date_sk + AND ss_item_sk=i_item_sk + UNION ALL + SELECT 'web' as channel, 'ws_ship_customer_sk' col_name, d_year, d_qoy, i_category, ws_ext_sales_price ext_sales_price + FROM web_sales, item, date_dim + WHERE ws_ship_customer_sk IS NULL + AND ws_sold_date_sk=d_date_sk + AND ws_item_sk=i_item_sk + UNION ALL + SELECT 'catalog' as channel, 'cs_ship_addr_sk' col_name, d_year, d_qoy, i_category, cs_ext_sales_price ext_sales_price + FROM catalog_sales, item, date_dim + WHERE cs_ship_addr_sk IS NULL + AND cs_sold_date_sk=d_date_sk + AND cs_item_sk=i_item_sk) foo +GROUP BY channel, col_name, d_year, d_qoy, i_category +ORDER BY channel, col_name, d_year, d_qoy, i_category +limit 100; +---- RESULTS +'catalog','cs_ship_addr_sk',1998,1,'Books',14,24660.12 +'catalog','cs_ship_addr_sk',1998,1,'Children',5,5064.75 +'catalog','cs_ship_addr_sk',1998,1,'Electronics',13,31709.80 +'catalog','cs_ship_addr_sk',1998,1,'Home',12,11651.18 +'catalog','cs_ship_addr_sk',1998,1,'Jewelry',13,13102.23 +'catalog','cs_ship_addr_sk',1998,1,'Men',11,17458.37 +'catalog','cs_ship_addr_sk',1998,1,'Music',13,6741.65 +'catalog','cs_ship_addr_sk',1998,1,'Shoes',9,24531.24 +'catalog','cs_ship_addr_sk',1998,1,'Sports',9,19244.50 +'catalog','cs_ship_addr_sk',1998,1,'Women',13,24864.50 +'catalog','cs_ship_addr_sk',1998,2,'Books',14,16581.16 +'catalog','cs_ship_addr_sk',1998,2,'Children',12,26195.71 +'catalog','cs_ship_addr_sk',1998,2,'Electronics',9,7133.74 +'catalog','cs_ship_addr_sk',1998,2,'Home',15,44228.54 +'catalog','cs_ship_addr_sk',1998,2,'Jewelry',11,6804.54 +'catalog','cs_ship_addr_sk',1998,2,'Men',11,11700.11 +'catalog','cs_ship_addr_sk',1998,2,'Music',10,32062.82 +'catalog','cs_ship_addr_sk',1998,2,'Shoes',7,4682.58 +'catalog','cs_ship_addr_sk',1998,2,'Sports',7,5470.29 +'catalog','cs_ship_addr_sk',1998,2,'Women',5,14928.09 +'catalog','cs_ship_addr_sk',1998,2,'NULL',1,1535.61 +'catalog','cs_ship_addr_sk',1998,3,'Books',20,22224.09 +'catalog','cs_ship_addr_sk',1998,3,'Children',19,18667.83 +'catalog','cs_ship_addr_sk',1998,3,'Electronics',15,21854.97 +'catalog','cs_ship_addr_sk',1998,3,'Home',22,24690.28 +'catalog','cs_ship_addr_sk',1998,3,'Jewelry',14,4586.41 +'catalog','cs_ship_addr_sk',1998,3,'Men',20,30700.69 +'catalog','cs_ship_addr_sk',1998,3,'Music',23,44816.68 +'catalog','cs_ship_addr_sk',1998,3,'Shoes',20,33458.73 +'catalog','cs_ship_addr_sk',1998,3,'Sports',25,35679.92 +'catalog','cs_ship_addr_sk',1998,3,'Women',22,48029.28 +'catalog','cs_ship_addr_sk',1998,4,'Books',28,46283.99 +'catalog','cs_ship_addr_sk',1998,4,'Children',40,57305.90 +'catalog','cs_ship_addr_sk',1998,4,'Electronics',29,42656.07 +'catalog','cs_ship_addr_sk',1998,4,'Home',29,29708.36 +'catalog','cs_ship_addr_sk',1998,4,'Jewelry',29,24689.26 +'catalog','cs_ship_addr_sk',1998,4,'Men',36,64378.63 +'catalog','cs_ship_addr_sk',1998,4,'Music',35,51308.03 +'catalog','cs_ship_addr_sk',1998,4,'Shoes',27,10881.49 +'catalog','cs_ship_addr_sk',1998,4,'Sports',33,33380.56 +'catalog','cs_ship_addr_sk',1998,4,'Women',35,14347.96 +'catalog','cs_ship_addr_sk',1998,4,'NULL',2,839.65 +'catalog','cs_ship_addr_sk',1999,1,'Books',14,35278.57 +'catalog','cs_ship_addr_sk',1999,1,'Children',13,17183.17 +'catalog','cs_ship_addr_sk',1999,1,'Electronics',4,153.18 +'catalog','cs_ship_addr_sk',1999,1,'Home',11,19895.88 +'catalog','cs_ship_addr_sk',1999,1,'Jewelry',9,1341.90 +'catalog','cs_ship_addr_sk',1999,1,'Men',9,6625.86 +'catalog','cs_ship_addr_sk',1999,1,'Music',10,27861.23 +'catalog','cs_ship_addr_sk',1999,1,'Shoes',15,5365.20 +'catalog','cs_ship_addr_sk',1999,1,'Sports',9,10745.83 +'catalog','cs_ship_addr_sk',1999,1,'Women',11,15388.74 +'catalog','cs_ship_addr_sk',1999,2,'Books',6,3659.91 +'catalog','cs_ship_addr_sk',1999,2,'Children',13,6066.50 +'catalog','cs_ship_addr_sk',1999,2,'Electronics',10,14915.50 +'catalog','cs_ship_addr_sk',1999,2,'Home',13,12035.96 +'catalog','cs_ship_addr_sk',1999,2,'Jewelry',6,23975.00 +'catalog','cs_ship_addr_sk',1999,2,'Men',13,16414.16 +'catalog','cs_ship_addr_sk',1999,2,'Music',13,6061.53 +'catalog','cs_ship_addr_sk',1999,2,'Shoes',10,12500.71 +'catalog','cs_ship_addr_sk',1999,2,'Sports',11,2181.52 +'catalog','cs_ship_addr_sk',1999,2,'Women',11,16390.31 +'catalog','cs_ship_addr_sk',1999,3,'Books',18,29809.59 +'catalog','cs_ship_addr_sk',1999,3,'Children',16,12816.74 +'catalog','cs_ship_addr_sk',1999,3,'Electronics',17,36415.09 +'catalog','cs_ship_addr_sk',1999,3,'Home',15,19664.04 +'catalog','cs_ship_addr_sk',1999,3,'Jewelry',20,23257.68 +'catalog','cs_ship_addr_sk',1999,3,'Men',19,20150.72 +'catalog','cs_ship_addr_sk',1999,3,'Music',20,12062.24 +'catalog','cs_ship_addr_sk',1999,3,'Shoes',13,14924.61 +'catalog','cs_ship_addr_sk',1999,3,'Sports',24,56456.44 +'catalog','cs_ship_addr_sk',1999,3,'Women',17,6958.54 +'catalog','cs_ship_addr_sk',1999,4,'Books',27,50890.95 +'catalog','cs_ship_addr_sk',1999,4,'Children',36,30608.13 +'catalog','cs_ship_addr_sk',1999,4,'Electronics',28,59307.37 +'catalog','cs_ship_addr_sk',1999,4,'Home',46,51713.78 +'catalog','cs_ship_addr_sk',1999,4,'Jewelry',34,44238.07 +'catalog','cs_ship_addr_sk',1999,4,'Men',19,17925.34 +'catalog','cs_ship_addr_sk',1999,4,'Music',28,14816.79 +'catalog','cs_ship_addr_sk',1999,4,'Shoes',35,48226.50 +'catalog','cs_ship_addr_sk',1999,4,'Sports',32,35012.19 +'catalog','cs_ship_addr_sk',1999,4,'Women',30,24033.32 +'catalog','cs_ship_addr_sk',1999,4,'NULL',1,NULL +'catalog','cs_ship_addr_sk',2000,1,'Books',15,50786.51 +'catalog','cs_ship_addr_sk',2000,1,'Children',6,5623.72 +'catalog','cs_ship_addr_sk',2000,1,'Electronics',13,35869.18 +'catalog','cs_ship_addr_sk',2000,1,'Home',14,11212.87 +'catalog','cs_ship_addr_sk',2000,1,'Jewelry',9,16751.45 +'catalog','cs_ship_addr_sk',2000,1,'Men',14,34465.53 +'catalog','cs_ship_addr_sk',2000,1,'Music',9,23103.03 +'catalog','cs_ship_addr_sk',2000,1,'Shoes',9,2854.29 +'catalog','cs_ship_addr_sk',2000,1,'Sports',5,159.04 +'catalog','cs_ship_addr_sk',2000,1,'Women',8,8663.29 +'catalog','cs_ship_addr_sk',2000,2,'Books',14,18943.05 +'catalog','cs_ship_addr_sk',2000,2,'Children',8,7309.74 +'catalog','cs_ship_addr_sk',2000,2,'Electronics',9,11856.27 +'catalog','cs_ship_addr_sk',2000,2,'Home',8,10107.78 +'catalog','cs_ship_addr_sk',2000,2,'Jewelry',9,19113.02 +'catalog','cs_ship_addr_sk',2000,2,'Men',12,41513.90 +'catalog','cs_ship_addr_sk',2000,2,'Music',10,7181.12 +---- TYPES +STRING, STRING, INT, INT, STRING, BIGINT, DECIMAL +==== http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/f05bd241/testdata/workloads/tpcds/queries/tpcds-q77a.test ---------------------------------------------------------------------- diff --git a/testdata/workloads/tpcds/queries/tpcds-q77a.test b/testdata/workloads/tpcds/queries/tpcds-q77a.test new file mode 100644 index 0000000..f5627ff --- /dev/null +++ b/testdata/workloads/tpcds/queries/tpcds-q77a.test @@ -0,0 +1,167 @@ +==== +---- QUERY: TPCDS-Q77A +-- RESULT MISMATCH FROM ORIGINAL +-- FIXED. TAKE ACTUAL RESULT AS EXPECTED +with ss as + (select s_store_sk, + sum(ss_ext_sales_price) as sales, + sum(ss_net_profit) as profit + from store_sales, + date_dim, + store + where ss_sold_date_sk = d_date_sk + and cast(d_date as timestamp) between cast('2000-08-23' as timestamp) + and (cast('2000-08-23' as timestamp) + interval 30 days) + and ss_store_sk = s_store_sk + group by s_store_sk) + , + sr as + (select s_store_sk, + sum(sr_return_amt) as return_amt, + sum(sr_net_loss) as profit_loss + from store_returns, + date_dim, + store + where sr_returned_date_sk = d_date_sk + and cast(d_date as timestamp) between cast('2000-08-23' as timestamp) + and (cast('2000-08-23' as timestamp) + interval 30 days) + and sr_store_sk = s_store_sk + group by s_store_sk), + cs as + (select cs_call_center_sk, + sum(cs_ext_sales_price) as sales, + sum(cs_net_profit) as profit + from catalog_sales, + date_dim + where cs_sold_date_sk = d_date_sk + and cast(d_date as timestamp) between cast('2000-08-23' as timestamp) + and (cast('2000-08-23' as timestamp) + interval 30 days) + group by cs_call_center_sk + ), + cr as + (select cr_call_center_sk, + sum(cr_return_amount) as return_amt, + sum(cr_net_loss) as profit_loss + from catalog_returns, + date_dim + where cr_returned_date_sk = d_date_sk + and cast(d_date as timestamp) between cast('2000-08-23' as timestamp) + and (cast('2000-08-23' as timestamp) + interval 30 days) + group by cr_call_center_sk + ), + ws as + ( select wp_web_page_sk, + sum(ws_ext_sales_price) as sales, + sum(ws_net_profit) as profit + from web_sales, + date_dim, + web_page + where ws_sold_date_sk = d_date_sk + and cast(d_date as timestamp) between cast('2000-08-23' as timestamp) + and (cast('2000-08-23' as timestamp) + interval 30 days) + and ws_web_page_sk = wp_web_page_sk + group by wp_web_page_sk), + wr as + (select wp_web_page_sk, + sum(wr_return_amt) as return_amt, + sum(wr_net_loss) as profit_loss + from web_returns, + date_dim, + web_page + where wr_returned_date_sk = d_date_sk + and cast(d_date as timestamp) between cast('2000-08-23' as timestamp) + and (cast('2000-08-23' as timestamp) + interval 30 days) + and wr_web_page_sk = wp_web_page_sk + group by wp_web_page_sk) + , + results as + (select channel + , id + , sum(sales) as sales + , sum(return_amt) as return_amt + , sum(profit) as profit + from + (select 'store channel' as channel + , ss.s_store_sk as id + , sales + , coalesce(return_amt, 0) as return_amt + , (profit - coalesce(profit_loss,0)) as profit + from ss left join sr + on ss.s_store_sk = sr.s_store_sk + union all + select 'catalog channel' as channel + , cs_call_center_sk as id + , sales + , return_amt + , (profit - profit_loss) as profit + from cs + , cr + union all + select 'web channel' as channel + , ws.wp_web_page_sk as id + , sales + , coalesce(return_amt, 0) return_amt + , (profit - coalesce(profit_loss,0)) as profit + from ws left join wr + on ws.wp_web_page_sk = wr.wp_web_page_sk + ) x + group by channel, id ) + + select * + from ( + select channel, id, sales, return_amt, profit from results + union + select channel, NULL AS id, sum(sales) as sales, sum(return_amt) as return_amt, sum(profit) as profit from results group by channel + union + select NULL AS channel, NULL AS id, sum(sales) as sales, sum(return_amt) as return_amt, sum(profit) as profit from results +) foo +order by channel, id + limit 100; +---- RESULTS +'catalog channel',1,132885061.65,2050279.74,-12674076.58 +'catalog channel',2,140503047.65,2050279.74,-14906564.08 +'catalog channel',5,130483795.90,2050279.74,-13798294.43 +'catalog channel',NULL,538912.55,2050279.74,-1383554.73 +'catalog channel',NULL,404410817.75,8201118.96,-42762489.82 +'store channel',1,20390161.35,562762.31,-9133254.67 +'store channel',2,19807085.95,539649.43,-8817821.00 +'store channel',4,19599593.20,557973.00,-8389920.41 +'store channel',7,19480205.51,520479.41,-8861241.78 +'store channel',8,18636331.60,472731.69,-8409599.72 +'store channel',10,19335995.71,519959.15,-8771453.62 +'store channel',NULL,117249373.32,3173554.99,-52383291.20 +'web channel',1,1226811.57,28406.98,-227375.53 +'web channel',2,1191229.91,99179.48,-264992.86 +'web channel',5,1467083.19,21625.36,-147366.78 +'web channel',7,1343208.21,67708.76,-200969.21 +'web channel',8,1262065.97,46749.46,-271001.70 +'web channel',11,1425934.76,10034.84,-84693.54 +'web channel',13,1335813.60,62142.91,-218022.02 +'web channel',14,1469352.58,50742.65,-197789.09 +'web channel',17,1219451.02,28732.85,-205497.30 +'web channel',19,1343058.55,24108.59,-175397.06 +'web channel',20,1511303.97,42538.28,-89439.28 +'web channel',23,1409483.07,37116.42,-89855.78 +'web channel',25,1370755.17,48916.38,-207512.02 +'web channel',26,1465712.89,48072.56,-157007.72 +'web channel',29,1407813.82,19233.11,-188381.47 +'web channel',31,1369226.19,25494.42,-180972.91 +'web channel',32,1166947.23,50731.53,-189061.60 +'web channel',35,1400811.57,22363.43,-189390.67 +'web channel',37,1407716.73,32534.27,-127244.28 +'web channel',38,1444241.42,41815.25,-135372.36 +'web channel',41,1492530.29,19599.96,-101110.49 +'web channel',43,1343104.79,41175.01,-227340.10 +'web channel',44,1416507.16,37134.99,-274620.10 +'web channel',47,1449718.94,15989.92,-105473.72 +'web channel',49,1414898.83,45004.31,-146020.31 +'web channel',50,1319375.84,28284.26,-151036.44 +'web channel',53,1389137.89,24570.34,-120694.61 +'web channel',55,1463362.30,38157.61,-154431.83 +'web channel',56,1355553.42,46633.14,-164174.45 +'web channel',59,1395251.52,10683.93,-213197.75 +'web channel',NULL,41277462.40,1115481.00,-5205442.98 +'NULL',NULL,562937653.47,12490154.95,-100351224.00 +---- TYPES +STRING, INT, DECIMAL, DECIMAL, DECIMAL +====
