pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with nested left-joins and coalesce
4+ messages / 2 participants
[nested] [flat]
* Problem with nested left-joins and coalesce
@ 2011-08-08 06:53 ai <listar@mail.ru>
0 siblings, 1 reply; 4+ messages in thread
From: ai @ 2011-08-08 06:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with nested left-joins and coalesce
@ 2011-08-08 15:02 Carla <cgourofino@hotmail.com>
parent: ai <listar@mail.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Carla @ 2011-08-08 15:02 UTC (permalink / raw)
To: ai <listar@mail.ru>; +Cc: pgsql-sql
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****
>
> ** **
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with nested left-joins and coalesce
@ 2011-08-08 15:34 ai <listar@mail.ru>
parent: Carla <cgourofino@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: ai @ 2011-08-08 15:34 UTC (permalink / raw)
To: cgourofino@hotmail.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with nested left-joins and coalesce
@ 2011-08-08 19:14 Carla <cgourofino@hotmail.com>
parent: ai <listar@mail.ru>
0 siblings, 0 replies; 4+ messages in thread
From: Carla @ 2011-08-08 19:14 UTC (permalink / raw)
To: ai <listar@mail.ru>; +Cc: pgsql-sql
Sorry, my mistake. =( Now I understood the whole problem.
2011/8/8 ai <listar@mail.ru>
> 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****
>
> ****
>
> ** **
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-08-08 19:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-08 06:53 Problem with nested left-joins and coalesce ai <listar@mail.ru>
2011-08-08 15:02 ` Carla <cgourofino@hotmail.com>
2011-08-08 15:34 ` ai <listar@mail.ru>
2011-08-08 19:14 ` Carla <cgourofino@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox