Thread: BUG #3598: Strange behaviour of character columns in select with views
BUG #3598: Strange behaviour of character columns in select with views
From
"Luiz K. Matsumura"
Date:
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.
Re: BUG #3598: Strange behaviour of character columns in select with views
From
"Heikki Linnakangas"
Date:
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
Re: BUG #3598: Strange behaviour of character columns in select with views
From
"Luiz K. Matsumura"
Date:
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