[
https://issues.apache.org/jira/browse/TRAFODION-1230?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14716679#comment-14716679
]
Suresh Subbiah commented on TRAFODION-1230:
-------------------------------------------
Weishiun Tsai (wei-shiun-tsai) wrote on 2015-05-06: #1
mytest.log Edit (54.1 KiB, text/plain)
Weishiun Tsai (wei-shiun-tsai) wrote on 2015-05-06: #2
vsbb delete also has the same problem. The delete statement would delete a
different number of rows when HBASE_ROWSET_VSBB_OPT is on (default). Turning it
off brings the delete statement to delete the correct number of rows. Here is a
script to reproduce the delete problem. It does not requires any QA tables:
log mytest1.log clear;
create schema mytest1;
set schema mytest1;
create table t12tab (
a int not null, b int, c int,
d char(2), e char(4), f char(8))
attribute extent (1024, 1024), maxextents 15
store by (a);
insert into t12tab values
(1,10,100,'d1','e1','f1'),
(2,20,200,'d2','e2','f2'), (3,30,300,'d3','e3','f3'),
(4,40,400,'d4','e4','f4'), (5,50,500,'d5','e5','f5'),
(6,60,600,'d6','e6','f6'), (7,70,700,'d7','e7','f7'),
(8,80,800,'d8','e8','f8'), (9,90,900,'d9','e9','f9'),
(10,100,1000,'da','ea','fa'), (11,110,1100,'db','eb','fb'),
(12,120,1200,'dc','ec','fc'), (13,130,1300,'dd','ed','fd'),
(14,140,1400,'de','ee','fe'), (15,150,1500,'df','ef','ff');
create table t02tabB (
a int not null, b int, c int,
d char(10), e varchar(10), f char(10),
v1 int not null,
v2 int not null,
v3 int not null,
v4 int not null,
v5 int not null)
store by (a, v1, v2, v3, v4, v5) AS (
select * from t12tab
transpose 10 as v1
transpose 100,22,222 as v2
transpose 1000,33,333 as v3
transpose 10000,44,444 as v4
transpose 100000,55,555 as v5);
-- should be 1215
select count(*) from t02tabB;
insert into t02tabB (
select * from t12tab
transpose 0 as v1
transpose 1,3,5,7 as v2
transpose 2,4,6,8 as v3
transpose 3,5,7,9 as v4
transpose 4,6,8,10 as v5);
-- should be 5055
select count(*) from t02tabB;
prepare x1 from update t02tabB
set b = (select a from t12tab where a = 1),
c = (select a from t12tab where a = 11),
d = (select d from t12tab where a = 15),
e = (select e from t12tab where c = 1000),
f = (select f from t12tab where b = 100)
where v1 < 100000 and v2 < 99 and v3 < 500;
explain options 'f' x1;
-- 4110 updated
execute x1;
prepare x2 from delete from t02tabB
where d = (select d from t12tab where a = 15)
and e = (select e from t12tab where c = 1000)
and f = (select f from t12tab where b = 100);
explain options 'f' x2;
-- 4110 deleted
execute x2;
drop schema mytest1 cascade;
Weishiun Tsai (wei-shiun-tsai) wrote on 2015-05-06: #3
Download full text (5.4 KiB)
Using the v0505 build installed on a workstation. This is the result when
HBASE_ROWSET_VSBB_OPT is on (default). Notice that the last delete statement
only deleted 2586 rows:
>>create schema mytest1;
--- SQL operation complete.
>>set schema mytest1;
--- SQL operation complete.
>>
>>create table t12tab (
+>a int not null, b int, c int,
+>d char(2), e char(4), f char(8))
+>attribute extent (1024, 1024), maxextents 15
+>store by (a);
--- SQL operation complete.
>>
>>insert into t12tab values
+>(1,10,100,'d1','e1','f1'),
+>(2,20,200,'d2','e2','f2'), (3,30,300,'d3','e3','f3'),
+>(4,40,400,'d4','e4','f4'), (5,50,500,'d5','e5','f5'),
+>(6,60,600,'d6','e6','f6'), (7,70,700,'d7','e7','f7'),
+>(8,80,800,'d8','e8','f8'), (9,90,900,'d9','e9','f9'),
+>(10,100,1000,'da','ea','fa'), (11,110,1100,'db','eb','fb'),
+>(12,120,1200,'dc','ec','fc'), (13,130,1300,'dd','ed','fd'),
+>(14,140,1400,'de','ee','fe'), (15,150,1500,'df','ef','ff');
--- 15 row(s) inserted.
>>
>>create table t02tabB (
+>a int not null, b int, c int,
+>d char(10), e varchar(10), f char(10),
+>v1 int not null,
+>v2 int not null,
+>v3 int not null,
+>v4 int not null,
+>v5 int not null)
+>store by (a, v1, v2, v3, v4, v5) AS (
+>select * from t12tab
+>transpose 10 as v1
+>transpose 100,22,222 as v2
+>transpose 1000,33,333 as v3
+>transpose 10000,44,444 as v4
+>transpose 100000,55,555 as v5);
--- 1215 row(s) inserted.
>>
>>-- should be 1215
>>select count(*) from t02tabB;
(EXPR)
--------------------
1215
--- 1 row(s) selected.
>>
>>insert into t02tabB (
+>select * from t12tab
+>transpose 0 as v1
+>transpose 1,3,5,7 as v2
+>transpose 2,4,6,8 as v3
+>transpose 3,5,7,9 as v4
+>transpose 4,6,8,10 as v5);
--- 3840 row(s) inserted.
>>
>>-- should be 5055
>>select count(*) from t02tabB;
(EXPR)
--------------------
5055
--- 1 row(s) selected.
>>
>>prepare x1 from update t02tabB
+>set b = (select a from t12tab where a = 1),
+>c = (select a from t12tab where a = 11),
+>d = (select d from t12tab where a = 15),
+>e = (select e from t12tab where c = 1000),
+>f = (select f from t12tab where b = 100)
+>where v1 < 100000 and v2 < 99 and v3 < 500;
--- SQL command prepared.
>>
>>explain options 'f' x1;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
18 . 19 root x 4.00E+000
16 17 18 tuple_flow 4.00E+000
. . 17 trafodion_vsbb_updat T02TABB 1.00E+000
15 2 16 hybrid_hash_join 4.00E+000
14 4 15 hybrid_hash_join 4.00E+000
13 6 14 hybrid_hash_join 4.00E+000
12 8 13 hybrid_hash_join 4.00E+000
11 10 12 hybrid_hash_join 4.00E+000
. . 11 trafodion_scan T02TABB 4.00E+000
9 . 10 sort_scalar_aggr 1.00E+000
. . 9 trafodion_scan ...
Read more...
Weishiun Tsai (wei-shiun-tsai) wrote on 2015-05-06: #4
Download full text (5.4 KiB)
On the same v0505 build, when the CQD HBASE_ROWSET_VSBB_OPT is tuned off. The
last delete deleted 4110 rows. This is the correct behavior:
>>cqd HBASE_ROWSET_VSBB_OPT 'OFF';
--- SQL operation complete.
>>
>>create schema mytest1;
--- SQL operation complete.
>>set schema mytest1;
--- SQL operation complete.
>>
>>create table t12tab (
+>a int not null, b int, c int,
+>d char(2), e char(4), f char(8))
+>attribute extent (1024, 1024), maxextents 15
+>store by (a);
--- SQL operation complete.
>>
>>insert into t12tab values
+>(1,10,100,'d1','e1','f1'),
+>(2,20,200,'d2','e2','f2'), (3,30,300,'d3','e3','f3'),
+>(4,40,400,'d4','e4','f4'), (5,50,500,'d5','e5','f5'),
+>(6,60,600,'d6','e6','f6'), (7,70,700,'d7','e7','f7'),
+>(8,80,800,'d8','e8','f8'), (9,90,900,'d9','e9','f9'),
+>(10,100,1000,'da','ea','fa'), (11,110,1100,'db','eb','fb'),
+>(12,120,1200,'dc','ec','fc'), (13,130,1300,'dd','ed','fd'),
+>(14,140,1400,'de','ee','fe'), (15,150,1500,'df','ef','ff');
--- 15 row(s) inserted.
>>
>>create table t02tabB (
+>a int not null, b int, c int,
+>d char(10), e varchar(10), f char(10),
+>v1 int not null,
+>v2 int not null,
+>v3 int not null,
+>v4 int not null,
+>v5 int not null)
+>store by (a, v1, v2, v3, v4, v5) AS (
+>select * from t12tab
+>transpose 10 as v1
+>transpose 100,22,222 as v2
+>transpose 1000,33,333 as v3
+>transpose 10000,44,444 as v4
+>transpose 100000,55,555 as v5);
--- 1215 row(s) inserted.
>>
>>-- should be 1215
>>select count(*) from t02tabB;
(EXPR)
--------------------
1215
--- 1 row(s) selected.
>>
>>insert into t02tabB (
+>select * from t12tab
+>transpose 0 as v1
+>transpose 1,3,5,7 as v2
+>transpose 2,4,6,8 as v3
+>transpose 3,5,7,9 as v4
+>transpose 4,6,8,10 as v5);
--- 3840 row(s) inserted.
>>
>>-- should be 5055
>>select count(*) from t02tabB;
(EXPR)
--------------------
5055
--- 1 row(s) selected.
>>
>>prepare x1 from update t02tabB
+>set b = (select a from t12tab where a = 1),
+>c = (select a from t12tab where a = 11),
+>d = (select d from t12tab where a = 15),
+>e = (select e from t12tab where c = 1000),
+>f = (select f from t12tab where b = 100)
+>where v1 < 100000 and v2 < 99 and v3 < 500;
--- SQL command prepared.
>>
>>explain options 'f' x1;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
18 . 19 root x 4.00E+000
16 17 18 tuple_flow 4.00E+000
. . 17 trafodion_update T02TABB 1.00E+000
15 2 16 hybrid_hash_join 4.00E+000
14 4 15 hybrid_hash_join 4.00E+000
13 6 14 hybrid_hash_join 4.00E+000
12 8 13 hybrid_hash_join 4.00E+000
11 10 12 hybrid_hash_join 4.00E+000
. . 11 trafodion_scan T02TABB 4.00E+000
9 . 10 sort_scalar_aggr 1.00E+000
....
Read more...
summary: - vsbb scan causes query to return wrong result
+ vsbb scan/delete causes query to return wrong result
summary: - vsbb scan/delete causes query to return wrong result
+ vsbb scan/delete cause query to return wrong result
Sandhya Sundaresan (sandhya-sundaresan) on 2015-05-12
Changed in trafodion:
assignee: nobody → Selvaganesan Govindarajan (selva-ganesan)
Selvaganesan Govindarajan (selva-ganesan) wrote on 2015-05-21: #5
Please refer to https://review.trafodion.org/#/c/1683/ for details
Changed in trafodion:
status: New → Fix Committed
Weishiun Tsai (wei-shiun-tsai) wrote on 2015-05-26: #6
Download full text (8.2 KiB)
Tried on the v0526 build. While the vsbb delete problem sited in the 2nd
example is no longer seen, the vsbb scan problem sited in the 1st example still
remains. The query still returns different results with the CQD
HBASE_ROWSET_VSBB_OPT turned ON (default) and OFF. Here is the execution output
showing the different results on the v0526 build. This case is reopened.
>>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,
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
+> )
+> );
--- SQL command prepared.
>>
>>explain options 'f' x1;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
22 . 23 root 1.00E+000
21 . 22 sort_partial_aggr_ro 1.00E+000
20 . 21 esp_exchange 1:8(hash2) 1.00E+000
19 . 20 sort_partial_aggr_le 1.00E+000
18 2 19 hybrid_hash_join 7.00E+000
14 17 18 merge_join 7.00E+000
16 . 17 sort 7.19E+003
15 . 16 esp_exchange 8(hash2):8(hash2) 7.19E+003
. . 15 trafodion_scan HOUSEHOLD_DEMOGRAPHI 7.19E+003
11 13 14 nested_join 1.09E+004
12 . 13 probe_cache 1.00E+000
. . 12 trafodion_vsbb_scan CUSTOMER_DEMOGRAPHIC 1.00E+000
10 . ...
Read more...
Changed in trafodion:
status: Fix Committed → New
Weishiun Tsai (wei-shiun-tsai) wrote on 2015-06-11: #7
Download full text (7.8 KiB)
Verified on the v0609 build (which has additional fix to this problem) with
full SQL regression test suite. This problem no longer showed up. The query
sited by the case returned the correct result with vsbb_scan as well:
>>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
---- ---- ---- -------------------- -------- -------------------- ---------
22 . 23 root 1.00E+000
21 . 22 sort_partial_aggr_ro 1.00E+000
20 . 21 esp_exchange 1:13(hash2) 1.00E+000
19 . 20 sort_partial_aggr_le 1.00E+000
18 2 19 hybrid_hash_join 7.00E+000
14 17 18 merge_join 7.00E+000
16 . 17 sort 7.19E+003
15 . 16 esp_exchange 13(hash2):8(hash2) 7.19E+003
. . 15 trafodion_scan HOUSEHOLD_DEMOGRAPHI 7.19E+003
11 13 14 nested_join 1.14E+004
12 . 13 probe_cache 1.00E+000
. . 12 trafodion_vsbb_scan CUSTOMER_DEMOGRAPHIC 1.00E+000
10 . 11 sort 1.14E+004
9 4 10 hybrid_hash_join 1.14E+004
8 6 9 hybrid_hash_join 6.62E+...
Read more...
Changed in trafodion:
status: New → Fix Released
> 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: 1.1 (pre-incubation)
>
>
> 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)
