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

The following bug has been logged online:

Bug reference: 5076
Logged by: Mauro Infantino
Email address: mauro(dot)infantino(at)latincontrol-soft(dot)com
PostgreSQL version: 8.3.8
Operating system: Windows
Description: LEFT OUTER JOIN and WHERE madness
Details:

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).

Thanks.

"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

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.