SubQueryConversion = true -- error "no current record for fetch operation" with complex joins

[EPluribusUnum]

Hi *!

https://www.firebirdsql.org/en/community-news/firebird-5-0-1-improvements-in-optimizer

This SQL works with SubQueryConversion = false, but fails with SubQueryConversion = true.

The cursor identified in the UPDATE or DELETE statement is not positioned on a row. no current record for fetch operation.

select gyp.id, round(sum(ak.menny),3), g.ktmn_kod_vv, g.ikt_t_kod_vv, gyp.munkalap_szam, fj.kod,
    round(a.kiadott_menny - a.visszavett_menny - a.felhasznalt_menny,3), fj.id
    from tm_gyp_anyag_kiad_f_g fg
    join tm_gyp gyp on fg.tm_gyp_id = gyp.id
    join tm_gepsor g on gyp.tm_gepsor_id = g.id
    join tm_uzemcsarnok u on g.tm_uzemcsarnok_id = u.id
    join tm_gyp_anyag_kiad ak on ak.tm_gyp_anyag_kiad_f_g_id = fg.id
    join tm_gyp_anyag_v a on ak.tm_gyp_anyag_id = a.id
    join ktfj fj on fj.id = a.ktfj_id
    where fg.allapot in ('L') and fg.tipus = 'V' and fg.id in (select first 1 id from tm_gyp_anyag_kiad_f_g where cru = 'TESZTFULL' and tipus = 'V' order by id)
    group by gyp.id, a.ktfj_id, a.mert_id, g.ktmn_kod_vv, g.ikt_t_kod_vv, a.id, gyp.munkalap_szam, fj.kod, a.ori_id,
             a.kiadott_menny, a.visszavett_menny, a.felhasznalt_menny, fj.id
    order by gyp.id, a.ktfj_id

I'll send you the database on request.

Firebird-5.0.1.1469-0-windows-x64

[sim1984]

Is this part of the query written sensibly?

in (select first 1 id from tm_gyp_anyag_kiad_f_g where cru = 'TESZTFULL' and tipus = 'V' order by id)

cru and tipus are fields of which table?

[EPluribusUnum]

With alias: in (select first 1 x.id from tm_gyp_anyag_kiad_f_g x where x.cru = 'TESZTFULL' and x.tipus = 'V' order by x.id).

The error still ouccres.

[sim1984]

Please provide a explain plan for this query. And it is desirable to write the texts of queries and plans in ``` query ```, so that the formatting is preserved.

select 
    gyp.id, 
    round(sum(ak.menny),3), 
    g.ktmn_kod_vv, 
    g.ikt_t_kod_vv, gyp.munkalap_szam, fj.kod,
    round(a.kiadott_menny - a.visszavett_menny - a.felhasznalt_menny,3), 
    fj.id
from 
    tm_gyp_anyag_kiad_f_g fg
    join tm_gyp gyp on fg.tm_gyp_id = gyp.id
    join tm_gepsor g on gyp.tm_gepsor_id = g.id
    join tm_uzemcsarnok u on g.tm_uzemcsarnok_id = u.id
    join tm_gyp_anyag_kiad ak on ak.tm_gyp_anyag_kiad_f_g_id = fg.id
    join tm_gyp_anyag_v a on ak.tm_gyp_anyag_id = a.id
    join ktfj fj on fj.id = a.ktfj_id
where fg.allapot in ('L') and fg.tipus = 'V' 
  and fg.id in (select first 1 x.id from tm_gyp_anyag_kiad_f_g x where x.cru = 'TESZTFULL' and x.tipus = 'V' order by x.id)
group by 
   gyp.id, a.ktfj_id, a.mert_id, g.ktmn_kod_vv, g.ikt_t_kod_vv, a.id, 
   gyp.munkalap_szam, fj.kod, a.ori_id,
   a.kiadott_menny, a.visszavett_menny, a.felhasznalt_menny, fj.id
order by gyp.id, a.ktfj_id

[EPluribusUnum]

SubQueryConversion = false PLAN

Sub-query
    -> Filter
        -> Table "TM_GYP_ANYAG" as "A X T2" Access By ID
            -> Bitmap
                -> Index "TM_GYP_ANYAG_UK1" Unique Scan
Sub-query
    -> Filter
        -> Table "TM_GYP_ANYAG" as "A X T2" Access By ID
            -> Bitmap
                -> Index "TM_GYP_ANYAG_UK1" Unique Scan
Sub-query
    -> Filter
        -> First N Records
            -> Refetch
                -> Sort (record length: 28, key length: 8)
                    -> Filter
                        -> Table "TM_GYP_ANYAG_KIAD_F_G" as "X" Full Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Nested Loop Join (inner)
                -> Unique Sort (record length: 36, key length: 8)
                    -> Filter
                        -> Table "TM_GYP_ANYAG_KIAD" as "A K K" Access By ID
                            -> Bitmap
                                -> Index "TM_GYP_ANYAG_KIAD_FK2" List Scan (full match)
                -> Filter
                    -> Table "KTBT" as "A BT" Access By ID
                        -> Bitmap
                            -> Index "KTBT_PK" Unique Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Nested Loop Join (inner)
                -> Unique Sort (record length: 36, key length: 8)
                    -> Filter
                        -> Table "TM_GYP_ANYAG_KIAD" as "A K K" Access By ID
                            -> Bitmap
                                -> Index "TM_GYP_ANYAG_KIAD_FK2" List Scan (full match)
                -> Nested Loop Join (inner)
                    -> Filter
                        -> Table "KTBT" as "A BTKI" Access By ID
                            -> Bitmap
                                -> Index "KTBT_PK" Unique Scan
                    -> Filter
                        -> Table "KTBT" as "A BTV" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "KTBT_FK5" Range Scan (full match)
                                -> Bitmap
                                    -> Index "KTBT_UK3" Unique Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Filter
                -> Table "TM_GYP_MUSZAK_K_A" as "A TM_GYP_MUSZAK_K_A" Access By ID
                    -> Bitmap
                        -> Index "TM_GYP_MUSZAK_K_A_FK2" List Scan (full match)
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Nested Loop Join (inner)
                -> Unique Sort (record length: 36, key length: 8)
                    -> Filter
                        -> Table "TM_GYP_ANYAG_KIAD" as "A K K" Access By ID
                            -> Bitmap
                                -> Index "TM_GYP_ANYAG_KIAD_FK2" List Scan (full match)
                -> Filter
                    -> Table "KTBT" as "A BT" Access By ID
                        -> Bitmap
                            -> Index "KTBT_PK" Unique Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Nested Loop Join (inner)
                -> Unique Sort (record length: 36, key length: 8)
                    -> Filter
                        -> Table "TM_GYP_ANYAG_KIAD" as "A K K" Access By ID
                            -> Bitmap
                                -> Index "TM_GYP_ANYAG_KIAD_FK2" List Scan (full match)
                -> Nested Loop Join (inner)
                    -> Filter
                        -> Table "KTBT" as "A BTKI" Access By ID
                            -> Bitmap
                                -> Index "KTBT_PK" Unique Scan
                    -> Filter
                        -> Table "KTBT" as "A BTV" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "KTBT_FK5" Range Scan (full match)
                                -> Bitmap
                                    -> Index "KTBT_UK3" Unique Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Filter
                -> Table "TM_GYP_MUSZAK_K_A" as "A TM_GYP_MUSZAK_K_A" Access By ID
                    -> Bitmap
                        -> Index "TM_GYP_MUSZAK_K_A_FK2" List Scan (full match)
Select Expression
    -> Aggregate
        -> Sort (record length: 664, key length: 276)
            -> Nested Loop Join (inner)
                -> Nested Loop Join (inner)
                    -> Nested Loop Join (inner)
                        -> Nested Loop Join (inner)
                            -> Nested Loop Join (inner)
                                -> Filter
                                    -> Nested Loop Join (outer)
                                        -> Nested Loop Join (inner)
                                            -> Union
                                                -> Filter
                                                    -> Table "TM_GYP_ANYAG" as "A X T" Access By ID
                                                        -> Bitmap
                                                            -> Index "TM_GYP_ANYAG_UK1" Range Scan (partial match: 1/3)
                                                -> Filter
                                                    -> Table "TM_GYP_ANYAG" as "A X T" Access By ID
                                                        -> Bitmap
                                                            -> Index "TM_GYP_ANYAG_UK1" Range Scan (partial match: 1/3)
                                                -> Filter
                                                    -> Hash Join (inner)
                                                        -> Filter
                                                            -> Table "TM_GYP_ANYAG" as "A X T" Access By ID
                                                                -> Bitmap
                                                                    -> Index "TM_GYP_ANYAG_UK1" Range Scan (partial match: 1/3)
                                                        -> Record Buffer (record length: 41)
                                                            -> Filter
                                                                -> Table "TM_GYP_ANYAG" as "A X T2" Access By ID
                                                                    -> Bitmap
                                                                        -> Index "TM_GYP_ANYAG_UK1" Range Scan (partial match: 1/3)
                                            -> Filter
                                                -> Table "TM_GYP_ANYAG" as "A A" Access By ID
                                                    -> Bitmap
                                                        -> Index "TM_GYP_ANYAG_PK" List Scan (full match)
                                        -> Filter
                                            -> Table "TM_GYP_ANYAG" as "A UCA" Access By ID
                                                -> Bitmap
                                                    -> Index "TM_GYP_ANYAG_UK1" Unique Scan
                                -> Filter
                                    -> Table "KTFJ" as "FJ" Access By ID
                                        -> Bitmap
                                            -> Index "KTFJ_PK" Unique Scan
                                -> Filter
                                    -> Table "TM_GYP_ANYAG_KIAD" as "AK" Access By ID
                                        -> Bitmap
                                            -> Index "TM_GYP_ANYAG_KIAD_FK2" Range Scan (full match)
                            -> Filter
                                -> Table "TM_GYP_ANYAG_KIAD_F_G" as "FG" Access By ID
                                    -> Bitmap
                                        -> Index "TM_GYP_ANYAG_KIAD_F_G_PK" Unique Scan
                        -> Filter
                            -> Table "TM_GYP" as "GYP" Access By ID
                                -> Bitmap
                                    -> Index "TM_GYP_PK" Unique Scan
                    -> Filter
                        -> Table "TM_GEPSOR" as "G" Access By ID
                            -> Bitmap
                                -> Index "TM_GEPSOR_PK" Unique Scan
                -> Filter
                    -> Table "TM_UZEMCSARNOK" as "U" Access By ID
                        -> Bitmap
                            -> Index "TM_UZEMCSARNOK_PK" Unique Scan

SubQueryConversion = true PLAN

Sub-query
    -> Filter
        -> Table "TM_GYP_ANYAG" as "A X T2" Access By ID
            -> Bitmap
                -> Index "TM_GYP_ANYAG_UK1" Unique Scan
Sub-query
    -> Filter
        -> Table "TM_GYP_ANYAG" as "A X T2" Access By ID
            -> Bitmap
                -> Index "TM_GYP_ANYAG_UK1" Unique Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Nested Loop Join (inner)
                -> Unique Sort (record length: 36, key length: 8)
                    -> Filter
                        -> Table "TM_GYP_ANYAG_KIAD" as "A K K" Access By ID
                            -> Bitmap
                                -> Index "TM_GYP_ANYAG_KIAD_FK2" List Scan (full match)
                -> Filter
                    -> Table "KTBT" as "A BT" Access By ID
                        -> Bitmap
                            -> Index "KTBT_PK" Unique Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Nested Loop Join (inner)
                -> Unique Sort (record length: 36, key length: 8)
                    -> Filter
                        -> Table "TM_GYP_ANYAG_KIAD" as "A K K" Access By ID
                            -> Bitmap
                                -> Index "TM_GYP_ANYAG_KIAD_FK2" List Scan (full match)
                -> Nested Loop Join (inner)
                    -> Filter
                        -> Table "KTBT" as "A BTKI" Access By ID
                            -> Bitmap
                                -> Index "KTBT_PK" Unique Scan
                    -> Filter
                        -> Table "KTBT" as "A BTV" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "KTBT_FK5" Range Scan (full match)
                                -> Bitmap
                                    -> Index "KTBT_UK3" Unique Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Filter
                -> Table "TM_GYP_MUSZAK_K_A" as "A TM_GYP_MUSZAK_K_A" Access By ID
                    -> Bitmap
                        -> Index "TM_GYP_MUSZAK_K_A_FK2" List Scan (full match)
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Nested Loop Join (inner)
                -> Unique Sort (record length: 36, key length: 8)
                    -> Filter
                        -> Table "TM_GYP_ANYAG_KIAD" as "A K K" Access By ID
                            -> Bitmap
                                -> Index "TM_GYP_ANYAG_KIAD_FK2" List Scan (full match)
                -> Filter
                    -> Table "KTBT" as "A BT" Access By ID
                        -> Bitmap
                            -> Index "KTBT_PK" Unique Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Nested Loop Join (inner)
                -> Unique Sort (record length: 36, key length: 8)
                    -> Filter
                        -> Table "TM_GYP_ANYAG_KIAD" as "A K K" Access By ID
                            -> Bitmap
                                -> Index "TM_GYP_ANYAG_KIAD_FK2" List Scan (full match)
                -> Nested Loop Join (inner)
                    -> Filter
                        -> Table "KTBT" as "A BTKI" Access By ID
                            -> Bitmap
                                -> Index "KTBT_PK" Unique Scan
                    -> Filter
                        -> Table "KTBT" as "A BTV" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "KTBT_FK5" Range Scan (full match)
                                -> Bitmap
                                    -> Index "KTBT_UK3" Unique Scan
Sub-query
    -> Singularity Check
        -> Aggregate
            -> Filter
                -> Table "TM_GYP_MUSZAK_K_A" as "A TM_GYP_MUSZAK_K_A" Access By ID
                    -> Bitmap
                        -> Index "TM_GYP_MUSZAK_K_A_FK2" List Scan (full match)
Select Expression
    -> Aggregate
        -> Sort (record length: 728, key length: 276)
            -> Nested Loop Join (semi)
                -> Filter
                    -> Hash Join (semi)
                        -> Nested Loop Join (inner)
                            -> Filter
                                -> Table "TM_GYP_ANYAG_KIAD_F_G" as "FG" Full Scan
                            -> Filter
                                -> Table "TM_GYP" as "GYP" Access By ID
                                    -> Bitmap
                                        -> Index "TM_GYP_PK" Unique Scan
                            -> Filter
                                -> Table "TM_GEPSOR" as "G" Access By ID
                                    -> Bitmap
                                        -> Index "TM_GEPSOR_PK" Unique Scan
                            -> Filter
                                -> Table "TM_UZEMCSARNOK" as "U" Access By ID
                                    -> Bitmap
                                        -> Index "TM_UZEMCSARNOK_PK" Unique Scan
                            -> Filter
                                -> Table "TM_GYP_ANYAG_KIAD" as "AK" Access By ID
                                    -> Bitmap
                                        -> Index "TM_GYP_ANYAG_KIAD_UK1" Unique Scan
                        -> Record Buffer (record length: 65)
                            -> First N Records
                                -> Refetch
                                    -> Sort (record length: 28, key length: 8)
                                        -> Filter
                                            -> Table "TM_GYP_ANYAG_KIAD_F_G" as "X" Full Scan
                -> Filter
                    -> Nested Loop Join (outer)
                        -> Nested Loop Join (inner)
                            -> Union
                                -> Filter
                                    -> Table "TM_GYP_ANYAG" as "A X T" Access By ID
                                        -> Bitmap
                                            -> Index "TM_GYP_ANYAG_UK1" Range Scan (partial match: 1/3)
                                -> Filter
                                    -> Table "TM_GYP_ANYAG" as "A X T" Access By ID
                                        -> Bitmap
                                            -> Index "TM_GYP_ANYAG_UK1" Range Scan (partial match: 1/3)
                                -> Filter
                                    -> Hash Join (inner)
                                        -> Filter
                                            -> Table "TM_GYP_ANYAG" as "A X T" Access By ID
                                                -> Bitmap
                                                    -> Index "TM_GYP_ANYAG_UK1" Range Scan (partial match: 1/3)
                                        -> Record Buffer (record length: 41)
                                            -> Filter
                                                -> Table "TM_GYP_ANYAG" as "A X T2" Access By ID
                                                    -> Bitmap
                                                        -> Index "TM_GYP_ANYAG_UK1" Range Scan (partial match: 1/3)
                            -> Filter
                                -> Table "TM_GYP_ANYAG" as "A A" Access By ID
                                    -> Bitmap
                                        -> Index "TM_GYP_ANYAG_PK" List Scan (full match)
                        -> Filter
                            -> Table "TM_GYP_ANYAG" as "A UCA" Access By ID
                                -> Bitmap
                                    -> Index "TM_GYP_ANYAG_UK1" Unique Scan
                -> Filter
                    -> Table "KTFJ" as "FJ" Access By ID
                        -> Bitmap
                            -> Index "KTFJ_PK" Unique Scan

[sim1984]

Views? The plans are somehow too long.

[sim1984]

and fg.id in (select first 1 x.id from tm_gyp_anyag_kiad_f_g x where x.cru = 'TESZTFULL' and x.tipus = 'V' order by x.id)

Can be replaced with

and fg.id = (select first 1 x.id from tm_gyp_anyag_kiad_f_g x where x.cru = 'TESZTFULL' and x.tipus = 'V' order by x.id)

But you have more than one semi-join in your plan, which means there are still IN/EXISTS hiding somewhere.

[EPluribusUnum]

tm_gyp_anyag_v is a view.

[sim1984]

There is a high probability that the error is in this view. That is, the view tm_gyp_anyag_v itself already gives an incorrect result.

[dyemanov]

Please send the database or download URL to me (firebird2 at yandex dot ru).

[EPluribusUnum]

@dyemanov , I sent an email with the details.

[dyemanov]

Got it, thanks.

[dyemanov]

It should be fixed in the next snapshot build, please verify and report back. Overall impression about SubQueryConversion = true (once we fix the reported bugs) would be also appreciated.

[EPluribusUnum]

Fix confirmed works with snapshot Firebird-5.0.2.1486-0-5af13c1-windows-x64.zip