Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-6106

test_tpcds_q53 extremely flaky because of decimal_v2 not being reset

    Details

      Description

      10:21:07  TestTpcdsQuery.test_tpcds_q53[exec_option: {'batch_size': 0, 'num_nodes': 0, 'disable_codegen_rows_threshold': 0, 'disable_codegen': False, 'abort_on_error': 1, 'exec_single_node_rows_threshold': 0} | table_format: parquet/none] 
      10:21:07 [gw2] linux2 -- Python 2.6.6 /data/jenkins/workspace/impala-umbrella-build-and-test/repos/Impala/bin/../infra/python/env/bin/python
      10:21:07 query_test/test_tpcds_queries.py:163: in test_tpcds_q53
      10:21:07     self.run_test_case(self.get_workload() + '-q53', vector)
      10:21:07 common/impala_test_suite.py:420: in run_test_case
      10:21:07     self.__verify_results_and_errors(vector, test_section, result, use_db)
      10:21:07 common/impala_test_suite.py:293: in __verify_results_and_errors
      10:21:07     replace_filenames_with_placeholder)
      10:21:07 common/test_result_verifier.py:404: in verify_raw_results
      10:21:07     VERIFIER_MAP[verifier](expected, actual)
      10:21:07 common/test_result_verifier.py:231: in verify_query_result_is_equal
      10:21:07     assert expected_results == actual_results
      10:21:07 E   assert Comparing QueryTestResults (expected vs actual):
      10:21:07 E     30,165.67,340.63 != 30,165.67,340.635000
      10:21:07 E     30,247.07,340.63 != 30,247.07,340.635000
      10:21:07 E     30,627.63,340.63 != 30,627.63,340.635000
      10:21:07 E     619,158.76,348.64 != 619,158.76,348.640000
      10:21:07 E     619,210.81,348.64 != 619,210.81,348.640000
      10:21:07 E     619,464.26,348.64 != 619,464.26,348.640000
      10:21:07 E     619,560.73,348.64 != 619,560.73,348.640000
      10:21:07 E     271,79.26,354.33 != 271,79.26,354.330000
      10:21:07 E     271,86.87,354.33 != 271,86.87,354.330000
      10:21:07 E     271,179.61,354.33 != 271,179.61,354.330000
      10:21:07 E     271,1071.58,354.33 != 271,1071.58,354.330000
      10:21:07 E     827,82.44,356.59 != 827,82.44,356.590000
      10:21:07 E     827,666.52,356.59 != 827,320.05,356.590000
      10:21:07 E     296,188.61,369.12 != 827,666.52,356.590000
      10:21:07 E     296,265.76,369.12 != 296,188.61,369.125000
      10:21:07 E     296,655.24,369.12 != 296,265.76,369.125000
      10:21:07 E     308,200.28,385.98 != 296,655.24,369.125000
      10:21:07 E     308,214.07,385.98 != 308,200.28,385.982500
      10:21:07 E     308,489.17,385.98 != 308,214.07,385.982500
      10:21:07 E     308,640.41,385.98 != 308,489.17,385.982500
      10:21:07 E     486,178.80,400.53 != 308,640.41,385.982500
      10:21:07 E     486,455.08,400.53 != 486,178.80,400.530000
      10:21:07 E     486,468.01,400.53 != 486,455.08,400.530000
      10:21:07 E     486,500.23,400.53 != 486,468.01,400.530000
      10:21:07 E     554,191.48,407.97 != 486,500.23,400.530000
      10:21:07 E     554,346.80,407.97 != 554,191.48,407.975000
      10:21:07 E     554,660.97,407.97 != 554,346.80,407.975000
      10:21:07 E     208,151.84,410.38 != 554,660.97,407.975000
      10:21:07 E     208,207.02,410.38 != 208,151.84,410.385000
      10:21:07 E     208,533.75,410.38 != 208,207.02,410.385000
      10:21:07 E     208,748.93,410.38 != 208,533.75,410.385000
      10:21:07 E     662,199.83,412.13 != 208,748.93,410.385000
      10:21:07 E     662,300.61,412.13 != 662,199.83,412.135000
      10:21:07 E     662,460.94,412.13 != 662,300.61,412.135000
      10:21:07 E     662,687.16,412.13 != 662,460.94,412.135000
      10:21:07 E     394,264.65,413.40 != 662,687.16,412.135000
      10:21:07 E     394,272.02,413.40 != 394,264.65,413.405000
      10:21:07 E     394,674.12,413.40 != 394,272.02,413.405000
      10:21:07 E     221,183.63,416.08 != 394,674.12,413.405000
      10:21:07 E     221,534.62,416.08 != 221,183.63,416.082500
      10:21:07 E     221,552.44,416.08 != 221,534.62,416.082500
      10:21:07 E     621,232.20,418.11 != 221,552.44,416.082500
      10:21:07 E     621,624.80,418.11 != 621,232.20,418.115000
      10:21:07 E     507,76.98,419.46 != 621,624.80,418.115000
      10:21:07 E     507,505.36,419.46 != 507,76.98,419.465000
      10:21:07 E     507,644.24,419.46 != 507,505.36,419.465000
      10:21:07 E     316,36.25,423.03 != 507,644.24,419.465000
      10:21:07 E     316,158.85,423.03 != 316,36.25,423.035000
      10:21:07 E     316,697.32,423.03 != 316,158.85,423.035000
      10:21:07 E     316,799.72,423.03 != 316,697.32,423.035000
      10:21:07 E     56,110.81,423.09 != 316,799.72,423.035000
      10:21:07 E     56,371.77,423.09 != 56,110.81,423.097500
      10:21:07 E     56,500.77,423.09 != 56,371.77,423.097500
      10:21:07 E     56,709.04,423.09 != 56,500.77,423.097500
      10:21:07 E     517,194.87,426.28 != 56,709.04,423.097500
      10:21:07 E     517,313.41,426.28 != 517,194.87,426.287500
      10:21:07 E     517,482.41,426.28 != 517,313.41,426.287500
      10:21:07 E     517,714.46,426.28 != 517,482.41,426.287500
      10:21:07 E     411,364.09,426.89 != 517,714.46,426.287500
      10:21:07 E     411,519.60,426.89 != 411,364.09,426.892500
      10:21:07 E     247,313.42,427.68 != 411,519.60,426.892500
      10:21:07 E     247,353.08,427.68 != 247,313.42,427.682500
      10:21:07 E     247,628.37,427.68 != 247,353.08,427.682500
      10:21:07 E     652,228.75,434.75 != 247,628.37,427.682500
      10:21:07 E     652,314.98,434.75 != 652,228.75,434.752500
      10:21:07 E     652,365.90,434.75 != 652,314.98,434.752500
      10:21:07 E     652,829.38,434.75 != 652,365.90,434.752500
      10:21:07 E     129,301.69,436.45 != 652,829.38,434.752500
      10:21:07 E     129,325.26,436.45 != 129,301.69,436.452500
      10:21:07 E     129,550.47,436.45 != 129,325.26,436.452500
      10:21:07 E     129,568.39,436.45 != 129,550.47,436.452500
      10:21:07 E     99,164.24,438.66 != 129,568.39,436.452500
      10:21:07 E     99,183.75,438.66 != 99,164.24,438.667500
      10:21:07 E     99,1013.10,438.66 != 99,183.75,438.667500
      10:21:07 E     235,111.64,441.03 != 99,393.58,438.667500
      10:21:07 E     235,179.51,441.03 != 99,1013.10,438.667500
      10:21:07 E     235,493.83,441.03 != 235,111.64,441.030000
      10:21:07 E     235,979.14,441.03 != 235,179.51,441.030000
      10:21:07 E     360,143.83,445.41 != 235,493.83,441.030000
      10:21:07 E     360,293.99,445.41 != 235,979.14,441.030000
      10:21:07 E     360,928.51,445.41 != 360,143.83,445.415000
      10:21:07 E     732,106.56,447.06 != 360,293.99,445.415000
      10:21:07 E     732,300.77,447.06 != 360,928.51,445.415000
      10:21:07 E     732,498.49,447.06 != 732,106.56,447.065000
      10:21:07 E     732,882.44,447.06 != 732,300.77,447.065000
      10:21:07 E     190,202.77,451.98 != 732,498.49,447.065000
      10:21:07 E     190,233.10,451.98 != 732,882.44,447.065000
      10:21:07 E     190,358.97,451.98 != 190,202.77,451.982500
      10:21:07 E     190,1013.09,451.98 != 190,233.10,451.982500
      10:21:07 E     147,212.53,455.89 != 190,358.97,451.982500
      10:21:07 E     147,366.10,455.89 != 190,1013.09,451.982500
      10:21:07 E     147,553.92,455.89 != 147,212.53,455.892500
      10:21:07 E     147,691.02,455.89 != 147,366.10,455.892500
      10:21:07 E     665,283.25,456.08 != 147,553.92,455.892500
      10:21:07 E     665,357.22,456.08 != 147,691.02,455.892500
      10:21:07 E     665,691.20,456.08 != 665,283.25,456.087500
      10:21:07 E     85,325.28,456.28 != 665,357.22,456.087500
      10:21:07 E     85,350.10,456.28 != 665,691.20,456.087500
      10:21:07 E     85,387.62,456.28 != 85,325.28,456.280000
      10:21:07 E     85,762.12,456.28 != 85,350.10,456.280000
      10:21:07 ----------------------------- Captured stderr call -----------------------------
      10:21:07 -- executing against localhost:21000
      10:21:07 use tpcds_parquet;
      10:21:07 
      10:21:07 SET batch_size=0;
      10:21:07 SET num_nodes=0;
      10:21:07 SET disable_codegen_rows_threshold=0;
      10:21:07 SET disable_codegen=False;
      10:21:07 SET abort_on_error=1;
      10:21:07 SET exec_single_node_rows_threshold=0;
      10:21:07 MainThread: Query Name: 
      10:21:07 TPCDS-Q53
      10:21:07 
      10:21:07 -- executing against localhost:21000
      10:21:07 select
      10:21:07   *
      10:21:07 from
      10:21:07   (select
      10:21:07     i_manufact_id,
      10:21:07     sum(ss_sales_price) sum_sales,
      10:21:07     avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales
      10:21:07   from
      10:21:07     item,
      10:21:07     store_sales,
      10:21:07     date_dim,
      10:21:07     store
      10:21:07   where
      10:21:07     ss_item_sk = i_item_sk
      10:21:07     and ss_sold_date_sk = d_date_sk
      10:21:07     and ss_store_sk = s_store_sk
      10:21:07     and d_month_seq in (1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7, 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 11)
      10:21:07     and ((i_category in ('Books', 'Children', 'Electronics')
      10:21:07       and i_class in ('personal', 'portable', 'reference', 'self-help')
      10:21:07       and i_brand in ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9'))
      10:21:07     or (i_category in ('Women', 'Music', 'Men')
      10:21:07       and i_class in ('accessories', 'classical', 'fragrances', 'pants')
      10:21:07       and i_brand in ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')))
      10:21:07   group by
      10:21:07     i_manufact_id,
      10:21:07     d_qoy
      10:21:07   ) tmp1
      10:21:07 where
      10:21:07   case when avg_quarterly_sales > 0 then abs (sum_sales - avg_quarterly_sales) / avg_quarterly_sales else null end > 0.1
      10:21:07 order by
      10:21:07   avg_quarterly_sales,
      10:21:07   sum_sales,
      10:21:07   i_manufact_id
      10:21:07 limit 100;
      10:21:07 
      10:21:07 MainThread: Comparing QueryTestResults (expected vs actual):
      10:21:07 30,165.67,340.63 != 30,165.67,340.635000
      10:21:07 30,247.07,340.63 != 30,247.07,340.635000
      10:21:07 30,627.63,340.63 != 30,627.63,340.635000
      10:21:07 619,158.76,348.64 != 619,158.76,348.640000
      10:21:07 619,210.81,348.64 != 619,210.81,348.640000
      10:21:07 619,464.26,348.64 != 619,464.26,348.640000
      10:21:07 619,560.73,348.64 != 619,560.73,348.640000
      10:21:07 271,79.26,354.33 != 271,79.26,354.330000
      10:21:07 271,86.87,354.33 != 271,86.87,354.330000
      10:21:07 271,179.61,354.33 != 271,179.61,354.330000
      10:21:07 271,1071.58,354.33 != 271,1071.58,354.330000
      10:21:07 827,82.44,356.59 != 827,82.44,356.590000
      10:21:07 827,666.52,356.59 != 827,320.05,356.590000
      10:21:07 296,188.61,369.12 != 827,666.52,356.590000
      10:21:07 296,265.76,369.12 != 296,188.61,369.125000
      10:21:07 296,655.24,369.12 != 296,265.76,369.125000
      10:21:07 308,200.28,385.98 != 296,655.24,369.125000
      10:21:07 308,214.07,385.98 != 308,200.28,385.982500
      10:21:07 308,489.17,385.98 != 308,214.07,385.982500
      10:21:07 308,640.41,385.98 != 308,489.17,385.982500
      10:21:07 486,178.80,400.53 != 308,640.41,385.982500
      10:21:07 486,455.08,400.53 != 486,178.80,400.530000
      10:21:07 486,468.01,400.53 != 486,455.08,400.530000
      10:21:07 486,500.23,400.53 != 486,468.01,400.530000
      10:21:07 554,191.48,407.97 != 486,500.23,400.530000
      10:21:07 554,346.80,407.97 != 554,191.48,407.975000
      10:21:07 554,660.97,407.97 != 554,346.80,407.975000
      10:21:07 208,151.84,410.38 != 554,660.97,407.975000
      10:21:07 208,207.02,410.38 != 208,151.84,410.385000
      10:21:07 208,533.75,410.38 != 208,207.02,410.385000
      10:21:07 208,748.93,410.38 != 208,533.75,410.385000
      10:21:07 662,199.83,412.13 != 208,748.93,410.385000
      10:21:07 662,300.61,412.13 != 662,199.83,412.135000
      10:21:07 662,460.94,412.13 != 662,300.61,412.135000
      10:21:07 662,687.16,412.13 != 662,460.94,412.135000
      10:21:07 394,264.65,413.40 != 662,687.16,412.135000
      10:21:07 394,272.02,413.40 != 394,264.65,413.405000
      10:21:07 394,674.12,413.40 != 394,272.02,413.405000
      10:21:07 221,183.63,416.08 != 394,674.12,413.405000
      10:21:07 221,534.62,416.08 != 221,183.63,416.082500
      10:21:07 221,552.44,416.08 != 221,534.62,416.082500
      10:21:07 621,232.20,418.11 != 221,552.44,416.082500
      10:21:07 621,624.80,418.11 != 621,232.20,418.115000
      10:21:07 507,76.98,419.46 != 621,624.80,418.115000
      10:21:07 507,505.36,419.46 != 507,76.98,419.465000
      10:21:07 507,644.24,419.46 != 507,505.36,419.465000
      10:21:07 316,36.25,423.03 != 507,644.24,419.465000
      10:21:07 316,158.85,423.03 != 316,36.25,423.035000
      10:21:07 316,697.32,423.03 != 316,158.85,423.035000
      10:21:07 316,799.72,423.03 != 316,697.32,423.035000
      10:21:07 56,110.81,423.09 != 316,799.72,423.035000
      10:21:07 56,371.77,423.09 != 56,110.81,423.097500
      10:21:07 56,500.77,423.09 != 56,371.77,423.097500
      10:21:07 56,709.04,423.09 != 56,500.77,423.097500
      10:21:07 517,194.87,426.28 != 56,709.04,423.097500
      10:21:07 517,313.41,426.28 != 517,194.87,426.287500
      10:21:07 517,482.41,426.28 != 517,313.41,426.287500
      10:21:07 517,714.46,426.28 != 517,482.41,426.287500
      10:21:07 411,364.09,426.89 != 517,714.46,426.287500
      10:21:07 411,519.60,426.89 != 411,364.09,426.892500
      10:21:07 247,313.42,427.68 != 411,519.60,426.892500
      10:21:07 247,353.08,427.68 != 247,313.42,427.682500
      10:21:07 247,628.37,427.68 != 247,353.08,427.682500
      10:21:07 652,228.75,434.75 != 247,628.37,427.682500
      10:21:07 652,314.98,434.75 != 652,228.75,434.752500
      10:21:07 652,365.90,434.75 != 652,314.98,434.752500
      10:21:07 652,829.38,434.75 != 652,365.90,434.752500
      10:21:07 129,301.69,436.45 != 652,829.38,434.752500
      10:21:07 129,325.26,436.45 != 129,301.69,436.452500
      10:21:07 129,550.47,436.45 != 129,325.26,436.452500
      10:21:07 129,568.39,436.45 != 129,550.47,436.452500
      10:21:07 99,164.24,438.66 != 129,568.39,436.452500
      10:21:07 99,183.75,438.66 != 99,164.24,438.667500
      10:21:07 99,1013.10,438.66 != 99,183.75,438.667500
      10:21:07 235,111.64,441.03 != 99,393.58,438.667500
      10:21:07 235,179.51,441.03 != 99,1013.10,438.667500
      10:21:07 235,493.83,441.03 != 235,111.64,441.030000
      10:21:07 235,979.14,441.03 != 235,179.51,441.030000
      10:21:07 360,143.83,445.41 != 235,493.83,441.030000
      10:21:07 360,293.99,445.41 != 235,979.14,441.030000
      10:21:07 360,928.51,445.41 != 360,143.83,445.415000
      10:21:07 732,106.56,447.06 != 360,293.99,445.415000
      10:21:07 732,300.77,447.06 != 360,928.51,445.415000
      10:21:07 732,498.49,447.06 != 732,106.56,447.065000
      10:21:07 732,882.44,447.06 != 732,300.77,447.065000
      10:21:07 190,202.77,451.98 != 732,498.49,447.065000
      10:21:07 190,233.10,451.98 != 732,882.44,447.065000
      10:21:07 190,358.97,451.98 != 190,202.77,451.982500
      10:21:07 190,1013.09,451.98 != 190,233.10,451.982500
      10:21:07 147,212.53,455.89 != 190,358.97,451.982500
      10:21:07 147,366.10,455.89 != 190,1013.09,451.982500
      10:21:07 147,553.92,455.89 != 147,212.53,455.892500
      10:21:07 147,691.02,455.89 != 147,366.10,455.892500
      10:21:07 665,283.25,456.08 != 147,553.92,455.892500
      10:21:07 665,357.22,456.08 != 147,691.02,455.892500
      10:21:07 665,691.20,456.08 != 665,283.25,456.087500
      10:21:07 85,325.28,456.28 != 665,357.22,456.087500
      10:21:07 85,350.10,456.28 != 665,691.20,456.087500
      10:21:07 85,387.62,456.28 != 85,325.28,456.280000
      10:21:07 85,762.12,456.28 != 85,350.10,456.280000
      10:21:07 ======= 1 failed, 1546 passed, 49 skipped, 36 xfailed in 4993.18 seconds =======
      

      The bad results are a result of running the query with decimal_v2=true.

      I tracked down the problem to the TPCDS-Q22A query test not resetting decimal_v2 after setting it. In the below test output I'd expect it to reset decimal_v2 at the end of the test.

      <testcase classname="query_test.test_tpcds_queries.TestTpcdsQuery" file="query_test/test_tpcds_queries.py" line="107" name="test_tpcds_q22a[exec_option: {&apos;batch_size&apos;: 0, &apos;num_nodes&apos;: 0, &apos;disable_codegen_rows_threshold&apos;: 0, &apos;disable_codegen&apos;: False, &apos;abort_on_error&apos;: 1, &apos;exec_single_node_rows_threshold&apos;: 0} | table_format: parquet/none]" time="17.4163360596"><system-err>-- executing against localhost:21000
      use tpcds_parquet;
      
      SET batch_size=0;
      SET num_nodes=0;
      SET disable_codegen_rows_threshold=0;
      SET disable_codegen=False;
      SET abort_on_error=1;
      SET exec_single_node_rows_threshold=0;
      MainThread: Query Name: 
      TPCDS-Q22A
      
      -- executing against localhost:21000
      -- RESULT MISMATCH FROM ORIGINAL in LSD of AVG() values
      -- FIXED, HAND_ROUNDED AVG() VALUES IN RESULT SET
      set decimal_v2=1;
      
      -- executing against localhost:21000
      
      with results as
      (select  i_product_name
                   ,i_brand
                   ,i_class
                   ,i_category
                   ,inv_quantity_on_hand qoh
             from inventory
                 ,date_dim
                 ,item
                 ,warehouse
             where  inv_date_sk=d_date_sk
                    and inv_item_sk=i_item_sk
                    and inv_warehouse_sk = w_warehouse_sk
                    and d_month_seq between 1200 and 1200 + 11
             -- group by i_product_name,i_brand,i_class,i_category
      ),
      results_rollup as
      (select i_product_name, i_brand, i_class, i_category,avg(qoh) qoh
      from results
      group by i_product_name,i_brand,i_class,i_category
      union all
      select i_product_name, i_brand, i_class, null i_category,avg(qoh) qoh
      from results
      group by i_product_name,i_brand,i_class
      union all
      select i_product_name, i_brand, null i_class, null i_category,avg(qoh) qoh
      from results
      group by i_product_name,i_brand
      union all
      select i_product_name, null i_brand, null i_class, null i_category,avg(qoh)  qoh
      from results
      group by i_product_name
      union all
      select null i_product_name, null i_brand, null i_class, null i_category,avg(qoh) qoh
      from results)
       select  i_product_name, i_brand, i_class, i_category, cast(qoh as decimal(12, 2))
            from results_rollup
            order by qoh, i_product_name, i_brand, i_class, i_category
      limit 100;
      
      </system-err></testcase>
      

      My hypothesis is that the comment interferes with the "set" command being parsed.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                tarmstrong Tim Armstrong
                Reporter:
                tarmstrong Tim Armstrong
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: