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

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)

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

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