Re: Problem with nested left-joins and coalesce

Hi Carla!

 

Well, maybe I too simplified my production code and now I can’t see something very simple, BUT I’m pretty sure that there isn’t  any original column value2 in sub4 except that I created with COALESCE…

 

Meanwhile, I want to note, that I made a little mistake in presented example: instead of “wrong” full example I wrote a “correct” one (but I’m sure you understood this because of my further explanation of “workaround”  =))

 

My mistake =(( don't kill me - I spend too much time with this piece of code today...

 

but just in case here is "wrong" (difference is in that "SELECT sub3.key3, sub4.value2 FROM" ):

 

SELECT * FROM

(

                SELECT 1 as key1

) sub1

LEFT JOIN

(

                SELECT sub3.key3, sub4.value2 FROM

                (

                               SELECT 1 as key3

                ) sub3

                LEFT JOIN

                (

                               SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2

                               FROM

                               (

                                               SELECT 1 as key5

                               ) sub5

                               LEFT JOIN

                               (

                                               SELECT 1 as key6, value1

                                               FROM

                                               (

                                                               SELECT NULL::integer as value1

                                               ) sub7

                                               WHERE false

                               ) sub6 ON false

                )

                sub4 ON sub4.key5=sub3.key3

)

sub2 ON sub1.key1 = sub2.key3

 

best regards,

alex

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Carla
Sent: Monday, August 08, 2011 10:03 PM
To: ai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Problem with nested left-joins and coalesce

 

Hi Alex!
When you wrote "COALESCE(sub6.value1, 1) as value2", you created a column "value2" that is different of the original column "sub4.value2".
Try running "SELECT sub3.key3, sub4.value2, value2 FROM ..." and you'll get the result:

key1;key3;value2;value2

1;1;null;1
It happens because the first column "value2" (i.e. "sub4.value2") doesn't have COALESCE on it.

2011/8/8 ai <listar@mail.ru>

Hi!

I have strange issue with nested left-joins in postgresql...

It's hard to explain, but easy to show =)

here we are:

 

SELECT * FROM

(

                SELECT 1 as key1

) sub1

LEFT JOIN

(

                SELECT sub3.key3, value2 FROM

                (

                               SELECT 1 as key3

                ) sub3

                LEFT JOIN

                (

                               SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2

                               FROM

                               (

                                               SELECT 1 as key5

                               ) sub5

                               LEFT JOIN

                               (

                                               SELECT 1 as key6, value1

                                               FROM

                                               (

                                                               SELECT NULL::integer as value1

                                               ) sub7

                                               WHERE false

                               ) sub6 ON false

 

                )

                sub4 ON sub4.key5=sub3.key3

)

sub2 ON sub1.key1 = sub2.key3

 

The result of this query:

key1;key3;value2

1;1;NULL

 

And this is the problem - value2 can't be NULL because of COALESCE in sub4 (at least I think that it can't be =))

Anyway if we'll change

SELECT sub3.key3, sub4.value2 FROM

with

SELECT sub3.key3, value2 FROM

we will got correct result:

key1;key3;value2

1;1;1

Is there something wrong with my mind&hands? or is it a bug?

 

Thanks in advance!

 

Kind regards

Alex