ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
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
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
regards, tom lane
On Thu, Nov 28, 2024 at 5:15 PM 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/19Please 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 = v.id
WHERE
v.id = 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 = pt.testrules_id
) AS taxes ON taxes.products_id = 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
Bertrand Mamasam <golgote@gmail.com> writes:
Ok thank you, here is a short version of the query that works before 16.5
but not in 16.5+ anymore.
Thanks for the test case! A quick "git bisect" says I broke it at
cb8e50a4a09fe541e32cd54ea90a97f2924121a1 is the first bad commit
commit cb8e50a4a09fe541e32cd54ea90a97f2924121a1
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Aug 30 12:42:12 2024 -0400
Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not.
Apparently that change was less safe than I thought. Looking ...
regards, tom lane
I wrote:
Thanks for the test case! A quick "git bisect" says I broke it at
cb8e50a4a09fe541e32cd54ea90a97f2924121a1 is the first bad commit
commit cb8e50a4a09fe541e32cd54ea90a97f2924121a1
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Aug 30 12:42:12 2024 -0400
Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not.
Apparently that change was less safe than I thought. Looking ...
Fixed here:
Thanks for the report!
regards, tom lane
On Thu, Nov 28, 2024 at 11:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
Thanks for the test case! A quick "git bisect" says I broke it at
cb8e50a4a09fe541e32cd54ea90a97f2924121a1 is the first bad commit
commit cb8e50a4a09fe541e32cd54ea90a97f2924121a1
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Aug 30 12:42:12 2024 -0400
Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not.
Apparently that change was less safe than I thought. Looking ...Fixed here:
Thanks for the report!
Excellent ! Thank you very much !!!
Bertrand