Re: Fetch zero result rows when executing a query?

On 2/4/15 12:13 PM, Stephen R. van den Berg wrote:
> If you know beforehand the query might generate more than one row (SELECT)
> yet you also know that you are not interested in those, then maxrows=1
> is best; then again, modifying the query to include a LIMIT 1 is even
> better, in which case maxrows can be zero again.

This seems to be a common pattern, and I think it's a *huge* mistake to
specify maxrows=1 and/or ignore rows after the first one in the driver
layer. If the user says "give me the only row returned by this query",
the interface should check that only one row is in reality returned by
the query. If the query returns more than one row, the user made a
mistake in formulating the query and she probably wants to know about
it. If she genuinely doesn't care about the rows after the first one,
she can always specify LIMIT 1.

For a sad example, look at PL/PgSQL's SELECT .. INTO ..; it's not
terribly difficult to write a query which returns more than one row *by
mistake* and have something really bad happen later on since it went
undetected during testing because you just happened to get the expected
row back first. And when you do want to specifically enforce it for
e.g. security critical code, you have to resort to really ugly hacks
like window functions.

.m