Thread: [BUGS] BUG #14504: Wrong index using via view for converted timestamp bytime zone
[BUGS] BUG #14504: Wrong index using via view for converted timestamp bytime zone
From
dsuchka@gmail.com
Date:
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