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

NOT EXISTS to IN (part of exists2in transformation for 10.0)

    XMLWordPrintable

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.2
    • None
    • None

    Description

      Transform queries like:

      ... WHERE EXISTS (SELECT 1 FROM inner_table WHERE inner_table.field = 
      2*outer_table.field AND maybe_something_else)...

      and

      ... WHERE NOT EXISTS (SELECT 1 FROM inner_table WHERE inner_table.field = 
      2*outer_table.field AND maybe_something_else)...

      into

      ... WHERE 2*outer_table.field IN (SELECT inner_table.field FROM inner_table 
      WHERE 1 = 1 AND maybe_something_else)..

      ... WHERE NOT( 2*outer_table.field IS NOT NULL AND NOT 2*outer_table.field IN 
      (SELECT 
      inner_table.field FROM inner_table WHERE inner_table.field IS NOT NULL AND 
      maybe_something_else)...

      To allow optimizations made for IN/ALL/ANY subqueries.

      Conversion is possible only if:
      1)real NULL is not important (top element of WHERE/ON AND/OR list, i.e. NULL
      equal to FALSE)
      2a)subquery has dependences in the WHERE clause and they are equalities and in is not "NOT EXISTS" (this kind of queries could be converted to semijoin)
      OR
      2b)subquery has the only dependences (after bringing them out subquery become independent) in the WHERE clause and they are equalities (could be materialized)
      3)the subquery is simple (has no aggregate function, GROUp BY, ORDER BY, LIMIT
      HAVING and so on)

      For NOT EXISTS conversion the subquery should be marked that its left part can't
      be NULL.

      Note: Number of equalities could be limited by already allocated space for select list for current implementation.

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              ratzpo Rasmus Johansson (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.