Re: BUG #13449: Auto type cast (int -> numeric) non-reasonable, will case performance problem

On 06/16/2015 10:17 PM, digoal(at)126(dot)com wrote:
> 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.

The current behaviour seems perfectly fine to me. If you want to force a
specific datatype, you're better off passing the parameter out-of-line,
and specify the datatype explicitly. I don't see any security issue here.

- Heikki