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.

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.
          • Closed

          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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33560 Spider: query with long IN clause has performance issues

          • Critical - Crashes, loss of data, severe memory leak.
          • 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

            Ascending order - Click to sort in descending order
            igor Igor Babaev (Inactive) created issue -
            igor Igor Babaev (Inactive) made changes -
            Field Original Value New Value
            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:
            {noformat}
            SELECT ... WHERE ... (expr1, ...) [NOT] IN (value_list) ...;
            =>
            WITH t(col1, ...) AS (VALUES value_list)
            SELECT ... WHERE ... (expr1, ...) [NOT] IN t ...;
            		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:
            {noformat}
            SELECT ... WHERE ... (expr1, ...) [NOT] IN (value_list) ...;
            =>
            WITH t(col1, ...) AS (VALUES value_list)
            SELECT ... WHERE ... (expr1, ...) [NOT] IN t ...;
            {noformat}
            igor Igor Babaev (Inactive) made changes -
            igor Igor Babaev (Inactive) made changes -
            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:
            {noformat}
            SELECT ... WHERE ... (expr1, ...) [NOT] IN (value_list) ...;
            =>
            WITH t(col1, ...) AS (VALUES value_list)
            SELECT ... WHERE ... (expr1, ...) [NOT] IN t ...;
            {noformat}
            		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:
            {noformat}
            SELECT ... WHERE ... (expr1, ...) [NOT] IN (value_list) ...;
            =>
            WITH t(col1, ...) AS (VALUES value_list)
            SELECT ... WHERE ... (expr1, ...) [NOT] IN (SELECT * FROM t)...;
            {noformat}
            cvicentiu Vicențiu Ciorbaru made changes -
            Labels gsoc17
            igor Igor Babaev (Inactive) made changes -
            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:
            {noformat}
            SELECT ... WHERE ... (expr1, ...) [NOT] IN (value_list) ...;
            =>
            WITH t(col1, ...) AS (VALUES value_list)
            SELECT ... WHERE ... (expr1, ...) [NOT] IN (SELECT * FROM t)...;
            {noformat}
            		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:
            {noformat}
            SELECT ... WHERE ... (expr1, ...) [NOT] IN (value_list) ...;
            =>
            WITH t(col1, ...) AS (SELECT * FROM VALUES value_list)
            SELECT ... WHERE ... (expr1, ...) [NOT] IN (SELECT * FROM t)...;
            {noformat}
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.3.0 [ 22127 ]
            serg Sergei Golubchik made changes -
            Sprint 10.3.3-1 [ 200 ]
            serg Sergei Golubchik made changes -
            Rank Ranked higher
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Fix Version/s 10.3.3 [ 22644 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            stephane@skysql.com VAROQUI Stephane made changes -
            Comment [ Re, all queries do send Rows_sent: 25 , are you refering about row_examined ? ]
            gunni Frank Sagurna made changes -
            Comment [ That is because i executed them in phpmyadmin. phpmyadmin does two queries. One for the count, and then one with LIMIT 25, so it can acutally show the row count, and then the first 25 rows. ]
            gunni Frank Sagurna made changes -
            pslawek83 Slawomir Pryczek made changes -
            babanski Alex Babanski made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 79831 ] MariaDB v4 [ 133144 ]
            ycp Yuchen Pei made changes -

            People

              igor Igor Babaev (Inactive)
              igor Igor Babaev (Inactive)
              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.