Is this a bug?

(using CVS code from yesterday)

nconway=# create view baz (a,b) as select 'hello', 'world';
WARNING:  column "a" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "b" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW
nconway=# 

-Neil



Thu, 02 Oct 2003 02:59:02 -0400
Neil Conway <neilc@samurai.com>
minor view creation weirdness
Neil Conway <neilc@samurai.com> writes:

> Is this a bug?
> 
> (using CVS code from yesterday)
> 
> nconway=# create view baz (a,b) as select 'hello', 'world';
> WARNING:  column "a" has type "unknown"
> DETAIL:  Proceeding with relation creation anyway.
> WARNING:  column "b" has type "unknown"
> DETAIL:  Proceeding with relation creation anyway.
> CREATE VIEW
> nconway=# 

7.3 does the same thing actually. I don't know what that means though.


-- 
greg


02 Oct 2003 03:40:43 -0400
Greg Stark <gsstark@mit.edu>
Re: minor view creation weirdness
On Thu, 2003-10-02 at 08:40, Greg Stark wrote:
> Neil Conway <neilc@samurai.com> writes:
> 
> > Is this a bug?
> > 
> > (using CVS code from yesterday)
> > 
> > nconway=# create view baz (a,b) as select 'hello', 'world';
> > WARNING:  column "a" has type "unknown"
> > DETAIL:  Proceeding with relation creation anyway.
> > WARNING:  column "b" has type "unknown"
> > DETAIL:  Proceeding with relation creation anyway.
> > CREATE VIEW
> > nconway=# 
> 
> 7.3 does the same thing actually. I don't know what that means though.

junk=# \d baz
       View "public.baz"
 Column |   Type    | Modifiers
--------+-----------+-----------
 a      | "unknown" |
 b      | "unknown" |
View definition:
 SELECT 'hello' AS a, 'world' AS b;
 

There is no table behind the view, so there is no way for PostgreSQL to
derive the column types of a and b.  A quoted string (as supplied in the
view definition) could be one of text, varchar, char, date, time,
timestamp, cidr and so on.


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Set your affection on things above, not on things on 
      the earth."          Colossians 3:2 


Thu, 02 Oct 2003 10:33:05 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: minor view creation weirdness
Neil Conway <neilc@samurai.com> writes:
> Is this a bug?

> nconway=# create view baz (a,b) as select 'hello', 'world';
> WARNING:  column "a" has type "unknown"
> DETAIL:  Proceeding with relation creation anyway.

It's always done that, although the spelling of the notice has
varied over the years.

These days we tend to force "unknown" to become "text" if a specific
data type is really required, and I suppose a case could be made that
CREATE VIEW should do that too.  But the consequences of guessing wrong
are probably worse here than elsewhere, since you can't really change
the view column type short of dropping and recreating the view.

I'd almost argue that we should change this message to an error:

	ERROR: column "a" has type "unknown"
	HINT: Explicitly cast the string literal to some specific type.

			regards, tom lane

Thu, 02 Oct 2003 10:16:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: minor view creation weirdness 
On Thu, 2003-10-02 at 10:16, Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > nconway=# create view baz (a,b) as select 'hello', 'world';
> > WARNING:  column "a" has type "unknown"
> > DETAIL:  Proceeding with relation creation anyway.
> 
> It's always done that, although the spelling of the notice has
> varied over the years.

> I'd almost argue that we should change this message to an error

I agree.

-Neil



Thu, 02 Oct 2003 15:58:06 -0400
Neil Conway <neilc@samurai.com>
Re: minor view creation weirdness
Neil Conway <neilc@samurai.com> writes:
> On Thu, 2003-10-02 at 10:16, Tom Lane wrote:
>> Neil Conway <neilc@samurai.com> writes:
>>> nconway=# create view baz (a,b) as select 'hello', 'world';
>>> WARNING:  column "a" has type "unknown"
>>> DETAIL:  Proceeding with relation creation anyway.

>> I'd almost argue that we should change this message to an error

> I agree.

Motion proposed and seconded; any objections out there?

			regards, tom lane

Thu, 02 Oct 2003 16:00:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: minor view creation weirdness 
>>>nconway=# create view baz (a,b) as select 'hello', 'world';
>>>WARNING:  column "a" has type "unknown"
>>>DETAIL:  Proceeding with relation creation anyway.
>>
>>It's always done that, although the spelling of the notice has
>>varied over the years.
> 
> 
>>I'd almost argue that we should change this message to an error
> 
> 
> I agree.

Except that it would totally break backwards-compatibility?  Or will 
'unknown' fields in views be dumped with explicit casts?

Chris



Fri, 03 Oct 2003 09:51:10 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: minor view creation weirdness

Tom Lane <tgl@sss.pgh.pa.us> writes:

> >> I'd almost argue that we should change this message to an error
> 
> > I agree.
> 
> Motion proposed and seconded; any objections out there?

Uhm, doesn't the spec have anything to say about this? 
I mean, the view sure looks like standard SQL on its face.

In any case, I would sure think there was something strange about a query
working fine as a select but not working in a view:

slo=>  select 'foo' as a,'bar' as b;
  a  |  b  
-----+-----
 foo | bar
(1 row)

slo=> create view x as select 'foo' as a,'bar' as b;
WARNING:  Attribute "a" has an unknown type
	Proceeding with relation creation anyway
WARNING:  Attribute "b" has an unknown type
	Proceeding with relation creation anyway
CREATE VIEW

slo=> select * from x;
  a  |  b  
-----+-----
 foo | bar
(1 row)

-- 
greg


03 Oct 2003 00:50:12 -0400
Greg Stark <gsstark@mit.edu>
Re: minor view creation weirdness
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>>> I'd almost argue that we should change this message to an error
>> 
>> I agree.

> Except that it would totally break backwards-compatibility?

Well, that's a possible problem.  How many such views do you think are
out there, given the existence of the warning?

			regards, tom lane

Fri, 03 Oct 2003 09:53:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: minor view creation weirdness 
On Fri, 2003-10-03 at 00:50, Greg Stark wrote:
> 
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
> > >> I'd almost argue that we should change this message to an error
> > 
> > > I agree.
> > 
> > Motion proposed and seconded; any objections out there?
> 
> Uhm, doesn't the spec have anything to say about this? 
> I mean, the view sure looks like standard SQL on its face.
> 
> In any case, I would sure think there was something strange about a query
> working fine as a select but not working in a view:
> 
> slo=>  select 'foo' as a,'bar' as b;
>   a  |  b  
> -----+-----
>  foo | bar
> (1 row)
> 
> slo=> create view x as select 'foo' as a,'bar' as b;
> WARNING:  Attribute "a" has an unknown type
> 	Proceeding with relation creation anyway
> WARNING:  Attribute "b" has an unknown type
> 	Proceeding with relation creation anyway
> CREATE VIEW
> 
> slo=> select * from x;
>   a  |  b  
> -----+-----
>  foo | bar
> (1 row)
> 

or the create table case:

rms=# create table x as select 'foo' as a, 'bar' as b;
WARNING:  42P16: attribute "a" has type UNKNOWN
DETAIL:  Proceeding with relation creation anyway.
LOCATION:  CheckAttributeType, heap.c:427
WARNING:  42P16: attribute "b" has type UNKNOWN
DETAIL:  Proceeding with relation creation anyway.
LOCATION:  CheckAttributeType, heap.c:427
WARNING:  attribute "a" has type UNKNOWN
DETAIL:  Proceeding with relation creation anyway.
WARNING:  attribute "b" has type UNKNOWN
DETAIL:  Proceeding with relation creation anyway.
SELECT

rms=# select * from x;
  a  |  b  
-----+-----
 foo | bar
(1 row)

rms=# \d x
        Table "public.x"
 Column |   Type    | Modifiers 
--------+-----------+-----------
 a      | "unknown" | 
 b      | "unknown" | 


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


03 Oct 2003 12:55:42 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: minor view creation weirdness
Greg Stark <gsstark@mit.edu> writes:
> Uhm, doesn't the spec have anything to say about this? 
> I mean, the view sure looks like standard SQL on its face.

Well, you might read the spec as requiring the view column to have
datatype CHAR(n) where n is the length of the unknown literal.
I see in SQL92:

         9) The data type of a <character string literal> is fixed-length
            character string. The length of a <character string literal>
            is the number of <character representation>s that it contains.
            ...
            Note: <character string literal>s are allowed to be zero-length
            strings (i.e., to contain no characters) even though it is
            not permitted to declare a <data type> that is CHARACTER with
            <length> zero.

The NOTE would appear to imply that

	CREATE VIEW v AS SELECT '';

is illegal, which is not a conclusion I care to follow blindly.  In any
case, in Postgres I would think we would want to take the type as "text"
not "CHAR(n)", spec or no spec.

			regards, tom lane

Fri, 03 Oct 2003 13:26:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: minor view creation weirdness 