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

Wrong result of SELECT query while two indexed colums are in WHERE section

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12, 5.5.36, 10.0.9
    • 5.5.37, 10.0.10
    • None
    • Centos 6.5 64bit base install

    Description

      Problem occurs on test table, which consists of two indexed comlumns and primary key column. With following query:

      SELECT count(*)
      		 
      FROM `test`
      		 
      WHERE `col2` = '636146' AND `col1` = '7+';

      result: 0

      Same query while running after: 

      SET optimizer_switch='index_merge=off';

      result: 73

      I attach my test database, which is derived from production database where issue occurs. I was able to reproduce problem on several other systems.

      I was unable to reproduce same error on MySQL 5.5.35

      Issue might be related to "intersect(col1,col2);" in query EXPLAIN, while any modification of query (eg. adding OR condition to WHERE expression) seems to avoid the issue. Deleting primary key (which is not involved in query), changing table to MyISAM seems to cause correct behavior of system.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-5177 ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            benas Vojtech Benes (Inactive) created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Labels MariaDB_5.5 optimizer optimizer
            serg Sergei Golubchik made changes -
            Description Problem occurs on test table, which consists of two indexed comlumns and primary key column. With following query:

            SELECT count(*)
            FROM `test`
            WHERE `col2` = '636146' AND `col1` = '7+';
            result: 0

            Same query while running after:
            SET optimizer_switch='index_merge=off';
            result: 73

            I attach my test database, which is derived from production database where issue occurs. I was able to reproduce problem on several other systems.

            I was unable to reproduce same error on MySQL 5.5.35

            Issue might be related to "intersect(col1,col2);" in query EXPLAIN, while any modification of query (eg. adding OR condition to WHERE expression) seems to avoid the issue. Deleting primary key (which is not involved in query), changing table to MyISAM seems to cause correct behavior of system.             		Problem occurs on test table, which consists of two indexed comlumns and primary key column. With following query:
            {code:sql}
            SELECT count(*)
            FROM `test`
            WHERE `col2` = '636146' AND `col1` = '7+';
            {code}
            result: 0

            Same query while running after:
            {code:sql}
            SET optimizer_switch='index_merge=off';
            {code}
            result: 73

            I attach my test database, which is derived from production database where issue occurs. I was able to reproduce problem on several other systems.

            I was unable to reproduce same error on MySQL 5.5.35

            Issue might be related to "intersect(col1,col2);" in query EXPLAIN, while any modification of query (eg. adding OR condition to WHERE expression) seems to avoid the issue. Deleting primary key (which is not involved in query), changing table to MyISAM seems to cause correct behavior of system.
            serg Sergei Golubchik made changes -
            Assignee Elena Stepanova [ elenst ]
            serg Sergei Golubchik made changes -
            Affects Version/s 5.5.35 [ 14000 ]
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            Fix Version/s 10.0.10 [ 14500 ]
            Fix Version/s 5.5.37 [ 15000 ]
            Affects Version/s 5.3.12 [ 12000 ]
            Affects Version/s 10.0.9 [ 14400 ]
            Assignee Elena Stepanova [ elenst ] Sergei Petrunia [ psergey ]
            Priority Critical [ 2 ] Major [ 3 ]
            psergei Sergei Petrunia made changes -
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 36413 ] MariaDB v2 [ 43397 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 43397 ] MariaDB v3 [ 63154 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 63154 ] MariaDB v4 [ 147607 ]

            People

              psergei Sergei Petrunia
              benas Vojtech Benes (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.