[
https://issues.apache.org/jira/browse/TRAFODION-1230?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah resolved TRAFODION-1230.
---------------------------------------
Resolution: Fixed
Fix Version/s: (was: 2.0-incubating)
1.1 (pre-incubation)
> 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)
