Re: Odd Shortcut behaviour in PG14

On 11/23/23 08:55, Zahir Lalani wrote:
> Hello all
>
> Got a really weird problem with shortcut processing on one server.
>
> We have just upgraded to PG14 from PG11. The following code works as
> expected on our primary Dev server, and we recently upgraded our QA
> server to the same level. However in this case the shortcut processing
> seems broken.

To be clear both the Dev and QA servers are at 14.x now?

If so are they at same minor version?

Same OS and version?

Using same data set?

Also from here:

https://www.postgresql.org/docs/14/sql-expressions.html#SYNTAX-EXPRESS-EVAL

"
4.2.14. Expression Evaluation Rules

The order of evaluation of subexpressions is not defined. In particular,
the inputs of an operator or function are not necessarily evaluated
left-to-right or in any other fixed order.

Furthermore, if the result of an expression can be determined by
evaluating only some parts of it, then other subexpressions might not be
evaluated at all. For instance, if one wrote:

SELECT true OR somefunc();

then somefunc() would (probably) not be called at all. The same would be
the case if one wrote:

SELECT somefunc() OR true;

Note that this is not the same as the left-to-right “short-circuiting”
of Boolean operators that is found in some programming languages.
"

This also held in version 11.

>
> Here is the code in question:
>
> 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 true and (ekey > 0)*
>
> This code has worked up till now and works on the dev server. The
> expectation being that if ekey=0 the lateral join will be ignored.
> However on the new QA server this is hit and miss. It fails many times
> as it seems to ignore the shortcut even though ekey is 0. We can make it
> work by doing this:
>
> 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) and true*
>
> This should theoretically be no different – but it solves the issue
> 100%. Any guidance on why this would be the case?
>
> *Zahir Lalani**
> *Director of Enterprise Systems
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com