From: Tom Lane <tgl@sss.pgh.pa.us>
To: 1165125080@qq.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17364: Errors caused by views
In-reply-to: <17364-3072d21ebb3da224@postgresql.org>
References: <17364-3072d21ebb3da224@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Wed, 12 Jan 2022 15:53:31 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3011205.1642010290.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 12 Jan 2022 12:58:10 -0500
Message-ID: <3011206.1642010290@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3011206.1642010290%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> I run the following SQL statements on postgres13.2. They are simple, but=
 an
> error occurs.

> create table test1 (id int, c_data varchar);
> insert into test1 values (1, '201201');
> insert into test1 values (2, 'error');

> create table test2(id int, status varchar);
> insert into test2 values (1, 'on');
> insert into test2 values (2, 'off');

> create view test_view as
> SELECT test1.c_data
> from test1 LEFT JOIN test2
> ON (test2.id =3D test1.id)
> WHERE (test2.status)::text =3D 'on'::text;

> select * from test_view;
> c_data
> ----------
> 20201101
> (1 row)

> select * from
> (select to_date(c_data, 'YYYYMMDD') as date
> from test_view) t1
> where t1.date >=3D '2020 - 10 - 01':: TIMESTAMP;
> ERROR: invalid value "erro" for "YYYY"
> DETAIL: Value must be an integer.

The reason this is failing is that the outer WHERE clause is being
pushed down to the scan of test1, so it's applied before the join
occurs, and then to_date spits up on the "error" value.  You can see
that in EXPLAIN:

=3D# explain select * from
(select to_date(c_data, 'YYYYMMDD') as date
from test_view) t1         =

where t1.date >=3D '2020-10-01':: TIMESTAMP;
                                                    QUERY PLAN            =
                                         =

--------------------------------------------------------------------------=
-----------------------------------------
 Hash Join  (cost=3D25.95..56.75 rows=3D13 width=3D4)
   Hash Cond: (test1.id =3D test2.id)
   ->  Seq Scan on test1  (cost=3D0.00..29.05 rows=3D423 width=3D36)
         Filter: (to_date((c_data)::text, 'YYYYMMDD'::text) >=3D '2020-10-=
01 00:00:00'::timestamp without time zone)
   ->  Hash  (cost=3D25.88..25.88 rows=3D6 width=3D4)
         ->  Seq Scan on test2  (cost=3D0.00..25.88 rows=3D6 width=3D4)
               Filter: ((status)::text =3D 'on'::text)
(7 rows)

That's not something we're going to consider a bug, because if we
prevented the push-down from happening it would be quite disastrous
for the performance of many queries.

There are a couple of things you can do to control it yourself,
though:

* Insert "OFFSET 0" as an optimization fence:

=3D# explain select * from
(select to_date(c_data, 'YYYYMMDD') as date
from test_view offset 0) t1
where t1.date >=3D '2020-10-01':: TIMESTAMP;
                                QUERY PLAN                                =
 =

--------------------------------------------------------------------------=
-
 Subquery Scan on t1  (cost=3D25.95..54.36 rows=3D13 width=3D4)
   Filter: (t1.date >=3D '2020-10-01 00:00:00'::timestamp without time zon=
e)
   ->  Hash Join  (cost=3D25.95..53.89 rows=3D38 width=3D4)
         Hash Cond: (test1.id =3D test2.id)
         ->  Seq Scan on test1  (cost=3D0.00..22.70 rows=3D1270 width=3D36=
)
         ->  Hash  (cost=3D25.88..25.88 rows=3D6 width=3D4)
               ->  Seq Scan on test2  (cost=3D0.00..25.88 rows=3D6 width=3D=
4)
                     Filter: ((status)::text =3D 'on'::text)
(8 rows)

OFFSET 0 in the view definition would serve too, I think,
but I didn't try it.

* Wrap the possibly-error-causing function in a VOLATILE wrapper function:

=3D# create or replace function mytodate(varchar) returns date as $$
select to_date($1, 'YYYYMMDD')$$ language sql volatile;
CREATE FUNCTION
=3D# explain verbose select * from
(select mytodate(c_data) as date
from test_view) t1
where t1.date >=3D '2020-10-01':: TIMESTAMP;
                                  QUERY PLAN                              =
     =

--------------------------------------------------------------------------=
-----
 Subquery Scan on t1  (cost=3D25.95..54.36 rows=3D13 width=3D4)
   Output: t1.date
   Filter: (t1.date >=3D '2020-10-01 00:00:00'::timestamp without time zon=
e)
   ->  Hash Join  (cost=3D25.95..53.89 rows=3D38 width=3D4)
         Output: to_date((test1.c_data)::text, 'YYYYMMDD'::text)
         Hash Cond: (test1.id =3D test2.id)
         ->  Seq Scan on public.test1  (cost=3D0.00..22.70 rows=3D1270 wid=
th=3D36)
               Output: test1.id, test1.c_data
         ->  Hash  (cost=3D25.88..25.88 rows=3D6 width=3D4)
               Output: test2.id
               ->  Seq Scan on public.test2  (cost=3D0.00..25.88 rows=3D6 =
width=3D4)
                     Output: test2.id
                     Filter: ((test2.status)::text =3D 'on'::text)
(13 rows)

The presence of a volatile function in the subquery's output list
has about the same effect as OFFSET 0, preventing the WHERE
condition from being pushed down past it.

In both cases you're significantly de-optimizing your query, so
these answers might not be very satisfactory if you have a large
amount of data to work with.  I'd really suggest reconsidering
your data design, or making the query a bit less fragile, perhaps
with a sub-select expression along the lines of

case when c_data ~ '^\d+$' then to_date(c_data, 'YYYYMMDD') else null end

			regards, tom lane