Yingyi Bu has submitted this change and it was merged. Change subject: Add tpcds queries q1, q9, q85, q90, q92 ......................................................................
Add tpcds queries q1, q9, q85, q90, q92 Change-Id: I5956f424677dbf41bcdcaa771853bde50397e704 Reviewed-on: https://asterix-gerrit.ics.uci.edu/1332 Sonar-Qube: Jenkins <[email protected]> Tested-by: Jenkins <[email protected]> Integration-Tests: Jenkins <[email protected]> Reviewed-by: Yingyi Bu <[email protected]> --- M asterixdb/asterix-app/data/tpcds/customer_address.csv M asterixdb/asterix-app/data/tpcds/customer_demographics.csv M asterixdb/asterix-app/data/tpcds/date_dim.csv M asterixdb/asterix-app/data/tpcds/item.csv M asterixdb/asterix-app/data/tpcds/time_dim.csv M asterixdb/asterix-app/data/tpcds/web_page.csv M asterixdb/asterix-app/data/tpcds/web_returns.csv M asterixdb/asterix-app/data/tpcds/web_sales.csv A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q01/q01.1.adm A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q09/q09.1.adm A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q85/q85.1.adm A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q90/q90.1.adm A asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q92/q92.1.adm M asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q94/q94.1.adm M asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q95/q95.1.adm M asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.1.adm M asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml 32 files changed, 1,143 insertions(+), 13 deletions(-) Approvals: Yingyi Bu: Looks good to me, approved Jenkins: Verified; No violations found; Verified diff --git a/asterixdb/asterix-app/data/tpcds/customer_address.csv b/asterixdb/asterix-app/data/tpcds/customer_address.csv index c5185f3..4be1187 100644 --- a/asterixdb/asterix-app/data/tpcds/customer_address.csv +++ b/asterixdb/asterix-app/data/tpcds/customer_address.csv @@ -7,4 +7,5 @@ 7|AAAAAAAAHAAAAAAA||Hill 7th|Road|Suite U|Farmington|||39145|United States|-7|| 8|AAAAAAAAIAAAAAAA|875|Lincoln |Ct.|Suite Y|Union|Bledsoe County|WA|35708|United States|-5|apartment| 9|AAAAAAAAJAAAAAAA|819|1st Laurel|Ave|Suite 70|New Hope|Perry County|IL|35709|United States|-6|condo| -10|AAAAAAAAKAAAAAAA|851|Woodland Poplar|ST|Suite Y|Martinsville|Taos County|TX|90419|United States|-9|condo| \ No newline at end of file +10|AAAAAAAAKAAAAAAA|851|Woodland Poplar|ST|Suite Y|Martinsville|Taos County|TX|90419|United States|-9|condo| +11|AAAAAAAAMCCAAAAA|762|Smith |Drive|Suite N|Oakland|Curry County|OR|99843|United States|-8|single family| \ No newline at end of file diff --git a/asterixdb/asterix-app/data/tpcds/customer_demographics.csv b/asterixdb/asterix-app/data/tpcds/customer_demographics.csv index 32ae389..e841a1c 100644 --- a/asterixdb/asterix-app/data/tpcds/customer_demographics.csv +++ b/asterixdb/asterix-app/data/tpcds/customer_demographics.csv @@ -3,4 +3,5 @@ 3|F|U|Unknown|500|Good|0|0|0| 4|F|U|Secondary|1500|Good|0|0|0| 5|F|D|4 yr Degree|3500|Good|0|0|0| -6|M|W|Advanced Degree|7500|Good|0|0|0| \ No newline at end of file +6|M|W|Advanced Degree|7500|Good|0|0|0| +7|M|M|4 yr Degree|3500|Good|0|0|0| \ No newline at end of file diff --git a/asterixdb/asterix-app/data/tpcds/date_dim.csv b/asterixdb/asterix-app/data/tpcds/date_dim.csv index 3e5647d..2252fbc 100644 --- a/asterixdb/asterix-app/data/tpcds/date_dim.csv +++ b/asterixdb/asterix-app/data/tpcds/date_dim.csv @@ -10,4 +10,5 @@ 2415031|AAAAAAAAHLJNECAA|1998-03-11|0|2|1|2003|3|1|11|1|1900|1|2|Wednesday|1900Q1|N|N|N|2415021|2415020|2414666|2414939|N|N|N|N|N| 2415032|AAAAAAAADLJNECAA|2001-01-12|1197|53|1|2001|6|12|7|1|1900|1|1|Saturday|1900Q1|N|Y|N|2415021|2415020|2414662|2414935|N|N|N|N|N| 2415033|AAAAAAAACLJNECAA|1999-06-29|0|1|1|1999|5|4|25|1|1900|1|1|Friday|1900Q1|N|Y|N|2415021|2415020|2414661|2414934|N|N|N|N|N| -2415034|AAAAAAAAJNJNECAA|1900-02-14|1|7|1|1900|2|2|14|1|1900|1|7|Tuesday|1900Q1|N|N|N|2415052|2415082|2414700|2414973|N|N|N|N|N| \ No newline at end of file +2415034|AAAAAAAAJNJNECAA|1900-02-14|1|7|1|1900|2|2|14|1|1900|1|7|Tuesday|1900Q1|N|N|N|2415052|2415082|2414700|2414973|N|N|N|N|N| +2415035|AAAAAAAAOFGGFCAA|1998-06-15|1183|5146|395|1998|3|8|12|3|1998|395|5146|Wednesday|1998Q3|N|N|N|2451027|2451238|2450673|2450947|N|N|N|N|N| \ No newline at end of file diff --git a/asterixdb/asterix-app/data/tpcds/item.csv b/asterixdb/asterix-app/data/tpcds/item.csv index 261888a..6c0294e 100644 --- a/asterixdb/asterix-app/data/tpcds/item.csv +++ b/asterixdb/asterix-app/data/tpcds/item.csv @@ -20,4 +20,6 @@ 20|AAAAAAAAEBAAAAAA|1997-10-27|2000-10-26|Legal, foreign days know losses; briefly equivalent arguments will expect today. New, front grounds look hot, other aspects. Actually national husbands show usually of course other stations; huge, k|29.35|18.78|1003001|exportiamalg #1|3|maternity|1|Women|995|antin stn st|extra large|97245417ivory0043452|tan|Gram|Unknown|21|barable| 21|AAAAAAAAEBAAAAAA|2000-10-27||Legal, foreign days know losses; briefly equivalent arguments will expect today. New, front grounds look hot, other aspects. Actually national husbands show usually of course other stations; huge, k|10.71|8.46|6016006|corpbrand #6|3|consignment|6|Jewelry|995|antin stn st|N/A|1050463678plum205437|sienna|Pallet|Unknown|4|oughtable| 22|AAAAAAAAGBAAAAAA|1997-10-27|1999-10-27|Members endure already near additional details. Ministers should ignore whole times. Bright, brief beliefs become highly bright men; o|7.11|3.27|10005006|scholarunivamalg #6|5|karoke|10|Electronics|169|n stcallyought|N/A|4900684033pink844758|sandy|Each|Unknown|40|ableable| -23|AAAAAAAAGBAAAAAA|1999-10-28|2001-10-26|Below long minutes make primarily by a months. Secure effects get much upo|3.73|1.41|10005006|scholarunivamalg #7|5|karoke|10|Electronics|260|n stcallyought|N/A|569seashell149755477|papaya|Bunch|Unknown|44|priable| \ No newline at end of file +23|AAAAAAAAGBAAAAAA|1999-10-28|2001-10-26|Below long minutes make primarily by a months. Secure effects get much upo|3.73|1.41|10005006|scholarunivamalg #7|5|karoke|10|Electronics|260|n stcallyought|N/A|569seashell149755477|papaya|Bunch|Unknown|44|priable| +24|AAAAAAAALFAAAAAA|1997-10-27||True potatoes sound equal heads|0.64|0.28|10006007|corpunivamalg #7|6|musical|10|Electronics|269|callyationought|N/A|2925royal36510681005|lace|Lb|Unknown|74|oughtn st| +25|AAAAAAAAOCBAAAAA|2000-10-27||African markets could hit enough popular members. Black, widespread brothers keep quiet candidates; simple, special girls ought to want statutory instructions. Severe emp|4.38|2.14|1004001|scholarmaxi #4|4|history|9|Books|286|callyeingable|N/A|4609saddle1108631196|seashell|Bundle|Unknown|22|pribarpri| \ No newline at end of file diff --git a/asterixdb/asterix-app/data/tpcds/time_dim.csv b/asterixdb/asterix-app/data/tpcds/time_dim.csv index bc73d37..289ec64 100644 --- a/asterixdb/asterix-app/data/tpcds/time_dim.csv +++ b/asterixdb/asterix-app/data/tpcds/time_dim.csv @@ -5,4 +5,6 @@ 5|AAAAAAAAMCMKAAAA|44075|12|14|35|PM|first|afternoon|lunch| 6|AAAAAAAAGBFABAAA|66837|18|33|57|PM|second|evening|dinner| 7|AAAAAAAAPIBBBAAA|70030|19|27|10|PM|second|evening|dinner| -8|AAAAAAAAAIBFBAAA|86399|23|59|59|PM|third|evening|| \ No newline at end of file +8|AAAAAAAAAIBFBAAA|86399|23|59|59|PM|third|evening|| +9|AAAAAAAAANOEAAAA|20175|6|36|15|AM|third|night|| +10|AAAAAAAAMCMKAAAA|44075|15|14|35|PM|first|afternoon|lunch| \ No newline at end of file diff --git a/asterixdb/asterix-app/data/tpcds/web_page.csv b/asterixdb/asterix-app/data/tpcds/web_page.csv index 2aceb67..7e09ab1 100644 --- a/asterixdb/asterix-app/data/tpcds/web_page.csv +++ b/asterixdb/asterix-app/data/tpcds/web_page.csv @@ -1,5 +1,5 @@ 1|AAAAAAAABAAAAAAA|9/3/97||2415031|2415030|Y|9|http://www.foo.com|welcome|2531|8|3|4| -2|AAAAAAAACAAAAAAA|9/3/97|9/2/00|2415026|2415025|N||http://www.foo.com|protected|1564|4|3|1| +2|AAAAAAAACAAAAAAA|9/3/97|9/2/00|2415026|2415025|N||http://www.foo.com|protected|5100|4|3|1| 3|AAAAAAAACAAAAAAA|9/3/00||2415030|2415029|N||http://www.foo.com|feedback|1564|4|3|4| 4|AAAAAAAAEAAAAAAA|9/3/97|9/3/99|2415023|2415029|N||http://www.foo.com|general|3732|18|7|1| 5|AAAAAAAAEAAAAAAA|9/4/99|9/2/01|2415025|2415030|N||http://www.foo.com|welcome|3732|18|3|1| diff --git a/asterixdb/asterix-app/data/tpcds/web_returns.csv b/asterixdb/asterix-app/data/tpcds/web_returns.csv index e89a512..f88f1d0 100644 --- a/asterixdb/asterix-app/data/tpcds/web_returns.csv +++ b/asterixdb/asterix-app/data/tpcds/web_returns.csv @@ -3,6 +3,7 @@ 2415028|4|5|10|5|3|4|1|2|3|2|7|8|5|18|1700.08|7.38|89.46|35.63|1342.26|38.57|19.14|24.37|1385.27| 2415027|5|5|1|1|2|10|1|4|2|4|8|4|6|16|1956|156.48|2112.48|22.56|221.76|215.16|504.84|1236|400.8| 2415022|6|10|10|4|4|9|8|6|1|1|10|1|1|3|134.4|0|134.4|78.06|108.78|108.86|15.06|10.48|186.84| +2415035|4|25|10|7|3|11|6|7|3|2|7|8|56|18|1700.08|7.38|89.46|35.63|1342.26|38.57|19.14|24.37|1385.27| 2415025|5|5|8|3|2|2|2|3|3|1|4|7|18|43|520.3|20.81|541.11|54.02|3468.38|504.69|14.36|1.25|3543.21| 2415028|8|1|8|5|2|4|3|6|4|8|1|1|9|4|7.68|2.39|82.07|11.4|54.52|58.16|1.5|20.02|68.31| 2415025|4|8|2|2|5|9|3|2|5|9|2|9|10|28|707|28.28|735.28|16.84|382.2|424.2|248.86|33.94|427.32| @@ -10,4 +11,5 @@ 2415027|8|3|5|3|1|5|9|4|5|4|4|1|12|26|528.84|37.01|565.85|90.97|237.12|449.51|23|56.33|365.1| 2415027|8|10|3|6|1|1|8|6|5|1|10|10|13|16|417.6|25.05|442.65|22.13|539.36|50.11|128.62|238.87|586.54| 2415028||3|4||1||9|1||8|4||14|4|100.87|||11.84||9.39|||| -2415027|4|9|6|1|1|10|2|6|4|6|10|10|15|5|219.85|15.38|235.23|28.94|233.9|147.29|42.81|29.75|278.22| \ No newline at end of file +2415027|4|9|6|1|1|10|2|6|4|6|10|10|15|5|219.85|15.38|235.23|28.94|233.9|147.29|42.81|29.75|278.22| +2415035|8|25|3|7|1|11|8|7|5|1|10|10|42|16|417.6|25.05|442.65|22.13|539.36|50.11|128.62|238.87|586.54| \ No newline at end of file diff --git a/asterixdb/asterix-app/data/tpcds/web_sales.csv b/asterixdb/asterix-app/data/tpcds/web_sales.csv index 63d0514..a945c1c 100644 --- a/asterixdb/asterix-app/data/tpcds/web_sales.csv +++ b/asterixdb/asterix-app/data/tpcds/web_sales.csv @@ -7,10 +7,14 @@ 2415022|7|2415025|4|9|4|1|1|2|4|4|3|10|4|4|8|293|7|15|83.92|174.55|69.82|1570.95|1047.3|1258.8|2618.25|20.94|0|392.7|1047.3|1068.24|1440|1460.94|-211.5| 2415026|5|2415022|4|4|1|2|9|8|6|1|10|7|2|5|7|202|8|16|45.27|128.56|68.13|966.88|1090.08|724.32|2056.96|87.2|0|370.24|1090.08|1177.28|1460.32|1547.52|365.76| 2415024|5|2415027|7|3|1|4|7|5|2|2|10|2|4|2|4|49|9|54|53.45|60.39|26.57|1826.28|1434.78|2886.3|3261.06|48.78|215.21|488.7|1219.57|1268.35|1708.27|1757.05|-1666.73| +2415035|5|2415026|24|4|1|2|9|8|6|1|10|7|2|5|7|202|9|16|45.27|128.56|148.13|66.88|1090.08|724.32|2056.96|87.2|0|370.24|1090.08|1177.28|1460.32|1547.52|365.76| 2415029|6|2415022|4|1|6|1|10|2|4|3|9|5|3|2|4|267|10|47|49.64|68.99|12.41|2659.26|583.27|2333.08|3242.53|52.49|0|1134.58|583.27|635.76|1717.85|1770.34|-1749.81| 2415031|2|2415029|15|5|3|5|7|5|6|5|5|1|2|1|1|221|8|12|7.24|12.16|11.91|3|142.92|86.88|145.92|10|0|29.16|142.92|152.92|172.08|182.08|56.04| -2415024|3|2415024|3|8|1|1|9|3|4|2|1|7|3|5|6|246|12|6|45.21|67.36|30.31|222.3|181.86|271.26|404.16|16.36|0|185.88|181.86|198.22|367.74|384.1|-89.4| +2415024|9|2415024|3|8|1|1|9|3|4|4|1|2|3|5|6|246|12|6|45.21|67.36|30.31|222.3|181.86|271.26|404.16|16.36|0|185.88|181.86|198.22|367.74|384.1|-89.4| 2415024|4|2415024|22|5|6|1|9|7|5|2|5|4|2|2|8|237|10|43|9.62|13.08|9.02|174.58|387.86|413.66|562.44|19.39|0|117.82|387.86|407.25|505.68|525.07|-25.8| +2415035|5|2415026|24|4|1|2|9|8|6|1|10|7|2|5|7|202|8|16|45.27|128.56|68.13|1966.88|1090.08|724.32|2056.96|87.2|0|370.24|1090.08|1177.28|1460.32|1547.52|295.95| 2415029|4|2415024|8|3|6|2|3|2|3|3|7|10|2|1|1|223|14|57|35.95|62.55|43.78|1069.89|2495.46|2049.15|3565.35|224.59|0|962.16|2495.46|2720.05|3457.62|3682.21|446.31| -2415030|4|2415029|1|2|4|5|10|5|1|3|8|1|1|4|7|72|15|93|65.8|152.65|29|11499.45|2697|6119.4|14196.45|215.76|0|7097.76|2697|2912.76|9794.76|10010.52|-3422.4| -2415025|4|2415029|1|2|4|5|2|5|1|3|8|1|1|4|7|72|16|93|65.8|152.65|29|11499.45|2697|6119.4|14196.45|215.76|0|7097.76|2697|2912.76|9794.76|10010.52|-3422.4| \ No newline at end of file +2415030|10|2415029|1|2|4|5|10|5|1|4|8|2|1|4|7|72|15|93|65.8|152.65|29|11499.45|2697|6119.4|14196.45|215.76|0|7097.76|2697|2912.76|9794.76|10010.52|-3422.4| +2415025|10|2415029|1|2|4|5|2|5|1|4|8|2|1|4|7|72|16|93|65.8|152.65|29|11499.45|2697|6119.4|14196.45|215.76|0|7097.76|2697|2912.76|9794.76|10010.52|-3422.4| +2415035|8|2415027|25|6|5|1|8|9|6|2|4|10|5|2|6|185|56|90|72.05|161.39|27.43|12056.4|2468.7|6484.5|14525.1|74.06|0|4647.6|2468.7|2542.76|7116.3|7190.36|-4015.8| +2415035|9|2415024|25|8|1|1|9|3|4|4|1|2|3|5|6|246|42|6|45.21|67.36|130.31|222.3|181.86|271.26|404.16|16.36|0|185.88|181.86|198.22|367.74|384.1|170.59| \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.1.ddl.sqlpp new file mode 100644 index 0000000..46c5073 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.1.ddl.sqlpp @@ -0,0 +1,143 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpcds if exists; +create dataverse tpcds; + +use tpcds; + + +create type tpcds.store_returns_type as + closed { + sr_returned_date_sk : int64?, + sr_return_time_sk : int64?, + sr_item_sk : int64, + sr_customer_sk : int64?, + sr_cdemo_sk : int64?, + sr_hdemo_sk : int64?, + sr_addr_sk : int64?, + sr_store_sk : int64?, + sr_reason_sk : int64?, + sr_ticket_number : int64, + sr_return_quantity : int64?, + sr_return_amt : double, + sr_return_tax : double?, + sr_return_amt_inc_tax : double?, + sr_fee : double?, + sr_return_ship_cost : double?, + sr_refunded_cash : double?, + sr_reversed_charge : double?, + sr_store_credit : double?, + sr_net_loss : double? +} + +create type tpcds.date_dim_type as + closed { + d_date_sk : int64, + d_date_id : string, + d_date : string?, + d_month_seq : int64?, + d_week_seq : int64?, + d_quarter_seq : int64?, + d_year : int64? , + d_dow : int64? , + d_moy : int64?, + d_dom : int64?, + d_qoy : int64?, + d_fy_year : int64?, + d_fy_quarter_seq : int64?, + d_fy_week_seq : int64?, + d_day_name : string?, + d_quarter_name : string?, + d_holiday : string?, + d_weekend : string?, + d_following_holiday : string?, + d_first_dom : int64?, + d_last_dom : int64?, + d_same_day_ly : int64?, + d_same_day_lq : int64?, + d_current_day : string?, + d_current_week : string?, + d_current_month : string?, + d_current_quarter : string?, + d_current_year : string? +} + +create type tpcds.store_type as + closed { + s_store_sk : int64, + s_store_id : string, + s_rec_start_date : string?, + s_rec_end_date : string?, + s_closed_date_sk : int64?, + s_store_name : string?, + s_number_employees : int64?, + s_floor_space : int64?, + s_hours : string?, + s_manager : string?, + s_market_id : int64?, + s_geography_class : string?, + s_market_desc : string?, + s_market_manager : string?, + s_division_id : int64?, + s_division_name : string?, + s_company_id : int64?, + s_company_name : string?, + s_street_number : string?, + s_street_name : string?, + s_street_type : string?, + s_suite_number : string?, + s_city : string?, + s_county : string?, + s_state : string?, + s_zip : string?, + s_country : string?, + s_gmt_offset : double?, + s_tax_precentage : double? +} + +create type tpcds.customer_type as + closed { + c_customer_sk : int64, + c_customer_id : string, + c_current_cdemo_sk : int64?, + c_current_hdemo_sk : int64?, + c_current_addr_sk : int64?, + c_first_shipto_date_sk : int64?, + c_first_sales_date_sk : int64?, + c_salutation : string?, + c_first_name : string?, + c_last_name : string?, + c_preferred_cust_flag : string?, + c_birth_day : int64?, + c_birth_month : int64?, + c_birth_year : int64?, + c_birth_country : string?, + c_login : string?, + c_email_address : string?, + c_last_review_date : string? +} + +create dataset store_returns (store_returns_type) primary key sr_item_sk, sr_ticket_number; + +create dataset date_dim(date_dim_type) primary key d_date_sk; + +create dataset store (store_type) primary key s_store_sk; + +create dataset customer (customer_type) primary key c_customer_sk; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.2.update.sqlpp new file mode 100644 index 0000000..16e7fd3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.2.update.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +use tpcds; + + +load dataset store_returns using localfs ((`path`=`asterix_nc1://data/tpcds/store_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset store using localfs ((`path`=`asterix_nc1://data/tpcds/store.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.3.query.sqlpp new file mode 100644 index 0000000..f4f6d52 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q01/q01.3.query.sqlpp @@ -0,0 +1,46 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + + +use tpcds; + +with customer_total_return as +(select sr_customer_sk as ctr_customer_sk +,sr_store_sk as ctr_store_sk +,sum(sr_fee) as ctr_total_return +from store_returns +,date_dim +where sr_returned_date_sk = d_date_sk +and d_year = 2000 +group by sr_customer_sk +,sr_store_sk) + +select c_customer_id +from customer_total_return ctr1 +,store +,customer +where ctr1.ctr_total_return > ( + select value (avg(ctr2.ctr_total_return)*1.2) + from customer_total_return ctr2 + where ctr1.ctr_store_sk = ctr2.ctr_store_sk)[0] +and s_store_sk = ctr1.ctr_store_sk +and s_state = 'TN' +and ctr1.ctr_customer_sk = c_customer_sk +order by c_customer_id +limit 100; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.1.ddl.sqlpp new file mode 100644 index 0000000..9dd7f40 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.1.ddl.sqlpp @@ -0,0 +1,61 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpcds if exists; +create dataverse tpcds; + +use tpcds; + + +create type tpcds.reason_type as + closed { + r_reason_sk : int64, + r_reason_id : string, + r_reason_desc : string? +} +create type tpcds.store_sales_type as + closed { + ss_sold_date_sk: int64?, + ss_sold_time_sk: int64?, + ss_item_sk: int64, + ss_customer_sk: int64?, + ss_cdemo_sk: int64?, + ss_hdemo_sk: int64?, + ss_addr_sk: int64?, + ss_store_sk: int64?, + ss_promo_sk: int64?, + ss_ticket_number: int64, + ss_quantity: int64?, + ss_wholesale_cost: double?, + ss_list_price: double?, + ss_sales_price: double?, + ss_ext_discount_amt: double?, + ss_ext_sales_price: double?, + ss_ext_wholesale_cost: double?, + ss_ext_list_price: double?, + ss_ext_tax: double?, + ss_coupon_amt: double?, + ss_net_paid: double?, + ss_net_paid_inc_tax: double?, + ss_net_profit: double? +} + +create dataset reason(reason_type) primary key r_reason_sk + +create dataset store_sales (store_sales_type) primary key ss_item_sk, ss_ticket_number; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.2.update.sqlpp new file mode 100644 index 0000000..f7fcbca --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.2.update.sqlpp @@ -0,0 +1,25 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +use tpcds; + + +load dataset reason using localfs ((`path`=`asterix_nc1://data/tpcds/reason.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.3.query.sqlpp new file mode 100644 index 0000000..774747e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q09/q09.3.query.sqlpp @@ -0,0 +1,70 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + + +use tpcds; + +select case when (select value count(ss) + from store_sales ss + where ss_quantity >= 1 and ss_quantity <= 20) > 25437 + then (select avg(ss_ext_discount_amt) + from store_sales + where ss_quantity >= 1 and ss_quantity <= 20) + else (select avg(ss_net_profit) + from store_sales + where ss_quantity >= 1 and ss_quantity <= 20) end bucket1 , + case when (select value count(ss) + from store_sales ss + where ss_quantity >= 21 and ss_quantity <= 40) > 22746 + then (select avg(ss_ext_discount_amt) + from store_sales + where ss_quantity >= 21 and ss_quantity <= 40) + else (select avg(ss_net_profit) + from store_sales + where ss_quantity >= 21 and ss_quantity <= 40) end bucket2, + case when (select value count(ss) + from store_sales ss + where ss_quantity >= 41 and ss_quantity <= 60) > 9387 + then (select avg(ss_ext_discount_amt) + from store_sales + where ss_quantity >= 41 and ss_quantity <= 60) + else (select avg(ss_net_profit) + from store_sales + where ss_quantity >= 41 and ss_quantity <= 60) end bucket3, + case when (select value count(ss) + from store_sales ss + where ss_quantity >= 61 and ss_quantity <= 80) > 10098 + then (select avg(ss_ext_discount_amt) + from store_sales + where ss_quantity >= 61 and ss_quantity <= 80) + else (select avg(ss_net_profit) + from store_sales + where ss_quantity >= 61 and ss_quantity <= 80) end bucket4, + case when (select value count(ss) + from store_sales ss + where ss_quantity >= 81 and ss_quantity <= 100) > 18213 + then (select avg(ss_ext_discount_amt) + from store_sales + where ss_quantity >= 81 and ss_quantity <= 100) + else (select avg(ss_net_profit) + from store_sales + where ss_quantity >= 81 and ss_quantity <= 100) end bucket5 +from reason +where r_reason_sk = 1 +; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.1.ddl.sqlpp new file mode 100644 index 0000000..2354901 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.1.ddl.sqlpp @@ -0,0 +1,191 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpcds if exists; +create dataverse tpcds; + +use tpcds; + + +create type tpcds.web_page_type as + closed { + wp_web_page_sk : int64, + wp_web_page_id : string, + wp_rec_start_date : string?, + wp_rec_end_date : string?, + wp_creation_date_sk : int64?, + wp_access_date_sk : int64?, + wp_autogen_flag : string?, + wp_customer_sk : int64?, + wp_url : string?, + wp_type : string?, + wp_char_count : int64?, + wp_link_count : int64?, + wp_image_count : int64?, + wp_max_ad_count : int64? +} + + +create type tpcds.customer_address_type as closed { + ca_address_sk : int64, + ca_address_id : string, + ca_street_number : string?, + ca_street_name : string?, + ca_street_type : string?, + ca_suite_number : string?, + ca_city : string?, + ca_county : string?, + ca_state : string?, + ca_zip : string?, + ca_country : string?, + ca_gmt_offset : double?, + ca_location_type : string? + } + +create type tpcds.reason_type as + closed { + r_reason_sk : int64, + r_reason_id : string, + r_reason_desc : string? +} + +create type tpcds.customer_demographics_type as + closed { + cd_demo_sk : int64, + cd_gender : string?, + cd_marital_status : string?, + cd_education_status : string?, + cd_purchase_estimate : int64?, + cd_credit_rating : string?, + cd_dep_count : int64?, + cd_dep_employed_count : int64?, + cd_dep_college_count : int64? +} + +create type tpcds.web_sales_type as + closed { + ws_sold_date_sk : int64?, + ws_sold_time_sk : int64?, + ws_ship_date_sk : int64?, + ws_item_sk : int64, + ws_bill_customer_sk : int64?, + ws_bill_cdemo_sk : int64?, + ws_bill_hdemo_sk : int64?, + ws_bill_addr_sk : int64?, + ws_ship_customer_sk : int64?, + ws_ship_cdemo_sk : int64?, + ws_ship_hdemo_sk : int64?, + ws_ship_addr_sk : int64?, + ws_web_page_sk : int64?, + ws_web_site_sk : int64?, + ws_ship_mode_sk : int64?, + ws_warehouse_sk : int64?, + ws_promo_sk : int64?, + ws_order_number : int64, + ws_quantity : int64?, + ws_wholesale_cost : double?, + ws_list_price : double?, + ws_sales_price : double?, + ws_ext_discount_amt : double?, + ws_ext_sales_price : double?, + ws_ext_wholesale_cost : double?, + ws_ext_list_price : double?, + ws_ext_tax : double?, + ws_coupon_amt : double?, + ws_ext_ship_cost : double?, + ws_net_paid : double?, + ws_net_paid_inc_tax : double?, + ws_net_paid_inc_ship : double?, + ws_net_paid_inc_ship_tax : double?, + ws_net_profit : double? +} + +create type tpcds.date_dim_type as + closed { + d_date_sk : int64, + d_date_id : string, + d_date : string?, + d_month_seq : int64?, + d_week_seq : int64?, + d_quarter_seq : int64?, + d_year : int64? , + d_dow : int64? , + d_moy : int64?, + d_dom : int64?, + d_qoy : int64?, + d_fy_year : int64?, + d_fy_quarter_seq : int64?, + d_fy_week_seq : int64?, + d_day_name : string?, + d_quarter_name : string?, + d_holiday : string?, + d_weekend : string?, + d_following_holiday : string?, + d_first_dom : int64?, + d_last_dom : int64?, + d_same_day_ly : int64?, + d_same_day_lq : int64?, + d_current_day : string?, + d_current_week : string?, + d_current_month : string?, + d_current_quarter : string?, + d_current_year : string? +} + +create type tpcds.web_returns_type as + closed { + wr_returned_date_sk : int64?, + wr_returned_time_sk : int64?, + wr_item_sk : int64, + wr_refunded_customer_sk : int64?, + wr_refunded_cdemo_sk : int64?, + wr_refunded_hdemo_sk : int64?, + wr_refunded_addr_sk : int64?, + wr_returning_customer_sk : int64?, + wr_returning_cdemo_sk : int64?, + wr_returning_hdemo_sk : int64?, + wr_returning_addr_sk : int64?, + wr_web_page_sk : int64?, + wr_reason_sk : int64?, + wr_order_number : int64, + wr_return_quantity : int64?, + wr_return_amt : double?, + wr_return_tax : double?, + wr_return_amt_inc_tax : double?, + wr_fee : double?, + wr_return_ship_cost: double?, + wr_refunded_cash: double?, + wr_reversed_charge: double?, + wr_account_credit: double?, + wr_net_loss: double? +} + +create dataset web_page (web_page_type) primary key wp_web_page_sk; + +create dataset customer_address(customer_address_type) primary key ca_address_sk; + +create dataset reason(reason_type) primary key r_reason_sk + +create dataset customer_demographics(customer_demographics_type) primary key cd_demo_sk; + +create dataset web_sales (web_sales_type) primary key ws_item_sk, ws_order_number; + +create dataset date_dim(date_dim_type) primary key d_date_sk; + +create dataset web_returns (web_returns_type) primary key wr_item_sk, wr_order_number; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.2.update.sqlpp new file mode 100644 index 0000000..fdb2d4f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.2.update.sqlpp @@ -0,0 +1,35 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +use tpcds; + + +load dataset web_page using localfs ((`path`=`asterix_nc1://data/tpcds/web_page.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset reason using localfs ((`path`=`asterix_nc1://data/tpcds/reason.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset customer_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/customer_demographics.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset web_returns using localfs ((`path`=`asterix_nc1://data/tpcds/web_returns.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.3.query.sqlpp new file mode 100644 index 0000000..6330d9b --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q85/q85.3.query.sqlpp @@ -0,0 +1,103 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + + +use tpcds; + +select substr(r_reason_desc,1,20) + ,avg(ws_quantity) + ,avg(wr_refunded_cash) + ,avg(wr_fee) + from web_sales, web_returns, web_page, customer_demographics cd1, + customer_demographics cd2, customer_address, date_dim, reason + where ws_web_page_sk = wp_web_page_sk + and ws_item_sk = wr_item_sk + and ws_order_number = wr_order_number + and ws_sold_date_sk = d_date_sk and d_year = 1998 + and cd1.cd_demo_sk = wr_refunded_cdemo_sk + and cd2.cd_demo_sk = wr_returning_cdemo_sk + and ca_address_sk = wr_refunded_addr_sk + and r_reason_sk = wr_reason_sk + and + ( + ( + cd1.cd_marital_status = 'M' + and + cd1.cd_marital_status = cd2.cd_marital_status + and + cd1.cd_education_status = '4 yr Degree' + and + cd1.cd_education_status = cd2.cd_education_status + and + ws_sales_price >= 100.00 and ws_sales_price <= 150.00 + ) + or + ( + cd1.cd_marital_status = 'D' + and + cd1.cd_marital_status = cd2.cd_marital_status + and + cd1.cd_education_status = 'Primary' + and + cd1.cd_education_status = cd2.cd_education_status + and + ws_sales_price >= 50.00 and ws_sales_price <= 100.00 + ) + or + ( + cd1.cd_marital_status = 'U' + and + cd1.cd_marital_status = cd2.cd_marital_status + and + cd1.cd_education_status = 'Advanced Degree' + and + cd1.cd_education_status = cd2.cd_education_status + and + ws_sales_price >= 150.00 and ws_sales_price <= 200.00 + ) + ) + and + ( + ( + ca_country = 'United States' + and + ca_state in ['KY', 'GA', 'NM'] + and ws_net_profit >= 100 and ws_net_profit <= 200 + ) + or + ( + ca_country = 'United States' + and + ca_state in ['MT', 'OR', 'IN'] + and ws_net_profit >= 150 and ws_net_profit <= 300 + ) + or + ( + ca_country = 'United States' + and + ca_state in ['WI', 'MO', 'WV'] + and ws_net_profit >= 50 and ws_net_profit <= 250 + ) + ) +group by r_reason_desc +order by substr(r_reason_desc,1,20) + ,avg(ws_quantity) + ,avg(wr_refunded_cash) + ,avg(wr_fee) +limit 100; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.1.ddl.sqlpp new file mode 100644 index 0000000..f6cc711 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.1.ddl.sqlpp @@ -0,0 +1,109 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpcds if exists; +create dataverse tpcds; + +use tpcds; + + +create type tpcds.web_page_type as + closed { + wp_web_page_sk : int64, + wp_web_page_id : string, + wp_rec_start_date : string?, + wp_rec_end_date : string?, + wp_creation_date_sk : int64?, + wp_access_date_sk : int64?, + wp_autogen_flag : string?, + wp_customer_sk : int64?, + wp_url : string?, + wp_type : string?, + wp_char_count : int64?, + wp_link_count : int64?, + wp_image_count : int64?, + wp_max_ad_count : int64? +} + +create type tpcds.household_demographics_type as + closed { + hd_demo_sk : int64, + hd_income_band_sk : int64?, + hd_buy_potential : string?, + hd_dep_count : int64?, + hd_vehicle_count : int64? +} +create type tpcds.time_dim_type as + closed { + t_time_sk : int64, + t_time_id : string, + t_time : int64?, + t_hour : int64?, + t_minute : int64?, + t_second : int64?, + t_am_pm : string?, + t_shift : string?, + t_sub_shift : string?, + t_meal_time : string? +} +create type tpcds.web_sales_type as + closed { + ws_sold_date_sk : int64?, + ws_sold_time_sk : int64?, + ws_ship_date_sk : int64?, + ws_item_sk : int64, + ws_bill_customer_sk : int64?, + ws_bill_cdemo_sk : int64?, + ws_bill_hdemo_sk : int64?, + ws_bill_addr_sk : int64?, + ws_ship_customer_sk : int64?, + ws_ship_cdemo_sk : int64?, + ws_ship_hdemo_sk : int64?, + ws_ship_addr_sk : int64?, + ws_web_page_sk : int64?, + ws_web_site_sk : int64?, + ws_ship_mode_sk : int64?, + ws_warehouse_sk : int64?, + ws_promo_sk : int64?, + ws_order_number : int64, + ws_quantity : int64?, + ws_wholesale_cost : double?, + ws_list_price : double?, + ws_sales_price : double?, + ws_ext_discount_amt : double?, + ws_ext_sales_price : double?, + ws_ext_wholesale_cost : double?, + ws_ext_list_price : double?, + ws_ext_tax : double?, + ws_coupon_amt : double?, + ws_ext_ship_cost : double?, + ws_net_paid : double?, + ws_net_paid_inc_tax : double?, + ws_net_paid_inc_ship : double?, + ws_net_paid_inc_ship_tax : double?, + ws_net_profit : double? +} + +create dataset web_page (web_page_type) primary key wp_web_page_sk; + +create dataset household_demographics (household_demographics_type) primary key hd_demo_sk; + +create dataset tpcds.time_dim(time_dim_type) primary key t_time_sk + +create dataset web_sales (web_sales_type) primary key ws_item_sk, ws_order_number; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.2.update.sqlpp new file mode 100644 index 0000000..319491a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.2.update.sqlpp @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +use tpcds; + + +load dataset web_page using localfs ((`path`=`asterix_nc1://data/tpcds/web_page.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset household_demographics using localfs ((`path`=`asterix_nc1://data/tpcds/household_demographics.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset time_dim using localfs ((`path`=`asterix_nc1://data/tpcds/time_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.3.query.sqlpp new file mode 100644 index 0000000..82c4097 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.3.query.sqlpp @@ -0,0 +1,43 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + + +use tpcds; + +select ((array_count((select 1 as amc + from web_sales ws1, household_demographics , time_dim, web_page + where ws1.ws_sold_time_sk = time_dim.t_time_sk + and ws1.ws_ship_hdemo_sk = household_demographics.hd_demo_sk + and ws1.ws_web_page_sk = web_page.wp_web_page_sk + and time_dim.t_hour >= 6 + and time_dim.t_hour <= 6+1 + and household_demographics.hd_dep_count = 8 + and web_page.wp_char_count >= 5000 + and web_page.wp_char_count <= 5200 limit 10))) *1.0 /(array_count((select 1 as pmc + from web_sales ws2, household_demographics , time_dim, web_page + where ws2.ws_sold_time_sk = time_dim.t_time_sk + and ws2.ws_ship_hdemo_sk = household_demographics.hd_demo_sk + and ws2.ws_web_page_sk = web_page.wp_web_page_sk + and time_dim.t_hour >= 14 + and time_dim.t_hour <= 14+1 + and household_demographics.hd_dep_count = 8 + and web_page.wp_char_count >= 5000 + and web_page.wp_char_count <= 5200 limit 10)))) am_pm_ratio + order by am_pm_ratio + limit 100; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.1.ddl.sqlpp new file mode 100644 index 0000000..bc536d6 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.1.ddl.sqlpp @@ -0,0 +1,126 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +drop dataverse tpcds if exists; +create dataverse tpcds; + +use tpcds; + + +create type tpcds.item_type as + closed { + i_item_sk : int64, + i_item_id : string, + i_rec_start_date : string?, + i_rec_end_date : string?, + i_item_desc : string?, + i_current_price : double?, + i_wholesale_cost : double?, + i_brand_id : int64?, + i_brand : string?, + i_class_id : int64?, + i_class : string?, + i_category_id : int64?, + i_category : string?, + i_manufact_id : int64?, + i_manufact : string?, + i_size : string?, + i_formulation : string?, + i_color : string?, + i_units : string?, + i_container : string?, + i_manager_id : int64?, + i_product_name : string? +} + +create type tpcds.web_sales_type as + closed { + ws_sold_date_sk : int64?, + ws_sold_time_sk : int64?, + ws_ship_date_sk : int64?, + ws_item_sk : int64, + ws_bill_customer_sk : int64?, + ws_bill_cdemo_sk : int64?, + ws_bill_hdemo_sk : int64?, + ws_bill_addr_sk : int64?, + ws_ship_customer_sk : int64?, + ws_ship_cdemo_sk : int64?, + ws_ship_hdemo_sk : int64?, + ws_ship_addr_sk : int64?, + ws_web_page_sk : int64?, + ws_web_site_sk : int64?, + ws_ship_mode_sk : int64?, + ws_warehouse_sk : int64?, + ws_promo_sk : int64?, + ws_order_number : int64, + ws_quantity : int64?, + ws_wholesale_cost : double?, + ws_list_price : double?, + ws_sales_price : double?, + ws_ext_discount_amt : double?, + ws_ext_sales_price : double?, + ws_ext_wholesale_cost : double?, + ws_ext_list_price : double?, + ws_ext_tax : double?, + ws_coupon_amt : double?, + ws_ext_ship_cost : double?, + ws_net_paid : double?, + ws_net_paid_inc_tax : double?, + ws_net_paid_inc_ship : double?, + ws_net_paid_inc_ship_tax : double?, + ws_net_profit : double? +} + +create type tpcds.date_dim_type as + closed { + d_date_sk : int64, + d_date_id : string, + d_date : string?, + d_month_seq : int64?, + d_week_seq : int64?, + d_quarter_seq : int64?, + d_year : int64? , + d_dow : int64? , + d_moy : int64?, + d_dom : int64?, + d_qoy : int64?, + d_fy_year : int64?, + d_fy_quarter_seq : int64?, + d_fy_week_seq : int64?, + d_day_name : string?, + d_quarter_name : string?, + d_holiday : string?, + d_weekend : string?, + d_following_holiday : string?, + d_first_dom : int64?, + d_last_dom : int64?, + d_same_day_ly : int64?, + d_same_day_lq : int64?, + d_current_day : string?, + d_current_week : string?, + d_current_month : string?, + d_current_quarter : string?, + d_current_year : string? +} + +create dataset item (item_type) primary key i_item_sk; + +create dataset web_sales (web_sales_type) primary key ws_item_sk, ws_order_number; + +create dataset date_dim(date_dim_type) primary key d_date_sk; diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.2.update.sqlpp new file mode 100644 index 0000000..3e8ef27 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.2.update.sqlpp @@ -0,0 +1,27 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +use tpcds; + + +load dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset web_sales using localfs ((`path`=`asterix_nc1://data/tpcds/web_sales.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); + +load dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),(`format`=`delimited-text`),(`delimiter`=`|`)); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.3.query.sqlpp new file mode 100644 index 0000000..d3f1dff --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q92/q92.3.query.sqlpp @@ -0,0 +1,50 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + + +use tpcds; + +select + sum(ws1.ws_ext_discount_amt) as Excess_Discount_Amount +from + web_sales ws1, + item i1, + date_dim d1 +where +i1.i_manufact_id = 269 +and i1.i_item_sk = ws1.ws_item_sk +and date(d1.d_date) >= date('1998-03-18') +and date(d1.d_date) <= date('1998-06-16') +and d1.d_date_sk = ws1.ws_sold_date_sk +and ws1.ws_ext_discount_amt + > + ( + SELECT VALUE + (1.3 * avg(ws2.ws_ext_discount_amt)) + FROM + web_sales ws2, + date_dim d2 + WHERE + ws2.ws_item_sk = i1.i_item_sk + and date(d2.d_date) >= date('1998-03-18') + and date(d2.d_date) <= date('1998-06-16') + and d2.d_date_sk = ws2.ws_sold_date_sk + )[0] +order by sum(ws1.ws_ext_discount_amt) +limit 100; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q01/q01.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q01/q01.1.adm new file mode 100644 index 0000000..6ff7465 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q01/q01.1.adm @@ -0,0 +1 @@ +{ "c_customer_id": "AAAAAAAAJAAAAAAA" } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q09/q09.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q09/q09.1.adm new file mode 100644 index 0000000..8f8ea21 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q09/q09.1.adm @@ -0,0 +1 @@ +{ "bucket1": [ { "$2": -746.2933333333334 } ], "bucket2": [ { "$4": -20.0 } ], "bucket3": [ { "$6": null } ], "bucket4": [ { "$8": -1671.84 } ], "bucket5": [ { "$10": -161.56 } ] } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q85/q85.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q85/q85.1.adm new file mode 100644 index 0000000..b9d9f53 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q85/q85.1.adm @@ -0,0 +1 @@ +{ "$1": "Did not like the mak", "$2": 6.0, "$3": 50.11, "$4": 22.13 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q90/q90.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q90/q90.1.adm new file mode 100644 index 0000000..6a5b48c --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q90/q90.1.adm @@ -0,0 +1 @@ +{ "am_pm_ratio": 1.0 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q92/q92.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q92/q92.1.adm new file mode 100644 index 0000000..49e4705 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q92/q92.1.adm @@ -0,0 +1 @@ +{ "Excess_Discount_Amount": 1966.88 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q94/q94.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q94/q94.1.adm index ed2027f..2d3eb18 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q94/q94.1.adm +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q94/q94.1.adm @@ -1 +1 @@ -{ "order_count": 13, "total_shipping_cost": 4813.119999999999, "total_net_profit": 4754.880000000001 } \ No newline at end of file +{ "order_count": 30, "total_shipping_cost": 11107.2, "total_net_profit": 9925.650000000001 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q95/q95.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q95/q95.1.adm index 887359c..64a4a1f 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q95/q95.1.adm +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q95/q95.1.adm @@ -1 +1 @@ -{ "order_count": 1, "total_shipping_cost": 910.86, "total_net_profit": -129.96 } \ No newline at end of file +{ "order_count": 2, "total_shipping_cost": 1281.1, "total_net_profit": 235.79999999999998 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.1.adm index 65db206..33c4f67 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.1.adm +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1602/query-ASTERIXDB-1602.1.adm @@ -1 +1 @@ -{ "$1": 144 } \ No newline at end of file +{ "$1": 196 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml index 5a2083e..15c856d 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml @@ -6165,6 +6165,31 @@ <output-dir compare="Text">q98</output-dir> </compilation-unit> </test-case> + <test-case FilePath="tpcds"> + <compilation-unit name="q01"> + <output-dir compare="Text">q01</output-dir> + </compilation-unit> + </test-case> + <test-case FilePath="tpcds"> + <compilation-unit name="q09"> + <output-dir compare="Text">q09</output-dir> + </compilation-unit> + </test-case> + <test-case FilePath="tpcds"> + <compilation-unit name="q92"> + <output-dir compare="Text">q92</output-dir> + </compilation-unit> + </test-case> + <test-case FilePath="tpcds"> + <compilation-unit name="q90"> + <output-dir compare="Text">q90</output-dir> + </compilation-unit> + </test-case> + <test-case FilePath="tpcds"> + <compilation-unit name="q85"> + <output-dir compare="Text">q85</output-dir> + </compilation-unit> + </test-case> </test-group> <test-group name="tpch"> <test-case FilePath="tpch"> -- To view, visit https://asterix-gerrit.ics.uci.edu/1332 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-MessageType: merged Gerrit-Change-Id: I5956f424677dbf41bcdcaa771853bde50397e704 Gerrit-PatchSet: 3 Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-Owner: Vignesh Raghunathan <[email protected]> Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Vignesh Raghunathan <[email protected]> Gerrit-Reviewer: Yingyi Bu <[email protected]>
