On Thu, Nov 28, 2024 at 5:15 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Bertrand Mamasam <golgote@xxxxxxxxx> 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
;
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
