NOT TRUE!
The second query is effectively "(SELECT TRUE ...WHERE ... LIMIT 1) OR
(SELECT TRUE ...WHERE ... LIMIT 1) AS ..."
The first portion in parentheses can return either a single row of TRUE,
or no row (NULL). Ditto for the second portion. The OR means that you
logically combine TRUEs and/or NULLs into a SINGLE value. This can be
trivially verified by the following SELECT:
select (select true from anytable where TRUE limit 1) or (select true
from anytable where TRUE limit 1);
Vary the capitalized "TRUE"s each between true and false, and you will
see that EXACTLY ONE ROW IS RETURNED IN EACH CASE, having a resultant
(combined) value of either TRUE or NULL.
The real issue here is why the original query executes in a fraction of
a second under 7.4.x, and runs for hours on 8.0.4.
-- Dean
On 2005-10-17 11:17, Jim C. Nasby wrote:
> Those two queries aren't the same. The first one can only return 0 or 1 rows;
> the second one can return 0, 1, or 2 rows.
>
> An explain analyze of each should show why one is much faster than the
> other.
>
> On Mon, Oct 17, 2005 at 10:29:43AM -0700, Dean Gibson (DB Administrator) wrote:
>
>> In the query below, if I replace:
>>
>> (SELECT TRUE FROM archivejb WHERE ( (callsign = gen.callsign AND
>> license_status = 'A' AND prev_callsign = gen.vanity_callsign)
>> OR (callsign =
>> gen.vanity_callsign AND licensee_id =
>> gen.licensee_id))
>> AND grant_date < receipt_date LIMIT
>> 1) AS _verified,
>>
>> with:
>>
>> (SELECT TRUE FROM archivejb WHERE callsign = gen.callsign AND
>> license_status = 'A' AND prev_callsign = gen.vanity_callsign
>> AND grant_date < receipt_date LIMIT
>> 1) OR
>> (SELECT TRUE FROM archivejb WHERE callsign =
>> gen.vanity_callsign AND licensee_id = gen.licensee_id
>> AND grant_date < receipt_date LIMIT
>> 1) AS _verified,
>>