Обсуждение: BUG #4939: error query result
The following bug has been logged online: Bug reference: 4939 Logged by: limaozeng Email address: limaozeng@163.com PostgreSQL version: 8.4.0 Operating system: linux-32 bit Description: error query result Details: create table t(str char(200)); insert into t values 'mzli'; insert into t values 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvw xyz'; select user; current_user -------------- mzli (1 row) select * from t where str in (user, 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijk'); str ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- -------------------------------------------------- mzli abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx yz (2 rows) only 'mzli' ought to be appeared in the result list.
"limaozeng" <limaozeng@163.com> wrote: > select * from t where str in (user, 'abc...ijk'); > str > ----------- > mzli > abc...xyz > (2 rows) > > only 'mzli' ought to be appeared in the result list. Your query is interpreted as select * from t::name where str in (user::name, 'abc...ijk'::name); Strings are truncated in 63 bytes by casting to "name" type, and the first 63 bytes of unexpected row matched the head of values in the IN clause. It should work if you cast "user" to text type. =# select * from t where str in (user::text, 'abc...ijk'); The result might be a designed behavior, but is very surprising. What should we care for it? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes: > The result might be a designed behavior, but is very surprising. > What should we care for it? I think the only thing we could do about it is downgrade the implicit casts to "name", which seems like a cure worse than the disease --- it'd interfere with searches in the system catalogs. The OP could avoid the problem by declaring "str" as text rather than char(n), which on the whole seems like the preferable solution. char(200) has got no redeeming social value whatsoever... regards, tom lane
On Fri, Jul 24, 2009 at 6:02 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > I think the only thing we could do about it is downgrade the implicit > casts to "name", which seems like a cure worse than the disease --- > it'd interfere with searches in the system catalogs. We could try to avoid user-visible functions like current_user returning "name" data types. If no user-visible functions returned "name" then this wouldn't happen unless people were actually querying catalog tables. that would mean changing the return type of these functions: getpgusername current_user session_user current_database current_schema getdatabaseencoding pg_client_encoding pg_encoding_to_char pg_get_userbyid The only downside I see is that it means a strdup when these functions are called which might be annoying. However now that I look a number of them are actually already calling namein anyways. Are they used internally in places that expect a name? -- greg http://mit.edu/~gsstark/resume.pdf
Greg Stark <gsstark@mit.edu> writes: > On Fri, Jul 24, 2009 at 6:02 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >> I think the only thing we could do about it is downgrade the implicit >> casts to "name", which seems like a cure worse than the disease --- >> it'd interfere with searches in the system catalogs. > We could try to avoid user-visible functions like current_user > returning "name" data types. Only if you want to break system catalog searches. Example: regression=# explain select * from pg_class where relname = 'foo'; QUERY PLAN -------------------------------------------------------------------------------- ------------- Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.00..8.27 rows= 1 width=185) Index Cond: (relname = 'foo'::name) (2 rows) regression=# explain select * from pg_class where relname = 'foo'::text; QUERY PLAN ----------------------------------------------------------- Seq Scan on pg_class (cost=0.00..30.87 rows=3 width=185) Filter: ((relname)::text = 'foo'::text) (2 rows) If we do what you suggest, and don't want to take the above hit, we would have to make name be a preferred type, which would *definitely* create a bunch of problems. regards, tom lane