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

SELECT with WHERE IN and ORDER BY does not return rows

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.4.7
    • Fix Version/s: N/A
    • Component/s: N/A
    • Labels:
      None
    • Environment:
      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

              • Assignee:
                alice Alice Sherepa
                Reporter:
                Ellerbrockr RE
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: