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: 09/Dec/18 02:12 PM
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:
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
There are no comments yet on this issue.