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
