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 1kXZYY-0003nb-Q2 for pgsql-bugs@arkaria.postgresql.org; Wed, 28 Oct 2020 00:43:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kXZYX-00058m-Pr for pgsql-bugs@arkaria.postgresql.org; Wed, 28 Oct 2020 00:43:13 +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 1kXZYX-00058d-JX for pgsql-bugs@lists.postgresql.org; Wed, 28 Oct 2020 00:43:13 +0000 Received: from n3.nabble.com ([162.255.23.22]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kXZYU-0006BL-CA for pgsql-bugs@postgresql.org; Wed, 28 Oct 2020 00:43:12 +0000 Received: from n3.nabble.com (localhost [127.0.0.1]) by n3.nabble.com (Postfix) with ESMTP id DAB7C22365153 for ; Tue, 27 Oct 2020 17:43:08 -0700 (MST) Date: Tue, 27 Oct 2020 17:43:08 -0700 (MST) From: greigwise To: pgsql-bugs@postgresql.org Message-ID: <1603845788861-0.post@n3.nabble.com> Subject: Non-Materialized CTE bug? MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk create table types (type varchar); CREATE TABLE insert into types values ('a'); with test (i, j) as (values ( 1, '{"key": "a", "value": "true"}'::json), (2, '{"key": "b", "value": "xxx"}'::json)), test2 (i, j) as (select i, (j->>'value')::boolean from test a join types b on b.type = a.j->>'key') select * from test2 where j = true; ERROR: invalid input syntax for type boolean: "xxx" Why would this result in an error here? It seems like the second row from the "test" CTE should be excluded since the key value "b" will not join with anything in the "types" table. If I materialize the "test2" CTE, then it works as I'd expect without error. Is this a bug? It seems like the optimizer should be smart enough to know that it has to materialize that CTE. This is on version 12.4. Thanks for the help. Greig Wise -- Sent from: https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html