Re: [HACKERS] smallint out of range EXECUTEing prepared statement
> "Justin" == Justin Pryzby 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> PREPARE Justin> 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) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] smallint out of range EXECUTEing prepared statement
On Wed, Jan 18, 2017 at 3:15 PM, Justin Pryzby wrote: > Is this expected behavior ? > > ts=# SELECT * FROM t WHERE site_id=32768 LIMIT 1; > (0 rows) > > ts=# PREPARE x AS SELECT * FROM t WHERE site_id=$1 LIMIT 1; > PREPARE > ts=# EXECUTE x(32768); > ERROR: smallint out of range > Probably. If you show the definition of "t", or at least "t.site_id", that can be confirmed. And, IMO, this question is more in line with the purpose of the -general list. David J.
[HACKERS] smallint out of range EXECUTEing prepared statement
Is this expected behavior ? ts=# SELECT * FROM t WHERE site_id=32768 LIMIT 1; (0 rows) ts=# PREPARE x AS SELECT * FROM t WHERE site_id=$1 LIMIT 1; PREPARE ts=# EXECUTE x(32768); ERROR: smallint out of range ts=# PREPARE y AS SELECT * FROM t WHERE site_id::int=$1 LIMIT 1; PREPARE ts=# EXECUTE y(32768); (0 rows) Note, we also sometimes get small/int out of range when SELECTing from a view, and we end up as a workaround putting a ::big/int cast into the view or multiplying by 1. Thanks, Justin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers