Is this a bug or have I just not noticed a nuance with SQL

Assume I have create the two tables

create table foo (
         id int4,
);

create table foo_child (
         name text
) inherits (foo);

If I do

select id, name from foo_child union select id, null as name from foo;
it works

select id, null as text from foo union select id, name from foo_child;
fails with

unable to trasform {insert whatever type here} into unknown
         Each UNION | EXCEPT | INTERSECT clause must have compatible target 
types

If this isn't a bug, it would be nice to be a nice feature to be able to 
coax a data type into an 'unknown' field...

I know it would make my life easier... :)


- Thomas Swan
- Graduate Student  - Computer Science
- The University of Mississippi
-
- "People can be categorized into two fundamental
- groups, those that divide people into two groups
- and those that don't."-


Is this a bug or have I just not noticed a
nuance with SQL

Assume I have create the two tables 

create table foo (
        id
int4,
);

create table foo_child (

        name
text
) inherits (foo);

If I do 

select id, name from foo_child
union select id, null as name from foo;
it works

select id, null as text from foo
union select id, name from foo_child;
fails with 

unable to trasform {insert
whatever type here} into unknown
        Each UNION
| EXCEPT | INTERSECT clause must have compatible target types

If this isn't a bug, it would be
nice to be a nice feature to be able to coax a data type into an
'unknown' field... 

I know it would make my life easier... :)


- Thomas Swan
                                  
- Graduate Student  - Computer Science
- The University of Mississippi
- 
- "People can be categorized into two fundamental 
- groups, those that divide people into two groups 
- and those that don't."-
Mon, 07 Aug 2000 11:01:36 -0500
Thomas Swan <tswan@olemiss.edu>
UNIONS
Thomas Swan <tswan@olemiss.edu> writes:
> select id, null as text from foo union select id, name from foo_child;
> fails with
> unable to trasform {insert whatever type here} into unknown
>          Each UNION | EXCEPT | INTERSECT clause must have compatible target 
> types

The UNION type-resolution code could use some work; right now I think
the algorithm is to use the types of the first SELECT and force
everything else into that.  A more symmetrical
promote-to-common-supertype approach would be nice.  The UNION code is
such a mess that I haven't wanted to touch it until we do querytree
revisions in 7.2, though.

In the meantime, you should force the NULL to have the datatype you want
with something like "null::text" or "cast (null as text)".  Note that
the way you have it above is only assigning a column label that happens
to be "text"; it's not a type coercion.

			regards, tom lane

Mon, 07 Aug 2000 14:07:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: UNIONS 
At 01:07 PM 8/7/2000, Tom Lane wrote:
>Thomas Swan <tswan@olemiss.edu> writes:
> > select id, null as text from foo union select id, name from foo_child;
> > fails with
> > unable to trasform {insert whatever type here} into unknown
> >          Each UNION | EXCEPT | INTERSECT clause must have compatible 
> target
> > types
>
>The UNION type-resolution code could use some work; right now I think
>the algorithm is to use the types of the first SELECT and force
>everything else into that.  A more symmetrical
>promote-to-common-supertype approach would be nice.  The UNION code is
>such a mess that I haven't wanted to touch it until we do querytree
>revisions in 7.2, though.
>
>In the meantime, you should force the NULL to have the datatype you want
>with something like "null::text" or "cast (null as text)".  Note that
>the way you have it above is only assigning a column label that happens
>to be "text"; it's not a type coercion.

The reason I was asking is that I had an idea for doing the select ** from 
tablename* that would expand.

It could be macro of sorts but part of it depending on creating a null 
table or the equivalent of it with nothing but a null column for each 
different column of the set.  I had  a reverse traversal of the classes set 
up, but it didn't work because I could allow for all the columns of all the 
children.

If you could recommend a place to start, I wouldn't mind looking at the 
existing code and seeing what I could do.

-
- Thomas Swan
- Graduate Student  - Computer Science
- The University of Mississippi
-
- "People can be categorized into two fundamental
- groups, those that divide people into two groups
- and those that don't."

At 01:07 PM 8/7/2000, Tom Lane wrote:
Thomas Swan <tswan@olemiss.edu>
writes:
> select id, null as text from foo union select id, name from
foo_child;
> fails with
> unable to trasform {insert whatever type here} into unknown
>          Each UNION |
EXCEPT | INTERSECT clause must have compatible target 
> types

The UNION type-resolution code could use some work; right now I
think
the algorithm is to use the types of the first SELECT and force
everything else into that.  A more symmetrical
promote-to-common-supertype approach would be nice.  The UNION code
is
such a mess that I haven't wanted to touch it until we do querytree
revisions in 7.2, though.

In the meantime, you should force the NULL to have the datatype you
want
with something like "null::text" or "cast (null as
text)".  Note that
the way you have it above is only assigning a column label that
happens
to be "text"; it's not a type coercion.

The reason I was asking is that I had an idea for doing the select **
from tablename* that would expand.

It could be macro of sorts but part of it depending on creating a null
table or the equivalent of it with nothing but a null column for each
different column of the set.  I had  a reverse traversal of the
classes set up, but it didn't work because I could allow for all the
columns of all the children.   

If you could recommend a place to start, I wouldn't mind looking at the
existing code and seeing what I could do.


- 
- Thomas Swan
                                  
- Graduate Student  - Computer Science
- The University of Mississippi
- 
- "People can be categorized into two fundamental 
- groups, those that divide people into two groups 
- and those that don't."
Mon, 07 Aug 2000 13:56:31 -0500
Thomas Swan <tswan@olemiss.edu>
Re: UNIONS 
Thomas Swan <tswan@olemiss.edu> writes:
> The reason I was asking is that I had an idea for doing the select ** from 
> tablename* that would expand.

> It could be macro of sorts but part of it depending on creating a null 
> table or the equivalent of it with nothing but a null column for each 
> different column of the set.

What happens when two different child tables have similarly-named
columns of different types?

In any case, this wouldn't be a very satisfactory solution because you
couldn't tell the difference between a null stored in a child table and
the lack of any column at all.  We really need to do it the hard way,
ie, issue a new tuple descriptor as we pass into each new child table.

There appears to have once been support for that back in the Berkeley
days; you might care to dig through Postgres 4.2 or so to see how they
did it.

			regards, tom lane

Mon, 07 Aug 2000 15:02:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: UNIONS 