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 1jN0xC-0003gN-9V for pgsql-bugs@arkaria.postgresql.org; Fri, 10 Apr 2020 21:12:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jN0xB-00081M-1B for pgsql-bugs@arkaria.postgresql.org; Fri, 10 Apr 2020 21:12:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jN0xA-00081F-RL for pgsql-bugs@lists.postgresql.org; Fri, 10 Apr 2020 21:12:44 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jN0x7-0005ex-He for pgsql-bugs@lists.postgresql.org; Fri, 10 Apr 2020 21:12:43 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 03ALCcdA001820; Fri, 10 Apr 2020 17:12:38 -0400 From: Tom Lane To: valefbonetwo@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16356: Can't perform json operations on casted text in where clause when a join is used. WHERE is too early In-reply-to: <16356-871a3cce0a87d34e@postgresql.org> References: <16356-871a3cce0a87d34e@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Fri, 10 Apr 2020 20:33:41 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1818.1586553158.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 10 Apr 2020 17:12:38 -0400 Message-ID: <1819.1586553158@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > It's somehow resurrected the value of 'a' that was well-eliminated prior= to > this where clause. So what gives? Why does the join cause it to apply th= e > last where clause (which should happen logically last) too early? The documentation explicitly disclaims any specific evaluation order for WHERE clauses, cf https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRES= S-EVAL If you really need to, you can force the matter by putting some type of optimization fence into the sub-select ("OFFSET 0" is the traditional way, or you can use WITH ... AS MATERIALIZED in v12 and up). This is typically disastrous for performance, of course. Not being able to filter rows before joining would make the join much slower. I'll just note that the sort of EAV schema that you have here is widely agreed to be an anti-pattern in database design. regards, tom lane