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

same values in `IN` set vs equal comparison produces the different performance

Details

    Description

      The following query returns a different execution plan and a significantly different execution time. The following repro is exactly same as the initial bug report in MENT.

       
      		 
      MariaDB [sysbench]> explain select a.* from sysbench.sbtest1 a left join sysbench2.sbtest1 as b on a.id = b.id where a.id = 1000 order by b.id;
      		 
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
      		 
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
      		 
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
      		 
      |    1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const | 1    |             |
      		 
      |    1 | SIMPLE      | b     | const | PRIMARY       | PRIMARY | 4       | const | 1    | Using index |
      		 
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
      		 
      2 rows in set (0.000 sec)
      		 
       
      MariaDB [sysbench]> explain select a.* from sysbench.sbtest1 a left join sysbench2.sbtest1 as b on a.id = b.id where a.id in ( 1000, 1000) order by b.id;
      		 
      +------+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
      		 
      | id   | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra                                        |
      		 
      +------+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
      		 
      |    1 | SIMPLE      | a     | range  | PRIMARY       | PRIMARY | 4       | NULL          | 1    | Using where; Using temporary; Using filesort |
      		 
      |    1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | sysbench.a.id | 1    | Using index                                  |
      		 
      +------+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
      		 
      2 rows in set (0.000 sec)

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29895 prepared view crash server (unit.conc_view)

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

          Activity

            Ascending order - Click to sort in descending order
            allen.lee@mariadb.com Allen Lee (Inactive) created issue -
            oleg.smirnov Oleg Smirnov made changes -
            Field Original Value New Value
            Fix Version/s 10.6 [ 24028 ]
            oleg.smirnov Oleg Smirnov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Oleg Smirnov [ JIRAUSER50405 ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia added a comment -

            Review input provided on slack and on a call.

            psergei Sergei Petrunia added a comment - Review input provided on slack and on a call.
            psergei Sergei Petrunia made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Oleg Smirnov [ JIRAUSER50405 ]
            oleg.smirnov Oleg Smirnov added a comment -

            Pushed to 10.6.

            oleg.smirnov Oleg Smirnov added a comment - Pushed to 10.6.
            oleg.smirnov Oleg Smirnov made changes -
            Component/s Optimizer [ 10200 ]
            Fix Version/s 10.6.11 [ 28441 ]
            Fix Version/s 10.7.7 [ 28442 ]
            Fix Version/s 10.8.6 [ 28443 ]
            Fix Version/s 10.9.4 [ 28444 ]
            Fix Version/s 10.10.2 [ 28410 ]
            Fix Version/s 10.11.1 [ 28454 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            sanja Oleksandr Byelkin made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            sanja Oleksandr Byelkin added a comment - - edited

            The problem was mix of temporary and permanent changes in the conditions:

            1. we make copy of condition for future use in the statement memory which include creating the list of AND arguments
            2. temporary optimisation change IN to = in the list of the arguments and write a rollback
            3. other optimisation remove the AND (I do not heck if it is temporary or permanent, but delete was called for the ist and so memory "poisoned by user")
            (but still live in the statement memory)
            4. we are trying to rollback user poisoned memory to rollback IN to =

            see MDEV-29895

            sanja Oleksandr Byelkin added a comment - - edited The problem was mix of temporary and permanent changes in the conditions: 1. we make copy of condition for future use in the statement memory which include creating the list of AND arguments 2. temporary optimisation change IN to = in the list of the arguments and write a rollback 3. other optimisation remove the AND (I do not heck if it is temporary or permanent, but delete was called for the ist and so memory "poisoned by user") (but still live in the statement memory) 4. we are trying to rollback user poisoned memory to rollback IN to = see MDEV-29895
            sanja Oleksandr Byelkin made changes -
            serg Sergei Golubchik made changes -
            Comment [ reverted ]
            serg Sergei Golubchik made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            oleg.smirnov Oleg Smirnov made changes -
            marko Marko Mäkelä made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 115064

            People

              oleg.smirnov Oleg Smirnov
              allen.lee@mariadb.com Allen Lee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.