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
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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: {code:java} 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) {code} EXPLAIN says 3 rows are returned but none are displayed. Doing any of the following shows the rows: Removing the ORDER BY 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') |
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: {code:java} 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) {code} 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') |
| Assignee | Alice Sherepa [ alice ] |
| Link |
This issue relates to |
| Component/s | N/A [ 14411 ] | |
| Fix Version/s | N/A [ 14700 ] | |
| Resolution | Duplicate [ 3 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 99776 ] | MariaDB v4 [ 156768 ] |