Firebird 5 - wrong plan

[EPluribusUnum]

Hi *!

SELECT lit.gl_fk_tetel14_cpy_run_with_ex(15021, 15021, 'KA', null, null, 'N') FROM rdb$database

This call builds this SELECT :

SELECT t.fkisz_id, t.nyitzar, t.ervdat, t.bizonylat, t.szoveg,
t.szla, t.szla2, t.minosit1, t.minosit2, t.minosit3, t.minosit4, t.minosit5, t.minosit6,
t.eszla, t.eszla2, t.eminosit1, t.eminosit2, t.eminosit3, t.eminosit4, t.eminosit5, t.eminosit6,
t.tartozik, t.kovetel, t.mertek, t.megyseg, t.emertek, t.emegyseg, t.dertek, t.dnem, t.edertek, t.ednem,
t.alrkod, t.gytip, t.gysor, t.ugyf_id, t.kod1, t.kod2, t.kod3, t.kod4, t.kod5,
(SELECT ubt.id FROM pu_btet ubt WHERE ubt.pbiz_id = 15021 AND ubt.gysor = bt.gysor)
FROM pu_btet bt
INNER JOIN pu_biz b ON b.id = bt.pbiz_id
LEFT OUTER JOIN fk_buftet t ON t.alrsor = bt.id
WHERE bt.pbiz_id = 15021 AND
t.tabla = IIF(b.igaz = 'F', 'FK_TETEL', 'FK_BUFFER') AND
t.alrsor IS NOT NULL

FB30 plan
PLAN (T EV INDEX (PU_EV_KONYV_FK1))
PLAN (T FK_TETEL INDEX (PU_EV_KONYV_FK2))
PLAN (T FK_BUFFER INDEX (KT_EV_KTBT_FK_FK2, KT_EV_KTBT_FK_FK4, KT_EV_KTBT_FK_FK3, KT_EV_KTBT_FK_FK5))
PLAN (T KP INDEX (KAPENZTAR_FK2))
PLAN (UBT INDEX (PU_BTET_UK))
PLAN JOIN (JOIN (B INDEX (PU_BIZ_PK), BT INDEX (PU_BTET_BIZ_FK)), T FK_BUFFER INDEX (FK_BUFFER_ALRSOR), T FK_TETEL INDEX (FK_TETEL_ALRSOR))

FB40 plan
PLAN (T EV INDEX (PU_EV_KONYV_FK1))
PLAN (T FK_TETEL INDEX (PU_EV_KONYV_FK2))
PLAN (T FK_BUFFER INDEX (KT_EV_KTBT_FK_FK2, KT_EV_KTBT_FK_FK4, KT_EV_KTBT_FK_FK3, KT_EV_KTBT_FK_FK5))
PLAN (T KP INDEX (KAPENZTAR_FK2))
PLAN (UBT INDEX (PU_BTET_UK))
PLAN JOIN (JOIN (B INDEX (PU_BIZ_PK), BT INDEX (PU_BTET_BIZ_FK)), T FK_BUFFER INDEX (FK_BUFFER_ALRSOR), T FK_TETEL INDEX (FK_TETEL_ALRSOR))

FB50 (1121) plan
PLAN (T EV INDEX (PU_EV_KONYV_FK1))
PLAN (T FK_TETEL INDEX (PU_EV_KONYV_FK2))
PLAN (T FK_BUFFER INDEX (KT_EV_KTBT_FK_FK2, KT_EV_KTBT_FK_FK4, KT_EV_KTBT_FK_FK3, KT_EV_KTBT_FK_FK5))
PLAN (T KP INDEX (KAPENZTAR_FK2))
PLAN (UBT INDEX (PU_BTET_UK))
PLAN JOIN (JOIN (T FK_BUFFER NATURAL, T FK_TETEL NATURAL, BT INDEX (PU_BTET_PK)), B INDEX (PU_BIZ_PK))

Also on 50
SELECT lit.gl_fk_tetel14_cpy_run_with_ex(15021, 15021, 'KA', null, null, 'N') FROM rdb$database
sometimes inserts more than 1 record

java.lang.RuntimeException: exception 23; GL_EXCEPTION_EX; 3. könyvelés INSERT a 11/PKEZI/2017 bizonylathoz; At function 'EX.EX' line: 33, col: 5
At trigger 'FK_BUFFER_AI_PU_BIZ_BIU1__052' line: 16, col: 12
At procedure 'FK_BOV_WRITE14' line: 81, col: 5
At procedure 'FK_BOV14' line: 256, col: 5
At procedure 'GL_FK_TETEL14_CPY' line: 151, col: 3; At function 'LIT.GL_FK_TETEL14_CPY_RUN_WITH_EX' line: 336, col: 9 [SQLState:HY000, ISC error code:335544517]
jdbc:firebirdsql:172.16.173.98/3050:c:\Libra3sTesztRendszer\data\libra3sdbteszt_fulldb.gdb3s
select lit.gl_fk_tetel14_cpy_run_with_ex(15021, 15021, 'KA', null, null, 'N')
from rdb$database

I can send the FTP details for database on request.

[sim1984]

why did you decide that this plan was wrong? In 5.0 there were changes in the optimizer . In most cases, the plan is more optimal. Just compare the execution speed of the same query. as for the extra insert, I have a suspicion that this is due to the fact that it is in the procedure with which join takes place. This cannot be done because the order of execution of sub-operations in the request is not defined.

[dyemanov]

Is it reproducible on the database you provided me the last time? I see that at least the plan is the same as you reported here.

[EPluribusUnum]

@sim1984 FK_TETEL table can be very big. This NATURAL read cause millions of unnecessary reads on big customer databases : most or all of them are filtered out with the ON t.alrsor = bt.id condition.
One example. PU_BIZ count : 3941088, PU_BTET count : 4501807, FK_TETEL count : 7615604, FK_BUFFER count : 99
FB30, FB40 : 1 PU_BIZ index read, 1 PU_BTET index read, 1 FK_BUFFER index read, exec time 0.0 ms
FB50 : 1 PU_BIZ index read, 6885311 PU_BTET index read, 99 FK_BUFFER non index read, 7615604 FK_TETEL non-index read, exec time 2m 23s 891ms

@dyemanov , found the wrong result problem! It is an INSERT-SELECT issue, the newly inserted records were not filtered out in WHERE, and sometimes the FOR SELECT read up the record inserted in the body of the loop. Adding an extra condition fixed it, it's not an FB engine bug.

[omachtandras]

Is it reproducible on the database you provided me the last time? I see that at least the plan is the same as you reported here.

Yes.

[dyemanov]

In fact, this is a duplicate #1476. The plan changes due to #6992 which converts LEFT JOIN into INNER JOIN. Condition t.alrsor IS NOT NULL does not prevent this transformation because there's also condition t.tabla = IIF(b.igaz = 'F', 'FK_TETEL', 'FK_BUFFER'). And finally UNION is placed on the first position (our old limitation), thus making the plan bad.

I will investigate ways to improve the things.