Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lt8m4-0000DI-0q for pgsql-bugs@arkaria.postgresql.org; Tue, 15 Jun 2021 13:06:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lt8m2-0000oJ-1Q for pgsql-bugs@arkaria.postgresql.org; Tue, 15 Jun 2021 13:06:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lt8m1-0000oB-IB for pgsql-bugs@lists.postgresql.org; Tue, 15 Jun 2021 13:06:33 +0000 Received: from smtp03.optiver.com ([195.246.114.70]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lt8ly-0003QV-UB for pgsql-bugs@lists.postgresql.org; Tue, 15 Jun 2021 13:06:32 +0000 Received: from pps.filterd (smtp03.optiver.com [127.0.0.1]) by smtp03.optiver.com (8.16.0.42/8.16.0.42) with SMTP id 15FCuslH018662 for ; Tue, 15 Jun 2021 15:06:28 +0200 Received: from opammb0561.comp.optiver.com (opammb0561.comp.optiver.com [10.131.160.1]) by smtp03.optiver.com with ESMTP id 396j8b0e3t-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT) for ; Tue, 15 Jun 2021 15:06:28 +0200 Received: from opammb0562.comp.optiver.com (10.131.160.2) by opammb0561.comp.optiver.com (10.131.160.1) with Microsoft SMTP Server (TLS) id 15.0.1497.18; Tue, 15 Jun 2021 15:06:28 +0200 Received: from opammb0562.comp.optiver.com ([fe80::f88f:f345:d769:f129]) by opammb0562.comp.optiver.com ([fe80::f88f:f345:d769:f129%17]) with mapi id 15.00.1497.018; Tue, 15 Jun 2021 15:06:28 +0200 From: Floris Van Nee To: "pgsql-bugs@lists.postgresql.org" Subject: Postgres turns LEFT JOIN into INNER JOIN - incorrect results Thread-Topic: Postgres turns LEFT JOIN into INNER JOIN - incorrect results Thread-Index: Addh5xN0RsT7WAAVTuuQXZ4FWwf9Ow== Date: Tue, 15 Jun 2021 13:06:27 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.131.160.182] x-tm-as-product-ver: SMEX-14.0.0.3031-8.6.1012-26220.007 x-tm-as-result: No-10--12.992500-5.000000 x-tmase-matchedrid: 6K44twgshi5//2guIJ+U2P0peXGEEBlvDvKSaIxu6kpXG3yI9k2vbIfN L7D/HMEOKHWHnxIi33mD28NNt7xCyCkBeV/1d1sZ30kDaWZBE1TQTttTsZbKLwcR01yaR+FBVCe Rwk3k2W4/+RSNQ9LGXlDQ43dkW3a5RS5eRZNKL9GL6bUMM+bbIv98fIf4wIXlIeOLVF/ekdARSB +McTz1ommHm+BmWbxCnCrgBrAeALonIQMuGe8To7mQWToO0X1/69xONVZSrH2jrwHqIrC9iEWYF vwCgnnKBmHSBRtWoiui+vGQCCdLtb9Z6B1LT2EqbrJ9gVnOsZ0h6cl1707zKgV/ZwpeHKKPAxOp P4umNEUVJADbu4rGs9sM3o1IauDHuiJKcVHI13kD2WXLXdz+AQBiOiJvgsY/T2sTalHWsPSeAiC mPx4NwMidYBYDjITpLzP5snaeb1R+qWQy8FC7MtTN8iFOQW9HP9nh4+RT8FNbNIJ/SDdETgHL8g QXNJthlQNE68ID+2k= x-tm-as-user-approved-sender: Yes x-tm-as-user-blocked-sender: No x-tmase-result: 10--12.992500-5.000000 x-tmase-version: SMEX-14.0.0.3031-8.6.1012-26220.007 x-tm-snts-smtp: 60394541B4708D4D6B29AED14A1D3A966857A32BB23F1A21D469E8BDB49E281C2000:8 Content-Type: multipart/alternative; boundary="_000_dfb0057d6a8d4b4f8a63330dd621d4b1opammb0562compoptiverco_" MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:6.0.391,18.0.761 definitions=2021-06-15_04:2021-06-14,2021-06-15 signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_dfb0057d6a8d4b4f8a63330dd621d4b1opammb0562compoptiverco_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, We've ran into a situation in which Postgres returns an incorrect query res= ult. I've managed to narrow it down to the following reproducible example. = I've encountered it on 12.4, but it reproduces on HEAD. set random_page_cost=3D1; create table t1 as select a::text from generate_series(1, 1000) a; create index on t1 (a); analyze t1; CREATE OR REPLACE FUNCTION test_internal(_a text) RETURNS TABLE(_a text) LANGUAGE sql STABLE PARALLEL SAFE ROWS 1 AS $function$ SELECT t1.a FROM t1 WHERE t1.a =3D _a $function$ ; CREATE OR REPLACE FUNCTION test(_a text) RETURNS TABLE(a text) LANGUAGE sql STABLE PARALLEL SAFE ROWS 1 AS $function$ SELECT t2.a FROM (VALUES ('a')) t2(a) LEFT JOIN test_internal(_a) t1 ON TRUE WHERE t2.a =3D _a $function$ ; -- this returns 0 rows (incorrect) SELECT * FROM test('a'); -- this returns 1 row (correct) SELECT t2.a FROM (VALUES ('a')) t2(a) LEFT JOIN test_internal('a') t1 ON TRUE WHERE t2.a =3D 'a'; The two SELECT queries should be identical. The first one calls a function = that runs the SELECT, the second one runs the same query but outside of the= function. However, Postgres seems to forget that the query uses a LEFT JOIN. This is = the plan that it chooses for both queries. In the first case, it optimizes = the whole VALUES part away and just scans relation t1. In the second case, = it properly chooses a Nested Loop Left Join node. postgres=3D# explain postgres-# SELECT * FROM test('a') postgres-# ; QUERY PLAN ------------------------------------------------------------------------- Index Only Scan using t1_a_idx on t1 (cost=3D0.28..2.29 rows=3D1 width=3D3= 2) Index Cond: (a =3D 'a'::text) (2 rows) postgres=3D# postgres=3D# explain postgres-# SELECT postgres-# t2.a postgres-# FROM (VALUES ('a')) t2(a) postgres-# LEFT JOIN LATERAL test_internal('a') t1 ON TRUE postgres-# WHERE t2.a =3D 'a' postgres-# ; QUERY PLAN ---------------------------------------------------------------------------= --- Nested Loop Left Join (cost=3D0.28..2.31 rows=3D1 width=3D32) -> Result (cost=3D0.00..0.01 rows=3D1 width=3D0) -> Index Only Scan using t1_a_idx on t1 (cost=3D0.28..2.29 rows=3D1 wi= dth=3D0) Index Cond: (a =3D 'a'::text) (4 rows) --_000_dfb0057d6a8d4b4f8a63330dd621d4b1opammb0562compoptiverco_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

We’ve ran into a situation in which Postgres r= eturns an incorrect query result. I’ve managed to narrow it down to t= he following reproducible example. I’ve encountered it on 12.4, but i= t reproduces on HEAD.

 

=  

= set random_page_cost=3D1;

= create table t1 as select a::text from generate_series(1, 1000) a;

= create index on t1 (a);

= analyze t1;

=  

= CREATE OR REPLACE FUNCTION test_internal(_a text)

= RETURNS TABLE(_a text)

= LANGUAGE sql

= STABLE PARALLEL SAFE ROWS 1

= AS $function$

=    SELECT

=       t1.a

=    FROM t1

=    WHERE t1.a =3D _a

= $function$

= ;

=  

= CREATE OR REPLACE FUNCTION test(_a text)

= RETURNS TABLE(a text)

= LANGUAGE sql

= STABLE PARALLEL SAFE ROWS 1

= AS $function$

=    SELECT

=       t2.a

=    FROM (VALUES ('a')) t2(a)

=    LEFT JOIN test_internal(_a) t1 ON TRUE

=    WHERE t2.a =3D _a

= $function$

= ;

=  

= -- this returns 0 rows (incorrect)

= SELECT * FROM test('a');

=  

= -- this returns 1 row (correct)

= SELECT

=    t2.a

= FROM (VALUES ('a')) t2(a)

= LEFT JOIN test_internal('a') t1 ON TRUE

= WHERE t2.a =3D 'a';

=  

 

The two SELECT queries should be identical. The firs= t one calls a function that runs the SELECT, the second one runs the same q= uery but outside of the function.

However, Postgres seems to forget that the query use= s a LEFT JOIN. This is the plan that it chooses for both queries. In the fi= rst case, it optimizes the whole VALUES part away and just scans relation t= 1. In the second case, it properly chooses a Nested Loop Left Join node.

 

 

= postgres=3D# explain

= postgres-# SELECT * FROM test('a')

= postgres-# ;

=             &nb= sp;            =       QUERY PLAN     &nbs= p;            &= nbsp;           &nbs= p;

= -------------------------------------------------------------------------

= Index Only Scan using t1_a_idx on t1  (cost=3D0.28..2.29 rows=3D1 widt= h=3D32)

=    Index Cond: (a =3D 'a'::text)

= (2 rows)

=  

= postgres=3D#

= postgres=3D# explain

= postgres-# SELECT

= postgres-#    t2.a

= postgres-# FROM (VALUES ('a')) t2(a)

= postgres-# LEFT JOIN LATERAL test_internal('a') t1 ON TRUE

= postgres-# WHERE t2.a =3D 'a'

= postgres-# ;

=             &nb= sp;            =          QUERY PLAN  &nbs= p;            &= nbsp;           &nbs= p;     

= ---------------------------------------------------------------------------= ---

= Nested Loop Left Join  (cost=3D0.28..2.31 rows=3D1 width=3D32)

=    ->  Result  (cost=3D0.00..0.01 rows=3D1 width=3D0= )

=    ->  Index Only Scan using t1_a_idx on t1  (cost= =3D0.28..2.29 rows=3D1 width=3D0)

=          Index Cond: (a =3D 'a'::te= xt)

= (4 rows)

--_000_dfb0057d6a8d4b4f8a63330dd621d4b1opammb0562compoptiverco_--