Обсуждение: 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) -- --Chris Albertson chris@topdog.logicon.com Voice: 626-351-0089 X127 Logicon RDA, Pasadena California Fax: 626-351-0699
> 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