Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-1230

LP Bug: 1452424 - vsbb scan/delete cause query to return wrong result

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.1 (pre-incubation)
    • Component/s: sql-exe
    • Labels:
      None

      Description

      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.

        Attachments

          Activity

            People

            • Assignee:
              selvag Selvaganesan Govindarajan
              Reporter:
              WTsai Weishiun Tsai
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: