Обсуждение: Re: [HACKERS] smallint out of range EXECUTEing prepared statement

>>>>> "Justin" == Justin Pryzby <pryzby@telsasoft.com> writes:
Justin> Is this expected behavior ?
Justin> ts=# SELECT * FROM t WHERE site_id=32768 LIMIT 1;Justin> (0 rows)
Justin> ts=# PREPARE x AS SELECT * FROM t WHERE site_id=$1 LIMIT 1;Justin> PREPAREJustin> ts=# EXECUTE x(32768);Justin>
ERROR: smallint out of range
 

If column "site_id" is of type smallint, then parse analysis will deduce
a type of smallint for $1, which is otherwise of unknown type. So the
prepared statement "x" then has one parameter of type smallint.

Passing 32768 for that parameter therefore fails with the expected error.
Justin> ts=# PREPARE y AS SELECT * FROM t WHERE site_id::int=$1 LIMIT 1;Justin> PREPARE

Now $1 is of type integer, not smallint, because parse analysis sees
(integer = unknown) and deduces the type from that.

(a better way would be WHERE site_id = $1::integer, which would allow
index usage on site_id, unlike your example)

-- 
Andrew (irc:RhodiumToad)