You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:20:29 UTC
[jira] [Created] (TRAFODION-1230) LP Bug: 1452424 - vsbb
scan/delete cause query to return wrong result
Alice Chen created TRAFODION-1230:
-------------------------------------
Summary: LP Bug: 1452424 - vsbb scan/delete cause query to return wrong result
Key: TRAFODION-1230
URL: https://issues.apache.org/jira/browse/TRAFODION-1230
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Reporter: Weishiun Tsai
Assignee: Selvaganesan Govindarajan
Priority: Blocker
Fix For: 2.0-incubating
This is seen several times on the r1.2 v0505 build. The following query returns a different result on the v0505 build compared to the r1.1 rc3 (v0427) build. Comparing the query plans from the 2 builds, they look similar except that a tranfodion_scan node in v1.1 rc3 (v0427) has been replaced with a trafodion_vsbb_scan node in v0505.
An experiment was done on the v0505 build installed on a workstation. The same query was compiled/executed twice as x1 as x2. x1 was compiled with the default CQD HBASE_ROWSET_VSBB_OPT value (ON by default), and x2 was compiled with this CQD turned off. As shown here in the execution output, the 2 query plans were identitcal except for node 14, which was trafodion_vsbb_scan with HBASE_ROWSET_VSBB_OPT on, and trafodion_scan with HBASE_ROWSET_VSBB_OPT off. x1 and x2 returned 2 different results. x2âs result is consistent with the v1.1 rc3 result. It is also what we have been seeing since pre-Trafodion days, so x2 presumably has the correct result.
This problem is not always reproducible, but it does show up frequently on the v0505 build. This execution output here was from the v0505 build installed on a workstation. The query requires 6 QA tpcds1x tables. But the 6 tables are small enough to fit into a workstation instance. Scripts to populate these 6 tables are available upon request (Running the script requires DCS up and running with a minimum of 4 servers configured.)
---------------------------------------------------------------------------------------------
Here is the entire script to reproduce it (when it is reproducible):
log mytest.log clear;
set schema g_tpcds1x;
cqd HBASE_ROWSET_VSBB_OPT reset;
prepare x1 from
select avg(ss_quantity), avg(ss_ext_sales_price), avg(ss_ext_wholesale_cost), sum(ss_ext_wholesale_cost)
from store_sales, store, customer_demographics, household_demographics, customer_address, date_dim
where s_store_sk = ss_store_sk
and ss_sold_date_sk = d_date_sk and d_year = 2001
and
(
(
ss_hdemo_sk=hd_demo_sk
and
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'S'
and
cd_education_status = '2 yr Degree'
and
ss_sales_price between 100.00 and 150.00
and
hd_dep_count = 3
)
or
(
ss_hdemo_sk=hd_demo_sk
and
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'W'
and
cd_education_status = 'College'
and
ss_sales_price between 50.00 and 100.00
and
hd_dep_count = 1
)
or
(
ss_hdemo_sk=hd_demo_sk
and
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'D'
and
cd_education_status = 'Advanced Degree'
and
ss_sales_price between 150.00 and 200.00
and
hd_dep_count = 1
)
)
and
(
(
ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('WV', 'VT', 'IA')
and ss_net_profit between 100 and 200
)
or
(ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('NC', 'IA', 'OK')
and ss_net_profit between 150 and 300
)
or
(ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('SD', 'ND', 'OK')
and ss_net_profit between 50 and 250
)
);
explain options 'f' x1;
explain x1;
execute x1;
cqd HBASE_ROWSET_VSBB_OPT 'OFF';
prepare x2 from
select avg(ss_quantity), avg(ss_ext_sales_price), avg(ss_ext_wholesale_cost), sum(ss_ext_wholesale_cost)
from store_sales, store, customer_demographics, household_demographics, customer_address, date_dim
where s_store_sk = ss_store_sk
and ss_sold_date_sk = d_date_sk and d_year = 2001
and
(
(
ss_hdemo_sk=hd_demo_sk
and
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'S'
and
cd_education_status = '2 yr Degree'
and
ss_sales_price between 100.00 and 150.00
and
hd_dep_count = 3
)
or
(
ss_hdemo_sk=hd_demo_sk
and
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'W'
and
cd_education_status = 'College'
and
ss_sales_price between 50.00 and 100.00
and
hd_dep_count = 1
)
or
(
ss_hdemo_sk=hd_demo_sk
and
cd_demo_sk = ss_cdemo_sk
and
cd_marital_status = 'D'
and
cd_education_status = 'Advanced Degree'
and
ss_sales_price between 150.00 and 200.00
and
hd_dep_count = 1
)
)
and
(
(
ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('WV', 'VT', 'IA')
and ss_net_profit between 100 and 200
)
or
(ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('NC', 'IA', 'OK')
and ss_net_profit between 150 and 300
)
or
(ss_addr_sk = ca_address_sk
and
ca_country = 'United States'
and
ca_state in ('SD', 'ND', 'OK')
and ss_net_profit between 50 and 250
)
);
explain options 'f' x2;
explain x2;
execute x2;
---------------------------------------------------------------------------------------------
Here is the execution output from the v0505 build installed on a workstation. Launchpad does not allow long description. So the output here only shows explain options âfâ of x1 and x2. The full explain output for x1 and x2 can be seen from the attached mytest.log file.
>>set schema g_tpcds1x;
--- SQL operation complete.
>>
>>cqd HBASE_ROWSET_VSBB_OPT reset;
--- SQL operation complete.
>>
>>prepare x1 from
+>select avg(ss_quantity), avg(ss_ext_sales_price), avg(ss_ext_wholesale_cost),
sum(ss_ext_wholesale_cost)
+>from store_sales, store, customer_demographics, household_demographics, custom
er_address, date_dim
+>where s_store_sk = ss_store_sk
+>and ss_sold_date_sk = d_date_sk and d_year = 2001
+>and
+>(
+>(
+>ss_hdemo_sk=hd_demo_sk
+>and
+>cd_demo_sk = ss_cdemo_sk
+>and
+>cd_marital_status = 'S'
+>and
+>cd_education_status = '2 yr Degree'
+>and
+>ss_sales_price between 100.00 and 150.00
+>and
+>hd_dep_count = 3
+>)
+>or
+>(
+>ss_hdemo_sk=hd_demo_sk
+>and
+>cd_demo_sk = ss_cdemo_sk
+>and
+>cd_marital_status = 'W'
+>and
+>cd_education_status = 'College'
+>and
+>ss_sales_price between 50.00 and 100.00
+>and
+>hd_dep_count = 1
+>)
+>or
+>(
+>ss_hdemo_sk=hd_demo_sk
+>and
+>cd_demo_sk = ss_cdemo_sk
+>and
+>cd_marital_status = 'D'
+>and
+>cd_education_status = 'Advanced Degree'
+>and
+>ss_sales_price between 150.00 and 200.00
+>and
+>hd_dep_count = 1
+>)
+>)
+>and
+>(
+>(
+>ss_addr_sk = ca_address_sk
+>and
+>ca_country = 'United States'
+>and
+>ca_state in ('WV', 'VT', 'IA')
+>and ss_net_profit between 100 and 200
+>)
+>or
+>(ss_addr_sk = ca_address_sk
+>and
+>ca_country = 'United States'
+>and
+>ca_state in ('NC', 'IA', 'OK')
+>and ss_net_profit between 150 and 300
+>)
+>or
+>(ss_addr_sk = ca_address_sk
+>and
+>ca_country = 'United States'
+>and
+>ca_state in ('SD', 'ND', 'OK')
+>and ss_net_profit between 50 and 250
+>)
+>);
--- SQL command prepared.
>>
>>explain options 'f' x1;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
19 . 20 root 1.00E+000
18 . 19 sort_scalar_aggr 1.00E+000
17 1 18 hybrid_hash_join 5.00E+000
16 . 17 esp_exchange 1:4(hash2) 5.00E+000
13 15 16 nested_join 5.00E+000
14 . 15 probe_cache 5.97E-004
. . 14 trafodion_vsbb_scan CUSTOMER_DEMOGRAPHIC 5.97E-004
12 3 13 hybrid_hash_join 8.37E+003
11 . 12 esp_exchange 4(hash2):5(hash2) 8.37E+003
10 5 11 hybrid_hash_join 8.37E+003
9 7 10 hybrid_hash_join 4.91E+005
8 . 9 esp_exchange 5(hash2):4(hash2) 2.75E+006
. . 8 trafodion_scan STORE_SALES 2.75E+006
6 . 7 esp_exchange 5(rep-b):4(hash2) 3.25E+002
. . 6 trafodion_scan DATE_DIM 3.25E+002
4 . 5 esp_exchange 5(rep-b):4(hash2) 4.84E+004
. . 4 trafodion_scan CUSTOMER_ADDRESS 4.84E+004
2 . 3 esp_exchange 4(rep-b):4(hash2) 7.19E+003
. . 2 trafodion_scan HOUSEHOLD_DEMOGRAPHI 7.19E+003
. . 1 trafodion_scan STORE 1.20E+001
--- SQL operation complete.
>>
>>execute x1;
(EXPR) (EXPR) (EXPR) (EXPR)
-------------------- --------------------- --------------------- ------------
---------
21 1432.28 1135.38
4541.54
--- 1 row(s) selected.
>>
>>cqd HBASE_ROWSET_VSBB_OPT 'OFF';
--- SQL operation complete.
>>
>>prepare x2 from
+>select avg(ss_quantity), avg(ss_ext_sales_price), avg(ss_ext_wholesale_cost),
sum(ss_ext_wholesale_cost)
+>from store_sales, store, customer_demographics, household_demographics, custom
er_address, date_dim
+>where s_store_sk = ss_store_sk
+>and ss_sold_date_sk = d_date_sk and d_year = 2001
+>and
+>(
+>(
+>ss_hdemo_sk=hd_demo_sk
+>and
+>cd_demo_sk = ss_cdemo_sk
+>and
+>cd_marital_status = 'S'
+>and
+>cd_education_status = '2 yr Degree'
+>and
+>ss_sales_price between 100.00 and 150.00
+>and
+>hd_dep_count = 3
+>)
+>or
+>(
+>ss_hdemo_sk=hd_demo_sk
+>and
+>cd_demo_sk = ss_cdemo_sk
+>and
+>cd_marital_status = 'W'
+>and
+>cd_education_status = 'College'
+>and
+>ss_sales_price between 50.00 and 100.00
+>and
+>hd_dep_count = 1
+>)
+>or
+>(
+>ss_hdemo_sk=hd_demo_sk
+>and
+>cd_demo_sk = ss_cdemo_sk
+>and
+>cd_marital_status = 'D'
+>and
+>cd_education_status = 'Advanced Degree'
+>and
+>ss_sales_price between 150.00 and 200.00
+>and
+>hd_dep_count = 1
+>)
+>)
+>and
+>(
+>(
+>ss_addr_sk = ca_address_sk
+>and
+>ca_country = 'United States'
+>and
+>ca_state in ('WV', 'VT', 'IA')
+>and ss_net_profit between 100 and 200
+>)
+>or
+>(ss_addr_sk = ca_address_sk
+>and
+>ca_country = 'United States'
+>and
+>ca_state in ('NC', 'IA', 'OK')
+>and ss_net_profit between 150 and 300
+>)
+>or
+>(ss_addr_sk = ca_address_sk
+>and
+>ca_country = 'United States'
+>and
+>ca_state in ('SD', 'ND', 'OK')
+>and ss_net_profit between 50 and 250
+>)
+>);
--- SQL command prepared.
>>explain options 'f' x2;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
19 . 20 root 1.00E+000
18 . 19 sort_scalar_aggr 1.00E+000
17 1 18 hybrid_hash_join 5.00E+000
16 . 17 esp_exchange 1:4(hash2) 5.00E+000
13 15 16 nested_join 5.00E+000
14 . 15 probe_cache 5.97E-004
. . 14 trafodion_scan CUSTOMER_DEMOGRAPHIC 5.97E-004
12 3 13 hybrid_hash_join 8.37E+003
11 . 12 esp_exchange 4(hash2):5(hash2) 8.37E+003
10 5 11 hybrid_hash_join 8.37E+003
9 7 10 hybrid_hash_join 4.91E+005
8 . 9 esp_exchange 5(hash2):4(hash2) 2.75E+006
. . 8 trafodion_scan STORE_SALES 2.75E+006
6 . 7 esp_exchange 5(rep-b):4(hash2) 3.25E+002
. . 6 trafodion_scan DATE_DIM 3.25E+002
4 . 5 esp_exchange 5(rep-b):4(hash2) 4.84E+004
. . 4 trafodion_scan CUSTOMER_ADDRESS 4.84E+004
2 . 3 esp_exchange 4(rep-b):4(hash2) 7.19E+003
. . 2 trafodion_scan HOUSEHOLD_DEMOGRAPHI 7.19E+003
. . 1 trafodion_scan STORE 1.20E+001
--- SQL operation complete.
>>execute x2;
(EXPR) (EXPR) (EXPR) (EXPR)
-------------------- --------------------- --------------------- -------------
--------
19 1360.77 1066.19
5330.99
--- 1 row(s) selected.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)