X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id E58E4D1B8B5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 14 Jun 2004 14:39:03 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 32404-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 14 Jun 2004 17:38:47 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id AB1BAD1B20E
	for <pgsql-sql@postgresql.org>; Mon, 14 Jun 2004 14:38:14 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 6E36835A06; Mon, 14 Jun 2004 10:38:04 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 6C71835A05; Mon, 14 Jun 2004 10:38:04 -0700 (PDT)
Date: Mon, 14 Jun 2004 10:38:04 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Jaime Casanova <el_vigia_ec@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query with =ALL
In-Reply-To: <BAY8-F116TMlxRjxUBc0007a4fd@hotmail.com>
Message-ID: <20040614103144.L48103@megazone.bigpanda.com>
References: <BAY8-F116TMlxRjxUBc0007a4fd@hotmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200406/112
X-Sequence-Number: 18057


On Mon, 14 Jun 2004, Jaime Casanova wrote:

> i have an strange result here, i'm using 7.4.2 on redhat 8
>
> i have a query like this
>
> SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
> CPA.cpa_fechavencimiento
> FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
> WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
> AND
>       CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
> '2004-2005' AND
>       CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
>       CPA.cur_paralelo = ALL (SELECT cur_paralelo FROM aca_t_curso WHERE
> ent_codigo = 1 AND
>
> sec_codigo = 1 AND
>
> ani_codigo = '2004-2005' AND
>
> cic_codigo = 1 AND
>
> esp_codigo = 0 AND
>
> cur_codigo = 1)
>
> that bring no values but if i do this (the same query without the =ALL part)
>
> SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
> CPA.cpa_fechavencimiento
> FROM rec_m_cuadropagos CPA, rec_m_rubro RUB
> WHERE RUB.ent_codigo = CPA.ent_codigo AND RUB.rub_codigo = CPA.rub_codigo
> AND
>       CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
> '2004-2005' AND
>       CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1
>
> get a resultset, one would think that the =ALL subquery is filtering out
> that rows but the cur_paralelo has same values in both sides
>
> select distinct cur_paralelo from rec_m_cuadropagos WHERE ent_codigo = 1 AND
>
> sec_codigo = 1 AND
>
> ani_codigo = '2004-2005' AND
>
> cic_codigo = 1 AND
>
> esp_codigo = 0 AND
>
> cur_codigo = 1
> intersect
> SELECT cur_paralelo FROM aca_t_curso WHERE ent_codigo = 1 AND
>
> sec_codigo = 1 AND
>
> ani_codigo = '2004-2005' AND
>
> cic_codigo = 1 AND
>
> esp_codigo = 0 AND
>
> cur_codigo = 1
>
> this query proves that its result is 'A', 'B', 'C'
>
> any idea, is something wrong in my thinking?

AFAIK, The query isn't check all values that meet the other criteria
against all values of the subquery but instead check the value from each
row that meets the other criteria against all the values in the subquery.
If the values in the subquery are distinct, it's not going to return true.