The following bug has been logged on the website:
Bug reference: 13449
Logged by: digoal
Email address: digoal@126.com
PostgreSQL version: 9.4.4
Operating system: CentOS 6.x x64
Description:
When i use an big digital, it auto convert to numeric. and there is no
int&numeric operator, so left opr auto convert to numeric also.
for exp:
postgres=# create table t3(id int);
CREATE TABLE
postgres=# insert into t3 select generate_series(1,10000000);
INSERT 0 10000000
postgres=# create index idx_t3_id on t3(id);
CREATE INDEX
postgres=# explain analyze select * from t3 where
id>999999999999999999999999999999999;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_t3_id on t3 (cost=0.43..238213.43 rows=3333333
width=4) (actual time=4052.914..4052.914 rows=0 loops=1)
Filter: ((id)::numeric > '999999999999999999999999999999999'::numeric)
Rows Removed by Filter: 10000000
Heap Fetches: 10000000
Planning time: 0.283 ms
Execution time: 4052.944 ms
(6 rows)
postgres=# explain analyze select * from t3 where
id=999999999999999999999999999999999;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_t3_id on t3 (cost=0.43..238213.43 rows=50000
width=4) (actual time=3907.391..3907.391 rows=0 loops=1)
Filter: ((id)::numeric = '999999999999999999999999999999999'::numeric)
Rows Removed by Filter: 10000000
Heap Fetches: 10000000
Planning time: 0.103 ms
Execution time: 3907.421 ms
(6 rows)
I think ,this case, PostgreSQL should convert
999999999999999999999999999999999 to the same type with column id's type
int. and raise error.
there is some problem, user can use this to SQL injection attack or other
things, Oops, Application has ability to filter the overflow digital, but i
think PostgreSQL also has responsibility to prevent overflow occure.