MIME-Version: 1.0
From: Bertrand Mamasam <golgote@gmail.com>
Date: Thu, 28 Nov 2024 15:35:08 +0100
Message-ID: <CACZ67_UA_EVrqiFXJu9XK50baEpH=ofEPJswa2kFxg6xuSw-ww@mail.gmail.com>
Subject: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN
 LATERAL and CTE
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000dfb64a0627f9fddb"
Archived-At: <https://www.postgresql.org/message-id/CACZ67_UA_EVrqiFXJu9XK50baEpH%3DofEPJswa2kFxg6xuSw-ww%40mail.gmail.com>
Precedence: bulk

--000000000000dfb64a0627f9fddb
Content-Type: text/plain; charset="UTF-8"

Hello,

My query has been working fine in 16.4 and before, but not anymore in 16.5,
16.6 and 17. It is a query with multiple CTE, some of which are using
values of the previous ones, and the end of the query sort of make a mix of
found values with aggregation from a LATERAL JOIN. Something like this :

WITH
taxrules AS (...)
, defaultprices AS (...)
, baseprices AS (...)
, currentprices AS (...)
, discountedprices AS (...)

SELECT
discountedprices.variants_id,
discountedprices.products_id,
sum(COALESCE(taxes.tax_price, 0))

FROM
discountedprices
LEFT JOIN LATERAL (
    SELECT
    products_taxrules.products_id,
    round(discountedprices.price * taxrules.rate_percent, 4) -
discountedprices.price AS tax_price
    FROM taxrules
    INNER JOIN products_taxrules ON taxrules.id =
products_taxrules.taxrules_id
) AS taxes ON taxes.products_id = discountedprices.products_id
WHERE
discountedprices.variants_id = ANY(ARRAY[12345])
GROUP BY
discountedprices.variants_id,
discountedprices.products_id,
discountedprices.price
;

I get this error in PG16.5, 16.6 and 17 :
wrong varnullingrels (b 3) (expected (b)) for Var 1/19

The query works again if I add a COALESCE on the line in the LATERAL JOIN
query like this :
round(discountedprices.price * COALESCE(taxrules.rate_percent, 0), 4) -
discountedprices.price AS tax_price

The query also works if I use an INNER JOIN LATERAL instead of a LEFT JOIN
LATERAL.

But the taxrules.rate_percent cannot be null anyway. It comes from the
result of this calculation : (1 + t.rate_percent / 100) AS rate_percent in
the taxrules CTE.

So now I wonder if my sql was wrong and should be fixed or if since 16.5
Postgresql has a bug in the way it deals with values in a LEFT JOIN LATERAL
?

Thanks for your help,

Bertrand Mansion
Mamasam

--000000000000dfb64a0627f9fddb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello,</div><div><br></div><div>My query has been wor=
king fine in 16.4 and before, but not anymore in 16.5, 16.6 and 17. It is a=
 query with multiple CTE, some of which are using values of the previous on=
es, and the end of the query sort of make a mix of found values with aggreg=
ation from a LATERAL JOIN. Something like this : <br></div><div><br></div><=
div>WITH<br>taxrules AS (...)<br>, defaultprices AS (...)<br>, baseprices A=
S (...)<br>, currentprices AS (...)<br>, discountedprices AS (...)<br><br>S=
ELECT<br>	discountedprices.variants_id,<br>	discountedprices.products_id,<b=
r>	sum(COALESCE(taxes.tax_price, 0))<br><br>FROM <br>	discountedprices<br>	=
LEFT JOIN LATERAL (<br>=C2=A0=C2=A0=C2=A0 SELECT <br>=C2=A0=C2=A0=C2=A0 pro=
ducts_taxrules.products_id,<br>=C2=A0=C2=A0=C2=A0 round(discountedprices.pr=
ice * taxrules.rate_percent, 4) - discountedprices.price AS tax_price<br>=
=C2=A0=C2=A0=C2=A0 FROM taxrules<br>=C2=A0=C2=A0=C2=A0 INNER JOIN products_=
taxrules ON <a href=3D"http://taxrules.id">taxrules.id</a> =3D products_tax=
rules.taxrules_id<br>	) AS taxes ON taxes.products_id =3D discountedprices.=
products_id<br>WHERE<br>	discountedprices.variants_id =3D ANY(ARRAY[12345])=
<br>GROUP BY<br>	discountedprices.variants_id,<br>	discountedprices.product=
s_id,<br>	discountedprices.price<br>;</div><div><br></div><div>I get this e=
rror in PG16.5, 16.6 and 17 : <br>wrong varnullingrels (b 3) (expected (b))=
 for Var 1/19</div><div><br></div><div>The query works again if I add a COA=
LESCE on the line in the LATERAL JOIN query like this : <br>round(discounte=
dprices.price * COALESCE(taxrules.rate_percent, 0), 4) - discountedprices.p=
rice AS tax_price<br><br></div><div>The query also works if I use an INNER =
JOIN LATERAL instead of a LEFT JOIN LATERAL.<br></div><div><br></div><div>B=
ut the taxrules.rate_percent cannot be null anyway. It comes from the resul=
t of this calculation : (1 + t.rate_percent / 100) AS rate_percent in the t=
axrules CTE.<br></div><div><br></div><div>So now I wonder if my sql was wro=
ng and should be fixed or if since 16.5 Postgresql has a bug in the way it =
deals with values in a LEFT JOIN LATERAL ?<br><br></div><div>Thanks for you=
r help,<br><br></div><div>Bertrand Mansion<br></div><div>Mamasam<br></div><=
div><br></div></div>

--000000000000dfb64a0627f9fddb--