Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Update query hang totally on FB3 (infinite loop) [CORE5748] #6011

Open
firebird-automations opened this issue Feb 14, 2018 · 14 comments
Open

Update query hang totally on FB3 (infinite loop) [CORE5748] #6011

firebird-automations opened this issue Feb 14, 2018 · 14 comments
Labels
affect-version: 3.0.3 affect-version: 3.0.4 component: engine priority: major type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

Is duplicated by CORE1769

Attachments:
testCOREHang.7z
FBHangQuery.png

We try to migrate database from Interbase to Firebird but query hang on FB - with same query plan

restore attached database backup
there are two tables
SPRAWOZDANIE: 97k rows
OKRES: 6k rows

qyery run without any problem on Interbase but hang totally on FB3
i kill it after 30 minutes. It looks like infinite loop reached. There is "in" operator involved.

--------------------------------------------

update SPRAWOZDANIE S1
set S1.SPR_ZLEC_FAKT_ROK = 2018,
S1.SPR_ZLEC_FAKT_NR = 216
where
S1.DYR_ID = 14 and
S1.DYR_ID || '_' || S1.SPR_ID in (
select S.DYR_ID || '_' || S.SPR_ID
from SPRAWOZDANIE S
where
S.DYR_ID = 14 and
S.UMOWA_ID = '04130038' and
case S.UMOWA_ZLECFAKT_OKRES_ID
when 1 then S.SPR_OKRES_OD || '-' || coalesce(S.SPR_OKRES_DO, S.SPR_OKRES_OD)
when 2 then S.SPR_OKRES_OD || '-' || S.SPR_OKRES_OD /*podzapytanie: wybieramy nr kwartalu dla zadanego okres_numer zapytanie glowne: poczatkowy i koncowy okres_numer dla znalezionego okres_kwartal*/
when 3 then (
select min(O1.OKRES_NUMER) || '-' || max(O1.OKRES_NUMER)
from OKRES O1
where O1.DYR_ID || O1.OKRES_KWARTAL in (
select O2.DYR_ID || O2.OKRES_KWARTAL
from OKRES O2
where
O2.OKRES_NUMER = S.SPR_OKRES_OD and
O2.DYR_ID = S.DYR_ID
)
)
when 4 then (
select min(O1.OKRES_NUMER) || '-' || max(O1.OKRES_NUMER)
from OKRES O1
where O1.DYR_ID || O1.OKRES_POLROCZE in (
select O2.DYR_ID || O2.OKRES_POLROCZE
from OKRES O2
where
O2.OKRES_NUMER = S.SPR_OKRES_OD and
O2.DYR_ID = S.DYR_ID
)
)
when 5 then (
select min(O1.OKRES_NUMER) || '-' || max(O1.OKRES_NUMER)
from OKRES O1
where
O1.DYR_ID || O1.OKRES_ROK in (
select O2.DYR_ID || O2.OKRES_ROK
from OKRES O2
where
O2.OKRES_NUMER = S.SPR_OKRES_OD and
O2.DYR_ID = S.DYR_ID
)
)
else 'bledna wartosc s.umowa_zlecfakt_okres_id'
end = '2018.01-2018.03' and
S.STATUS_ID = 1 and
S.STATUS_ID <> 2
) and
S1.SPR_ZLEC_FAKT_NR is null

--------------------

plan: legacy
PLAN (O2 INDEX (OKRES_PK))
PLAN (O1 NATURAL)
PLAN (O2 INDEX (OKRES_PK))
PLAN (O1 NATURAL)
PLAN (O2 INDEX (OKRES_PK))
PLAN (O1 NATURAL)
PLAN (S INDEX (IXA_SPRAWOZDANIE__UMOWA))
PLAN (S1 INDEX (IXA_SPRAWOZDANIE__DYREKCJA))

----------------------------
plan explained

Select Expression
-> Filter
-> Filter
-> Table "OKRES" as "O2" Access By ID
-> Bitmap
-> Index "OKRES_PK" Unique Scan
Select Expression
-> Singularity Check
-> Aggregate
-> Filter
-> Table "OKRES" as "O1" Full Scan
Select Expression
-> Filter
-> Filter
-> Table "OKRES" as "O2" Access By ID
-> Bitmap
-> Index "OKRES_PK" Unique Scan
Select Expression
-> Singularity Check
-> Aggregate
-> Filter
-> Table "OKRES" as "O1" Full Scan
Select Expression
-> Filter
-> Filter
-> Table "OKRES" as "O2" Access By ID
-> Bitmap
-> Index "OKRES_PK" Unique Scan
Select Expression
-> Singularity Check
-> Aggregate
-> Filter
-> Table "OKRES" as "O1" Full Scan
Select Expression
-> Filter
-> Filter
-> Table "SPRAWOZDANIE" as "S" Access By ID
-> Bitmap
-> Index "IXA_SPRAWOZDANIE__UMOWA" Range Scan (partial match: 2/3)
Select Expression
-> Filter
-> Table "SPRAWOZDANIE" as "S1" Access By ID
-> Bitmap
-> Index "IXA_SPRAWOZDANIE__DYREKCJA" Range Scan (full match)

----------------
tested under
WI-V3.0.3.32897 Firebird 3.0
and also current snapshot
WI-V3.0.4.32912 Firebird 3.0
@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

Attachment: testCOREHang.7z [ 13209 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

Try rewriting this query using the MERGE statement

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I can rewrite it from "in" into "Exists" statment simply

but engine should not go into infinite loop and about this is this report

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Karol, that's known issue. Since FB2.0 (or 2.1 - does not matter too much) IN is executed exactly according to SQL specification but this causes inability to use index. And what you see as an infinite loop is actually 2 recursive IN statements with full scans.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Alex, you're correct about NOT IN, but IN is able to use indices. Not in this example though, because concatenation is used. The slow execution reason is elsewhere, especially given that plans are the same.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I have attached stats after 30 minutes
it looks to me like infinite loop, bacause for DYR_ID=14 there are only 7k rows
and also without indexes i can not imagine that going by natural scan for every row will can take such amount of fetches and records seq and indexes

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

Attachment: FBHangQuery.png [ 13210 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Replaced SQL with more readable version

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: We try to migrate database from Interbase to Firebird but query hang on FB - with same query plan

restore attached database backup
there are two tables
SPRAWOZDANIE: 97k rows
OKRES: 6k rows

qyery run without any problem on Interbase but hang totally on FB3
i kill it after 30 minutes. It looks like infinite loop reached. There is "in" operator involved.

--------------------------------------------

update sprawozdanie s1 set s1.spr_zlec_fakt_rok = 2018, s1.spr_zlec_fakt_nr = 216
where
s1.dyr_id = 14
and s1.dyr_id || '_' || s1.spr_id in
(select
s.dyr_id || '_' || s.spr_id
from
sprawozdanie s
where
s.dyr_id = 14
and s.umowa_id = '04130038'
and case s.umowa_zlecfakt_okres_id when 1 then s.spr_okres_od || '-' || COALESCE(s.spr_okres_do, s.spr_okres_od) when 2 then s.spr_okres_od || '-' || s.spr_okres_od/*podzapytanie: wybieramy nr kwartalu dla zadanego okres_numer zapytanie glowne: poczatkowy i koncowy okres_numer dla znalezionego okres_kwartal*/when 3 then
(select
min(o1.okres_numer) || '-' || max(o1.okres_numer)
from
okres o1
where
o1.dyr_id || o1.okres_kwartal in
(select
o2.dyr_id || o2.okres_kwartal
from
okres o2
where
o2.okres_numer = s.spr_okres_od
and o2.dyr_id = s.dyr_id)) when 4 then
(select
min(o1.okres_numer) || '-' || max(o1.okres_numer)
from
okres o1
where
o1.dyr_id || o1.okres_polrocze in
(select
o2.dyr_id || o2.okres_polrocze
from
okres o2
where
o2.okres_numer = s.spr_okres_od
and o2.dyr_id = s.dyr_id)) when 5 then
(select
min(o1.okres_numer) || '-' || max(o1.okres_numer)
from
okres o1
where
o1.dyr_id || o1.okres_rok in
(select
o2.dyr_id || o2.okres_rok
from
okres o2
where
o2.okres_numer = s.spr_okres_od
and o2.dyr_id = s.dyr_id)) else 'błędna wartość s.umowa_zlecfakt_okres_id' end = '2018.01-2018.03'
AND S.STATUS_ID = 1
and s.status_id <> 2)
AND s1.SPR_ZLEC_FAKT_NR IS NULL

--------------------

plan: legacy
PLAN (O2 INDEX (OKRES_PK))
PLAN (O1 NATURAL)
PLAN (O2 INDEX (OKRES_PK))
PLAN (O1 NATURAL)
PLAN (O2 INDEX (OKRES_PK))
PLAN (O1 NATURAL)
PLAN (S INDEX (IXA_SPRAWOZDANIE__UMOWA))
PLAN (S1 INDEX (IXA_SPRAWOZDANIE__DYREKCJA))

----------------------------
plan explained

Select Expression
-> Filter
-> Filter
-> Table "OKRES" as "O2" Access By ID
-> Bitmap
-> Index "OKRES_PK" Unique Scan
Select Expression
-> Singularity Check
-> Aggregate
-> Filter
-> Table "OKRES" as "O1" Full Scan
Select Expression
-> Filter
-> Filter
-> Table "OKRES" as "O2" Access By ID
-> Bitmap
-> Index "OKRES_PK" Unique Scan
Select Expression
-> Singularity Check
-> Aggregate
-> Filter
-> Table "OKRES" as "O1" Full Scan
Select Expression
-> Filter
-> Filter
-> Table "OKRES" as "O2" Access By ID
-> Bitmap
-> Index "OKRES_PK" Unique Scan
Select Expression
-> Singularity Check
-> Aggregate
-> Filter
-> Table "OKRES" as "O1" Full Scan
Select Expression
-> Filter
-> Filter
-> Table "SPRAWOZDANIE" as "S" Access By ID
-> Bitmap
-> Index "IXA_SPRAWOZDANIE__UMOWA" Range Scan (partial match: 2/3)
Select Expression
-> Filter
-> Table "SPRAWOZDANIE" as "S1" Access By ID
-> Bitmap
-> Index "IXA_SPRAWOZDANIE__DYREKCJA" Range Scan (full match)

----------------
tested under
WI-V3.0.3.32897 Firebird 3.0
and also current snapshot
WI-V3.0.4.32912 Firebird 3.0

=>

We try to migrate database from Interbase to Firebird but query hang on FB - with same query plan

restore attached database backup
there are two tables
SPRAWOZDANIE: 97k rows
OKRES: 6k rows

qyery run without any problem on Interbase but hang totally on FB3
i kill it after 30 minutes. It looks like infinite loop reached. There is "in" operator involved.

--------------------------------------------

update SPRAWOZDANIE S1
set S1.SPR_ZLEC_FAKT_ROK = 2018,
S1.SPR_ZLEC_FAKT_NR = 216
where
S1.DYR_ID = 14 and
S1.DYR_ID || '_' || S1.SPR_ID in (
select S.DYR_ID || '_' || S.SPR_ID
from SPRAWOZDANIE S
where
S.DYR_ID = 14 and
S.UMOWA_ID = '04130038' and
case S.UMOWA_ZLECFAKT_OKRES_ID
when 1 then S.SPR_OKRES_OD || '-' || coalesce(S.SPR_OKRES_DO, S.SPR_OKRES_OD)
when 2 then S.SPR_OKRES_OD || '-' || S.SPR_OKRES_OD /*podzapytanie: wybieramy nr kwartalu dla zadanego okres_numer zapytanie glowne: poczatkowy i koncowy okres_numer dla znalezionego okres_kwartal*/
when 3 then (
select min(O1.OKRES_NUMER) || '-' || max(O1.OKRES_NUMER)
from OKRES O1
where O1.DYR_ID || O1.OKRES_KWARTAL in (
select O2.DYR_ID || O2.OKRES_KWARTAL
from OKRES O2
where
O2.OKRES_NUMER = S.SPR_OKRES_OD and
O2.DYR_ID = S.DYR_ID
)
)
when 4 then (
select min(O1.OKRES_NUMER) || '-' || max(O1.OKRES_NUMER)
from OKRES O1
where O1.DYR_ID || O1.OKRES_POLROCZE in (
select O2.DYR_ID || O2.OKRES_POLROCZE
from OKRES O2
where
O2.OKRES_NUMER = S.SPR_OKRES_OD and
O2.DYR_ID = S.DYR_ID
)
)
when 5 then (
select min(O1.OKRES_NUMER) || '-' || max(O1.OKRES_NUMER)
from OKRES O1
where
O1.DYR_ID || O1.OKRES_ROK in (
select O2.DYR_ID || O2.OKRES_ROK
from OKRES O2
where
O2.OKRES_NUMER = S.SPR_OKRES_OD and
O2.DYR_ID = S.DYR_ID
)
)
else 'bledna wartosc s.umowa_zlecfakt_okres_id'
end = '2018.01-2018.03' and
S.STATUS_ID = 1 and
S.STATUS_ID <> 2
) and
S1.SPR_ZLEC_FAKT_NR is null

--------------------

plan: legacy
PLAN (O2 INDEX (OKRES_PK))
PLAN (O1 NATURAL)
PLAN (O2 INDEX (OKRES_PK))
PLAN (O1 NATURAL)
PLAN (O2 INDEX (OKRES_PK))
PLAN (O1 NATURAL)
PLAN (S INDEX (IXA_SPRAWOZDANIE__UMOWA))
PLAN (S1 INDEX (IXA_SPRAWOZDANIE__DYREKCJA))

----------------------------
plan explained

Select Expression
-> Filter
-> Filter
-> Table "OKRES" as "O2" Access By ID
-> Bitmap
-> Index "OKRES_PK" Unique Scan
Select Expression
-> Singularity Check
-> Aggregate
-> Filter
-> Table "OKRES" as "O1" Full Scan
Select Expression
-> Filter
-> Filter
-> Table "OKRES" as "O2" Access By ID
-> Bitmap
-> Index "OKRES_PK" Unique Scan
Select Expression
-> Singularity Check
-> Aggregate
-> Filter
-> Table "OKRES" as "O1" Full Scan
Select Expression
-> Filter
-> Filter
-> Table "OKRES" as "O2" Access By ID
-> Bitmap
-> Index "OKRES_PK" Unique Scan
Select Expression
-> Singularity Check
-> Aggregate
-> Filter
-> Table "OKRES" as "O1" Full Scan
Select Expression
-> Filter
-> Filter
-> Table "SPRAWOZDANIE" as "S" Access By ID
-> Bitmap
-> Index "IXA_SPRAWOZDANIE__UMOWA" Range Scan (partial match: 2/3)
Select Expression
-> Filter
-> Table "SPRAWOZDANIE" as "S1" Access By ID
-> Bitmap
-> Index "IXA_SPRAWOZDANIE__DYREKCJA" Range Scan (full match)

----------------
tested under
WI-V3.0.3.32897 Firebird 3.0
and also current snapshot
WI-V3.0.4.32912 Firebird 3.0

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Karol,

This issue seems more of an optimization question then a new engine issue (there is already a tracker case about the optimization of IN() operand), as such I believe this case should be closed as duplicate.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is duplicated by CORE1769 [ CORE1769 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

@sean Leyne i do not suppose that this is only optimization problem.
Database has only 1000 pages. Look at attached stats before i kill this query.
there are:
fetches: 1 717 499 964
sequential: 342 121 292
index reads: 342 175 909

also table OKRES: 6k rows
SPRAWOZDANIE: 97k rows but for DYR_ID=14 only 7k rows

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

@sean Leyne
probably you have right here - i have waited longer and script finished.
but stats are really amazing ;-)

Executing statement...
Statement executed (elapsed time: 1:22:48 (hh:mm:ss)).
4 274 459 621 fetches, 683 marks, 789 reads, 314 writes.
0 inserts, 0 updates, 0 deletes, 851 596 938 index, 851 460 300 seq.
Delta memory: 1619320 bytes.
0 rows affected directly.
Total execution time: 1:22:48 (hh:mm:ss)
Script execution finished.

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

For fix CORE5748, it is necessary to teach the optimizer to perform IN / EXISTS as a semi-join. Then it will be possible to apply various optimizations for this kind of join, for example HASH OUTER JOIN.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 3.0.3 affect-version: 3.0.4 component: engine priority: major type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations