Got no reply on "questions".  Someone here may want to
know this...

I think I may have uncovered an error in the parser.  The 
following is the simplest example that shows the problem.
Maybe a counter needs to be reset by 'union' or checked
after select not statement.  I would like to use this syntax
in my libpq program.  Is this a bug?  Is it already known?

Would someone please e-mail me the syntax for the
"explicit cast" the system wants

I am using 6.3.2 on an Ultra SPARC.  The error occurs on
a Linux RH50 Intel system too.

I think the following should work but does not:

   testdb=> select 'a' as X 
   testdb-> union
   testdb-> select 'b' as X;
   NOTICE:  there is more than one operator < for types
   NOTICE:  unknown and unknown. You will have to retype this query
   ERROR:  using an explicit cast

Notice that this does work
 
   testdb=>  select 'b' as X;
   x
   -
   b
   (1 row)

And this works too:

   testdb=> select 1 as X
   testdb-> union
   testdb-> select 2 as X;
   x
   -
   1
   2
   (2 rows)


-- 
--Chris Albertson

  chris@topdog.logicon.com                Voice:  626-351-0089  X127
  Logicon RDA, Pasadena California          Fax:  626-351-0699

Thu, 21 May 1998 15:39:14 -0700
Chris Albertson <chris@topdog.pas1.logicon.com>
Error in parser with UNIONS.
> I think I may have uncovered an error in the parser.  The
> following is the simplest example that shows the problem.
> Maybe a counter needs to be reset by 'union' or checked
> after select not statement.  I would like to use this syntax
> in my libpq program.  Is this a bug?  Is it already known?

Not already known, and it is a feature for now. I _should_ be able to
get it to work in v6.4, since I have already made changes elsewhere to
do a better job of guessing types in underspecified queries.

For example, in v6.3.2 the following query does not work:

postgres=> select 'a' || 'b' as "Concat";
Concat
------
ab
(1 row)

The underlying reason for the problem is Postgres' conservative approach
to typing and type coersion. I've made changes to make it a bit more
thorough in its matching attempts, and will look at this case soon.

> Would someone please e-mail me the syntax for the
> "explicit cast" the system wants

postgres=> select text 'a' as X
postgres-> union
postgres-> select text 'b';
x
-
a
b
(2 rows)

Note that this is the SQL92-style of specification; you can also use
"'a'::text" rather than "text 'a'". This example was run on something
similar to the current development source tree, but I would expect
v6.3.2 to behave the same way.

                           - Tom

Fri, 22 May 1998 01:26:03 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Error in parser with UNIONS.
> 
> Got no reply on "questions".  Someone here may want to
> know this...
> 
> I think I may have uncovered an error in the parser.  The 
> following is the simplest example that shows the problem.
> Maybe a counter needs to be reset by 'union' or checked
> after select not statement.  I would like to use this syntax
> in my libpq program.  Is this a bug?  Is it already known?
> 
> Would someone please e-mail me the syntax for the
> "explicit cast" the system wants
> 
> I am using 6.3.2 on an Ultra SPARC.  The error occurs on
> a Linux RH50 Intel system too.
> 
> I think the following should work but does not:
> 
>    testdb=> select 'a' as X 
>    testdb-> union
>    testdb-> select 'b' as X;
>    NOTICE:  there is more than one operator < for types
>    NOTICE:  unknown and unknown. You will have to retype this query
>    ERROR:  using an explicit cast
> 
> Notice that this does work
>  
>    testdb=>  select 'b' as X;
>    x
>    -
>    b
>    (1 row)
> 
> And this works too:
> 
>    testdb=> select 1 as X
>    testdb-> union
>    testdb-> select 2 as X;
>    x
>    -
>    1
>    2
>    (2 rows)
> 

This caused because UNION removes duplicates, and to do that, it must
sort, but the character constants can't be sorted because they could be
text, varchar, char(), etc.  6.4 will fix that with new auto-casting. 
For now, us UNION ALL, which will not remove duplicates.


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 21 May 1998 23:38:08 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Error in parser with UNIONS.
Made some progress:

postgres=> select 1.2 as float8 union select 1;
float8
------
     1
   1.2
(2 rows)

postgres=> select text 'a' as text union select 'b';
text
----
a
b
(2 rows)

At the moment I'm forcing the types of the union to match the types of
the first/top clause in the union:

postgres=> select 1 as all_integers
postgres-> union select '2.2'::float4 union select 3.3;
all_integers
------------
           1
           2
           3
(3 rows)

The better strategy might be to choose the "best" type of the bunch, but
is more difficult because of the nice recursion technique used in the
parser. However, it does work OK when selecting _into_ a table:

postgres=> create table ff (f float);
CREATE
postgres=> insert into ff
postgres-> select 1 union select '2.2'::float4 union select 3.3;
INSERT 0 3
postgres=> select * from ff;
               f
----------------
               1
2.20000004768372
             3.3
(3 rows)

Comments??

                     - Tom

Fri, 22 May 1998 15:38:46 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Error in parser with UNIONS.
Tom Lane he say:
> Made some progress:
> 
> postgres=> select 1.2 as float8 union select 1;
> float8
> ------
>      1
>    1.2
> (2 rows)
> 
> postgres=> select text 'a' as text union select 'b';
> text
> ----
> a
> b
> (2 rows)
> 
> At the moment I'm forcing the types of the union to match the types of
> the first/top clause in the union:
> 
> postgres=> select 1 as all_integers
> postgres-> union select '2.2'::float4 union select 3.3;
> all_integers
> ------------
>            1
>            2
>            3
> (3 rows)
> 
> The better strategy might be to choose the "best" type of the bunch, but
> is more difficult because of the nice recursion technique used in the
> parser. However, it does work OK when selecting _into_ a table:
> 
> postgres=> create table ff (f float);
> CREATE
> postgres=> insert into ff
> postgres-> select 1 union select '2.2'::float4 union select 3.3;
> INSERT 0 3
> postgres=> select * from ff;
>                f
> ----------------
>                1
> 2.20000004768372
>              3.3
> (3 rows)
> 
> Comments??
> 

Great stuff!
-dg

Sat, 30 May 1998 22:29:43 -0700 (PDT)
dg@illustra.com (David Gould)
Re: [HACKERS] Error in parser with UNIONS.