Обсуждение: Strange interaction of union and expressions

Hello all,

We noticed some very odd behavior today with Postgres 7.4.5

Running the following SQL:

create table a (foo varchar);
insert into a (foo) values ('baz');
create table b (foo varchar);
insert into b (foo) values ('woof');
select '"' || foo || '"' as foo
    from (select foo from a) as bar
        union select foo from b;

Produces this output:

  foo
-------
 "baz"
 woof
(2 rows)

I would expect the following instead:

  foo
-------
 "baz"
 "woof"
(2 rows)

Is this a known issue?  Has it been fixed?

Thanks,

--
--------------------------------------------------------------------
Aaron Bingham
Software Engineer
Cenix BioScience GmbH
--------------------------------------------------------------------

Kevin Murphy <murphy2@speakeasy.net> writes:

> On Apr 20, 2005, at 1:24 PM, Aaron Bingham wrote:
>> create table a (foo varchar);
>> insert into a (foo) values ('baz');
>> create table b (foo varchar);
>> insert into b (foo) values ('woof');
>> select '"' || foo || '"' as foo
>>     from (select foo from a) as bar
>>         union select foo from b;
>>
>
> No, it's doing what you asked.
>
> You mean:
>
> select '"' || foo || '"' from (select foo from a union select foo from
> b) as subq;
>
> Right?

Ah, I get it now.  Thanks!

--
--------------------------------------------------------------------
Aaron Bingham
Software Engineer
Cenix BioScience GmbH
--------------------------------------------------------------------

I see no problem. You get what you want.

Correct query is:

select '"' || foo || '"' as foo
     from (select foo from a union select foo from b ) as bar;

Oleg

On Wed, 20 Apr 2005, Aaron Bingham wrote:

> Hello all,
>
> We noticed some very odd behavior today with Postgres 7.4.5
>
> Running the following SQL:
>
> create table a (foo varchar);
> insert into a (foo) values ('baz');
> create table b (foo varchar);
> insert into b (foo) values ('woof');
> select '"' || foo || '"' as foo
>    from (select foo from a) as bar
>        union select foo from b;
>
> Produces this output:
>
>  foo
> -------
> "baz"
> woof
> (2 rows)
>
> I would expect the following instead:
>
>  foo
> -------
> "baz"
> "woof"
> (2 rows)
>
> Is this a known issue?  Has it been fixed?
>
> Thanks,
>
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

On Wed, Apr 20, 2005 at 07:24:08PM +0200, Aaron Bingham wrote:

Hi,

> create table a (foo varchar);
>x insert into a (foo) values ('baz');
> create table b (foo varchar);
> insert into b (foo) values ('woof');
> select '"' || foo || '"' as foo
>     from (select foo from a) as bar
>         union select foo from b;

It's a precedence issue; not a problem really.  Try this:

select '"' || foo || '"' as foo
     from (select foo from a
         union select foo from b) as bar;

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"¿Que diferencia tiene para los muertos, los huérfanos, y aquellos que han
perdido su hogar, si la loca destrucción ha sido realizada bajo el nombre
del totalitarismo o del santo nombre de la libertad y la democracia?" (Gandhi)

On Apr 20, 2005, at 1:24 PM, Aaron Bingham wrote:
> create table a (foo varchar);
> insert into a (foo) values ('baz');
> create table b (foo varchar);
> insert into b (foo) values ('woof');
> select '"' || foo || '"' as foo
>     from (select foo from a) as bar
>         union select foo from b;
>

No, it's doing what you asked.

You mean:

select '"' || foo || '"' from (select foo from a union select foo from
b) as subq;

Right?

-Kevin