MIME-Version: 1.0
References: <CACZ67_UA_EVrqiFXJu9XK50baEpH=ofEPJswa2kFxg6xuSw-ww@mail.gmail.com>
 <2090950.1732810550@sss.pgh.pa.us>
In-Reply-To: <2090950.1732810550@sss.pgh.pa.us>
From: Bertrand Mamasam <golgote@gmail.com>
Date: Thu, 28 Nov 2024 18:29:01 +0100
Message-ID: <CACZ67_ULS3HExOnRou1SXZZMzcAA2fE_WF3pzaFDEDW9v3ACMQ@mail.gmail.com>
Subject: Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN
 LATERAL and CTE
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000bf250a0627fc6bfd"
Archived-At: <https://www.postgresql.org/message-id/CACZ67_ULS3HExOnRou1SXZZMzcAA2fE_WF3pzaFDEDW9v3ACMQ%40mail.gmail.com>
Precedence: bulk

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

On Thu, Nov 28, 2024 at 5:15=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Bertrand Mamasam <golgote@gmail.com> writes:
> > I get this error in PG16.5, 16.6 and 17 :
> > wrong varnullingrels (b 3) (expected (b)) for Var 1/19
>
> Please provide a self-contained test case.  A fragmentary query
> with no underlying tables is useless for investigation.
>
> https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>

Ok thank you, here is a short version of the query that works before 16.5
but not in 16.5+ anymore.

```sql
CREATE TABLE testrules (
    id text PRIMARY KEY,
    rate_percent numeric(10,3) NOT NULL DEFAULT 0.000
);
INSERT INTO "testrules"("id","rate_percent") VALUES ('EU-FR-20', 20);

CREATE TABLE products_testrules (
    products_id bigint,
    testrules_id text REFERENCES testrules(id) ON DELETE CASCADE ON UPDATE
CASCADE,
    CONSTRAINT products_testrules_pkey PRIMARY KEY (products_id,
testrules_id)
);
INSERT INTO "public"."products_testrules"("products_id","testrules_id")
VALUES (52, 'EU-FR-20');

CREATE TABLE testvariants (
    id BIGSERIAL PRIMARY KEY,
    products_id bigint
);
INSERT INTO "public"."testvariants"("id","products_id") VALUES (20, 52);

CREATE TABLE testprices (
    id BIGSERIAL PRIMARY KEY,
    variants_id bigint NOT NULL,
    price numeric(10,4) NOT NULL
);
INSERT INTO "public"."testprices"("id","variants_id","price")
VALUES
(645046,20,120.833);

WITH tr AS (
SELECT
t.id,
(1 + t.rate_percent / 100) AS rate_percent
FROM testrules t
)
, vd AS (

    SELECT
    v.id AS variants_id,
    v.products_id,
    p.price
    FROM testvariants v
    JOIN testprices p ON p.variants_id =3D v.id
    WHERE
v.id =3D 20

)
SELECT
vd.variants_id,
vd.products_id,
vd.price + (sum(COALESCE(taxes.tax_price, 0) )) as price_tax,
min(COALESCE(taxes.tax_price, 0))
FROM
vd
LEFT  JOIN LATERAL (

SELECT
pt.products_id,
tr.id,
round(vd.price * tr.rate_percent, 4) - vd.price AS tax_price
FROM tr
INNER JOIN products_testrules pt ON tr.id =3D pt.testrules_id

) AS taxes ON taxes.products_id =3D vd.products_id

GROUP BY
vd.variants_id,
vd.products_id,
vd.price
;
```

In 16.4, it returns :
| 20 | 52 | 144,9996 | 24,1666 |

In 16.6, it throws :
ERROR:  wrong varnullingrels (b 3) (expected (b)) for Var 5/3

Thanks for your help.

Bertrand

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Nov 28, 2024 at 5:15=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">Bertrand Mamasam &lt;<a href=3D"mailto:=
golgote@gmail.com" target=3D"_blank">golgote@gmail.com</a>&gt; writes:<br>
&gt; I get this error in PG16.5, 16.6 and 17 :<br>
&gt; wrong varnullingrels (b 3) (expected (b)) for Var 1/19<br>
<br>
Please provide a self-contained test case.=C2=A0 A fragmentary query<br>
with no underlying tables is useless for investigation.<br>
<br>
<a href=3D"https://wiki.postgresql.org/wiki/Guide_to_reporting_problems" re=
l=3D"noreferrer" target=3D"_blank">https://wiki.postgresql.org/wiki/Guide_t=
o_reporting_problems</a><br></blockquote><div><br></div><div>Ok thank you, =
here is a short version of the query that works before 16.5 but not in 16.5=
+ anymore.</div><div><br></div><div>```sql<br></div><div>CREATE TABLE testr=
ules (<br>=C2=A0 =C2=A0 id text PRIMARY KEY,<br>=C2=A0 =C2=A0 rate_percent =
numeric(10,3) NOT NULL DEFAULT 0.000<br>);<br>INSERT INTO &quot;testrules&q=
uot;(&quot;id&quot;,&quot;rate_percent&quot;) VALUES (&#39;EU-FR-20&#39;, 2=
0);<br><br>CREATE TABLE products_testrules (<br>=C2=A0 =C2=A0 products_id b=
igint,<br>=C2=A0 =C2=A0 testrules_id text REFERENCES testrules(id) ON DELET=
E CASCADE ON UPDATE CASCADE,<br>=C2=A0 =C2=A0 CONSTRAINT products_testrules=
_pkey PRIMARY KEY (products_id, testrules_id)<br>);<br>INSERT INTO &quot;pu=
blic&quot;.&quot;products_testrules&quot;(&quot;products_id&quot;,&quot;tes=
trules_id&quot;) VALUES (52, &#39;EU-FR-20&#39;);<br><br>CREATE TABLE testv=
ariants (<br>=C2=A0 =C2=A0 id BIGSERIAL PRIMARY KEY,<br>=C2=A0 =C2=A0 produ=
cts_id bigint<br>);<br>INSERT INTO &quot;public&quot;.&quot;testvariants&qu=
ot;(&quot;id&quot;,&quot;products_id&quot;) VALUES (20, 52);<br><br>CREATE =
TABLE testprices (<br>=C2=A0 =C2=A0 id BIGSERIAL PRIMARY KEY,<br>=C2=A0 =C2=
=A0 variants_id bigint NOT NULL,<br>=C2=A0 =C2=A0 price numeric(10,4) NOT N=
ULL<br>);<br>INSERT INTO &quot;public&quot;.&quot;testprices&quot;(&quot;id=
&quot;,&quot;variants_id&quot;,&quot;price&quot;)<br>VALUES<br>(645046,20,1=
20.833);<br><br>WITH tr AS (		<br>	SELECT<br>	<a href=3D"http://t.id">t.id<=
/a>, <br>	(1 + t.rate_percent / 100) AS rate_percent<br>	FROM testrules t<b=
r>)<br>, vd AS (<br><br>=C2=A0 =C2=A0 SELECT<br>=C2=A0 =C2=A0 <a href=3D"ht=
tp://v.id">v.id</a> AS variants_id,<br>=C2=A0 =C2=A0 v.products_id,<br>=C2=
=A0 =C2=A0 p.price<br>=C2=A0 =C2=A0 FROM testvariants v<br>=C2=A0 =C2=A0 JO=
IN testprices p ON p.variants_id =3D <a href=3D"http://v.id">v.id</a> <br>=
=C2=A0 =C2=A0 WHERE<br>		<a href=3D"http://v.id">v.id</a> =3D 20<br><br>)<b=
r>SELECT<br>	vd.variants_id,<br>	vd.products_id, <br>	vd.price + (sum(COALE=
SCE(taxes.tax_price, 0) )) as price_tax, <br>	min(COALESCE(taxes.tax_price,=
 0))<br>FROM <br>	vd<br>	LEFT =C2=A0JOIN LATERAL (<br><br>		SELECT <br>		pt=
.products_id, <br>		<a href=3D"http://tr.id">tr.id</a>,<br>		round(vd.price=
 * tr.rate_percent, 4) - vd.price AS tax_price<br>		FROM tr<br>		INNER JOIN=
 products_testrules pt ON <a href=3D"http://tr.id">tr.id</a> =3D pt.testrul=
es_id<br><br>	) AS taxes ON taxes.products_id =3D vd.products_id<br><br>GRO=
UP BY<br>	vd.variants_id,<br>	vd.products_id,<br>	vd.price<br>;</div><div>`=
``</div><div><br></div><div>In 16.4, it returns : <br></div><div>| 20	| 52 =
|	144,9996 |	24,1666 |<br></div><div><br></div><div>In 16.6, it throws :</d=
iv><div>ERROR: =C2=A0wrong varnullingrels (b 3) (expected (b)) for Var 5/3<=
/div><div><br></div><div>Thanks for your help.</div><div><br></div><div>Ber=
trand<br></div></div></div>

--000000000000bf250a0627fc6bfd--