agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Desmond Coertzen <patrolliekaptein@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Subselect left join / not exists()
Date: Fri, 26 Feb 2016 10:00:05 -0500
Message-ID: <32307.1456498805@sss.pgh.pa.us> (raw)
In-Reply-To: <CALQ6=2BRu5P5=u5RE8su_JQhJBj+1b-oSMbuq97d40M4C6iwgQ@mail.gmail.com>
References: <CALQ6=2BRu5P5=u5RE8su_JQhJBj+1b-oSMbuq97d40M4C6iwgQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

Desmond Coertzen <patrolliekaptein@gmail.com> writes:
> On Postgres 8.4.22.

You realize of course that 8.4.x has been out of support for more than
a year ...

> The first form of the query looked like:

> select lots, of, stuff,
> (select max(ls2.fiscal_ts)::date
>  from long_story ls2
>  where ls2.contract_id = ls.contract_id and ls2.tr_value > 0 and
> sp_tr_is_cash(ls2.primary_key_id)
>  and not exists(select * from long_story ls2r where ls2r.reverse_of_pk_id =
> ls2.primary_key_id)
> ) as last_cash_tr_ts
> from long_story ls
> where ls.create_ts >= current_date and ls.tr_type_id = 4;

> The subselect columm "last_cash_tr_ts" produces null or bogus result.

You haven't provided nearly enough detail for anyone to judge whether
this is actually a bug or just your wrong expectation of what should
happen.  If you'd like people to look into it, please provide a
self-contained test case: not only the query but table definitions
and sample data.  (Ideally, a SQL script that reproduces the problem
starting from an empty database would make it easy for people to test.
We're not likely to take the time to try to reverse-engineer context
from an incomplete bug report.)

If it is a bug, it will not get fixed in 8.4.x anyway, because there
will never be any more 8.4.x releases.  However, if the bug still exists
in newer release branches, we'd definitely endeavor to fix it there.

			regards, tom lane


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

view thread (10+ messages)  latest in thread

Message-ID: <32307.1456498805@sss.pgh.pa.us>
Permalink:  ../32307.1456498805@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/32307.1456498805@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, patrolliekaptein@gmail.com
  Subject: Re: Subselect left join / not exists()
  In-Reply-To: <32307.1456498805@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox