Thread
-
Error in parser with UNIONS.
Chris Albertson <chris@topdog.pas1.logicon.com> — 1998-05-21T22:39:14Z
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
-
Re: [HACKERS] Error in parser with UNIONS.
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-05-22T01:26:03Z
> 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 -
Re: [HACKERS] Error in parser with UNIONS.
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-05-22T03:38:08Z
> > 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)
-
Re: [HACKERS] Error in parser with UNIONS.
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-05-22T15:38:46Z
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 -
Re: [HACKERS] Error in parser with UNIONS.
David Gould <dg@illustra.com> — 1998-05-31T05:29:43Z
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