Update query hang totally on FB3 (infinite loop)
------------------------------------------------
Key: CORE-5748
URL: http://tracker.firebirdsql.org/browse/CORE-5748
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.3, 3.0.4
Environment: WI-V3.0.3.32897 Firebird 3.0
Reporter: Karol Bieniaszewski
Attachments: testCOREHang.7z
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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
