Re: The tragedy of SQL

On 17/09/21 23:49, Raymond Brinzer wrote:
> On Tue, Sep 14, 2021 at 9:06 AM Merlin Moncure <mmoncure@gmail.com> wrote:
>> I've long thought that there is more algebraic type syntax sitting
>> underneath SQL yearning to get out.
> I wanted to come back to this, because I've been looking to take a
> single problem (from my perspective) and explain it concisely.  Your
> intuition is right on the mark.
>
> Shell syntax is a pretty good lingua franca, so let's use it.  Say you
> were working at the command line, and you said something like:
>
> cat somefile | awk '{print $3 " " $1 " " $5;}' | sort | grep "^Robert"
>
> And the shell responded with something like:  ERROR: syntax error at
> or near "sort".  After a little tinkering, you discover:  that's
> because the grep has to come before the sort.  But why?
>
> The database is not going to evaluate relational operations in order,
> passing the output of one into the next as a shell pipe does.
> Nevertheless, they are logically independent.  Each should take in a
> relation and either a second relation or a predicate, and return a
> relation.  Or, to put it mathily, relations are closed under
> relational operations.  So:
>
> Operation 1 | Operation 2
> and
> Operation 2 | Operation 1
>
> should both be valid, whether or not they're semantically equivalent
> (though they often are).  The operations are inherently atomic, and
> can be understood in isolation.

[...]

In Mathematics which way round you do things may be important. For
numbers in the Real & Complex domains then this does not matter. 
However, in the Quaternions it does matter, here A * B is not always the
same as B * A.  And amongst the Octonions it is even worse, as there the
order in which you do things may lead to different results, so A * (B *
C) is not necessarily the same as (A * B) * C.

Another example is rotating things in 3 dimensions.  Hold a book with
its front facing you.  Rotate the book towards you so it is now flat,
them rotate the book along the vertical access so it is now edge on. 
When you do the operations in the reverse order, then you get a
different result! Yes, you can blame the Quaternions.

In PostgreSQL, if the operations are 'not idempotent' (relies on at
least one function that has varying output for the same input
parameters) then the order in which you do things could lead to
different results.

For the optimizer to be effective then it must be allowed to do
operations in the best order it sees fit -- this is documented. Just as
you must not rely on the order in which results are returned, unless you
explicitly have an ORDER BY -- as the system will extract results in the
fastest way it knows, which may not necessarily be in the same order as
the values where inserted. This would be true, even if you had a totally
different query language.


Cheers,
Gavin