I love how we would admonish sql but love lisp. There isn't a perfect language. SQL is fine. C is damn good. C++ is impossible, Java is C++ but simple, Python is a C wrapper. God help us if we settled on Fortran. We would still have single core processors. Lisp at least allowed multithreading but is very hard to debug.
But back to the issue at hand, SQL is hard. It falls into the trap that C++ did where it is very hard and time consuming to make good schemas and organize data correctly but it is very easy to do it badly. Then we try and fix bad design in places where the fixes don't belong like the view or God forbid the controller. That leads to horrible code and even more bugs.
I make a career of fixing broken schemas and teaching good design. I am 15 years in and learn how people screw up designs every day. Sometimes they are beyond repair and I end up creating new designs and migrate the data. Who knew that you should hire experts to teach novices but experts are expensive and most of the time the code is throwaway anyway.
I don't get why there are so many programming languages out there. C is virtually perfect. Python is C with benefits. Everything else appears to just be offshoots of Python or Lisp and no one uses Lisp as far as I can tell. SQL isn't really a programming language. It is just a layer on top of data.
On Fri, Sep 17, 2021, 3:44 PM Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> wrote:
On 17/09/21 23:49, Raymond Brinzer wrote:
> On Tue, Sep 14, 2021 at 9:06 AM Merlin Moncure <mmoncure@xxxxxxxxx> 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
