public inbox for [email protected]
help / color / mirror / Atom feedERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
3+ messages / 2 participants
[nested] [flat]
* ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
@ 2024-11-28 14:35 Bertrand Mamasam <[email protected]>
2024-11-28 16:15 ` Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE Tom Lane <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Bertrand Mamasam @ 2024-11-28 14:35 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
2024-11-28 14:35 ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE Bertrand Mamasam <[email protected]>
@ 2024-11-28 16:15 ` Tom Lane <[email protected]>
2024-11-28 17:29 ` Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE Bertrand Mamasam <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2024-11-28 16:15 UTC (permalink / raw)
To: Bertrand Mamasam <[email protected]>; +Cc: pgsql-general
Bertrand Mamasam <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
2024-11-28 14:35 ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE Bertrand Mamasam <[email protected]>
2024-11-28 16:15 ` Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE Tom Lane <[email protected]>
@ 2024-11-28 17:29 ` Bertrand Mamasam <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Bertrand Mamasam @ 2024-11-28 17:29 UTC (permalink / raw)
To: pgsql-general
On Thu, Nov 28, 2024 at 5:15 PM Tom Lane <[email protected]> wrote:
> Bertrand Mamasam <[email protected]> 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 = 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-11-28 17:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-28 14:35 ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE Bertrand Mamasam <[email protected]>
2024-11-28 16:15 ` Tom Lane <[email protected]>
2024-11-28 17:29 ` Bertrand Mamasam <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox