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,
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
