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

Поиск
Список
Период
Сортировка
От Juan Fuentes
Тема Possible bug on Postgres 12 (CASE THEN evaluated prematurely) -Change of behaviour compared to 11, 10, 9
Дата
Msg-id C0FDEC5E-0E01-4FAB-A7A6-3FAC1F94B51E@gmail.com
обсуждение исходный текст
Ответы Re: Possible bug on Postgres 12 (CASE THEN evaluated prematurely) - Change of behaviour compared to 11, 10, 9  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
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%');


В списке pgsql-hackers по дате отправления:

Предыдущее
От: Kyotaro Horiguchi
Дата:
Сообщение: Re: Read access for pg_monitor to pg_replication_origin_status view
Следующее
От: Atsushi Torikoshi
Дата:
Сообщение: Re: Is it useful to record whether plans are generic or custom?