Thread: Possible bug on Postgres 12 (CASE THEN evaluated prematurely) -Change of behaviour compared to 11, 10, 9

Greetings,

Our system uses an EAV like database and generates queries like the example below.

As you could see the query includes castings, we noticed testing with Postgres 12 that the castings of the CASE THEN
statement(commented out below) where failing in some cases, of course if you do the INNER JOIN and CASE WHEN first our
expectationis that the value can be casted. 

Changing INNER JOIN to LEFT JOIN solved the issue in Postgres 12, testing with earlier versions of Postgres INNER JOIN
workedperfectly.  

Has somebody already reported anything like this? Maybe an issue with some optimisation?

Best,
Juan

Example query:

SELECT DISTINCT t0.id
FROM samples t0
INNER JOIN sample_properties t1 ON t0.id = t1.samp_id
INNER JOIN sample_type_property_types t2 ON t1.stpt_id = t2.id
INNER JOIN property_types t3 ON t2.prty_id = t3.id
INNER JOIN data_types t4 ON t3.daty_id = t4.id
LEFT JOIN controlled_vocabulary_terms t5 ON t1.cvte_id = t5.id
LEFT JOIN materials t6 ON t1.mate_prop_id = t6.id
INNER JOIN sample_properties t7 ON t0.id = t7.samp_id
INNER JOIN sample_type_property_types t8 ON t7.stpt_id = t8.id
INNER JOIN property_types t9 ON t8.prty_id = t9.id
INNER JOIN data_types t10 ON t9.daty_id = t10.id
LEFT JOIN controlled_vocabulary_terms t11 ON t7.cvte_id = t11.id
LEFT JOIN materials t12 ON t7.mate_prop_id = t12.id
INNER JOIN sample_properties t13 ON t0.id = t13.samp_id
INNER JOIN sample_type_property_types t14 ON t13.stpt_id = t14.id
INNER JOIN property_types t15 ON t14.prty_id = t15.id
INNER JOIN data_types t16 ON t15.daty_id = t16.id
LEFT JOIN controlled_vocabulary_terms t17 ON t13.cvte_id = t17.id
LEFT JOIN materials t18 ON t13.mate_prop_id = t18.id
INNER JOIN sample_properties t19 ON t0.id = t19.samp_id
INNER JOIN sample_type_property_types t20 ON t19.stpt_id = t20.id
INNER JOIN property_types t21 ON t20.prty_id = t21.id
INNER JOIN data_types t22 ON t21.daty_id = t22.id
LEFT JOIN controlled_vocabulary_terms t23 ON t19.cvte_id = t23.id
LEFT JOIN materials t24 ON t19.mate_prop_id = t24.id
INNER JOIN sample_properties t25 ON t0.id = t25.samp_id
INNER JOIN sample_type_property_types t26 ON t25.stpt_id = t26.id
INNER JOIN property_types t27 ON t26.prty_id = t27.id
INNER JOIN data_types t28 ON t27.daty_id = t28.id
LEFT JOIN controlled_vocabulary_terms t29 ON t25.cvte_id = t29.id
LEFT JOIN materials t30 ON t25.mate_prop_id = t30.id
WHERE t0.saty_id IN (SELECT unnest(ARRAY[5])) AND t3.is_internal_namespace = true
  AND t3.code = 'STORAGE_POSITION.STORAGE_CODE'
  AND (lower(t1.value) = 'default_storage' OR lower(t5.code) = 'default_storage' OR lower(t6.code) = 'default_storage')
  AND t7.stpt_id = (SELECT id FROM sample_type_property_types WHERE saty_id = 5 AND prty_id = (SELECT id FROM
property_typesWHERE is_internal_namespace = true AND code = 'STORAGE_POSITION.STORAGE_RACK_ROW')) 
  AND t7.value::numeric = 1
--  AND
--    CASE WHEN t9.is_internal_namespace = true
--      AND t9.code = 'STORAGE_POSITION.STORAGE_RACK_ROW'
--      AND (t10.code = 'INTEGER' OR t10.code = 'REAL')
--    THEN t7.value::numeric = 1
--    ELSE false
--    END
  AND t13.stpt_id = (SELECT id FROM sample_type_property_types WHERE saty_id = 5 AND prty_id = (SELECT id FROM
property_typesWHERE is_internal_namespace = true AND code = 'STORAGE_POSITION.STORAGE_RACK_COLUMN')) 
  AND t13.value::numeric = 2
--  AND
--    CASE WHEN t15.is_internal_namespace = true
--      AND t15.code = 'STORAGE_POSITION.STORAGE_RACK_COLUMN'
--      AND (t16.code = 'INTEGER' OR t16.code = 'REAL')
--    THEN t13.value::numeric = 2
--    ELSE false
--    END
  AND t21.is_internal_namespace = true
  AND t21.code = 'STORAGE_POSITION.STORAGE_BOX_NAME'
  AND (lower(t19.value) = 'box2' OR lower(t23.code) = 'box2' OR lower(t24.code) = 'box2')
  AND t27.is_internal_namespace = true
  AND t27.code = 'STORAGE_POSITION.STORAGE_BOX_POSITION'
  AND (t25.value ILIKE '%a3%' OR t29.code ILIKE '%a3%' OR t30.code ILIKE '%a3%');

Juan Fuentes <juanmarianofuentes@gmail.com> writes:
> As you could see the query includes castings, we noticed testing with Postgres 12 that the castings of the CASE THEN
statement(commented out below) where failing in some cases, of course if you do the INNER JOIN and CASE WHEN first our
expectationis that the value can be casted. 

You're unlikely to get any useful comments on this if you don't provide
a self-contained example.  The query by itself lacks too many details.
As an example, one way "t7.value::numeric = 1" could fail despite being
inside a CASE is if t7 is a view whose "value" column is actually a
constant.  Flattening of the view would replace "t7.value" with that
constant, and then constant-folding would cause the failure, and neither
of those things are prevented by a CASE.  I kind of doubt that that's
the specific issue here, but I'm not going to guess at what is in your
thirty-some input tables.

            regards, tom lane

Thanks Tom!

I was just hopping somebody could point out if this kind of issue has been reported before spending 2 days fabricating
asimpler self contained example. 

Best,
Juan

> On 4 Jun 2020, at 16:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Juan Fuentes <juanmarianofuentes@gmail.com> writes:
>> As you could see the query includes castings, we noticed testing with Postgres 12 that the castings of the CASE THEN
statement(commented out below) where failing in some cases, of course if you do the INNER JOIN and CASE WHEN first our
expectationis that the value can be casted. 
>
> You're unlikely to get any useful comments on this if you don't provide
> a self-contained example.  The query by itself lacks too many details.
> As an example, one way "t7.value::numeric = 1" could fail despite being
> inside a CASE is if t7 is a view whose "value" column is actually a
> constant.  Flattening of the view would replace "t7.value" with that
> constant, and then constant-folding would cause the failure, and neither
> of those things are prevented by a CASE.  I kind of doubt that that's
> the specific issue here, but I'm not going to guess at what is in your
> thirty-some input tables.
>
>             regards, tom lane