Server hangs with 100% CPU usage on NOT IN predicate [CORE1137]

[firebird-automations]

Submitted by: @pcisar

Is duplicated by CORE2097
Is duplicated by CORE1423
Is related to QA298

Attachments:
SWSDATA.rar

It doesn't show up all the time (tested on the same db structure but with bogus data and it doesn't show up). But it always happen with attached database on next statement:

select * from stamm
where
Status in (8,18,19)
and
Stammid not in (select Stammid from Kontakte where Art='abgelegt')

Plan
PLAN (KONTAKTE NATURAL)
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Use of Stammid <> ALL (select..) doesn't make any difference.

But next statement without NOT works ok:

select * from stamm
where
Status in (8,18,19)
and
Stammid in
(select Stammid from Kontakte
where Art='abgelegt'
)

Plan
PLAN (KONTAKTE INDEX (KONTAKTERSTAMMID))
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Verified that this bug is not present in 1.5.4.

Commits: b4c98c2 3e1f88a

[firebird-automations]

Commented by: @pcisar

Windows database with data for issue replication.

[firebird-automations]

Modified by: @pcisar

Attachment: SWSDATA.rar [ 10270 ]

[firebird-automations]

Commented by: @dyemanov

RelNotes, page 102, "ALL predicate may be slow". It applies to NOT IN the same way.

[firebird-automations]

Commented by: @dyemanov

Execute time = 7m 50s. Everything is "as designed" (although I do understand that not everybody's happy, sigh).

[firebird-automations]

Commented by: @pcisar

Now I see, it's THIS one. Well, I would not expect it's so bad on just few records. The sample database has:

KONTAKTE: Average record length: 37.77, total records: 223228, Data pages: 17769
STAMM: Average record length: 18.93, total records: 9448, Data pages: 572

As it utilizes CPU on 100%, it makes FB unusable in this case especially in multi-user environment. Anyway, it's not a bug as it fixes another critical bug, but it's an important regression, so I'll change it to improvement with critical priority. We have to do something about it soon.

[firebird-automations]

Modified by: @pcisar

issuetype: Bug [ 1 ] => Improvement [ 4 ]

description: This is probably data sensitive (character set or collation could be important) issue, as it doesn't show up all the time (tested on the same db structure but with bogus data and it doesn't show up). But it always happen with attached database on next statement:

select * from stamm
where
Status in (8,18,19)
and
Stammid not in (select Stammid from Kontakte where Art='abgelegt')

Plan
PLAN (KONTAKTE NATURAL)
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Use of Stammid <> ALL (select..) doesn't make any difference.

But next statement without NOT works ok:

select * from stamm
where
Status in (8,18,19)
and
Stammid in
(select Stammid from Kontakte
where Art='abgelegt'
)

Plan
PLAN (KONTAKTE INDEX (KONTAKTERSTAMMID))
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Verified that this bug is not present in 1.5.4.

=>

It doesn't show up all the time (tested on the same db structure but with bogus data and it doesn't show up). But it always happen with attached database on next statement:

select * from stamm
where
Status in (8,18,19)
and
Stammid not in (select Stammid from Kontakte where Art='abgelegt')

Plan
PLAN (KONTAKTE NATURAL)
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Use of Stammid <> ALL (select..) doesn't make any difference.

But next statement without NOT works ok:

select * from stamm
where
Status in (8,18,19)
and
Stammid in
(select Stammid from Kontakte
where Art='abgelegt'
)

Plan
PLAN (KONTAKTE INDEX (KONTAKTERSTAMMID))
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Verified that this bug is not present in 1.5.4.

[firebird-automations]

Commented by: @AlexPeshkoff

I do not think it's possible to do something with it. To satisfy SQL standard, we can not use indices. As long as we do not use them, 100% CPU utilization is typical. I had to review all applications, when moving to FB2, in order to change "not in" to "not exists", which works fine and does exactly what "not in" did before.

BTW, may be SORT + MERGE may be used in such cases?

[firebird-automations]

Commented by: @dyemanov

The only more-or-less easy solution is to cache the subsquery rowset. The engine never did that (except for SORT, but that's just an implementation detail), so some major changes are requred. I have RsbBuffer implemented locally, but I don't think we should add it to the public codebase yet.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @dyemanov

Link: This issue is duplicated by CORE1423 [ CORE1423 ]

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 11622 ] => Firebird [ 15463 ]

[firebird-automations]

Modified by: @dyemanov

Link: This issue is duplicated by CORE2097 [ CORE2097 ]

[firebird-automations]

Modified by: @asfernandes

assignee: Dmitry Yemanov [ dimitr ] => Adriano dos Santos Fernandes [ asfernandes ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA298 [ QA298 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test