Обсуждение: BUG #3598: Strange behaviour of character columns in select with views

The following bug has been logged online:

Bug reference:      3598
Logged by:          Luiz K. Matsumura
Email address:      luiz@planit.com.br
PostgreSQL version: 8.2.4
Operating system:   Fedora Core 3
Description:        Strange behaviour of character columns in select with
views
Details:

Scenario:

CREATE TABLE table1
(
 id serial NOT NULL,
 col1 character varying(30),
 CONSTRAINT pk_table1 PRIMARY KEY (id)
);

CREATE TABLE table2
(
 fk_table1 integer,
 type1 character(3),
 id serial NOT NULL,
 CONSTRAINT pk_table2 PRIMARY KEY (id)
);

CREATE TABLE table3
(
 id serial NOT NULL,
 type2 integer,
 fk_table1 integer,
 CONSTRAINT pk_table3 PRIMARY KEY (id)
);

CREATE VIEW view1 AS
SELECT table1.id,
      table1.col1,
      table2.type1,
      NULL AS type2
FROM   table1
JOIN   table2 ON table2.fk_table1 = table1.id
UNION ALL
SELECT table1.id,
      table1.col1,
      NULL::character(3) AS type1,
      table3.type2
FROM   table1
JOIN   table3 ON table3.fk_table1 = table1.id;

When we do:

SELECT * from view1;
OR
SELECT id,col1,type1,type2 FROM view1;

column type1 return as bpchar


But if we do:
SELECT type1 FROM view1;
or
SELECT id,col1,type2,type1 FROM view1;

Now, type1 return as character(3) as expected.

Luiz K. Matsumura wrote:
> When we do:
>
> SELECT * from view1;
> OR
> SELECT id,col1,type1,type2 FROM view1;
>
> column type1 return as bpchar
>
>
> But if we do:
> SELECT type1 FROM view1;
> or
> SELECT id,col1,type2,type1 FROM view1;
>
> Now, type1 return as character(3) as expected.

I can't reproduce this. View1.type1 has has type char(3) as expected in
both cases, as witnessed by "CREATE VIEW f AS SELECT */type1 FROM
view1"; \d f". How did you determine the data types?

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> I can't reproduce this. View1.type1 has has type char(3) as expected in
> both cases, as witnessed by "CREATE VIEW f AS SELECT */type1 FROM
> view1"; \d f". How did you determine the data types?

I just did reproduce it: libpq's PQfmod() does report either the correct
typmod or -1, just as he says.  You can see the difference on the
backend side by looking at the top-level targetentries in EXPLAIN
VERBOSE, so it is a backend problem and not client-side.  My theory at
the moment is that there's something whacko about the planner's "use
physical tlist" optimization that's applied when it doesn't need to do
any projection (ie no computations or column rearrangements).  That code
works fine in simpler cases but there's something about this view that
confuses it.  Haven't dug into it in detail yet.

            regards, tom lane

Heikki Linnakangas wrote:
> Luiz K. Matsumura wrote:
>
>> When we do:
>>
>> SELECT * from view1;
>> OR
>> SELECT id,col1,type1,type2 FROM view1;
>>
>> column type1 return as bpchar
>>
>>
>> But if we do:
>> SELECT type1 FROM view1;
>> or
>> SELECT id,col1,type2,type1 FROM view1;
>>
>> Now, type1 return as character(3) as expected.
>>
>
> I can't reproduce this. View1.type1 has has type char(3) as expected in
> both cases, as witnessed by "CREATE VIEW f AS SELECT */type1 FROM
> view1"; \d f". How did you determine the data types?
>
>
Hi Heikki
I use pgAdmin3 query tool (the header of columns show the type of each
one), and my application in VFP that access postgres trough pgsql odbc
The same behavior in pgAdmin3 occur in my application.

--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.

"Luiz K. Matsumura" <luiz@planit.com.br> writes:
> Description:        Strange behaviour of character columns in select with
> views

I've applied a fix for this in CVS HEAD (8.3-to-be), but it seems
impractical to fix it in 8.2 (or 8.1 which also has the issue).
The problem is that when "null::char(3)" is simplified to a bare Const
expression node, the length knowledge is lost because there is no place
to store typmod in pre-8.3 Const nodes.  The peculiar-seeming dependence
on the form of the query is because the behavior depends on exactly
which version of the select list (before or after const simplification)
is looked at to generate the typmod value that ultimately gets sent to
the client.

While it might be possible to find a hack that improves the behavior for
specific cases in 8.2 (by changing around when we look at what), the
fundamental lack of the needed field in Const nodes is unfixable without
forcing initdb, which we won't do in released branches.  So I'm not
excited about looking for a band-aid answer for 8.2 or 8.1.

            regards, tom lane