asterixdb/asterix-app/src/test/resources/optimizerts/queries/tpcds/query-ASTERIXDB-1581.sqlpp

/*
 * 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_sales_type as closed {
    ss_sold_date_sk:           bigint?,
    ss_sold_time_sk:           bigint?,
    ss_item_sk:                bigint,
    ss_customer_sk:            bigint?,
    ss_cdemo_sk:               bigint?,
    ss_hdemo_sk:               bigint?,
    ss_addr_sk:                bigint?,
    ss_store_sk:               bigint?,
    ss_promo_sk:               bigint?,
    ss_ticket_number:          bigint,
    ss_quantity:               bigint?,
    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 type tpcds.item_type as closed {
    i_item_sk:                 bigint,
    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:                bigint? ,
    i_brand:                   string?,
    i_class_id:                bigint? ,
    i_class:                   string?,
    i_category_id:             bigint? ,
    i_category:                string?,
    i_manufact_id:             bigint? ,
    i_manufact:                string?,
    i_size:                    string?,
    i_formulation:             string?,
    i_color:                   string?,
    i_units:                   string?,
    i_container:               string?,
    i_manager_id:              bigint?,
    i_product_name:            string?
};

create dataset store_sales (store_sales_type)
primary key ss_item_sk, ss_ticket_number;

create dataset item (item_type)
primary key i_item_sk;

--- test with subplan into subplan pushdown disabled
set `compiler.subplan.merge` "false";

select case when (select value count(ss)
                  from store_sales ss
                  where ss_quantity >= 1 and ss_quantity <= 20)[0] < 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
from item
where i_item_sk = 1;

drop dataverse tpcds;