Re: bug #7499 additional comments

On 08/23/2012 04:12 AM, Denis Kolesnik wrote:

> Suppose a person who has basic SQL knowledges would learn on praxis
> how would result a query if a person adds the clause "limit 1" to it

Then they just got bitten by not learning enough and not testing their
code well enough; they were probably programming by recipe and
copy-and-paste, not by learning the platform they're working with.

http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-ORDERBY

"The ORDER BY clause causes the result rows to be sorted according to
the specified expression(s). If two rows are equal according to the
leftmost expression, they are compared according to the next expression
and so on. If they are equal according to all specified expressions,
they are returned in an implementation-dependent order."

It'd be really nice if every programming language and tool could be
completely safe and easy, with no undefined, implementation-defined or
inconsistent behaviour. Unfortunately, in the real world that doesn't
happen because perfectly specified platforms are (a) really hard to
actually write and (b) usually hard to optimise and thus slow.

Suppose a person with basic C knowledge wrote this (utterly wrong and
dangerous, do not use for anything) program:

#include <stdio.h>
#include <string.h>
#include <malloc.h>
int main() {
char * blah = (char*)malloc(10);
strcpy(blah,"1234567890");
printf("%s\n", blah);
}

This program has *at* *least* one bug that'll cause it to run most of
the time, but fail unpredictably, especially when used as part of a
larger program rather than standalone. Failure will depend on platform,
C library, kernel, compiler settings, and the contents of uninitialized
memory.

Is the platform responsible for the user shooting themselves in the foot
because they didn't learn about null termination of strings, buffer
over-runs, the dangers of using strcpy(), etc? To me it's a bug in the
user's code, not the platform.

Sure, the platform could be easier to use. It could add lots of bounds
checks, prohibit raw memory access, use garbage collection instead of
explicit pointer-based memory management, etc. Then you'd have a new
platform called Java, which is very useful - but not really something
you can use to write tiny programs that take microseconds to run, or
high-performance operating system kernels.

Even Java has plenty of traps and confusing characteristics. Anything to
do with threads. finalize() methods. try {} catch {} finally {}
constructs. Double-checked locking. Plenty more. That's in a language
that was designed to be an easier and safer alternative to C.

Everything is a compromise, including the SQL language and
implementations of it. If Pg made underspecified sorts an error then
lots of other people would scream "bug!" because pretty much every other
database system lets you do this so it'd be a portability problem - and
because it's a really useful behaviour for some purposes. If Pg's query
planner always ensured that sorts were stable and always did the same
sorts, people wouldn't use Pg because it'd be too slow.

More importantly, PostgreSQL has no way of *knowing* for sure that the
sort is underspecified. It can't know that the column you've specified
isn't unique, or at least unique within the subset of data you're
working with. It trusts you to know what you want.

The trick is to read the documentation, learn, and test your code well.

That's true of every language, even those that try to protect the
programmer from their mistakes as much as possible.

--
Craig Ringer