Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aZP7e-0001dt-Lq for pgsql-sql@arkaria.postgresql.org; Fri, 26 Feb 2016 20:36:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aZP7d-0001h9-2I for pgsql-sql@arkaria.postgresql.org; Fri, 26 Feb 2016 20:36:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aZP6d-0000cD-Nj for pgsql-sql@postgresql.org; Fri, 26 Feb 2016 20:35:19 +0000 Received: from mail-pf0-x236.google.com ([2607:f8b0:400e:c00::236]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aZP6Y-0004py-IO for pgsql-sql@postgresql.org; Fri, 26 Feb 2016 20:35:17 +0000 Received: by mail-pf0-x236.google.com with SMTP id w128so9856736pfb.2 for ; Fri, 26 Feb 2016 12:35:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to; bh=WD/NVqu044Ehrbtp8zG9Qxd8gjT1kYHMeqIEUVgJWs4=; b=skIOr9IFF6Bz0mYd3vjOELCxCaqcZNI0pXNMO/mCNvmMwPUfQwkYwdjk5yd+Ql8vzL 0M+es1+GdTDUEcp/VHqEuiak6kXDfTT3o8XeguokKfuOG1qaqRIl79JKMDdTuryJWmqd G8q4BfTFEHfpTMb/tSLh5Xv7i/ZPNcgNa/+NTRLfyY0myTcrb2Hoe3bfLOMQA4XxniE2 CJ8pn17wLUG8uXS5/Y7skkiCyMcOGdSdTlVbDx/EOg3+Y1sGbomJZy5CV9+HhCNQZDT4 XgozOHfOOLHCdn8+WTgl8+r7fvB7EsedJwlogGTo+hiI7lbziE9Zz2p5WEOeAGbTm5jz WwkA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to; bh=WD/NVqu044Ehrbtp8zG9Qxd8gjT1kYHMeqIEUVgJWs4=; b=EZ+9sqGkbjg96av3uiT6CDYLf5UnR/DJM6W6cE1gGOAISPW3nw2boBkLDm1aMQ4il0 5wLy+c2YE+a/NgK7PwIOpjwwGGkJoZWmSgrrSb6AQiOHy5rHP2XZrOdd5FRzftykElC8 wQRdmu5dcRh34aYr+ZjarOc722ZtfPw2GgLIuD6CMwlhhonen2QSuPBxmj6CdNWtCKdO mQvMVqjPJFmXXl/fLAcSPurbu60IL+sTr/rWVbxijGqqlGME3Bu6q/jKvxLDRFzgK2LM 37RVBDHkbdzOgWApn4hoijIMHkojLWdIH6Ik4FFQFGH7hEQ/ySufJGPnf1LSoo1zGcjW CjaQ== X-Gm-Message-State: AD7BkJLAX85Zn8QAQ7ldV1xQl28an72bAjE97UmfIufSt8LevV0bEQt1svqTDOhtqckB9Q== X-Received: by 10.98.13.154 with SMTP id 26mr4922406pfn.164.1456518912016; Fri, 26 Feb 2016 12:35:12 -0800 (PST) Received: from [155.100.214.120] ([155.100.214.120]) by smtp.gmail.com with ESMTPSA id o90sm21285931pfi.17.2016.02.26.12.35.10 for (version=TLSv1/SSLv3 cipher=OTHER); Fri, 26 Feb 2016 12:35:10 -0800 (PST) Subject: Re: Subselect left join / not exists() To: pgsql-sql@postgresql.org References: From: Rob Sargent Message-ID: <56D0B710.7070004@gmail.com> Date: Fri, 26 Feb 2016 13:35:28 -0700 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.5.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------000703070007040104050608" X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------000703070007040104050608 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > > wrote: > > On Fri, Feb 26, 2016 at 4:17 AM, Desmond Coertzen > >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 --------------000703070007040104050608 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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



The real schema and sql used might get people started

--------------000703070007040104050608--