CORE-1722 adding Coalesce in subquery in query like Select ID, Coalesce(Select SUM() ...) FROM SOME_TABLE do double plans and double count of reads from
Relate
This issue relate to:
CORE-2584 Wrong results for CASE used together with GROUP BY
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.
Description
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.
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.
Bug
Closed
Major
Optimization problem with COALESCE
Sender: dimitr
Logged In: YES
user_id=61270
There's no way to handle it better for now.