REGRESSION: natural plan instead of primary/unique index

[livius2]

Hi

regression v5 (WI-V5.0.4.1739 Firebird 5.0 b103e8f) to v3, minimal reproducible test case below

DDL

SET TERM ^ ;
CREATE PROCEDURE GET_ROWS_FROM_LIST (
    ITEM_LIST VARCHAR(10000),
    SEPARATOR VARCHAR(20) DEFAULT ',' )
RETURNS (
    ITEM VARCHAR(120) )
SQL SECURITY DEFINER

AS
BEGIN
  ITEM = ITEM_LIST;
  SUSPEND;
END
^
SET TERM ; ^

and now do select:

SELECT
    RL2.RDB$RELATION_ID
FROM
    RDB$DATABASE D 
    INNER JOIN GET_ROWS_FROM_LIST(D.RDB$RELATION_ID, ',') GRFL ON 1 = 1
    INNER JOIN RDB$RELATIONS RL2 ON RL2.RDB$RELATION_NAME = GRFL.ITEM

to get some records you can modify above sql to:

SELECT
    RL2.RDB$RELATION_ID
FROM
    RDB$DATABASE D 
    INNER JOIN GET_ROWS_FROM_LIST(IIF(D.RDB$RELATION_ID IS NULL, 15, 15), ',') GRFL ON 1 = 1
    INNER JOIN RDB$RELATIONS RL2 ON RL2.RDB$RELATION_ID = GRFL.ITEM

Firebird 5:
PLAN JOIN (D NATURAL, RL2 NATURAL, GRFL NATURAL)

Firebird 3:
PLAN JOIN (D NATURAL, JOIN (GRFL NATURAL, RL2 INDEX (RDB$INDEX_1)))

In our real life table for alias RL2 have 9kk rows so it is disaster ;-)

when i change query from INNER JOIN to LEFT JOIN with WHERE (same as INNER):

SELECT
    RL2.RDB$RELATION_ID
FROM
    RDB$DATABASE D 
    INNER JOIN GET_ROWS_FROM_LIST(IIF(D.RDB$RELATION_ID IS NULL, 15, 15), ',') GRFL ON 1 = 1
    LEFT JOIN RDB$RELATIONS RL2 ON RL2.RDB$RELATION_ID = GRFL.ITEM
WHERE
RL2.RDB$RELATION_ID IS NOT NULL

Firebird 5:
PLAN JOIN (JOIN (D NATURAL, GRFL NATURAL), RL2 INDEX (RDB$INDEX_1))

Firebird 3:
PLAN JOIN (D NATURAL, JOIN (GRFL NATURAL, RL2 INDEX (RDB$INDEX_1)))

Of course I can change the above query, but in the long run, if the pseudo-LEFT JOIN transformations are replaced by Firebird INNER JOINs (if the OuterJoinConversion option is no longer available), then unfortunately we’ll have a problem here.