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

Improvement: make derived_with_keys not generate identical draft keys

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    Description

      derived_with_keys optimization works by generating "draft" keys for the derived temporary table and feeding them into the optimizer.

      The "draft" keys can duplicate each other, as well as duplicate keys generated by other optimizations, e.g. IN-list-to-subquery optimization.

      This task is about not generating draft keys that are duplicate of each other. Generate just one key and use it multiple times.

      An example where one can see multiple identical keys are generated:

      CREATE TABLE t1 (l_orderkey int);
      INSERT INTO t1 VALUES (1),(2);
      CREATE TABLE t2 (o_orderkey int);
      INSERT INTO t2 VALUES (3),(4);
      SET IN_PREDICATE_CONVERSION_THRESHOLD= 2;
      SET OPTIMIZER_SWITCH= 'derived_with_keys=on';
      SELECT * FROM t1 JOIN t2 ON (l_orderkey = o_orderkey) WHERE l_orderkey IN (1, 2, 3);
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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