Does anybody know:

select 1.0 union select 1;
or
select 1 union select 1.0;

should return 1 or 1.0?

I see below on my Linux box:

test=# select 1 union select 1.0;
 ?column? 
----------
        1
(1 row)

test=# select 1.0 union select 1;
 ?column? 
----------
      1.0
(1 row)

This seems a little bit inconsistent...
--
Tatsuo Ishii

Wed, 15 Jan 2003 13:03:53 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
UNION result

I think the cause is that multi-type UNION queries use the first query
for casting the other parts of the UNION.  In the old days we would just
reject the query because the UNION columns are of different types.

---------------------------------------------------------------------------

Tatsuo Ishii wrote:
> Does anybody know:
> 
> select 1.0 union select 1;
> or
> select 1 union select 1.0;
> 
> should return 1 or 1.0?
> 
> I see below on my Linux box:
> 
> test=# select 1 union select 1.0;
>  ?column? 
> ----------
>         1
> (1 row)
> 
> test=# select 1.0 union select 1;
>  ?column? 
> ----------
>       1.0
> (1 row)
> 
> This seems a little bit inconsistent...
> --
> Tatsuo Ishii
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 14 Jan 2003 23:14:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: UNION result
Seems fine to me - the second select being cast to the type of the first
select.

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Tatsuo Ishii
> Sent: Wednesday, 15 January 2003 12:04 PM
> To: pgsql-hackers@postgresql.org
> Subject: [HACKERS] UNION result
>
>
> Does anybody know:
>
> select 1.0 union select 1;
> or
> select 1 union select 1.0;
>
> should return 1 or 1.0?
>
> I see below on my Linux box:
>
> test=# select 1 union select 1.0;
>  ?column?
> ----------
>         1
> (1 row)
>
> test=# select 1.0 union select 1;
>  ?column?
> ----------
>       1.0
> (1 row)
>
> This seems a little bit inconsistent...
> --
> Tatsuo Ishii
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


Wed, 15 Jan 2003 12:19:24 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: UNION result

On Wed, 15 Jan 2003, Tatsuo Ishii wrote:

> Does anybody know:
>
> select 1.0 union select 1;
> or
> select 1 union select 1.0;
>
> should return 1 or 1.0?

Hmm, I think (but am not sure) that the spec bit
in SQL92 that addresses this is 9.3
Set operation result data types based on the
text in 7.10 query expression.  It seems
to say to me that should always be an
approximate numeric (if 1.0 is an approximate
numeric).  Am I reading that right?


Tue, 14 Jan 2003 22:18:56 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: UNION result
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Hmm, I think (but am not sure) that the spec bit
> in SQL92 that addresses this is 9.3
> Set operation result data types based on the
> text in 7.10 query expression.  It seems
> to say to me that should always be an
> approximate numeric (if 1.0 is an approximate
> numeric).  Am I reading that right?

Yeah, the existing algorithm for determining CASE/UNION result datatype
does not have any smarts about preferring numeric over integer, which is
what's missing to handle this case per-spec.

There has been some speculation about junking the existing code (which
is mostly driven by a hardwired notion of "preferred types") in favor of
something driven by the contents of pg_cast.  (At least I recall a
message or two about it, but I can't find it in the archives at the
moment.)

Nobody's made a specific proposal though --- and I'm more than a little
bit worried about the possible speed penalty of turning what's presently
a simple C switch-statement into a bunch of catalog lookups.

			regards, tom lane

Wed, 15 Jan 2003 09:50:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: UNION result 
On Wed, 15 Jan 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > Hmm, I think (but am not sure) that the spec bit
> > in SQL92 that addresses this is 9.3
> > Set operation result data types based on the
> > text in 7.10 query expression.  It seems
> > to say to me that should always be an
> > approximate numeric (if 1.0 is an approximate
> > numeric).  Am I reading that right?
>
> Yeah, the existing algorithm for determining CASE/UNION result datatype
> does not have any smarts about preferring numeric over integer, which is
> what's missing to handle this case per-spec.
>
> There has been some speculation about junking the existing code (which
> is mostly driven by a hardwired notion of "preferred types") in favor of
> something driven by the contents of pg_cast.  (At least I recall a
> message or two about it, but I can't find it in the archives at the
> moment.)

It seems to me that the spec has a fairly hardwired notion of what types
should come out given the sql types.  The biggest problems that I can
see are that it doesn't extend well to an extensible type system and that
in alot of cases it doesn't seem to allow conversions (for example
select CAST(1 as float) union select '1' - if you were to allow
conversions the rules seem to be ambiguous)


Wed, 15 Jan 2003 07:54:12 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: UNION result 
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> It seems to me that the spec has a fairly hardwired notion of what types
> should come out given the sql types.  The biggest problems that I can
> see are that it doesn't extend well to an extensible type system and that
> in alot of cases it doesn't seem to allow conversions (for example
> select CAST(1 as float) union select '1' - if you were to allow
> conversions the rules seem to be ambiguous)

Agreed, we can't make use of the spec's rules as anything much better
than "spiritual guidance".  But it'd be nice if the rules we use match
what the spec says for the cases covered by the spec.  In particular,
I think it's intuitively correct that numeric union int should yield
numeric no matter which order you write them in.

Actually, now that I look at the code, 7.3 does in fact get this case
right, because we did add a check on pg_cast: it will prefer a type over
another if there is an implicit cast in only one direction.

regression=# select 1 union select 1.2;
 ?column?
----------
        1
      1.2
(2 rows)

The OP may have been fooled by this behavior:

regression=# select 1 union select 1.0;
 ?column?
----------
        1
(1 row)

which happens because '1' and '1.0' are considered equal numeric values,
even though they print differently.

I'm not convinced that the UNION algorithm is right yet, but surely it's
better than it was before.

			regards, tom lane

Wed, 15 Jan 2003 11:10:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: UNION result 
On Wed, 15 Jan 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > It seems to me that the spec has a fairly hardwired notion of what types
> > should come out given the sql types.  The biggest problems that I can
> > see are that it doesn't extend well to an extensible type system and that
> > in alot of cases it doesn't seem to allow conversions (for example
> > select CAST(1 as float) union select '1' - if you were to allow
> > conversions the rules seem to be ambiguous)
>
> Agreed, we can't make use of the spec's rules as anything much better
> than "spiritual guidance".  But it'd be nice if the rules we use match
> what the spec says for the cases covered by the spec.  In particular,
> I think it's intuitively correct that numeric union int should yield
> numeric no matter which order you write them in.
>
> Actually, now that I look at the code, 7.3 does in fact get this case
> right, because we did add a check on pg_cast: it will prefer a type over
> another if there is an implicit cast in only one direction.

> The OP may have been fooled by this behavior:
>
> regression=# select 1 union select 1.0;

And I was fooled by
select '1' union select 1;

because I'd forgotten that '1' isn't exactly a character string
constant. select '1'::text union select 1; properly errors.


Wed, 15 Jan 2003 08:33:30 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: UNION result 