Re: Subselect left join / not exists() - Mailing list pgsql-sql

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> wrote:

On Fri, Feb 26, 2016 at 4:17 AM, Desmond Coertzen <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.