BUG #4939: error query result

"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