FB3.0.4.33063 vs FB3.0.5.33100 manual plan cause "index cannot be used in the
specified plan"
---------------------------------------------------------------------------------------------
Key: CORE-6024
URL: http://tracker.firebirdsql.org/browse/CORE-6024
Project: Firebird Core
Issue Type: Bug
Environment: Windows 2008R2
FB3.0.5.33100
Reporter: Karol Bieniaszewski
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,
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_DATA_WPLATY
, 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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
