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

Optimization problem with COALESCE [CORE501] #852

Closed
firebird-automations opened this issue Mar 31, 2005 · 17 comments
Closed

Optimization problem with COALESCE [CORE501] #852

firebird-automations opened this issue Mar 31, 2005 · 17 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE1722 [ CORE1722 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @asfernandes

Attachment: core-501.sql [ 11030 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue relate to CORE2584 [ CORE2584 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA325 [ QA325 ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @asfernandes

Attachment: core-501.sql [ 11931 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Attached.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test added.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment