Обсуждение: BUG #13449: Auto type cast (int -> numeric) non-reasonable, will case performance problem

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.

On 06/16/2015 10:17 PM, digoal@126.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