Optimization problem with COALESCE [CORE501]

[firebird-automations]

Submitted by: chedek (chedek)

Is duplicated by CORE1722
Relate to CORE2584
Is related to QA325

Attachments:
core-501.sql
core-501.sql

SFID: 1174021#⁠
Submitted By: chedek

When I have 2 tables: A and B and try to execute
a query like this:

select coalesce((select b.cod from b where b.cod=a.
cod),0) from a

Plan
PLAN (B NATURAL)
PLAN (B NATURAL)
PLAN (A NATURAL)

Thus server try to execute internal select 2 times for
each row in main select. I think it happens because
server executes
coalesce(x,0) as
case when x is null then 0 else x end

...but for internal query it's not right.

Commits: 9150e6f b910161

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2005-03-31 14:09
Sender: dimitr
Logged In: YES
user_id=61270

There's no way to handle it better for now.

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 10525 ] => Firebird [ 14833 ]

[firebird-automations]

Modified by: @dyemanov

summary: FB1.5 and 2.0 optimizer problem => FB1.5 and 2.0 optimizer problem for COALESCE

[firebird-automations]

Modified by: @dyemanov

Link: This issue is duplicated by CORE1722 [ CORE1722 ]

[firebird-automations]

Commented by: @livius2

Why is no way to handle it better?
what is a different with case statement - there are no problem

[firebird-automations]

Modified by: @asfernandes

assignee: Arno Brinkman [ arnobrinkman ] => Adriano dos Santos Fernandes [ asfernandes ]

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

[firebird-automations]

Modified by: @asfernandes

Version: 2.1.1 [ 10223 ]

Version: 2.5 Alpha 1 [ 10224 ]

Version: 2.0.4 [ 10211 ]

Version: 2.1.0 [ 10041 ]

Version: 1.5.5 [ 10220 ]

Version: 2.0.3 [ 10200 ]

Version: 2.0.2 [ 10130 ]

Version: 2.0.1 [ 10090 ]

Version: 1.5.4 [ 10100 ]

Version: 2.0.0 [ 10091 ]

summary: FB1.5 and 2.0 optimizer problem for COALESCE => Optimization problem with COALESCE

[firebird-automations]

Commented by: @asfernandes

I'm attaching this test with the hope it is included on automated tests. It tests many problems I found when fixing CORE501, CORE1343 and CORE2041.

[firebird-automations]

Modified by: @asfernandes

Attachment: core-501.sql [ 11030 ]

[firebird-automations]

Modified by: @asfernandes

Link: This issue relate to CORE2584 [ CORE2584 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA325 [ QA325 ]

[firebird-automations]

Commented by: @pcisar

Adriano, do you by any chance still have the test script ? We lost the attachment in last tracker VM crash.

[firebird-automations]

Modified by: @asfernandes

Attachment: core-501.sql [ 11931 ]

[firebird-automations]

Commented by: @asfernandes

Attached.

[firebird-automations]

Commented by: @pcisar

Test added.

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully