DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=bLvKReqw47ur+Fcx05H7W6QhG5af/HWX2QPdsHZDXC5h1UsdCSuSgi6CqSIdjeCHtx12T8CrO8McmP/HwrmrUcd9DzjICSq1nRBPipex3joP1WHHcItVFnFsWqxynb7Xp4pv/IBAnx1tCD5NP+tFKkvclftcoYIurCHRlQEvkW4=
	;
References: <201203281501.38835.gary.stainburn@ringways.co.uk>
In-Reply-To: <201203281501.38835.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <2F810199-0078-400F-AF95-F04F6BFAB4B0@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: Wrong output from union
Date: Fri, 30 Mar 2012 18:19:50 -0700
To: Gary Stainburn <gary.stainburn@ringways.co.uk>

Documented behavior.  Please read the section on UNION for the why and the p=
roper alternative syntax:

http://www.postgresql.org/docs/9.0/interactive/sql-select.html


On Mar 28, 2012, at 7:01, Gary Stainburn <gary.stainburn@ringways.co.uk> wro=
te:

> Hi folks.
>=20
> I have two selects which in themselves report what they should. However, w=
hen=20
> I run a union to combine their outputs (to then feed a select/count) lines=
=20
> disappear.  Below are the two seperate selects, followed by the untion in=20=

> which duplicate rows are lost. I'm using Postgresql 8.3.3-2 RPMs on a Fedo=
ra=20
> 9 system.
>=20
> Can anyone tell me why it is happening and how to fix it.
>=20
> Ta.
>=20
> users=3D# select r_u_id || ':' ||r_deadline as key from requests where r_u=
_id is=20
> not NULL and r_deadline>=3D'2012-03-26' and r_deadline <=3D ('2012-03-26':=
:date=20
> + '7 days'::interval);                                                    =
                      =20
> key
> ---------------
> 25:2012-03-28
> 25:2012-03-28
> 25:2012-03-28
> 25:2012-03-30
> 25:2012-03-29
> 25:2012-03-27
> (6 rows)
>=20
> users=3D# select u_id || ':' || rm_timestamp::date as key from request_rem=
inders=20
> where u_id is not null and rm_timestamp>=3D'2012-03-26' and rm_timestamp <=
=3D=20
> ('2012-03-26'::date + '7 days'::interval);
> key
> -----
> (0 rows)
>=20
> users=3D# select r_u_id || ':' ||r_deadline as key from requests where r_u=
_id is=20
> not NULL and r_deadline>=3D'2012-03-26' and r_deadline <=3D ('2012-03-26':=
:date=20
> + '7 days'::interval)                                                     =
                =20
> union                                                                     =
                                                =20
> select u_id || ':' || rm_timestamp::date as key from request_reminders whe=
re=20
> u_id is not null and rm_timestamp>=3D'2012-03-26' and rm_timestamp <=3D=20=

> ('2012-03-26'::date + '7 days'::interval);
>      key
> ---------------
> 25:2012-03-27
> 25:2012-03-28
> 25:2012-03-29
> 25:2012-03-30
> (4 rows)
>=20
> users=3D#
> --=20
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk=20
>=20
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql