Thread: 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