Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.4.7
-
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
- relates to
-
-
- Closed
-