You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2015/08/27 15:45:45 UTC
[jira] [Closed] (TRAFODION-1230) LP Bug: 1452424 - vsbb scan/delete
cause query to return wrong result
[ https://issues.apache.org/jira/browse/TRAFODION-1230?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Suresh Subbiah closed TRAFODION-1230.
-------------------------------------
> 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)