[BUGS] BUG #14504: Wrong index using via view for converted timestamp bytime zone - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14504
Logged by:          Evgeniy Kozlov
Email address:      dsuchka@gmail.com
PostgreSQL version: 9.5.2
Operating system:   GNU\Linux (Gentoo 4.9.3 p1.5, pie-0.6.4)
Description:

I've been a little bit surprised by such behaviour when sometimes query
returns expected rows and sometimes does not by the same condition depending
on index usage (as shown explain analyze), and no rows removed from the
table.

How to reproduce it:

-- create table with time_stamp column & create view with time_stamp +
time_stamp at UTC
dzheika=# CREATE TABLE test_tz_bug (id integer PRIMARY KEY, time_stamp
timestamptz NOT NULL DEFAULT clock_timestamp());
CREATE TABLE
dzheika=# CREATE VIEW test_tz_bug_view AS SELECT x.id, x.time_stamp,
x.time_stamp AT TIME ZONE 'UTC' AS time_stamp_at_utc FROM test_tz_bug AS
x;
CREATE VIEW

-- fill data
dzheika=# WITH RECURSIVE src(id) AS (SELECT 1 UNION SELECT s.id+1 FROM src
AS s WHERE s.id < 10000) INSERT INTO test_tz_bug(id) SELECT x.id FROM src AS
x;INSERT 0 10000


-- now we can select some rows from view in 2 ways by time value: using
time_stamp & time_stamp_at_utc:
dzheika=# SELECT * FROM test_tz_bug_view WHERE id = 5555;
  id  |          time_stamp           |     time_stamp_at_utc      
------+-------------------------------+----------------------------
 5555 | 2017-01-18 17:25:41.459922+03 | 2017-01-18 14:25:41.459922
(1 строка)

dzheika=# SELECT * FROM test_tz_bug_view WHERE time_stamp = '2017-01-18
17:25:41.459922+03';
  id  |          time_stamp           |     time_stamp_at_utc      
------+-------------------------------+----------------------------
 5555 | 2017-01-18 17:25:41.459922+03 | 2017-01-18 14:25:41.459922
(1 строка)

dzheika=# SELECT * FROM test_tz_bug_view WHERE time_stamp_at_utc =
'2017-01-18 14:25:41.459922';
  id  |          time_stamp           |     time_stamp_at_utc      
------+-------------------------------+----------------------------
 5555 | 2017-01-18 17:25:41.459922+03 | 2017-01-18 14:25:41.459922
(1 строка)

--
-- But if we have an index on the original table by the time_stamp column,
condition with converted timestamp will work in wrong way:
--
dzheika=# CREATE INDEX test_tz_bug_time_stamp_idx ON test_tz_bug USING btree
(time_stamp);
CREATE INDEX
dzheika=# ANALYZE test_tz_bug;
ANALYZE
dzheika=# SELECT * FROM test_tz_bug_view WHERE time_stamp_at_utc =
'2017-01-18 14:25:41.459922';
 id | time_stamp | time_stamp_at_utc 
----+------------+-------------------
(0 строк)

dzheika=# EXPLAIN ANALYZE SELECT * FROM test_tz_bug_view WHERE
time_stamp_at_utc = '2017-01-18 14:25:41.459922';
                                                                QUERY PLAN
                                                              

-------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_tz_bug_time_stamp_idx on test_tz_bug x
(cost=0.29..8.30 rows=1 width=12) (actual time=0.016..0.016 rows=0
loops=1)
   Index Cond: ((time_stamp)::timestamp without time zone = '2017-01-18
14:25:41.459922'::timestamp without time zone)
 Planning time: 0.174 ms
 Execution time: 0.043 ms
(4 строки)


===========================================================================

If another index is used (or no indices are used), then select works
correctly:

dzheika=# SELECT * FROM test_tz_bug_view WHERE (time_stamp_at_utc +
'1s'::interval) = ('2017-01-18 14:25:41.459922'::timestamp +
'1s'::interval);
  id  |          time_stamp           |     time_stamp_at_utc      
------+-------------------------------+----------------------------
 5555 | 2017-01-18 17:25:41.459922+03 | 2017-01-18 14:25:41.459922
(1 строка)

dzheika=# EXPLAIN ANALYZE SELECT * FROM test_tz_bug_view WHERE
(time_stamp_at_utc + '1s'::interval) = ('2017-01-18
14:25:41.459922'::timestamp + '1s'::interval);
                                                                 QUERY PLAN
                                                               

--------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on test_tz_bug x  (cost=0.00..205.00 rows=50 width=12) (actual
time=2.163..3.593 rows=1 loops=1)
   Filter: (((time_stamp)::timestamp without time zone +
'00:00:01'::interval) = '2017-01-18 14:25:42.459922'::timestamp without time
zone)
   Rows Removed by Filter: 9999
 Planning time: 0.245 ms
 Execution time: 3.629 ms
(5 строк)


===========================================================================
PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc
(Gentoo 4.9.3 p1.5, pie-0.6.4) 4.9.3, 64-bit
PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10)
4.9.2, 64-bit


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs