Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4350

Explain shows negative selectivity for Q8 from DBT3, MWL#253

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      Explain for Q8 shows negative selectivity when run against DBT3 scales >= 10. I tried with SF10, and SF30. Output:

      EXPLAIN EXTENDED
      select o_year, sum(case when nation = 'IRAQ' then volume else 0 end) / sum(volume) as mkt_share
      from
      (
      select
      year(o_orderdate) as o_year,
      l_extendedprice * (1 - l_discount) as volume,
      n2.n_name as nation
      from
      part,
      supplier,
      lineitem,
      orders,
      customer,
      nation n1,
      nation n2,
      region
      where
      p_partkey = l_partkey
      and s_suppkey = l_suppkey
      and l_orderkey = o_orderkey
      and o_custkey = c_custkey
      and c_nationkey = n1.n_nationkey
      and n1.n_regionkey = r_regionkey
      and r_name = 'MIDDLE EAST'
      and s_nationkey = n2.n_nationkey
      and o_orderdate between date('1995-01-01') and date ('1996-12-31')
      and p_type = 'STANDARD ANODIZED BRASS'
      ) as all_nations
      group by o_year
      order by o_year;

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      id select_type table type possible_keys key key_len ref rows filtered Extra

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 20.00 Using where; Using temporary; Using filesort
      1 SIMPLE n1 ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3.region.r_regionkey 5 100.00 Using index
      1 SIMPLE n2 ALL PRIMARY NULL NULL NULL 25 100.00 Using join buffer (flat, BNL join)
      1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3.n2.n_nationkey 12000 100.00 Using index
      1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3.n1.n_nationkey 180000 100.00 Using index
      1 SIMPLE part ALL PRIMARY NULL NULL NULL 6000000 -0.11 Using where; Using join buffer (flat, BNL join)
      1 SIMPLE orders ref PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey 5 dbt3.customer.c_custkey 15 49.88 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
      1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 5 dbt3.supplier.s_suppkey 599 100.00 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Attachments

        Issue Links

          Activity

            I also experience a similar effect on a small dataset.

            My settings:

            -----------------------------------------------------------------+

            Variable_name Value

            -----------------------------------------------------------------+

            histogram_size 100
            histogram_type SINGLE_PREC_HB

            What I get:

            set optimizer_use_stat_tables='complementary';
            set optimizer_use_condition_selectivity=4;
            MariaDB [j10]> explain extended select * from t1 where a='';
            ------------------------------------------------------------------------------

            id select_type table type possible_keys key key_len ref rows filtered Extra

            ------------------------------------------------------------------------------

            1 SIMPLE t1 ALL NULL NULL NULL NULL 10321 -47.67 Using where

            ------------------------------------------------------------------------------
            1 row in set, 1 warning (0.00 sec)

            psergei Sergei Petrunia added a comment - I also experience a similar effect on a small dataset. My settings: -------------------------------------------------- ---------------+ Variable_name Value -------------------------------------------------- ---------------+ histogram_size 100 histogram_type SINGLE_PREC_HB What I get: set optimizer_use_stat_tables='complementary'; set optimizer_use_condition_selectivity=4; MariaDB [j10] > explain extended select * from t1 where a=''; ----- ----------- ----- ---- ------------- ---- ------- ---- ----- -------- ------------ id select_type table type possible_keys key key_len ref rows filtered Extra ----- ----------- ----- ---- ------------- ---- ------- ---- ----- -------- ------------ 1 SIMPLE t1 ALL NULL NULL NULL NULL 10321 -47.67 Using where ----- ----------- ----- ---- ------------- ---- ------- ---- ----- -------- ------------ 1 row in set, 1 warning (0.00 sec)

            Dataset for a small example

            psergei Sergei Petrunia added a comment - Dataset for a small example

            The exact steps to reproduce are: on a fresh server run:

            use test;
            source /tmp/mdev4350-another-case-dataset.sql
            set histogram_size=100;
            set histogram_type='SINGLE_PREC_HB';
            analyze table t1 persistent for all;
            set optimizer_use_condition_selectivity=4;
            set use_stat_tables='complementary';
            explain extended select * from t1 where a='';

            psergei Sergei Petrunia added a comment - The exact steps to reproduce are: on a fresh server run: use test; source /tmp/mdev4350-another-case-dataset.sql set histogram_size=100; set histogram_type='SINGLE_PREC_HB'; analyze table t1 persistent for all; set optimizer_use_condition_selectivity=4; set use_stat_tables='complementary'; explain extended select * from t1 where a='';
            igor Igor Babaev added a comment -

            A fix for the bug has been pushed into maria-10.0-mwl253.

            igor Igor Babaev added a comment - A fix for the bug has been pushed into maria-10.0-mwl253.

            People

              igor Igor Babaev
              timour Timour Katchaounov (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.