Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE

[Bertrand Mamasam]

On Thu, Nov 28, 2024 at 5:15 PM Tom Lane <t...@sss.pgh.pa.us> wrote:

> Bertrand Mamasam <golg...@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 = 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