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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>
> On Fri, Feb 26, 2016 at 4:17 AM, Desmond Coertzen
> <patrolliekaptein(at)gmail(dot)com <mailto:patrolliekaptein(at)gmail(dot)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