public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bertrand Mamasam <[email protected]>
To: [email protected]
Subject: Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
Date: Thu, 28 Nov 2024 18:29:01 +0100
Message-ID: <CACZ67_ULS3HExOnRou1SXZZMzcAA2fE_WF3pzaFDEDW9v3ACMQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CACZ67_UA_EVrqiFXJu9XK50baEpH=ofEPJswa2kFxg6xuSw-ww@mail.gmail.com>
	<[email protected]>

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

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
  In-Reply-To: <CACZ67_ULS3HExOnRou1SXZZMzcAA2fE_WF3pzaFDEDW9v3ACMQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox