Thread: The tragedy of SQL

Many languages are awesome.  I'm always astonished at what great
things people have come up with, over the years; it's been a
wonderfully fertile field.  We would certainly not be better off if
we'd just buckled down, and used COBOL and FORTRAN... or even
relatively good languages like C, APL, and Lisp.

It is certainly possible to change too lightly, for small reasons.
That doesn't mean that forever enduring the same problems is a good
idea.

On Tue, Sep 14, 2021 at 2:18 AM Rob Sargent <robjsargent@gmail.com> wrote:
>
> On 9/13/21 11:51 PM, Guyren Howe wrote:
>
> They are making a decent decision. SQL is a *fucking terrible* language, which I don’t blame them for not wanting to
learn.
>
> The whole industry, programming languages, infrastructure, everything would have developed differently if relations
werea natural, pleasurable thing to use in any programming language. Like an Array, or a Hash. 
> On Sep 13, 2021, 22:45 -0700, Hemil Ruparel <hemilruparel2002@gmail.com>, wrote:
>
> SQL is not the problem. Problem are the devs. I love SQL. I hate orms. The problem with databases is people refuse to
treatit as the entity it is and want to use their beautiful OO system. Problem is databases are not OO. We need to
recognizethat and treat databases as databases. 
>
> All languages are fucking terrible.  There are thousands of the them because some people bump into a feature they
don'tlike and run off an make another fucking terrible language.  For the love of God, please don't be one of those
people. The rest of us find languages we can abide and do productive things with using features we like and avoiding
thosewe don't.  I've always felt it was no small miracle the vendors managed to agree to ODBC/JDBC driver specs (even
thoughthe SQL language definition is "more like guidelines").  Go scream at the DOM and JavaScript. 



--
Ray Brinzer

Is it possible that this is mainly an emotional discussion?

Raymond Brinzer schreef op di 14-09-2021 om 02:39 [-0400]:
> Many languages are awesome.  I'm always astonished at what great
> things people have come up with, over the years; it's been a
> wonderfully fertile field.  We would certainly not be better off if
> we'd just buckled down, and used COBOL and FORTRAN... or even
> relatively good languages like C, APL, and Lisp.
> 
> It is certainly possible to change too lightly, for small reasons.
> That doesn't mean that forever enduring the same problems is a good
> idea.
> 
> On Tue, Sep 14, 2021 at 2:18 AM Rob Sargent <robjsargent@gmail.com>
> wrote:
> > On 9/13/21 11:51 PM, Guyren Howe wrote:
> > 
> > They are making a decent decision. SQL is a *fucking terrible*
> > language, which I don’t blame them for not wanting to learn.
> > 
> > The whole industry, programming languages, infrastructure,
> > everything would have developed differently if relations were a
> > natural, pleasurable thing to use in any programming language. Like
> > an Array, or a Hash.
> > On Sep 13, 2021, 22:45 -0700, Hemil Ruparel <
> > hemilruparel2002@gmail.com>, wrote:
> > 
> > SQL is not the problem. Problem are the devs. I love SQL. I hate
> > orms. The problem with databases is people refuse to treat it as
> > the entity it is and want to use their beautiful OO system. Problem
> > is databases are not OO. We need to recognize that and treat
> > databases as databases.
> > 
> > All languages are fucking terrible.  There are thousands of the
> > them because some people bump into a feature they don't like and
> > run off an make another fucking terrible language.  For the love of
> > God, please don't be one of those people.  The rest of us find
> > languages we can abide and do productive things with using features
> > we like and avoiding those we don't.  I've always felt it was no
> > small miracle the vendors managed to agree to ODBC/JDBC driver
> > specs (even though the SQL language definition is "more like
> > guidelines").  Go scream at the DOM and JavaScript.
> 
> 

On Mon, 13 Sep 2021, Guyren Howe wrote:

> They are making a decent decision. SQL is a *fucking terrible* language,
> which I don’t blame them for not wanting to learn.

>> SQL is not the problem. Problem are the devs. I love SQL. I hate orms.
>> The problem with databases is people refuse to treat it as the entity it
>> is and want to use their beautiful OO system. Problem is databases are
>> not OO. We need to recognize that and treat databases as databases.

Guyren/Hemil,

As a non-SQL expert who's used postgres since 1997 I've come to believe the
basic issue is that SQL is based on sets, neither procedural or object
oriented. Few people think in sets so they try to fit SQL into what they
know rather than understand the how sets work.

Rich

On Tue, Sep 14, 2021 at 12:32 AM Guyren Howe <guyren@gmail.com> wrote:
> If I had $5 million to invest in a startup, I would hire as many of the core Postgres devs as I could to make a new
databasewith all the sophistication of Postgres but based on Datalog (or something similar). (Or maybe add Datalog to
Postgres).If that could get traction, it would lead in a decade to a revolution in productivity in our industry. 

I've long thought that there is more algebraic type syntax sitting
underneath SQL yearning to get out.  If you wanted to try something
like that today, a language pre-compiler or translator which converted
the code to SQL is likely the only realistic approach if you wanted to
get traction.  History is not very kind to these approaches though and
SQL is evolving and has huge investments behind it...much more than 5
million bucks.

ORMs a function of poor development culture and vendor advocacy, not
the fault of SQL. If developers don't understand or are unwilling to
use joins in language A, they won't in language B either.

merlin

On Tuesday, 14 September 2021 14:06:13 BST Merlin Moncure wrote:

> On Tue, Sep 14, 2021 at 12:32 AM Guyren Howe <guyren@gmail.com> wrote:

> > If I had $5 million to invest in a startup, I would hire as many of the

> > core Postgres devs as I could to make a new database with all the

> > sophistication of Postgres but based on Datalog (or something similar).

> > (Or maybe add Datalog to Postgres). If that could get traction, it would

> > lead in a decade to a revolution in productivity in our industry.

> I've long thought that there is more algebraic type syntax sitting

> underneath SQL yearning to get out.  If you wanted to try something

> like that today, a language pre-compiler or translator which converted

> the code to SQL is likely the only realistic approach if you wanted to

> get traction.  History is not very kind to these approaches though and

> SQL is evolving and has huge investments behind it...much more than 5

> million bucks.

>

> ORMs a function of poor development culture and vendor advocacy, not

> the fault of SQL. If developers don't understand or are unwilling to

> use joins in language A, they won't in language B either.

>

> merlin

Back in the day, within IBM there were two separate relational databases.  System-R (which came from San Hose) and PRTV (the Peterlee Relational Test vehicle).  As I understand it SQL came from System-R and the optimizer (amongst other things) came from PRTV.

PRTV (https://en.wikipedia.org/wiki/IBM_Peterlee_Relational_Test_Vehicle_(PRTV)) did not use SQL, and was never a released product, except with a graphical add-on which was sold to two UK local authorities for urban planning.

So there are (and always have been) different ways to send requests to a relational DB, it is just that SQL won the day.

> ORMs a function of poor development culture and vendor advocacy, not

> the fault of SQL. If developers don't understand or are unwilling to

> use joins in language A, they won't in language B either.

>

> merlin

Back in the day, within IBM there were two separate relational databases.  System-R (which came from San Hose) and PRTV (the Peterlee Relational Test vehicle).  As I understand it SQL came from System-R and the optimizer (amongst other things) came from PRTV.

PRTV (https://en.wikipedia.org/wiki/IBM_Peterlee_Relational_Test_Vehicle_(PRTV)) did not use SQL, and was never a released product, except with a graphical add-on which was sold to two UK local authorities for urban planning.

So there are (and always have been) different ways to send requests to a relational DB, it is just that SQL won the day.

On Tue, Sep 14, 2021 at 9:01 AM Rob Sargent <robjsargent@gmail.com> wrote:
> > ORMs a function of poor development culture and vendor advocacy, not
> > the fault of SQL. If developers don't understand or are unwilling to
> > use joins in language A, they won't in language B either.
>
> Back in the day, within IBM there were two separate relational databases.  System-R (which came from San Hose) and
PRTV(the Peterlee Relational Test vehicle).  As I understand it SQL came from System-R and the optimizer (amongst other
things)came from PRTV. 
>
> PRTV (https://en.wikipedia.org/wiki/IBM_Peterlee_Relational_Test_Vehicle_(PRTV)) did not use SQL, and was never a
releasedproduct, except with a graphical add-on which was sold to two UK local authorities for urban planning. 
>
> So there are (and always have been) different ways to send requests to a relational DB, it is just that SQL won the
day.
>
> Ah, lets not forget Mr Lane's favourite: quel

Sure, I quite like, er, liked quel also, being more mathematical and
formal.  It's a shame it didn't make the cut. This is however a
telling example that standardization trumps purity once languages hit
a certain spot.  There are many languages with dumb things that will
never get fixed :-).  As they say, 'the devil you know'.

QUEL also uses idiomatic english for most operations, which I guess is
probably a contributing factor for developer resistance to SQL, since
native speakers are a minority of the earth's population. Oh well.


merlin

I started programming in 1967, and over the last 50+ years I've programmed in more languages than I would want to list.  I spent a decade writing in FORTRAN on a GA 18/30 (essentially a clone of the IBM 1130) with limited memory space, so you had to write EFFICIENT code, something that is a bit of a lost art these days.  I also spent a decade writing in COBOL.

I've not found many tasks that I couldn't find a way to write in whatever language I had available to write it in.  There may be bad (or at least inefficient) languages, but there are lots of bad programmers.  

--

Mike Nolan

htfoot@gmail.com

I didn't start in 1967, but 1984, I'm in agreement with the bad programmers premise. Since the beginning there have always been

lots of languages. It is my opinion, the more languages and concepts you know the better your success on the project.

Heck I didn't use triggers till late 90's, funny thing I have a PICK project right now.. too much fun

I started programming in 1967, and over the last 50+ years I've programmed in more languages than I would want to list.  I spent a decade writing in FORTRAN on a GA 18/30 (essentially a clone of the IBM 1130) with limited memory space, so you had to write EFFICIENT code, something that is a bit of a lost art these days.  I also spent a decade writing in COBOL.

I've not found many tasks that I couldn't find a way to write in whatever language I had available to write it in.  There may be bad (or at least inefficient) languages, but there are lots of bad programmers.  

--

Mike Nolan

htfoot@gmail.com

This is a subject which is important to me, but I find discussing it
often goes poorly.  Most people (not necessarily those on this list)
don't distinguish between SQL and the relational model.  When you
criticize SQL the language, people tend to defend relational
databases; when you praise relational databases, people have a
visceral reaction to SQL.

There also seems to be a divide between people who use languages to
express their thoughts, with the expectation that their thoughts will
be implemented, and those who regard a language merely as an interface
for manipulating an underlying system.  I think there's a lot of good
to be said of workmen who get the job done without complaining about
their tools.  But in the big picture, it seems to me that all the
progress we've made with computers has been a matter of improving the
toolchain.  The CPU is, after all, an underlying system, and there's
nothing you couldn't get done with assembler (or just machine code).
If you were smart enough, and had enough time.

The problem is, tools tend to impose an "IQ tax":  thought spent on
managing the tool is thought not spent on solving the problem.  I tend
to be stingy about paying that; I'm not smart enough, and I don't have
enough time.

Merlin's point about algebraic syntax fits well, here.  Once you're
used to it,  (x ∩ y) imposes less of a cognitive load than SELECT *
FROM x INTERSECT SELECT * FROM y.  You can see how that scales, as
expressions get larger.  There's a reason we no longer try to make
programming languages look like English, or other natural languages,
however reasonable it might have seemed in the 1970s.

And then there are very simple things I can't say reasonably, like
"SELECT * EXCEPT col_3", or "Tell me how many nulls are in each
column."  Naturally, I can get these done; but the gap between what
was required to explain the goal and what is required to accomplish it
is much too large.

So, the affection I have for SQL is due to it being a gateway to a
great idea; my frustration is that it's a bottleneck in getting to
that same idea.

On Tue, Sep 14, 2021 at 12:20 PM Rob Sargent <robjsargent@gmail.com> wrote:
>
> On 9/14/21 10:10 AM, Michael Nolan wrote:
>
> I started programming in 1967, and over the last 50+ years I've programmed in more languages than I would want to
list. I spent a decade writing in FORTRAN on a GA 18/30 (essentially a clone of the IBM 1130) with limited memory
space,so you had to write EFFICIENT code, something that is a bit of a lost art these days.  I also spent a decade
writingin COBOL. 
>
> I've not found many tasks that I couldn't find a way to write in whatever language I had available to write it in.
Theremay be bad (or at least inefficient) languages, but there are lots of bad programmers. 
> --
> Mike Nolan
> htfoot@gmail.com
>
> OK, I'm maybe responsible for this thread turning into a diatribe.  I shouted at OP 'cause he shouted at us. My
mistake,and I apologize. 
> I'm probably closer to Mike's "bad programmers" than I would care to admit but fully believe software is a "people
problem"more than most of us realize. 



--
Ray Brinzer

On Tue, Sep 14, 2021 at 2:41 PM Brian Dunavant <dunavant@gmail.com> wrote:

> I have the opposite perspective.  As a dev/manager, SQL is much more powerful at getting data storage from abstract
concept,into a usable structure, than any programming language I've had the (mis)fortune of using.   I've long since
lostcount of the massive volume of other people's code (especially ORMs) I've removed and replaced by updating SQL
statementsto do all the logic, and return me exactly what I want.  And typically this also comes with a (sometimes
massive)performance gain. 
>
> I've managed many a programmer that would complain that SQL is too hard and they don't want to learn it, but had no
problemspending days learning the ORM of the month that "saves them time" and writing complex inscrutable monstrosities
withthem. 
>
> Could SQL be better?  Absolutely.  But in terms of bang-for-my-buck, I feel learning SQL has saved me more
clock-time,and improved my productivity/value probably more than any other individual language in my career. 

Your experience doesn't surprise me at all.  Sure; it's better than
the alternatives.  An ORM can be a net benefit if you're doing simple
things, but the more complex the query, the more it starts to feel
like you're trying to have a serious conversation through a bad
translator.  This encourages programmers to keep queries simple, treat
the database as a big scratchpad, and do all the processing in code.
This easily turns into "reinventing the wheel, badly".

I would argue, though, that the programmers aren't completely wrong.
A good programmer strives for clarity, expressing ideas simply and
naturally, and avoiding repetition; SQL isn't good for that.  But
papering over the problem on the software side isn't the solution.

I'd just emphasize our agreement:  SQL (or another query language,
sitting in the same niche) could be better.  So it should be.

--
Ray Brinzer

Replies in-line

They are making a decent decision. SQL is a *fucking terrible* language, which I don’t blame them for not wanting to learn.

Based on what criteria?

The whole industry, programming languages, infrastructure, everything would have developed differently if relations were a natural, pleasurable thing to use in any programming language. Like an Array, or a Hash.

Thee is nothing natural about either relations or arrays and hashes/dictionaries. Relations are pretty literal implementation of the basic set theory. Having a decent understanding of the basic set theory is a condition  for understanding SQL. Now, we can discuss whether a language implementing a mathematical theory is "good" or "bad", whatever the meaning of "good" or "bad" in the given context. Historically, SQL is a good fit for the banking business and accounting and that is why it is still around.

Another misconception about SQL is treating it as a general purpose programming language. SQL is data description language, nothing more, nothing less. It doesn't need loops, arrays, hashes or subroutines, its principal purpose is to describe a subset of data. Without SQL you would have to read all the data and filter the unnecessary stuff yourself. Furthermore, part of SQL are so called "ACID requirements". Transaction can only see the data that was committed before the transaction has begun. Implementing ACID takes a lot of code, that's what MVCC is all about. However, that too has its origins in accounting. You cannot pay the money you don't have. And the last thing about SQL is transaction management. Without relational databases and SQL, you would need a proprietary transaction manager, just like MongoDB. And MongoDB has a complex proprietary transaction manager and is losing market share. So, to recapitulate:

• Declarative subset definition
• ACID consistency
  
• Transaction management
• Ideal fit for accounting.

That is why SQL is still around. And that is why we all live in a yellow subroutine (this reference is not for the millennials or younger).

-- 
I'll speak the key, the whole key and nothing but the key, so help me Codd.
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

All languages are fucking terrible.

I like English. It's not very complex and it allows me to express myself very well. You should see my native tongue, Croatian language, from the group of Slavic languages. It's fucking terrible.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On 9/14/21 02:18, Rob Sargent wrote:

All languages are fucking terrible.

I like English. It's not very complex and it allows me to express myself very well. You should see my native tongue, Croatian language, from the group of Slavic languages. It's fucking terrible.

On Tue, Sep 14, 2021 at 3:58 PM Guyren Howe <guyren@gmail.com> wrote:
> You’re confusing SQL with the relational model. Datalog and Quel and Tutorial D and other database languages and
systemscan and did provide those features also. 

By analogy:  Arabic and Roman numerals both describe the natural
numbers.  Hence, they have the same mathematical properties.  Spending
a little time doing algebra with Roman numerals should convince
anyone, however, that how you express a concept matters a lot.

--
Ray Brinzer

> On Sep 14, 2021, at 11:10 AM, Michael Nolan <htfoot@gmail.com> wrote:
>
> I started programming in 1967, and over the last 50+ years I've programmed in more languages than I would want to
list. I spent a decade writing in FORTRAN on a GA 18/30 (essentially a clone of the IBM 1130) with limited memory
space,so you had to write EFFICIENT code, something that is a bit of a lost art these days.  I also spent a decade
writingin COBOL. 
>
> I've not found many tasks that I couldn't find a way to write in whatever language I had available to write it in.
Theremay be bad (or at least inefficient) languages, but there are lots of bad programmers.   

Yep, me too.  I would say that SQL has not achieved its design goals yet.  The original concept was to write what you
wantto achieve and have the server figure out the best way to get at it.   

What people hate about SQL is that the programmer has to optimize SQL to get acceptable performance.  And the
optimizationis different for every implementation.  I think SQL has not hit its stride yet.  When the common $1000
serverhas 1024+ CPUs and 1+TB memory, and SQL implementations have adopted good multithreading architecture with access
to1024+ CPU dedicated AI engines, etc. a lot of the crap associated with performant SQL will go away. 

At this point, I think it will be smart to strip out implementation details that have made it into the SQL syntax.
Therewill no longer be a need for it. This will make the SQL language simpler and easier to use, understand, and reason
about.  

Of course, that might not happen until my grandchildren are retired and in a nursing home.  But who knows, stranger
thingshave happened. 

Neil
www.fairwindsoft.com

On Tue, Sep 14, 2021 at 4:16 PM FWS Neil <neil@fairwindsoft.com> wrote:
> What people hate about SQL is that the programmer has to optimize SQL to get acceptable performance.

Oh, no, that's just one thing.  :-)

And to be fair, it's a hard problem.  We're asking for an optimizer,
written for databases generally, to out-perform an intelligent human
who knows one particular database well.  And we don't collect all the
data the optimizer might want, because of the performance or storage
costs to the collection (e.g. keeping accurate record totals by
performing a write operation on every insert).

In my daydreams, I sometimes think that making the query planner more
modular, and perhaps writing it in a higher-level language might be
good.  Usually, optimizing for fast performance will beat optimizing
for performance fast.  So it's a system you'd want to be able to tune
and improve easily.

> And the optimization is different for every implementation.  I think SQL has not hit its stride yet.  When the common
$1000server has 1024+ CPUs and 1+TB memory, and SQL implementations have adopted good multithreading architecture with
accessto 1024+ CPU dedicated AI engines, etc. a lot of the crap associated with performant SQL will go away. 

Yeah... I don't.  When a common server is 1000 times faster, people
will find reasons to do 1000 times as much with it.

> At this point, I think it will be smart to strip out implementation details that have made it into the SQL syntax.
Therewill no longer be a need for it. This will make the SQL language simpler and easier to use, understand, and reason
about.

A clean division between the query and the performance hints seems
like it'd be a big improvement.  Rather like moving presentation
details from HTML off into CSS.  Again, I don't see them going away,
though.

--
Ray Brinzer

On 9/14/21 16:07, Raymond Brinzer wrote:
> By analogy:  Arabic and Roman numerals both describe the natural
> numbers.  Hence, they have the same mathematical properties.  Spending
> a little time doing algebra with Roman numerals should convince
> anyone, however, that how you express a concept matters a lot.

Analogy is not an accepted logical method. I do agree that the style of 
expression matters. That is why we have literature. Saying "hey there" 
and "friends, Romans, countrymen, lend me your ears" can have the same 
meaning in the given context but the latter expression is much more 
poetic. As software engineers, we are very much opposed to poetry, 
especially those among us using Perl.

However, the stated purpose of the SQL language is to describe sets and 
subsets. It's supposed to be verbose. Storage extensions are database 
specific and are here for performance reasons.


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On 9/14/21 15:57, Guyren Howe wrote:
> Verbosity. Redundancy. Lack of orthogonality. Resemblance to English.

Verbosity is a feature, as well as the resemblance to English. The 
language is meant to be understood by accountants. Once upon a time 
people were using something called "COmmon Business Oriented Language" 
which was also very verbose, for the same reason: it had to be 
understandable to the business people. SQL is written by the people with 
the background in mathematics and is thus more understandable for the 
people with background in mathematics. I have no problem with SQL.


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On 2021-09-14 17:01:40 -0400, Mladen Gogala wrote:
> On 9/14/21 15:57, Guyren Howe wrote:
> > Verbosity. Redundancy. Lack of orthogonality. Resemblance to English.
>
> Verbosity is a feature, as well as the resemblance to English. The language
> is meant to be understood by accountants. Once upon a time people were using
> something called "COmmon Business Oriented Language" which was also very
> verbose, for the same reason: it had to be understandable to the business
> people.

Let's rephrase that: Back in the 1960s people thought programming would
be easier (programs easier to understand and to write) if the syntax
looked similar to English prose.

That belief was mistaken. While a very simple COBOL program may be
readable for a layperson (while even a very simple C program is not, and
a Haskell or APL program looks like complete gibberish), they still
can't write it, and even the readability advantage quickly vanishes for
more complex programs, because while COBOL may look like English, it
isn't.

SQL is significantly younger than COBOL but its design was led by the
same belief that making the language look like ordinary English would
make it easy to learn.

(It also wasn't the last. A few years back I saw a language for
specifying test cases designed for "ordinary people". Again, it looked
like English, but wasn't).

Superficial syntax is in my experience the smallest hurdle. It's
semantics that people struggle with.

        hp

PS: COBOL is still in use.

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 2021-09-14 16:51:39 -0400, Mladen Gogala wrote:
> As software engineers, we are very much opposed to poetry, especially
> those among us using Perl.

When I learned Perl, Perl poetry was a real thing!

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 15/09/21 04:10, Michael Nolan wrote:
> I started programming in 1967, and over the last 50+ years I've 
> programmed in more languages than I would want to list.  I spent a 
> decade writing in FORTRAN on a GA 18/30 (essentially a clone of the 
> IBM 1130) with limited memory space, so you had to write EFFICIENT 
> code, something that is a bit of a lost art these days.  I also spent 
> a decade writing in COBOL.
>
> I've not found many tasks that I couldn't find a way to write in 
> whatever language I had available to write it in. There may be bad (or 
> at least inefficient) languages, but there are lots of bad programmers.
> --
> Mike Nolan
> htfoot@gmail.com

I remember programming in FORTRAN IV on an IBM 1130 at Auckland 
University.  My first attempt to explore Pythagorean triples was written 
in FORTRAN on that machine.  Finally had a useful program written in 
Java about 30 years later.  There are 4 triples starting with 60 that 
satisfy A*2 + B^2 + C^2 where A < B < C and the numbers are mutually 
prime. I was able to handle values of A up to the size of long, so I got 
some pretty big numbers for B & C.  Java's BigInteger class has its uses!

On the IBM 1130 it was faster to use X * X to find the square of a value 
than to use the power notation (of which I've forgotten the syntax).

And for my many sins, I spent years programming in COBOL.

I've written code in over 30 languages.  Probably had most fun writing a 
couple of trivial programs in ARM2/3 assembler -- all instructions 
except one are conditional.

There is no one perfect language, despite what some people might insist!


Cheers,
Gavin

On 15/09/21 10:30, Peter J. Holzer wrote:
> On 2021-09-14 16:51:39 -0400, Mladen Gogala wrote:
>> As software engineers, we are very much opposed to poetry, especially
>> those among us using Perl.
> When I learned Perl, Perl poetry was a real thing!
>
>          hp
>
Perl is too verbose, use APL!  See: https://tryapl.org/

To be honest, I've looked at APL but never programmed in it.


Cheers,
Gavin

So, on a practical note:  I'd like it if PostgreSQL supported
alternate languages for queries, as it does for stored procedures.

Yes, I know this will strike some of you as an abomination.  I think
we can take that part as read.  ;-)

I see two ways of going about this.  The quick & dirty way would be to
conditionally hand off the incoming code to a pre-processor, which
would return SQL to be passed along to the rest of the parser.  You'd
just add a few lines to parser.c, along the lines of:

#ifdef ALTERNATE_QUERY_LANGUAGE
    str = preprocess_the_code(str);
#endif

The rest would be defined outside the existing code.  I actually
experimented with this a few years ago, embedding some Chicken Scheme
into PostgreSQL, and it seemed to work reasonably well.  Basically, I
looked to see if the incoming query started with "(" and if it didn't,
I just returned the string unaltered.  If it did, I parsed as
s-expressions.

The "right", and more flexible way, would be to actually generate your
own parse tree, using the same nodes the native parser does.  I'm
sorry to say I didn't stick with that to the point of getting anything
working.  One encouraging thing, however is the fact that the parser
is mostly isolated from the rest of the system; if it was highly
integrated, it would be much harder.  Although gram.y does hedge a bit
on this:

"In general, nothing in this file should initiate database accesses".

Anyway, one way or the other, I think it could be done.

On Tue, Sep 14, 2021 at 1:32 AM Guyren Howe <guyren@gmail.com> wrote:
>
> A fun philosophical discussion.
>
> I am no fan of “worse is better”, and particularly its poster child, SQL.
>
> The world’s economic output would be substantially higher (5%?) if our industry had settled on almost anything other
thanSQL for relational databases. 
>
> So much of the design of *almost everything* in our industry is a reaction to SQL. ORMs fucking *everywhere* so you
don’thave to use SQL. Bad query and database design. Inefficient system designs that use ORMs rather than relations.
NoSQLdatabases. Countless hours on hours of developer time trying to work out how to write something in SQL that would
betrivial in, say, Datalog. 
>
> If I had $5 million to invest in a startup, I would hire as many of the core Postgres devs as I could to make a new
databasewith all the sophistication of Postgres but based on Datalog (or something similar). (Or maybe add Datalog to
Postgres).If that could get traction, it would lead in a decade to a revolution in productivity in our industry. 



--
Ray Brinzer

On 9/14/21 12:51 PM, Mladen Gogala wrote:
> Replies in-line
> 
> On 9/14/21 01:51, Guyren Howe wrote:
>> They are making a decent decision. SQL is a *fucking terrible* 
>> language, which I don’t blame them for not wanting to learn.
> 
> Based on what criteria?
> 
> 
>>
>> The whole industry, programming languages, infrastructure, everything 
>> would have developed differently if relations were a natural, 
>> pleasurable thing to use in any programming language. Like an Array, 
>> or a Hash.
> 
> Thee is nothing natural about either relations or arrays and 
> hashes/dictionaries. Relations are pretty literal implementation of the 
> basic set theory. Having a decent understanding of the basic set theory 
> is a condition  for understanding SQL. Now, we can discuss whether a 
> language implementing a mathematical theory is "good" or "bad", whatever 
> the meaning of "good" or "bad" in the given context. Historically, SQL 
> is a good fit for the banking business and accounting and that is why it 
> is still around.
> 

I can see what you are saying. Still as someone that comes from a 
biology background and a basic understanding of math I will say SQL has 
a broader appeal. All those years of memorizing organism classifications 
inadvertently led me to set theory; 'all dogs are animals, not all 
animals are dogs'. Also, time spent identifying plants/critters via 
dichotomous keys led me to boolean logic. The upshot is that once I got 
involved with SQL databases the basics made sense. The details I am 
still learning.

> 
> -- 
> I'll speak the key, the whole key and nothing but the key, so help me Codd.
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi Michael,

Appropriate comments interspersed below.

I'm happy writing SQL and moderately competent using it.  But like all 
the languages I've used, without exception, it has its pain points.


Cheers,
Gavin

On 15/09/21 11:25, Michael Nolan wrote:
> Of all the languages I wrote in, I think SNOBOL was the most fun to 
> write in, and LISP the least fun.  Control Data

I once read the first 40 pages of a SNOBOL manuel, but unfortunately 
never got the opportunity to try it out.


> assembler language
> programming was probably the most precise, because you could crash the 
> OS with a single mis-placed character, something I did more than once.

I knew a senior tech programmer who inadvertently tried to rewind a disc 
back to BT and switch to 800 BPI, fortunately only his program crashed.

Another time the memory protection was disabled (ICL no longer had the 
capacity to fix it) on our ICL 4/50 MainFrame and someone wrote a 
program to write 'J' into an area of memory.  That was legitimate, but 
it had a bug which caused it to write into more memory than it should 
have...  The machine crashed.  Our ICL 4/50 was the last surviving 
operational machine of its type in the world.

Our main machines were two ICL 4/72's each having a single fast 2MHz 
processor and a massive 1 MB of core memory with a battery of big 
exchangeable disks each with a whopping 60 MB of capacity & tape drives 
for 12" reels.


>
> In a graduate-level course, we studied ALGOL-68, which had so many 
> things in it that I'm not sure anybody ever actually implemented the 
> full language.  (But then again, has anybody implemented EVERYTHING in 
> the SQL standard?)

I learnt ALGOL-68 from a manual written in Backus-Naur notation on my 
own initiative.  Tried to write a simple statistics program, never 
finished it.  That was before I really understood the value of rigorous 
indenting standards.


>
> COBOL has strange verbs like 'move corresponding' that could 
> accomplish complicated tasks in a few lines but you have to be careful 
> that you knew what you were asking for!

In our site that was banned as being too dangerous.

And how about the 'lovely' ALTER GOTO construct???

Children don't try to use these constructs at home, as even experienced 
adults get burnt using them!


>
> And I'd take the SQL standard over the CODASYL standard any time!
> --

Agreed!


> Mike Nolan

Rich Shepard said on Tue, 14 Sep 2021 05:49:07 -0700 (PDT)

>On Mon, 13 Sep 2021, Guyren Howe wrote:
>
>> They are making a decent decision. SQL is a *fucking terrible*
>> language, which I don’t blame them for not wanting to learn.
>
>>> SQL is not the problem. Problem are the devs. I love SQL. I hate
>>> orms. The problem with databases is people refuse to treat it as
>>> the entity it is and want to use their beautiful OO system. Problem
>>> is databases are not OO. We need to recognize that and treat
>>> databases as databases.
>
>Guyren/Hemil,
>
>As a non-SQL expert who's used postgres since 1997 I've come to
>believe the basic issue is that SQL is based on sets, neither
>procedural or object oriented. Few people think in sets so they try to
>fit SQL into what they know rather than understand the how sets work.

Rich, could you please elaborate on SQL queries being based on sets? I
never thought of it that way, and would like to hear your related
thoughts.

SteveT

Steve Litt
Spring 2021 featured book: Troubleshooting Techniques of the Successful
Technologist http://www.troubleshooters.com/techniques

On Wed, Sep 15, 2021 at 8:31 AM Raymond Brinzer <ray.brinzer@gmail.com> wrote:
>
> So, on a practical note:  I'd like it if PostgreSQL supported
> alternate languages for queries, as it does for stored procedures.

I agree, and actually sent a patch some time ago to allow usage of
third-party parser(s).  They can coexist with the core one, meaning
that you can (if you write your parser this way) use both languages,
even in a multi-query string.  See
https://commitfest.postgresql.org/34/3100/ for more details.

That's arguably not enough to efficiently handle things like oracle
style sql-89 outer joins as there's still no way to hook into the
various transform* functions, but that's a start.

On Wed, 15 Sep 2021, Steve Litt wrote:

> Rich, could you please elaborate on SQL queries being based on sets? I
> never thought of it that way, and would like to hear your related
> thoughts.

SteveT,

In the 1980s, when there were computer magazines such as Byte and Database
Administrator (among many others), Joe Celko wrote a monthly database column
in one (or more, I forget these details). This lead me to buy and read his
books, including 'SQL for Smarties' and its many editions, 'SQL
Programming', and 'Thinking in Sets: Auxilliary, Temporal, and Virtual
Tables in SQL'.

I reccomend his books to answer your questions and give you a sound
foundation.

Regards,

Rich

On Wed, Sep 15, 2021 at 12:55 AM Steve Litt <slitt@troubleshooters.com> wrote:
> Rich, could you please elaborate on SQL queries being based on sets? I
> never thought of it that way, and would like to hear your related
> thoughts.

I'll take a crack at this.  Going through the setup will require a
little patience, but I think the underlying idea is neat enough to be
worth it.

A set is an unordered collection of unique elements.  This means:

{ 1, 2, 3 } = { 3, 2, 1 }
{ 2, 3, 3 } is not a set.

(The elements don't have to be numbers, but I'm using them for convenience.)

Take two sets, A and B:

A = { 1, 2, 3 }
B = { 4, 5, 6 }

The Cartesian product A x B is the complete set of ordered pairs (a,
b) you can make from them:

(1, 4)
(1, 5)
(1, 6)
(2, 4)
(2, 5)
(2, 6)
(3, 4)
(3, 5)
(3, 6)

These pairs are called tuples.  Tuples don't have to be pairs; if you
were using A x B x C, for instance, each one would have three
elements.

A relation is a subset of the Cartesian product of the sets.  For instance:

(1, 4)
(2, 5)
(3, 6)

(In math, some relations are functions; that one happens to be a
function which follows the rule f(a) = a + 3.  But that's not really a
database matter.)

Anyway, now: for "relation", say "table", and for "tuple", say "row".
This is (in theory) is what a relational database is about: storing
relations.

In math, tuples are ordered, but with databases we give the elements
names, instead:

(a: 1, b: 4)

It doesn't really matter, because the names uniquely identify the
elements, just as ordering does.  You can go back and forth between
orders and names, so the different representations have the same
structure.

So, let's say you do this:

CREATE TABLE whatever (a INTEGER, b STRING, UNIQUE(a,b));

What you are saying, in a sense, is:  "Consider the set of tuples
which pair every possible integer with every possible string.  I'm
going to be storing a subset of that."

What's interesting about this is:  because you're working with sets,
all the operations you can perform on sets work here.  Tables X and Y
are both sets of tuples, so you can find their cartesian product,
intersection, union, and so forth.  Proofs about sets apply, and so
on.

That is, if the tables are actually relations... which means they have
to be sets.  This is where things get controversial.

SQL allows tables to have duplicate records.  It also has NULL, which
allows duplicate records even in a table like the one above.  Although
we declared UNIQUE(a,b), we can still say:

INSERT INTO whatever VALUES (NULL,NULL), (NULL,NULL);

So, arguably SQL isn't actually relational.  To some, that's a matter
of it being practical, so that people can actually get work done.  To
others, it's a travesty which robs databases of power and clarity.

Anyway, that's my summary.

-- 
Ray Brinzer

Gavin Flower said on Wed, 15 Sep 2021 13:49:39 +1200

>Hi Michael,

[snip]

>>
>> COBOL has strange verbs like 'move corresponding' that could 
>> accomplish complicated tasks in a few lines but you have to be
>> careful that you knew what you were asking for!  
>
>In our site that was banned as being too dangerous.
>
>And how about the 'lovely' ALTER GOTO construct???
>
>Children don't try to use these constructs at home, as even
>experienced adults get burnt using them!

I never Cobolled professionally, but took 3 semesters of Cobol and
Santa Monica Community College in Santa Monica, California USA. They
taught us move corresponding, I used it, it was handy. I'd use it again
if I were a Cobol professional.

As far as alter, in 1981, before I became a programmer, I asked my
Cobol Programmer friend if there was anything you could put in a
program that would get you fired. He said yes, the alter statement :-).
In my 3 semesters of Cobol, I never once used the Alter statement.

SteveT

Steve Litt 
Spring 2021 featured book: Troubleshooting Techniques of the Successful
Technologist http://www.troubleshooters.com/techniques

On 9/15/21 11:52 AM, Steve Litt wrote:
> Gavin Flower said on Wed, 15 Sep 2021 13:49:39 +1200
>
>> Hi Michael,
> [snip]
>
>>> COBOL has strange verbs like 'move corresponding' that could
>>> accomplish complicated tasks in a few lines but you have to be
>>> careful that you knew what you were asking for!

MOVE CORRESPONDING is awesome, and a heck of a lot less dangerous than doing 
your own memory management in C!!

>>> In our site that was banned as being too dangerous.
>>>
>>> And how about the 'lovely' ALTER GOTO construct???
>>>
>>> Children don't try to use these constructs at home, as even
>>> experienced adults get burnt using them!
> I never Cobolled professionally, but took 3 semesters of Cobol and
> Santa Monica Community College in Santa Monica, California USA. They
> taught us move corresponding, I used it, it was handy. I'd use it again
> if I were a Cobol professional.
>
> As far as alter, in 1981, before I became a programmer, I asked my
> Cobol Programmer friend if there was anything you could put in a
> program that would get you fired. He said yes, the alter statement :-).
> In my 3 semesters of Cobol, I never once used the Alter statement.

Jump tables are great in scripting languages which let you embed a variable 
in a variable name.  Naturally, you must be careful; explicit variable names 
always help...


-- 
Angular momentum makes the world go 'round.

On Tue, Sep 14, 2021 at 3:16 PM FWS Neil <neil@fairwindsoft.com> wrote:
>
> > On Sep 14, 2021, at 11:10 AM, Michael Nolan <htfoot@gmail.com> wrote:
> >
> > I started programming in 1967, and over the last 50+ years I've programmed in more languages than I would want to
list. I spent a decade writing in FORTRAN on a GA 18/30 (essentially a clone of the IBM 1130) with limited memory
space,so you had to write EFFICIENT code, something that is a bit of a lost art these days.  I also spent a decade
writingin COBOL. 
> >
> > I've not found many tasks that I couldn't find a way to write in whatever language I had available to write it in.
Theremay be bad (or at least inefficient) languages, but there are lots of bad programmers. 
>
> Yep, me too.  I would say that SQL has not achieved its design goals yet.  The original concept was to write what you
wantto achieve and have the server figure out the best way to get at it. 
>
> What people hate about SQL is that the programmer has to optimize SQL to get acceptable performance.  And the
optimizationis different for every implementation.  I think SQL has not hit its stride yet.  When the common $1000
serverhas 1024+ CPUs and 1+TB memory, and SQL implementations have adopted good multithreading architecture with access
to1024+ CPU dedicated AI engines, etc. a lot of the crap associated with performant SQL will go away. 
>
> At this point, I think it will be smart to strip out implementation details that have made it into the SQL syntax.
Therewill no longer be a need for it. This will make the SQL language simpler and easier to use, understand, and reason
about.

I think you ought to recognize that many people on this list make
money directly from managing that complexity :-).

Processing time remains bounded, and for complex queries how much time
is spend executing vs planning is a very difficult tradeoff.  Just
like in math, there are many ways to describe the same thing, and it
is for the clever to pick the best ones that run efficiently and well.
And thank goodness for that; it sends my kids to college.

Relating to your other point relating to implementations, it's not
different than having different browsers that render html and execute
javascript.  Having several high quality implementations to choose
from is healthy and good, and allows choice best on cost and feature
needs.  I would personally be much more inclined to gripe about
implementations that do not support standard syntax or have other
major standards issues (SQL server, I'm looking at you).

merlin

On 16/09/21 05:47, Michael Nolan wrote:
> When I was working at the help desk at the computer center as an 
> undergrad, the professor in charge of that group used to give us 
> interesting little language tests for things we needed to watch out 
> for, especially with beginning programmers.
>
> One of his favorite ploys was to use the EQUIVALENCE function in 
> FORTRAN to equivalence a constant with a variable, then assign 
> something to that variable.  In one of the FORTRAN compilers, that 
> would result in overwriting the constant, so all future uses of it 
> would have the new value.  This would break many things, of course.
> --
> Mike Nolan
>
>
On the IBM 1130 we were warned not to assign a value to a number, like

     3 = 7

if we did then apparently

     x = 6 * 3

would assign the value of 42 to x.

Never tried it, I now wish I had!


Cheers,
Gavin

On 9/15/21 5:30 PM, FWS Neil wrote:
> 
> 
>> On Sep 15, 2021, at 2:44 PM, Merlin Moncure <mmoncure@gmail.com 
>> <mailto:mmoncure@gmail.com>> wrote:
>>

>> I think you ought to recognize that many people on this list make
>> money directly from managing that complexity :-).
>>
> 
> I did not intend to disparage anyone.  People, including myself, make 
> money when they provide value and there is certainly value here.
> 
> But, I am not sure I understand your inference.  Are you saying (and I 
> am not implying you are) that PostgreSQL does not progress in line with 
> the original SQL goals of simplifying data access because people are 
> making money off of the current complexity?
> 

I'm going to say Merlin was being one part sarcastic, one part saying 
people may not want to bite the hand that feeds them.

As to SQL, if Postgres wants to maintain it's goal of hewing to the SQL 
standard then what progress it came make is determined by the SQL 
standards committee.

> Neil
> www.fairwindsoft.com <http://www.fairwindsoft.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 16 Sep 2021, at 9:31, Gavin Flower wrote:

> would assign the value of 42 to x.
>
Which brings up another topic, e.g.,
https://news.mit.edu/2019/answer-life-universe-and-everything-sum-three-cubes-mathematics-0910

>  Never tried it, I now wish I had!
>
You could see if it’s accurately emulated, e.g., http://ibm1130.org/emu/

What is truly amazing about old style FORTRAN is that it has a theological aspect.

What other computer language can give truth to a maxim such as:
    IN FORTRAN GOD IS REAL

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem —
neat,plausible, and wrong. 
— H. L. Mencken, 1920

Em 15/09/2021 21:55, Adrian Klaver escreveu:
> On 9/15/21 5:30 PM, FWS Neil wrote:
>>
>>
>>> On Sep 15, 2021, at 2:44 PM, Merlin Moncure <mmoncure@gmail.com
>>> <mailto:mmoncure@gmail.com>> wrote:
>>>
>
>>> I think you ought to recognize that many people on this list make
>>> money directly from managing that complexity :-).
>>>
>>
>> I did not intend to disparage anyone.  People, including myself, make
>> money when they provide value and there is certainly value here.
>>
>> But, I am not sure I understand your inference.  Are you saying (and
>> I am not implying you are) that PostgreSQL does not progress in line
>> with the original SQL goals of simplifying data access because people
>> are making money off of the current complexity?
>>
>
> I'm going to say Merlin was being one part sarcastic, one part saying
> people may not want to bite the hand that feeds them.
>
> As to SQL, if Postgres wants to maintain it's goal of hewing to the
> SQL standard then what progress it came make is determined by the SQL
> standards committee.


I love the fact that PostgreSQL keep in sync with SQL standard. I love
SQL as it is (and I came from a world with Cobol, Clipper and Dbase, and
other 4GL etc tools).

Also, I teach SQL to my junior employees, and they love it once they
fully compreehends - even stop defending the NoSQL hype.

At other side, I would see no objection if someone else would implement
another language on top of PostgreSQL engine.


Just my 2c.

Regards,

Edson

On Wed, Sep 15, 2021 at 7:31 PM FWS Neil <neil@fairwindsoft.com> wrote:
> On Sep 15, 2021, at 2:44 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> > I think you ought to recognize that many people on this list make
> > money directly from managing that complexity :-).
>
> I did not intend to disparage anyone.  People, including myself, make money when they provide value and there is
certainlyvalue here. 
>
> But, I am not sure I understand your inference.  Are you saying (and I am not implying you are) that PostgreSQL does
notprogress in line with the original SQL goals of simplifying data access because people are making money off of the
currentcomplexity? 

Not at all.  I'm saying that this mailing list is stuffed with people
that work on and with the database, and SQL is in many cases
(including mine) a passion.   Postgres is a technical marvel so that
we make money by utilizing it effectively, and rendering, er,
constructive feedback to its stewards so that it may improve and we
may all become more efficient.

Points made upthread suggesting SQL is bad due to being based on sets
were ironic IMO.  SQL is successful and enduring precisely because you
can direct operations by describing data relationships rather than
implement specific operations by hand.  Fluency in that technique
leads to solution achievement that other technologies cannot approach.
It rewards artistry and creativity, which I guess rubs some people the
wrong way since it does not align to factory development strategies.

No offense taken; I enjoy the debate; this thread is an indulgence,
being a bit of time off from capricious C suite executives, agile
methodology zealots, etc. So fire away.

merlin

On 17/09/21 04:26, Michael Nolan wrote:
> In the same 1971 seminar where we studied Algol-68, we had to read and 
> write a short paper on the 1970 Codd paper on relational  theory, 
> which had only been out for about a year.  The professor running the 
> seminar noted that Codd proved that the relational model worked, but 
> didn't guarantee that it worked efficiently enough to be implementable 
> without immense computer resources, which of course became available.
> --
> Mike Nolan


Developing an effective optimizer might have helped ...


Cheers,
Gavin

On 16/09/21 04:52, Steve Litt wrote:
> Gavin Flower said on Wed, 15 Sep 2021 13:49:39 +1200
>
>> Hi Michael,
> [snip]
>
>>> COBOL has strange verbs like 'move corresponding' that could
>>> accomplish complicated tasks in a few lines but you have to be
>>> careful that you knew what you were asking for!
>> In our site that was banned as being too dangerous.
>>
>> And how about the 'lovely' ALTER GOTO construct???
>>
>> Children don't try to use these constructs at home, as even
>> experienced adults get burnt using them!
> I never Cobolled professionally, but took 3 semesters of Cobol and
> Santa Monica Community College in Santa Monica, California USA. They
> taught us move corresponding, I used it, it was handy. I'd use it again
> if I were a Cobol professional.
>
> As far as alter, in 1981, before I became a programmer, I asked my
> Cobol Programmer friend if there was anything you could put in a
> program that would get you fired. He said yes, the alter statement :-).
> In my 3 semesters of Cobol, I never once used the Alter statement.

[...]

I was very proud of using an ALTER GOTO in my first program in my 8 week 
COBOL training course.  Now I cringe every time I think about it!

Before the course, I was fluent in FORTRAN IV and had programmed in 
ALGOL-68.  So I was an experienced programmer.


Cheers,
Gavin



Cheers,
Gavin

As far as alter, in 1981, before I became a programmer, I asked my
>> Cobol Programmer friend if there was anything you could put in a
>> program that would get you fired. He said yes, the alter statement :-).
>> In my 3 semesters of Cobol, I never once used the Alter statement.
>
> [...]
>
> I was very proud of using an ALTER GOTO in my first program in my 8 
> week COBOL training course.  Now I cringe every time I think about it!
>
> Before the course, I was fluent in FORTRAN IV and had programmed in 
> ALGOL-68.  So I was an experienced programmer.
>
>
In what I see now as truly prescient of the early 80's U of BC CS 
department, algol-68, snobol, cobol, B, APL and half a dozen others I've 
complete forgoten each got a week with the same Towers of Hanoi task. I 
can only assume the powers saw the impending dump-heap relegation of the 
lot of them. The counter argument was that the major project was done PL/1.

Missing my original point here. The set theory is the _point_. SQL is a gargantuan distraction from using it efficiently. 

Imagine if COBOL was the only widely-available programming language with functions. You might use it, because functions are really great abstraction for programming. That wouldn’t mean that COBOL wasn’t an utterly awful language.

SQL is like that, only for relations, sets and logic.

On 9/16/21 19:29, Guyren Howe wrote:
> Missing my original point here. The set theory is the _point_. SQL is 
> a gargantuan distraction from using it efficiently.
>
> Imagine if COBOL was the only widely-available programming language 
> with functions. You might use it, because functions are really great 
> abstraction for programming. That wouldn’t mean that COBOL wasn’t an 
> utterly awful language.
>
> SQL is like that, only for relations, sets and logic.


I am probably a bit biased because I am using SQL for a few weeks now. I 
was the first person certified for teaching Oracle 6 internals in the 
EMEA region and I was a certified Oracle 5.1.22 DBA on VAX/VMS. So, by 
using SQL for several weeks now, I grew attached to it and I like it. My 
second problem is my mathematical background. I have a Bsc in 
mathematics and was quite well acquainted not only with the basic set 
theory but also with things like Zermelo's  axiom of choice, Zorn's 
lemma and well ordering theorem. I am still quite familiar with the 
relational algebra, unions, intersections, Cartesian products, 
equivalence relations and alike. I think that SQL represents relational 
algebra quite well. It must be doing something right, because it lasts 
for so long. After all, it was created by a mathematician. There is a 
famous quote from the move "Chinatown" saying that politicians, ugly 
buildings, and whores all get respectable if they last long enough. The 
same applies to computer languages. I love the smell of SQL in the morning.


-- 
I'll speak the key, the whole key and nothing but the key, so help me Codd.
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On 17/09/21 11:22, Rob Sargent wrote:
> As far as alter, in 1981, before I became a programmer, I asked my
>>> Cobol Programmer friend if there was anything you could put in a
>>> program that would get you fired. He said yes, the alter statement :-).
>>> In my 3 semesters of Cobol, I never once used the Alter statement.
>>
>> [...]
>>
>> I was very proud of using an ALTER GOTO in my first program in my 8 
>> week COBOL training course.  Now I cringe every time I think about it!
>>
>> Before the course, I was fluent in FORTRAN IV and had programmed in 
>> ALGOL-68.  So I was an experienced programmer.
>>
>>
> In what I see now as truly prescient of the early 80's U of BC CS 
> department, algol-68, snobol, cobol, B, APL and half a dozen others 
> I've complete forgoten each got a week with the same Towers of Hanoi 
> task. I can only assume the powers saw the impending dump-heap 
> relegation of the lot of them. The counter argument was that the major 
> project was done PL/1.
>
>
>
I've seen some documentation for all those languages except B, but I 
never programmed in any of: SNOBOL, B, nor APL.  I was sent on a 3 day 
PL/1 course, but never used it 'in anger'.

I once tried to discuss general programming ideas with a colleague, and 
started talking about a section of code.  They then proceeded to tell me 
the definition of SECTION in COBOL in detail, I didn't bother to tell 
them that I knew 3 things about SECTIONs in COBOL that they hadn't 
mentioned.  I gave up the discussion, saw no point in continuing, and I 
didn't want to appear patronising.

Until one has programmed in several languages, preferably of different 
types, it is difficult to separate out programming concepts from the 
philosophy and implementation of individual languages.

I think any Master Programmer should have fluency in at least 3 
languages and familiarity with at least 3 more.  So I think at least 3 
languages should be taught to CS students.  And of course the importance 
of how to RTFM effectively!

Once I helped a colleague debug a Smalltalk example based on a magazine 
article I'd read 2 years earlier.  I'm certain they implicitly assumed 
that I was for more experienced in Smalltalk than I was!  Perhaps I 
should ask her, but she's probably forgotten -- that was about 30 years 
ago.  Now I've been married to her for 25 years,

Another time I helped someone develop a screen saver in Visual Basic.  
Though it was many years later before I realized the name of the 
language.  I'd never seen VB before.  I even explained the OnEvent 
concept even though it was totally new to me, but I still successfully 
helped them to implement at least one such construct in the screen 
saver.  Having experience with a wide variety of different programming 
languages was a huge advantage.


Cheers,
Gavin

On 17/09/21 11:29, Guyren Howe wrote:
[...]
> The set theory is the _point_. SQL is a gargantuan distraction from 
> using it efficiently.
>
> Imagine if COBOL was the only widely-available programming language 
> with functions. You might use it, because functions are really great 
> abstraction for programming. That wouldn’t mean that COBOL wasn’t an 
> utterly awful language.
>
> SQL is like that, only for relations, sets and logic.


COBOL is horrible, but SQL is quite a neat language, though it has its 
issues.

Am happy to write SQL, but not that keen on getting back into COBOL!

SQL is far superior to COBOL, in their respective use cases.


Cheers,
Gavin

On 9/16/21 3:21 PM, Gavin Flower wrote:
> On 17/09/21 04:26, Michael Nolan wrote:
>> In the same 1971 seminar where we studied Algol-68, we had to read and 
>> write a short paper on the 1970 Codd paper on relational  theory, which 
>> had only been out for about a year.  The professor running the seminar 
>> noted that Codd proved that the relational model worked, but didn't 
>> guarantee that it worked efficiently enough to be implementable without 
>> immense computer resources, which of course became available.
>> -- 
>> Mike Nolan
>
>
> Developing an effective optimizer might have helped ...

Just a Simple Matter of Engineering... /s

-- 
Angular momentum makes the world go 'round.

Missing my original point here. The set theory is the _point_. SQL is a gargantuan distraction from using it efficiently. 

Imagine if COBOL was the only widely-available programming language with functions. You might use it, because functions are really great abstraction for programming. That wouldn’t mean that COBOL wasn’t an utterly awful language.

On Wed, Sep 15, 2021 at 2:46 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
> I agree, and actually sent a patch some time ago to allow usage of
> third-party parser(s).  They can coexist with the core one, meaning
> that you can (if you write your parser this way) use both languages,
> even in a multi-query string.  See
> https://commitfest.postgresql.org/34/3100/ for more details.

Bravo!  I look forward to reading the code when I have time; seems
like it will be a nice cheat sheet for the relevant internals,
particularly as you say that you commented thoroughly.

--
Ray Brinzer

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.  That's not the case here:

SELECT col_3, col_1, col_5 FROM sometable WHERE col_3 LIKE 'Robert%'
ORDER BY col_3, col_1, col_5;

Now, if this sort of thing suits the way you think, I say, "Great!"
I'm glad you have a language which suits you.  For me, it's too rigid;
it assumes too much about what I might want to say.  I wouldn't
program in a language like this, or use a shell like this.  I don't
want to write database queries like this.  I do, because it's how I
get to talk to the awesome toy in the background, but it always
chafes.

-- 
Ray Brinzer

On Fri, Sep 17, 2021 at 7:49 AM Raymond Brinzer <ray.brinzer@gmail.com> wrote:
> Now, if this sort of thing suits the way you think, I say, "Great!"
> I'm glad you have a language which suits you.

Reading this over, I realized I should have been more clear:  I mean
"you" generally.  I liked your comment about algebraic syntax; just
giving my take on it, not attributing anything to you.

-- 
Ray Brinzer

On Fri, Sep 17, 2021 at 06:49 Raymond Brinzer <ray.brinzer@gmail.com> 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.
...
> Now, if this sort of thing suits the way you think, I say, "Great!"
> I'm glad you have a language which suits you.  For me, it's too rigid;
> it assumes too much about what I might want to say.  I wouldn't
> program in a language like this, or use a shell like this.  I don't
> want to write database queries like this.  I do, because it's how I
> get to talk to the awesome toy in the background, but it always
> chafes.

This thread strikes home because I've long used my favorite language,
Raku (and Perl before that) as a powerful glue language to generate
code in several languages I was forced to use and maintain during my
working years including FORTRAN, C, C++, PostScript, and SQL. I still
generate a lot of PostScript, but Raku has made it *much* easier.

Most recently I've used Raku modules for both ORM and procedural
interfaces to PostgreSQL, but with Raku's powerful grammar capability
a dedicated user can write his own language interface if he wishes.
In addition, Raku has a native C and C++ interface to ease using
PostgreSQL compiled code when necessary.

Best regards,

-Tom

P.S. See <https://raku.org> as a starting place for Raku.

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