Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pNbVb-0000X3-0U for pgsql-general@arkaria.postgresql.org; Thu, 02 Feb 2023 15:28:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pNbVZ-00055U-SL for pgsql-general@arkaria.postgresql.org; Thu, 02 Feb 2023 15:28:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pNbVZ-00054s-Gq for pgsql-general@lists.postgresql.org; Thu, 02 Feb 2023 15:28:17 +0000 Received: from mout-u-204.mailbox.org ([80.241.59.204]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pNbVX-0000Am-4K for pgsql-general@lists.postgresql.org; Thu, 02 Feb 2023 15:28:16 +0000 Received: from smtp1.mailbox.org (smtp1.mailbox.org [IPv6:2001:67c:2050:b231:465::1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4P72jk3zrpz9sWx; Thu, 2 Feb 2023 16:28:10 +0100 (CET) Date: Thu, 2 Feb 2023 16:28:09 +0100 (CET) From: Erik Wienhold To: Zahir Lalani , "pgsql-general@lists.postgresql.org" Message-ID: <956888536.169901.1675351689652@office.mailbox.org> In-Reply-To: References: Subject: Re: From Clause Conditional MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4P72jk3zrpz9sWx List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 02/02/2023 13:54 CET Zahir Lalani wrote: > > Confidential > > Hello All > > We are testing a upgrade from pg11 to pg14 and have some issues to overco= me. > One of these is that we have upgraded pgsodium to the latest and there is= a > functional change =E2=80=93 this question is not about sodium BTW. > > So here is a sample bit of code that I will use to explain the issue =E2= =80=93 this > would usually be params passed in, but I have hard coded one particular c= ase > that does not need the decryption. The code below would return decrypted = data > if the key is supplied (non 0) otherwise return null. This code used to w= ork > because the secret box call would allow null params. It no longer does. W= hen > the key is 0, the data passed in would be null. > > LEFT JOIN lateral ( > SELECT > CASE > WHEN (0 > 0) THEN > convert_from(crypto_secretbox_open, 'utf8')::JSON > ELSE > NULL > END AS edata > FROM > crypto_secretbox_open(coalesce(null, '')::bytea, coalesce(null, '')::byte= a,0) > where (0>0) > ) enc ON true > > The issue is that, even when the key is 0, the select is still run (its p= art > of a lateral join) and what we need to achieve is to effectively have a > conditional where we only run the select if the key > 0 otherwise we retu= rn > null =E2=80=93 I have a brain freeze on this! I am sure there is an easy = solution, > but right now I can=E2=80=99t see it. Looks similar to a post from a few days ago: https://www.postgresql.org/message-id/flat/CAALojA-nHoxDr7B2k0e1-EtGMPsGPZi= CVeS_ds0aHG0SEOrPxg%40mail.gmail.com I don't know pgsodium but the query optimizer will evalute crypto_secretbox= _open if the function is immutable and is called with constant arguments. How is the key and the data passed to this query? Is it a prepared stateme= nt or is the query generated on the fly for specific key and data that is already known? In the latter case you can handle the case expression with two diff= erent queries (one with crypto_secret_box and one without) depending on whether a= key exists or not. -- Erik PS: Please don't hijack threads on this mailing list.