public inbox for [email protected]  

help / color / mirror / Atom feed

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

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

view thread (3+ messages)  latest in thread

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_UA_EVrqiFXJu9XK50baEpH=ofEPJswa2kFxg6xuSw-ww@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