Horton

Thread

Commits

  1. Avoid mislabeling of lateral references when pulling up a subquery.

  2. Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not.

  1. ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE

    Bertrand Mamasam <golgote@gmail.com> — 2024-11-28T14:35:08Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-28T16:15:50Z

    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

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

    Bertrand Mamasam <golgote@gmail.com> — 2024-11-28T17:29:01Z

    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/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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-28T18:51:02Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-28T22:35:25Z

    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:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=85990e2fd5610576635c65db9292297b1730c947

Thanks for the report!

			regards, tom lane

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

    Bertrand Mamasam <golgote@gmail.com> — 2024-11-28T23:13:10Z

    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:
>
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=85990e2fd5610576635c65db9292297b1730c947
>
> Thanks for the report!
>

Excellent ! Thank you very much !!!

Bertrand