Details
-
Bug
-
Resolution: Unresolved
-
Critical
-
7.2.0
-
7.2.0-5265
-
Untriaged
-
Centos 64-bit
-
-
0
-
Unknown
Description
Steps to reproduce -
- Create a 3 node cluster with 1 KV+N1QL+Index and 2 CBAS node.
- Create buckets to load TPCH data using cr_bucket.sh
- Load data into buckets using import.sh
- Create analytics dataset on all the buckets created in step 2 using cr_dataset.sql
- Create index as follows -
-
"region": [{"index_name": "r_regionkey_idx_region","indexed_field": ["r_regionkey:bigint"]},{"index_name": "r_name_idx_region","indexed_field": ["r_name:string"]},{"index_name": "r_comment_idx_region","indexed_field": ["r_comment:string"]}],"nation": [{"index_name": "n_nationkey_idx_nation","indexed_field": ["n_nationkey:bigint"]},{"index_name": "n_name_idx_nation","indexed_field": ["n_name:string"]},{"index_name": "n_regionkey_idx_nation","indexed_field": ["n_regionkey:bigint"]},{"index_name": "n_comment_idx_nation","indexed_field": ["n_comment:string"]}],"supplier": [{"index_name": "s_suppkey_idx_supplier","indexed_field": ["s_suppkey:bigint"]},{"index_name": "s_name_idx_supplier","indexed_field": ["s_name:string"]},{"index_name": "s_address_idx_supplier","indexed_field": ["s_address:string"]},{"index_name": "s_nationkey_idx_supplier","indexed_field": ["s_nationkey:bigint"]},{"index_name": "s_phone_idx_supplier","indexed_field": ["s_phone:string"]},{"index_name": "s_acctbal_idx_supplier","indexed_field": ["s_acctbal:double"]},{"index_name": "s_comment_idx_supplier","indexed_field": ["s_comment:string"]}],"customer": [{"index_name": "c_custkey_idx_customer","indexed_field": ["c_custkey:bigint"]},{"index_name": "c_name_idx_customer","indexed_field": ["c_name:string"]},{"index_name": "c_address_idx_customer","indexed_field": ["c_address:string"]},{"index_name": "c_nationkey_idx_customer","indexed_field": ["c_nationkey:bigint"]},{"index_name": "c_phone_idx_customer","indexed_field": ["c_phone:string"]},{"index_name": "c_acctbal_idx_customer","indexed_field": ["c_acctbal:double"]},{"index_name": "c_mktsegment_idx_customer","indexed_field": ["c_mktsegment:string"]},{"index_name": "c_comment_idx_customer","indexed_field": ["c_comment:string"]}],"part": [{"index_name": "p_partkey_idx_part","indexed_field": ["p_partkey:bigint"]},{"index_name": "p_name_idx_part","indexed_field": ["p_name:string"]},{"index_name": "p_mfgr_idx_part","indexed_field": ["p_mfgr:string"]},{"index_name": "p_brand_idx_part","indexed_field": ["p_brand:string"]},{"index_name": "p_type_idx_part","indexed_field": ["p_type:string"]},{"index_name": "p_size_idx_part","indexed_field": ["p_size:bigint"]},{"index_name": "p_container_idx_part","indexed_field": ["p_container:string"]},{"index_name": "p_retailprice_idx_part","indexed_field": ["p_retailprice:double"]},{"index_name": "p_comment_idx_part","indexed_field": ["p_comment:string"]}],"partsupp": [{"index_name": "ps_partkey_idx_partsupp","indexed_field": ["ps_partkey:bigint"]},{"index_name": "ps_suppkey_idx_partsupp","indexed_field": ["ps_suppkey:bigint"]},{"index_name": "ps_availqty_idx_partsupp","indexed_field": ["ps_availqty:bigint"]},{"index_name": "ps_supplycost_idx_partsupp","indexed_field": ["ps_supplycost:double"]},{"index_name": "ps_comment_idx_partsupp","indexed_field": ["ps_comment:string"]}],"orders": [{"index_name": "o_orderkey_idx_orders","indexed_field": ["o_orderkey:bigint"]},{"index_name": "o_custkey_idx_orders","indexed_field": ["o_custkey:string"]},{"index_name": "o_orderstatus_idx_orders","indexed_field": ["o_orderstatus:string"]},{"index_name": "o_totalprice_idx_orders","indexed_field": ["o_totalprice:double"]},{"index_name": "o_orderdate_idx_orders","indexed_field": ["o_orderdate:string"]},{"index_name": "o_orderpriority_idx_orders","indexed_field": ["o_orderpriority:string"]},{"index_name": "o_clerk_idx_orders","indexed_field": ["o_clerk:string"]},{"index_name": "o_shippriority_idx_orders","indexed_field": ["o_shippriority:bigint"]},{"index_name": "o_comment_idx_orders","indexed_field": ["o_comment:string"]}],"lineitem": [{"index_name": "l_orderkey_idx_lineitem","indexed_field": ["l_orderkey:bigint"]},{"index_name": "l_partkey_idx_lineitem","indexed_field": ["l_partkey:bigint"]},{"index_name": "l_suppkey_idx_lineitem","indexed_field": ["l_suppkey:bigint"]},{"index_name": "l_linenumber_idx_lineitem","indexed_field": ["l_linenumber:bigint"]},{"index_name": "l_quantity_idx_lineitem","indexed_field": ["l_quantity:bigint"]},{"index_name": "l_extendedprice_idx_lineitem","indexed_field": ["l_extendedprice:double"]},{"index_name": "l_discount_idx_lineitem","indexed_field": ["l_discount:double"]},{"index_name": "l_tax_idx_lineitem","indexed_field": ["l_tax:double"]},{"index_name": "l_returnflag_idx_lineitem","indexed_field": ["l_returnflag:string"]},{"index_name": "l_linestatus_idx_lineitem","indexed_field": ["l_linestatus:string"]},{"index_name": "l_shipdate_idx_lineitem","indexed_field": ["l_shipdate:string"]},{"index_name": "l_commitdate_idx_lineitem","indexed_field": ["l_commitdate:string"]},{"index_name": "l_receiptdate_idx_lineitem","indexed_field": ["l_receiptdate:string"]},{"index_name": "l_shipinstruct_idx_lineitem","indexed_field": ["l_shipinstruct:string"]},{"index_name": "l_shipmode_idx_lineitem","indexed_field": ["l_shipmode:string"]},{"index_name": "l_comment_idx_lineitem","indexed_field": ["l_comment:string"]}]
-
- Now create samples on all the dataset with sample-seed value set to 10 (using this seed value for my automation)
- Now run following query on analytics workbench and check the query plan
SET `compiler.queryplanshape` "rightdeep";
|
select s.s_acctbal,
|
s.s_name,
|
n.n_name,
|
p.p_partkey,
|
p.p_mfgr,
|
s.s_address,
|
s.s_phone,
|
s.s_comment
|
from part p,
|
supplier s,
|
partsupp ps,
|
nation n,
|
region r
|
where p.p_partkey = ps.ps_partkey
|
and s.s_suppkey = ps.ps_suppkey
|
and p.p_size = 37
|
and p.p_type like '%COPPER'
|
and s.s_nationkey = n.n_nationkey
|
and n.n_regionkey = r.r_regionkey
|
and r.r_name = 'EUROPE'
|
and ps.ps_supplycost = (select VALUE min(ps2.ps_supplycost)
|
from part p2,
|
partsupp ps2,
|
supplier s2,
|
nation n2,
|
region r2
|
where p2.p_partkey = ps2.ps_partkey
|
and p2.p_partkey = p.p_partkey
|
and s2.s_suppkey = ps2.ps_suppkey
|
and s2.s_nationkey = n2.n_nationkey
|
and n2.n_regionkey = r2.r_regionkey
|
and r2.r_name = 'EUROPE')[0] order by s.s_acctbal desc,
|
n.n_name,
|
s.s_name,
|
p.p_partkey limit 100;
|
Query Plan attached.
