Issue Details (XML | Word | Printable)

Key: CORE-5795
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

ORDER BY clause on compound index may disable usage of other indices

Created: 17/Apr/18 12:14 PM   Updated: 31/Dec/19 08:22 AM
Component/s: Engine
Affects Version/s: 3.0.3, 3.0.4
Fix Version/s: 4.0 Beta 1, 3.0.5

File Attachments: 1. File TESTPLAN.7z (5.85 MB)

Environment: WI-V3.0.4.32951 Firebird 3.0
Issue Links:
Duplicate
 
Relate
 

QA Status: Covered by another test(s)


 Description  « Hide
This is releated to old CORE-5020 but now i have reproductible test case with sample database

restore attached database backup
run the query

SELECT
       W.DYR_ID
       , W.INSP_ID
       , W.KONTO_ID
       , W.WPLATA_DATA_WYCIAGU
       , W.WPLATA_KONTRAHENT_ID
FROM
       WPLATA W
WHERE
       W.WPLATA_KONTRAHENT_ID IN (136804)
       AND W.DYR_ID = 9
ORDER BY
       W.DYR_ID
       , W.INSP_ID
       , W.KONTO_ID
       , W.WPLATA_DATA_WYCIAGU
       , W.WPLATA_NR_WYCIAGU
       , W.WPLATA_NR_POZYCJI

-------------------------------------------------
PLAN (W ORDER WPLATA_PK)


Executing statement...
Statement executed (elapsed time: 0.000s).
679228 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 334971 index, 0 seq.
Delta memory: 248896 bytes.
Total execution time: 0.500s
Script execution finished.


------------------------------------------------
plan should be
PLAN (W INDEX (IXA_WPLATA__KONTRAHENT__PK))

Executing statement...
Statement executed (elapsed time: 0.000s).
19 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 6 index, 0 seq.
Delta memory: 58112 bytes.
Total execution time: 0.063s
Script execution finished.

or
-----------------------------------------------
PLAN (W ORDER WPLATA_PK INDEX (IXA_WPLATA__KONTRAHENT__PK))


Executing statement...
Statement executed (elapsed time: 0.000s).
185 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 9 index, 0 seq.
Delta memory: 59264 bytes.
Total execution time: 0.047s
Script execution finished.



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Karol Bieniaszewski made changes - 17/Apr/18 12:15 PM
Field Original Value New Value
Attachment TESTPLAN.7z [ 13237 ]
Karol Bieniaszewski made changes - 17/Apr/18 12:19 PM
Environment WI-V3.0.4.32951 Firebird 3.0
Dmitry Yemanov made changes - 17/Apr/18 03:26 PM
Assignee Dmitry Yemanov [ dimitr ]
Dmitry Yemanov made changes - 20/Nov/18 04:43 PM
Link This issue relate to CORE-5965 [ CORE-5965 ]
Dmitry Yemanov made changes - 20/Nov/18 06:21 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 4.0 Beta 1 [ 10750 ]
Fix Version/s 3.0.5 [ 10885 ]
Resolution Fixed [ 1 ]
Pavel Zotov made changes - 09/Dec/18 02:12 PM
Status Resolved [ 5 ] Resolved [ 5 ]
QA Status No test Covered by another test(s)
Pavel Zotov made changes - 09/Dec/18 02:12 PM
Status Resolved [ 5 ] Closed [ 6 ]
Dmitry Yemanov made changes - 31/Dec/19 08:22 AM
Link This issue is duplicated by CORE-5845 [ CORE-5845 ]