Re: Problem using LIMIT 1 when only 1 record exists.

Neil Conway <neilc(at)samurai(dot)com> writes:
> "Steve Thames" <steve(at)softlife(dot)com> writes:
>> The SQL command:
>> SELECT last FROM table WHERE symbol='Symbol' AND expmoyr='Mmm-YY' ORDER BY
>> qdate DESC LIMIT 1;
>>
>> This query works fine when there is more than one record meeting the
>> criteria. When there is only 1, the query locks and no result is
>> returned.

> I'm skeptical that LIMIT is broken. Can you provide a reproducible
> example?

I'll bet very long odds that "locks" should be read as "ran longer than
Steve wanted to wait". Before this 7.4 change:

2002-11-22 17:10 tgl

* src/: backend/executor/nodeLimit.c, include/nodes/execnodes.h:
Redesign internal logic of nodeLimit so that it does not need to
fetch one more row from the subplan than the COUNT would appear to
require. This costs a little more logic but a number of people
have complained about the old implementation.

nodeLimit would have tried to fetch two rows from the underlying plan.
If only one matching row is actually available, this would result in
scanning the whole source table.

regards, tom lane