Re: BUG #5076: LEFT OUTER JOIN and WHERE madness

On Sun, 27 Sep 2009 00:31:04 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Mauro Infantino" <mauro(dot)infantino(at)latincontrol-soft(dot)com> writes:
>> I'm just experiencing the same as in here,
>> http://archives.postgresql.org/pgsql-bugs/2008-06/msg00175.php
>> It was not happening for 8.3.7 (at least on Windows).
>
> This is the very definition of an unhelpful bug report.
>
> You are not seeing the same thing that was reported against 8.3.3,
> because that bug is fixed --- there's a regression test proving so.
> You might be seeing something else with a similar symptom, but with
> no details or test case, there is nothing we can do about it.
>
> regards, tom lane

Thank you for your answer, Tom.

Attached is what I think would be enough to reproduce the problem. Please
tell me if you need anything else.

When I use this query,

SELECT v_relacionatributocont.bo_place_id,
v_relacionatributocont.descripcion,
v_relacionatributocont.atributo_id,
v_atributos.lista
FROM v_relacionatributocont
LEFT OUTER JOIN v_atributos ON v_relacionatributocont.atributo_id =
v_atributos.id
WHERE v_relacionatributocont.bo_place_id =
'{BF0EFA28-A615-11D5-B09D-004854841C8A}'
AND v_relacionatributocont.atributo_id IS NOT NULL
AND (v_atributos.lista = '' OR v_atributos.lista IS NULL)

I get three rows when only one should be returned. It's easier to see it
just by removing the last condition of the where clause. Just take a look
at the "lista" column's content.

Please tell me if I'm not seeing something obvious.

Thanks in advance,
Mauro Infantino.