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
            View 13 older comments
            gunni Frank Sagurna added a comment -

            Should i open an additional bug for this one?

            MariaDB [(none)]> set @@in_predicate_conversion_threshold=1000000;
            ERROR 1193 (HY000): Unknown system variable 'in_predicate_conversion_threshold'
            MariaDB [(none)]>

            gunni Frank Sagurna added a comment - Should i open an additional bug for this one? MariaDB [(none)] > set @@in_predicate_conversion_threshold=1000000; ERROR 1193 (HY000): Unknown system variable 'in_predicate_conversion_threshold' MariaDB [(none)] >
            psergei Sergei Petrunia added a comment -

            Apparently @@in_predicate_conversion_threshold is present in debug builds only.

            psergei Sergei Petrunia added a comment - Apparently @@in_predicate_conversion_threshold is present in debug builds only.
            psergei Sergei Petrunia added a comment -

            Added a documentation stub: https://mariadb.com/kb/en/library/conversion-of-big-in-predicates-into-subqueries/

            psergei Sergei Petrunia added a comment - Added a documentation stub: https://mariadb.com/kb/en/library/conversion-of-big-in-predicates-into-subqueries/
            alexm Alexander Menk added a comment - - edited

            Related: MDEV-20871
            @Frank: Did you ever create an additional bug for this one?

            alexm Alexander Menk added a comment - - edited Related: MDEV-20871 @Frank: Did you ever create an additional bug for this one?
            gunni Frank Sagurna added a comment - - edited

            @Alexander: No i only created MDEV-17795, but there is MDEV-16871

            gunni Frank Sagurna added a comment - - edited @Alexander: No i only created MDEV-17795 , but there is MDEV-16871

            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.