From: Gregory Stark <stark@enterprisedb.com>
To: Nis =?iso-8859-1?Q?J=F8rgensen?= <nis@superlativ.dk>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: The nested view from hell - Restricting a subquerry
In-Reply-To: <f81ep0$emn$1@sea.gmane.org> (Nis =?iso-8859-1?Q?J=F8rgensen'?=
	=?iso-8859-1?Q?s?= message of "Mon, 23 Jul 2007 07:36:28 +0200")
Organization: EnterpriseDB
References: <46A2CABF.3080108@obviously.com> <f7v6na$2gc$1@sea.gmane.org>
	<2385.1185127507@sss.pgh.pa.us> <f80r63$8lo$1@sea.gmane.org>
	<87y7h74un7.fsf@oxford.xeocode.com> <f81ep0$emn$1@sea.gmane.org>
Date: Mon, 23 Jul 2007 12:23:03 +0100
Message-ID: <87d4yjmkiw.fsf@oxford.xeocode.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Nis J=F8rgensen <nis@superlativ.dk> writes:

>>> Well, the query can be satisfied by looking only at the rows with an
>>> order_id matching the invoice_id given. The condition that this is the
>>> largest invoice in the group then needs to be checked afterwards.
>>>
>>> I certainly did not expect the query planner to be able to deduce this,
>>> though.
>>=20
>> No, that's not true. If you had two records in eg_order with the same or=
der_id
>> but different invoice_ids then the query would need both records to sati=
sfy
>> the query.
>
> I assume you mean "... then both records are necessary in order to
> calculate the results of the query". This does not contradict what I wrot=
e.

Sorry I meant, "the query as written can not be satisfied by looking only at
the rows with the specified invoice_id".

>     SELECT  order_id,
> 	max(order_view.invoice_id),
> 	sum(order_view.mileage)
>     FROM    (SELECT order_id,invoice_id, 0 as mileage FROM eg_order
>              UNION
>              SELECT order_id, 0, mileage FROM eg_order_line)
>              order_view GROUP BY order_view.order_id;
>
> This is then restricted on max(invoice_id)
>
> As far as I can tell, these steps produce the correct results (without
> the later information about primary keys provided by Bryce)
>
> INPUT: my_invoice_id
>
> 1. Look up all order_ids for which (order_id,my_invoice_id) appear in
> eg_orders
>
> 2. Find all rows (in both branches of the UNION) with these id_s
>
> 3. Group the rows, and calculate max(invoice_id)
>
> 4. Filter the result rows on max(invoice_id) =3D my_invoice_id.

So here's a hypothetical data set for which this algorithm fails:

order_id 	invoice_id	mileage
--------------------------------------------
1		1		100
1		2		100

Your algorithm would produce=20

order_id	max(invoice_id)	sum(mileage)
--------------------------------------------
1		1		100

Whereas the correct output would be to output no records at all.

--=20
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com