Issue Details (XML | Word | Printable)

Key: CORE-501
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: chedek
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Optimization problem with COALESCE

Created: 31/Mar/05 12:00 AM   Updated: 31/Mar/11 11:17 AM
Component/s: Engine
Affects Version/s: 2.0.0, 1.5.4, 2.0.1, 2.0.2, 2.0.3, 1.5.5, 2.1.0, 2.0.4, 2.5 Alpha 1, 2.1.1
Fix Version/s: 2.5 Beta 1

Time Tracking:
Not Specified

File Attachments: 1. File core-501.sql (5 kB)
2. File core-501.sql (5 kB)

Issue Links:
Duplicate
 
Relate

SF_ID: 1174021


 Description  « Hide
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.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:40 AM
Date: 2005-03-31 14:09
Sender: dimitr
Logged In: YES
user_id=61270

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

Karol Bieniaszewski added a comment - 05/Feb/08 09:57 AM
Why is no way to handle it better?
what is a different with case statement - there are no problem



Adriano dos Santos Fernandes added a comment - 01/Sep/08 10:11 AM
I'm attaching this test with the hope it is included on automated tests. It tests many problems I found when fixing CORE-501, CORE-1343 and CORE-2041.

Pavel Cisar added a comment - 30/Mar/11 09:27 AM
Adriano, do you by any chance still have the test script ? We lost the attachment in last tracker VM crash.

Adriano dos Santos Fernandes added a comment - 30/Mar/11 03:44 PM
Attached.

Pavel Cisar added a comment - 31/Mar/11 11:17 AM
Test added.