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

Server tries to create and use GROUP BY handler where it doesn't

    XMLWordPrintable

    Details

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

      Description

      Greetings,

      Here is the query that caused the server to use GROUP BY handler despite the mentioned handler type isn't used according to an execution plan:

      SELECT SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE FROM LINEITEM, PART WHERE ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#41' AND P_CONTAINER IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND L_QUANTITY >= 3 AND L_QUANTITY <= 3 + 1 AND P_SIZE BETWEEN 1 AND 5 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#33' AND P_CONTAINER IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND L_QUANTITY >= 14 AND L_QUANTITY <= 14 + 1 AND P_SIZE BETWEEN 1 AND 10 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#45' AND P_CONTAINER IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND L_QUANTITY >= 25 AND L_QUANTITY <= 25 + 1 AND P_SIZE BETWEEN 1 AND 15 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' );
      

      Here is the execution plan:

      MariaDB [tpch1c]> explain extended SELECT SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE FROM LINEITEM, PART WHERE ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#41' AND P_CONTAINER IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND L_QUANTITY >= 3 AND L_QUANTITY <= 3 + 1 AND P_SIZE BETWEEN 1 AND 5 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#33' AND P_CONTAINER IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND L_QUANTITY >= 14 AND L_QUANTITY <= 14 + 1 AND P_SIZE BETWEEN 1 AND 10 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#45' AND P_CONTAINER IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND L_QUANTITY >= 25 AND L_QUANTITY <= 25 + 1 AND P_SIZE BETWEEN 1 AND 15 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' );
      +------+-------------+----------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+
      | id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                 |
      +------+-------------+----------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+
      |    1 | SIMPLE      | LINEITEM | ALL  | NULL          | NULL | NULL    | NULL | 2000 |   100.00 | Using where with pushed condition                                     |
      |    1 | SIMPLE      | PART     | ALL  | NULL          | NULL | NULL    | NULL | 2000 |   100.00 | Using where with pushed condition; Using join buffer (flat, BNL join) |
      +------+-------------+----------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+
      2 rows in set, 2 warnings (0.90 sec)
      

      The issue could be reproduced only with server/columnstore engine built of:

      develop branch columnstore fork of the server's code
      develop branch columnstore engine

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              drrtuy Roman
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: