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

          Activity

            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)] >

            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/
            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.