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
            elenst Elena Stepanova added a comment -

            Reproducible with the data from the attachment and the query from the description: on 5.3 (14/73 rows), 5.5, 10.0 (0/73 rows), mysql-5.6 (70/73 rows).

            The bug is likely to be a duplicate of MDEV-5177, but since the latter turned out to be tricky, I suggest to make sure the bugfix covers this test case as well before closing it.

            elenst Elena Stepanova added a comment - Reproducible with the data from the attachment and the query from the description: on 5.3 (14/73 rows), 5.5, 10.0 (0/73 rows), mysql-5.6 (70/73 rows). The bug is likely to be a duplicate of MDEV-5177 , but since the latter turned out to be tricky, I suggest to make sure the bugfix covers this test case as well before closing it.
            psergei Sergei Petrunia added a comment - - edited

            Elena, is it reproducible on MySQL 5.6.16 or on MySQL 5.6.15?

            psergei Sergei Petrunia added a comment - - edited Elena, is it reproducible on MySQL 5.6.16 or on MySQL 5.6.15?
            elenst Elena Stepanova added a comment -

            I'm using the top of the public mysql-server/5.6 bzr tree, which has revno 5732 (tag mysql-5.6.16), dated 2014-01-09.

            elenst Elena Stepanova added a comment - I'm using the top of the public mysql-server/5.6 bzr tree, which has revno 5732 (tag mysql-5.6.16), dated 2014-01-09.
            psergei Sergei Petrunia added a comment -

            Ok, I can repeat this bug when I try on mariadb-5.5 without fix for MDEV-5177, and I cannot repeat it when fix for MDEV-5177 is applied. Other things also hint this is the same issue.

            psergei Sergei Petrunia added a comment - Ok, I can repeat this bug when I try on mariadb-5.5 without fix for MDEV-5177 , and I cannot repeat it when fix for MDEV-5177 is applied. Other things also hint this is the same issue.
            psergei Sergei Petrunia added a comment -

            Fixed by fix for MDEV-5177. Fix for MDEV-5177 is pushed into 5.5 tree.

            psergei Sergei Petrunia added a comment - Fixed by fix for MDEV-5177 . Fix for MDEV-5177 is pushed into 5.5 tree.
            psergei Sergei Petrunia added a comment -

            Vojtech, thanks for taking time to report the issue. Although this one has turned out to be already known, we (MariaDB devs) appreciate getting input.

            psergei Sergei Petrunia added a comment - Vojtech, thanks for taking time to report the issue. Although this one has turned out to be already known, we (MariaDB devs) appreciate getting input.
            benas Vojtech Benes (Inactive) added a comment -

            Thank you for fix. I'll be waiting for next release. In meantime I'll use 'index_merge=off' as workaround.

            benas Vojtech Benes (Inactive) added a comment - Thank you for fix. I'll be waiting for next release. In meantime I'll use 'index_merge=off' as workaround.

            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.