agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Subselect left join / not exists()
Date: Fri, 26 Feb 2016 13:35:28 -0700
Message-ID: <56D0B710.7070004@gmail.com> (raw)
In-Reply-To: <CALQ6=2DRwm8Oo-HEswvUPEm4rR8hNYYqeF7c6m1hv027J0oSvg@mail.gmail.com>
References: <CALQ6=2BRu5P5=u5RE8su_JQhJBj+1b-oSMbuq97d40M4C6iwgQ@mail.gmail.com>
<CAKFQuwYj-LXJxF8NNqVrEXnyXPVACDmaP6Qy3kgYUCaWZ5bK2A@mail.gmail.com>
<CALQ6=2DRwm8Oo-HEswvUPEm4rR8hNYYqeF7c6m1hv027J0oSvg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 02/26/2016 12:54 PM, Desmond Coertzen wrote:
> I don't use the terms "bogus" and "weird" lightly.
>
> A self contained test case is difficult to produce. I already built a
> script that creates a DB, three tables and test data, and then
> exercised the two forms of the the sub select. As expected, the test
> case does not provoke the behaviour whitnessed. Other than providing
> the entire DB dump to recreate the exact conditions that provoke this
> behaviour, I don't see how I can provide a self contained test case.
> The real table representing "long_story" in my report contains over
> 10.5 million rows and the behaviour in the sub select was not there
> before today. Possibly as my data collection grew, I may have stumbled
> over a problem.
>
> I will try anyway by inserting more rows to try and provoke the
> behaviour. I will continue my answer on Tom's reply.
>
>
> On Fri, Feb 26, 2016 at 4:53 PM, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
> On Fri, Feb 26, 2016 at 4:17 AM, Desmond Coertzen
> <patrolliekaptein@gmail.com <mailto:patrolliekaptein@gmail.com>>wrote:
>
>
> It's not the best DB design but the query without the null
> test and the max aggregate should have worked. I am convinced
> there must be a bug exposed when doing nested sub queries on
> the same table and the bug may show itself the deeper you
> stack - stack meaning nested subselect on the same table. I am
> also convinced that I am completely insane and may be missing
> something very obvious like a noob.
>
> Any help/comment highly appreciated in advance.
>
>
> If you deign to provide a self-contained test case showing where
> the non-aggregated query provides bogus results while the
> aggregated and limited one does not we would be most greatful
> since we could then test whether what you are seeing exists in a
> release of PostgreSQL that is currently supported. And if the
> behavior is correct we would have concrete values that could be
> used the in the explanation of said behavior.
>
> Don't expect us to be able to upgrade the quality of the
> discussion: If the best you can give us is phrases like "weird"
> and "bogus" to describe what you are seeing, and no explicit
> schema definitions, then they best I can say is that while this
> looks odd it is likely explainable and a direct function of the
> fact that "it's not the best DB design" and that because of such
> there are data anomalies that are potentially coming into play here.
>
> Or its a bug - potentially one that has been fixed.
>
> David J.
>
>
>
The real schema and sql used might get people started
view thread (10+ messages) latest in thread
Message-ID: <56D0B710.7070004@gmail.com>
Permalink: ../56D0B710.7070004@gmail.com/
Also on: postgresql.org/message-id/56D0B710.7070004@gmail.com
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: robjsargent@gmail.com
Subject: Re: Subselect left join / not exists()
In-Reply-To: <56D0B710.7070004@gmail.com>
* 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