Thread: [BUGS] BUG #14532: implict type case invalid in gin?

The following bug has been logged on the website:

Bug reference:      14532
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 9.6.1
Operating system:   CentOS 6.x x64
Description:

```
postgres=# create table tbl(id int, t timestamp);
CREATE TABLE
postgres=# insert into tbl select 1,now() from generate_series(1,10000);
INSERT 0 10000
postgres=# create index idx_tbl_1 on tbl using gin (id,t);
CREATE INDEX
postgres=# explain select * from tbl where t>current_date;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..230.00 rows=10000 width=12)
   Filter: (t > ('now'::cstring)::date)
(2 rows)

postgres=# set enable_seqscan=off;
SET
postgres=# explain select * from tbl where t>current_date;
                                 QUERY PLAN

----------------------------------------------------------------------------
 Seq Scan on tbl  (cost=10000000000.00..10000000230.00 rows=10000
width=12)
   Filter: (t > ('now'::cstring)::date)
(2 rows)

postgres=# create index idx_tbl_2 on tbl using btree(t);
CREATE INDEX
postgres=# explain select * from tbl where t>current_date;
                                 QUERY PLAN

----------------------------------------------------------------------------
 Index Scan using idx_tbl_2 on tbl  (cost=0.29..269.59 rows=10000
width=12)
   Index Cond: (t > ('now'::cstring)::date)
(2 rows)

postgres=# drop index idx_tbl_2;
DROP INDEX
postgres=# set enable_seqscan=on;
SET
postgres=# explain select * from tbl where t>(current_date)::timestamp;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..255.00 rows=10000 width=12)
   Filter: (t > (('now'::cstring)::date)::timestamp without time zone)
(2 rows)

postgres=# set enable_seqscan=off;
SET
postgres=# explain select * from tbl where t>(current_date)::timestamp;
                                   QUERY PLAN
    
---------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl  (cost=93.11..348.11 rows=10000 width=12)
   Recheck Cond: (t > (('now'::cstring)::date)::timestamp without time
zone)
   ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..90.61 rows=10000
width=0)
         Index Cond: (t > (('now'::cstring)::date)::timestamp without time
zone)
(4 rows)
```


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

digoal@126.com writes:
> postgres=# create table tbl(id int, t timestamp);
> CREATE TABLE
> postgres=# insert into tbl select 1,now() from generate_series(1,10000);
> INSERT 0 10000
> postgres=# create index idx_tbl_1 on tbl using gin (id,t);
> CREATE INDEX

Apparently you're using contrib/btree_gin, because in the core system
that would just fail.  btree_gin lacks any support for cross-type
operators, so it can't index "timestamp > date" comparisons.

            regards, tom lane


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