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

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      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

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              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.