Thread: VIEW definitions broken in 6.5.0

Hey hackers - 
I don't know if this is fixed in 6.5.1 or not, but the definition field
in the pg_views system table is broken in 6.5.0, and this breaks view
editing in pgaccess. The problem is that table qualifications are left
off the fieldnames in both the SELECT clause and the WHERE clause. Minimal
example given below:


test=> create table t1 (textid int4, nextid int4, words text);
CREATE
test=> create table t2 (nextid int4, words text);
CREATE
test=> create view v1 as select t1.textid,t1.words,t2.words as words2
from t1,t2 where t1.nextid=t2.nextid;
CREATE
test=> insert into t1 values (2,1,'some other text');
INSERT 384454 1
test=> insert into t2 values (1,'joint text');
INSERT 384455 1
test=> insert into t1 values (1,1,'some text');
INSERT 384456 1
test=> select * from v1;
textid|words          |words2    
------+---------------+----------    2|some other text|joint text    1|some text      |joint text
(2 rows)

test=> select definition from pg_views where viewname='v1';
definition 
-----------------------------------------------------------------------
SELECT "textid", "words", "words" AS "words2" FROM "t1", "t2" WHERE
"nextid" = "nextid"; (1 row)

test=> SELECT "textid", "words", "words" AS "words2" FROM "t1", "t2"
WHERE "nextid" = "nextid";
ERROR:  Column 'words' is ambiguous
test=> 

-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

>
> Hey hackers -
> I don't know if this is fixed in 6.5.1 or not, but the definition field
> in the pg_views system table is broken in 6.5.0, and this breaks view
> editing in pgaccess. The problem is that table qualifications are left
> off the fieldnames in both the SELECT clause and the WHERE clause. Minimal
> example given below:

Oh,

    I see the problem. It is because the rule backparsing utility
    prints the relation name only if it is referenced by  another
    name (... FROM t1 X, ...).

    I'll change it in the v6.5 tree to print it allways. For v6.6
    I'll work on rule recompilation which  requires  storing  the
    original query text and will avoid that problem entirely.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Jan, or someone, can you comment on this?


> Hey hackers - 
> I don't know if this is fixed in 6.5.1 or not, but the definition field
> in the pg_views system table is broken in 6.5.0, and this breaks view
> editing in pgaccess. The problem is that table qualifications are left
> off the fieldnames in both the SELECT clause and the WHERE clause. Minimal
> example given below:
> 
> 
> test=> create table t1 (textid int4, nextid int4, words text);
> CREATE
> test=> create table t2 (nextid int4, words text);
> CREATE
> test=> create view v1 as select t1.textid,t1.words,t2.words as words2
> from t1,t2 where t1.nextid=t2.nextid;
> CREATE
> test=> insert into t1 values (2,1,'some other text');
> INSERT 384454 1
> test=> insert into t2 values (1,'joint text');
> INSERT 384455 1
> test=> insert into t1 values (1,1,'some text');
> INSERT 384456 1
> test=> select * from v1;
> textid|words          |words2    
> ------+---------------+----------
>      2|some other text|joint text
>      1|some text      |joint text
> (2 rows)
> 
> test=> select definition from pg_views where viewname='v1';
> definition 
> -----------------------------------------------------------------------
> SELECT "textid", "words", "words" AS "words2" FROM "t1", "t2" WHERE
> "nextid" = "nextid"; (1 row)
> 
> test=> SELECT "textid", "words", "words" AS "words2" FROM "t1", "t2"
> WHERE "nextid" = "nextid";
> ERROR:  Column 'words' is ambiguous
> test=> 
> 
> -- 
> Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St.,  Houston, TX 77005
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

>> The problem is that table qualifications are left
>> off the fieldnames in both the SELECT clause and the WHERE clause.

Yes, that was reported and fixed a while ago.  It's definitely in
current and 6.5.2, not sure about 6.5.1.
        regards, tom lane