Issue Details (XML | Word | Printable)

Key: CORE-6024
Type: Bug Bug
Status: Resolved Resolved
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

FB3.0.4.33063 vs FB3.0.5.33100 manual plan cause "index cannot be used in the specified plan"

Created: 15/Mar/19 07:41 AM   Updated: 15/Jan/20 01:53 PM
Component/s: None
Affects Version/s: 3.0.5
Fix Version/s: 3.0.5

Environment:
Windows 2008R2
FB3.0.5.33100

QA Status: Done with caveats
Test Details:
Found issue on FB 4.0.
Sent letter to dimitr, 20.12.2019 23:51, waiting for reply.


 Description  « Hide
Something was broken between versions FB3.0.4.33063 vs FB3.0.5.33100
Under FB3.0.4.33063 there was possibility to specify ordered plan for this query now in FB3.0.5.33100 it is not possible.

Also look why this ordered plan is not default generated by the engine.


CREATE TABLE WPLATA
(
  DYR_ID Smallint NOT NULL,
  OKRES_NUMER Char(7) NOT NULL,
  INSP_ID Smallint NOT NULL,
  KONTO_ID Smallint NOT NULL,
  WPLATA_DATA_WYCIAGU Date NOT NULL,
  WPLATA_NR_WYCIAGU Varchar(10) NOT NULL,
  WPLATA_NR_POZYCJI Smallint NOT NULL,
  WPLATA_KONTRAHENT_ID Integer,
  CONSTRAINT PK_WPLATA PRIMARY KEY (DYR_ID,INSP_ID,KONTO_ID,WPLATA_DATA_WYCIAGU,WPLATA_NR_WYCIAGU,WPLATA_NR_POZYCJI)
);

CREATE INDEX IXA_WPLATA__KONTRAHENT__PK ON WPLATA (WPLATA_KONTRAHENT_ID,DYR_ID);

-----------------------------------------------------------------------------------------------------

SELECT
W.DYR_ID
, W.INSP_ID
, W.KONTO_ID
, W.WPLATA_DATA_WYCIAGU
, W.WPLATA_NR_WYCIAGU
, W.WPLATA_NR_POZYCJI
FROM
WPLATA W
WHERE
W.WPLATA_KONTRAHENT_ID IN (1452)
AND W.DYR_ID = 6
PLAN(W ORDER PK_WPLATA INDEX(IXA_WPLATA__KONTRAHENT__PK))
ORDER BY
W.DYR_ID
, W.INSP_ID
, W.KONTO_ID
, W.WPLATA_DATA_WYCIAGU
, W.WPLATA_NR_WYCIAGU
, W.WPLATA_NR_POZYCJI

-----------------------------------------------------------------------------------------------------

index PK_WPLATA cannot be used in the specified plan.


engine generate PLAN SORT (W INDEX (IXA_WPLATA__KONTRAHENT__PK)) which is not efficient


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Karol Bieniaszewski added a comment - 15/Mar/19 08:44 AM - edited

Dmitry Yemanov added a comment - 13/Dec/19 09:18 AM
What was the generated plan in FB 3.0.4.33063 *without* the manually provided one?

Karol Bieniaszewski added a comment - 13/Dec/19 01:11 PM
PLAN SORT (W INDEX (IXA_WPLATA__KONTRAHENT__PK))

Karol Bieniaszewski added a comment - 15/Jan/20 01:53 PM
Dmitry, thank you for the fix about storing manual plan.

May i ask about considering fix for plan genereted by engine to be ordered and indexed in the same time?
PLAN(W ORDER PK_WPLATA INDEX(IXA_WPLATA__KONTRAHENT__PK))