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

SELECT with WHERE IN and ORDER BY does not return rows

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.4.7
    • N/A
    • N/A
    • None
    • centos7 and fedora30

    Description

      Query that worked on 10.2 no longer works after upgrading to 10.4.7. Simple test case attached. I can recreate the issue on 10.4.7 and 10.4.8 on centos7 and fedora30 using the Mariab official RPM's (not vendor built RPM's). The following RPM's are installed:

      MariaDB-common-10.4.8-1.fc30.x86_64
      MariaDB-server-10.4.8-1.fc30.x86_64
      MariaDB-oqgraph-engine-10.4.8-1.fc30.x86_64
      MariaDB-client-10.4.8-1.fc30.x86_64

      Using the attached table and dataset execute the following:

      MariaDB [prod_sync_re]> EXPLAIN
          -> SELECT * FROM services
          ->          WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
          ->          ORDER BY seq;
       
       
      SELECT * FROM services
               WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
               ORDER BY seq;
               +------+-------------+----------+------------+------------------------+------------------------+---------+-------+---------+------------------------------------------------------------------------+
      | id   | select_type | table    | type       | possible_keys          | key                    | key_len | ref   | rows    | Extra                                                                  |
      +------+-------------+----------+------------+------------------------+------------------------+---------+-------+---------+------------------------------------------------------------------------+
      |    1 | SIMPLE      | services | ref|filter | service_id,provisioned | service_id|provisioned | 108|1   | const | 3 (22%) | Using index condition; Using where; Using filesort; Using rowid filter |
      +------+-------------+----------+------------+------------------------+------------------------+---------+-------+---------+------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [prod_sync_re]>
      MariaDB [prod_sync_re]>
      MariaDB [prod_sync_re]> SELECT * FROM services
          ->          WHERE service_id='189c2d2e-017f-40b8-9555-4ea30f0ae351' AND provisioned IN ('S', 'R')
          ->          ORDER BY seq;
      Empty set (0.000 sec)
      
      

      EXPLAIN says 3 rows are returned but none are displayed.

      Doing any of the following shows the rows:

      Removing the ORDER BY
      Removing the provisioned index
      Change the IN to IN ('S')
      Delete any of the following columns: service_supplier_id, billing_group_id, service_descrip
      Remove the IN from the WHERE clause and do a post query filter using HAVING provisioned IN ('S', 'R')

      Attachments

        Issue Links

          Activity

            People

              alice Alice Sherepa
              Ellerbrockr richardeaxon
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.