Using exists in Firebird 5.0.1 uses a different and slow plan compared to just the select statement

[dsonda]

Given the query below:

SELECT
  IIF(EXISTS(
      SELECT ORDEM_PRODUCAO.ORP_ID
      FROM ITEM_SEPARACAO_PEDIDO_VENDA
      INNER JOIN ORDEM_PRODUCAO
        ON (ITEM_SEPARACAO_PEDIDO_VENDA.ISP_ID = ORDEM_PRODUCAO.ISP_ID)
      WHERE (ITEM_SEPARACAO_PEDIDO_VENDA.IPV_ID = 9437253)), 'Sim', 'Não') as TEM_OP
FROM RDB$DATABASE

All records in ORDEM_PRODUCAO have ISP_ID field null. So, the FK index for this field has statistics 1.

Firebird 2.5.9:

PLAN JOIN (ITEM_SEPARACAO_PEDIDO_VENDA INDEX (FK_ITEM_SEP_PDV_ITEM_PEDIDO), ORDEM_PRODUCAO INDEX (FK_ORP_ISP))
PLAN (RDB$DATABASE NATURAL)
Prepare time = 16ms
Execute time = 0ms
Avg fetch time = 0,00 ms
Current memory = 19.186.008
Max memory = 19.255.648
Memory buffers = 2.048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 385

Firebird 5.0.1:

PLAN JOIN (ORDEM_PRODUCAO NATURAL, ITEM_SEPARACAO_PEDIDO_VENDA INDEX (PK_ITEM_SEPARACAO_PEDIDO_VENDA))
PLAN (RDB$DATABASE NATURAL)
Prepare time = 16ms
Execute time = 34s 718ms
Avg fetch time = 34.718,00 ms
Current memory = 4.399.790.672
Max memory = 4.400.434.560
Memory buffers = 262.144
Reads from disk to cache = 310.931
Writes from cache to disk = 0
Fetches from cache = 76.558.838

It seems to be the same problem as described in #8344.

But, in Firebird 5, executing just the select, without the exists, has the same result as Firebird 2.5.9.

Firebird 5.0.1:

SELECT ORDEM_PRODUCAO.ORP_ID
FROM ITEM_SEPARACAO_PEDIDO_VENDA
INNER JOIN ORDEM_PRODUCAO
  ON (ITEM_SEPARACAO_PEDIDO_VENDA.ISP_ID = ORDEM_PRODUCAO.ISP_ID)
WHERE (ITEM_SEPARACAO_PEDIDO_VENDA.IPV_ID = 9437253)

PLAN JOIN (ITEM_SEPARACAO_PEDIDO_VENDA INDEX (FK_ITEM_SEP_PDV_ITEM_PEDIDO), ORDEM_PRODUCAO INDEX (FK_ORP_ISP))
Prepare time = 0ms
Execute time = 0ms
Current memory = 4.399.838.576
Max memory = 4.400.434.560
Memory buffers = 262.144
Reads from disk to cache = 3
Writes from cache to disk = 0
Fetches from cache = 4

[dyemanov]

Can you provide the test database (with removed / anonymized unrelated data)?

[dsonda]

I created a database with just the two tables and columns used in the statements and inserted the same data as the original one.
You can download it from here