pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16712: Wrong Sub-Query Record Filtering
2+ messages / 2 participants
[nested] [flat]

* BUG #16712: Wrong Sub-Query Record Filtering
@ 2020-11-12 14:26  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2020-11-12 14:26 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: bugatti2.5sp@gmail.com

The following bug has been logged on the website:

Bug reference:      16712
Logged by:          SP
Email address:      bugatti2.5sp@gmail.com
PostgreSQL version: 13.0
Operating system:   seems any (tested on Ubuntu 18.04 and macOS 10.15)
Description:        

An unexpected result returns when running this query:

WITH data_cte(str) AS (
    SELECT *
    FROM (VALUES ('a')) data_tmp(str)
    WHERE FALSE
)
SELECT str::INTEGER
FROM data_cte;

While empty result is expected, we get this:

ERROR:  invalid input syntax for type integer: "a"

Because of these type casting error, it seems that the outer query has
access to the data_cte record!

The strange thing is that if records of data_tmp (or data_cte, it doesn't
matter) be more than one, result will be true. So, for this query:

WITH data_cte(str) AS (
    SELECT *
    FROM (VALUES ('1a'), ('2b')) data_tmp(str)
    WHERE FALSE
)
SELECT str::INTEGER
FROM data_cte;

we get this:

 substring 
-----------
(0 rows)



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #16712: Wrong Sub-Query Record Filtering
@ 2020-11-12 14:53  David G. Johnston <david.g.johnston@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: David G. Johnston @ 2020-11-12 14:53 UTC (permalink / raw)
  To: bugatti2.5sp@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>

On Thu, Nov 12, 2020 at 7:34 AM PG Bug reporting form <
noreply@postgresql.org> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      16712
> Logged by:          SP
> Email address:      bugatti2.5sp@gmail.com
> PostgreSQL version: 13.0
> Operating system:   seems any (tested on Ubuntu 18.04 and macOS 10.15)
> Description:
>
> An unexpected result returns when running this query:
>
> WITH data_cte(str) AS (
>     SELECT *
>     FROM (VALUES ('a')) data_tmp(str)
>     WHERE FALSE
> )
> SELECT str::INTEGER
> FROM data_cte;
>
> While empty result is expected, we get this:
>
> ERROR:  invalid input syntax for type integer: "a"
>
> Because of these type casting error, it seems that the outer query has
> access to the data_cte record!
>
>
It does - right there in the "FROM data_cte" clause.  The planner has
chosen to optimize this query by taking the two parts and making them one,
reducing a level of execution indirection.  If you don't wish for this to
happen you need to add MATERIALIZED to the WITH clause element.  This is
one of the more impactful changes with v13, in prior versions materialized
was the default (and optimization was not possible).

David J.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-11-12 14:53 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-12 14:26 BUG #16712: Wrong Sub-Query Record Filtering PG Bug reporting form <noreply@postgresql.org>
2020-11-12 14:53 ` David G. Johnston <david.g.johnston@gmail.com>

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