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

MariaDB does not eliminate duplicated identical deterministic scalar predicates

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 12.2.2
    • N/A
    • Optimizer
    • None
    • Not for Release Notes

    Description

      Two logically equivalent predicates, A and A AND A, return the same result but have significantly different execution times when A contains an expensive deterministic scalar expression.
      In the test case below, the predicate REPEAT(c,n) <> 'y' is duplicated exactly. Since A AND A is logically equivalent to A, the duplicated predicate could be simplified or evaluated only once per row. However, MariaDB appears to evaluate the same deterministic expression twice.
      In my test, both queries returned COUNT = 1000, but the duplicated-predicate query was consistently about 2x slower.

      How to repeat

      DROP DATABASE IF EXISTS perf_scalar_dup;
      CREATE DATABASE perf_scalar_dup;
      USE perf_scalar_dup;
       
      CREATE TABLE t(
        c VARCHAR(10),
        n INT
      );
       
      CREATE TABLE d(n INT);
       
      INSERT INTO d VALUES
        (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      INSERT INTO t
      SELECT 'x', 50000
      FROM d a JOIN d b JOIN d c;
       
      SET profiling = 1;
       
      SELECT SQL_NO_CACHE COUNT(*)
      FROM t
      WHERE REPEAT(c,n) <> 'y';
       
      SELECT SQL_NO_CACHE COUNT(*)
      FROM t
      WHERE REPEAT(c,n) <> 'y'
        AND REPEAT(c,n) <> 'y';
       
      SHOW PROFILES;
      

      Observed result

      Query 1 result: COUNT(*) = 1000
      Query 2 result: COUNT(*) = 1000
       
      Repeated test durations:
       
      Query 1:
      0.04721763 sec
      0.04868769 sec
      0.04792680 sec
       
      Query 2:
      0.09598557 sec
      0.09621345 sec
      0.09566383 sec
      

      Expected result
      MariaDB should recognize the duplicated identical deterministic predicate and avoid evaluating the same expression twice, so both queries should have comparable execution time.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              chen7897 cl hl
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.