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

Transform [NOT] IN predicate with long list of values INTO [NOT] IN subquery.

    XMLWordPrintable

Details

    • 10.3.3-1

    Description

      Currently if MariaDB checks whether possibility to use range access based on [NOT] IN predicate with a long list of values it build SEL_TREEs taking huge amount of memory.
      Meanwhile using CTE specified by table value constructor such a predicate could be transformed into an equivalent [NOT] IN subquery:

      SELECT ... WHERE ... (expr1, ...) [NOT] IN (value_list) ...;
      		 
      =>
      		 
      WITH t(col1, ...) AS (SELECT * FROM VALUES value_list) 
      SELECT ... WHERE ... (expr1, ...) [NOT] IN (SELECT * FROM t)...;

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14281 Wrong result from query with NOT IN predicate in WHERE

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17795 Query with long IN(...) list in WHERE about 40 times slower

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20105 Case for bringing in_subquery_conversion_threshold back in next possible release

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20900 IN predicate to IN subquery conversion causes performance regression

          • Major - Major loss of function.
          • Closed
          is blocked by

          Task - A task that needs to be done. MDEV-12172 Implement tables specified by table value constructors.

          • Major - Major loss of function.
          • Closed

          Activity

            People

              igor Igor Babaev
              igor Igor Babaev
              Votes:
              0 Vote for this issue
              Watchers:
              8 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.