From: greigwise <greigwise@comcast.net>
To: pgsql-bugs@postgresql.org
Subject: Non-Materialized CTE bug?
Date: Tue, 27 Oct 2020 17:43:08 -0700 (MST)
Message-ID: <1603845788861-0.post@n3.nabble.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: greigwise@comcast.net
Subject: Re: Non-Materialized CTE bug?
In-Reply-To: <1603845788861-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox