RE: Odd Shortcut behaviour in PG14

> -----Original Message-----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Sent: Friday, November 24, 2023 3:35 PM
> To: Zahir Lalani <ZahirLalani@oliver.agency>
> Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-
> generallists.postgresql.org <pgsql-general@lists.postgresql.org>
> Subject: Re: Odd Shortcut behaviour in PG14


> You *still* haven't defined what you mean by "fails".  We can't help you
> effectively with such tiny dribs and drabs of information.
> At the very least I'd like to see the whole query, because the fragment you've
> shown us does not reveal what ekey is or why you think the system should
> believe that it is or is not zero.
> But it's also unclear why that should matter.
>
> Having said that ... if the statement is being executed with a cached plan (via a
> named statement, or PREPARE, or inside plpgsql) then maybe the problem
> occurs if the plan switches from custom to generic?
> If so, messing with the plan_cache_mode setting might provide a workaround.
>
>                         regards, tom lane

Sorry Tom - let me try and clarify:

(ekey is a variable passed into the function)

    RETURN QUERY
        SELECT
            CASE WHEN (ekey = 0) THEN person.first_name ELSE (enc.edata->>'firstname')::text END AS first_name,
            CASE WHEN (ekey = 0) THEN person.last_name ELSE (enc.edata->>'lastname')::text END AS last_name,
            CASE WHEN (ekey = 0) THEN person.email ELSE (enc.edata->>'emailaddress')::text END AS email,
            CASE WHEN (ekey = 0) THEN person.first_name || ' ' || person.last_name ELSE (enc.edata->>'firstname')::text
||' ' || (enc.edata->>'lastname')::text END AS full_name 

        FROM "public".person AS person

        -- join to decrypt person data
        LEFT OUTER JOIN "public".secure AS sc ON
            CASE WHEN (ekey > 0) THEN sc.fk_org_id = org_id and sc.fk_entity_id = 6 and sc.fk_entity_obj_id = person.id
ELSEfalse END 
        LEFT JOIN lateral (
            SELECT
                CASE WHEN (ekey > 0) THEN convert_from(crypto_secretbox_open, 'utf8')::JSON ELSE NULL END AS edata
            FROM crypto_secretbox_open(
                sc.data,
                sc.nonce,
                boxkey)
        ) enc ON (ekey > 0)
        WHERE person.id = person_id

-----------------------
We have certain clients with encrypted data and this query needs to apply the decryption only if the record is deemed
tobe encrypted - this is determined by the variable ekey being > 0. The "failure" is the invocation of the lateral join
whenekey is 0 - i.e. its not encrypted data. The crypto function will fail if sc.data is null - as will be the case for
anon-encrypted record. 

Our assumption - probably incorrectly it seems, is that if the expression (ekey > 0) is false, PG will not invoke the
lateraljoin and hence not execute crypto. This has worked in PG11 and worked in 14.9. What is very odd is that in 14.10
thisstill seems to work in many cases, but not all. This particular function works in psql, and sometimes in the
applicationbut not always. The parameters are identical for when it works as expected and for when it does not - i.e,
whenit invokes the lateral even though ekey = 0 

To prove that the issue lies in this area alone, we have split the above function and run specific code for the two
casesof ekey which guarantees that it will never do the unexpected - this fixes the issue, but I would still like to
knowif our fundamental assumption about when the lateral runs was misguided 

Hope that gives more context

Z