Wow this just highlights why I absolutely hate working with raw SQL be it Postgres or MSSQL.
One of the reasons I started working on the PGObject Perl framework[1] was because I found that if I hand-coded the SQL and programmed around that, I was more productive, and had fewer bugs.
The approach taken (based on our experience with LedgerSMB) is to focus on rules for mapping object oriented calls to stored procedure calls. The framework is of course PostgreSQL-only (and relies a lot on system catalog lookups).
My experience, backing what you are saying, is that if you know what you are doing, and you write good, maintainable stored procedures/UDF's then the power is totally worth it. Unfortunately, there are still some obstacles, and most of these are tooling.
I think the hatred of SQL from the parent (in the context of this quiz) is that if you separate performance from result, as SQL does, then you have to think about performance differently. In theory you code for results first and then tune performance later. In practice, this only gets you so far and you need to code with some knowledge of performance limitations. I know I have been bitten as badly as anyone else.
But, the tradeoff is that when I write more of my code into my queries, I get better performance, fewer bugs, and a significantly smaller codebase. The knowledge is worth it warts and all.
(When I have some really nice code samples, I will submit to HN)
[1] The official repos are the repos with names starting with "PGObject" at https://github.com/ledgersmb
I'll let my ORM generate basic CRUD statements; the kind of statement which typically only involves PK lookups. But I find it better to write my own SQL statements for important queries, and of course let the ORM map the result set into objects. In fact, with enough experience, I've also found it _faster_ to just write a query in SQL versus learning yet another DSL or query builder API.
SQL's syntax is ugly because it was designed in the 70s where some people had quite different ideas what a DSL should look like (hey, COBOL, you are guilty, too!)
They're a pain in the ass.
Don't get me wrong, I have no problem with SQL as such but it has some shortcomings. Probably most things can be blamed on its age.
Why is the syntax of INSERT and UPDATE statements unnecessarily distinct? They do almost the same thing. Why are the values and column names in the INSERT statements' syntax separated and not with an UPDATE statement.
Why are there keywords that consists of more than one word? (Maybe the same ill-guided idea as with COBOL that being able to "read" SQL would make it easier to write/maintain?)
The syntax of SQL is in many places not tight enough, so you often have to hunt down a missing comma or typo, because the SQL parser is just not able to find out what you've done wrong.
I think the whole readable natural language premise is a neat idea, but much in the same way that natural language can be easily complicated, so too, with SQL. Heap abstractions of logic and arithmetic on top, and it gets even worse. But it's still a cool idea. If it works out in your favor, you'll get self documenting code. But alas, there is no floor or cieling to the complexity one can introduce, be it deliberately or accidentally. And not only is it the query itself (or the author) that might over complicate matters. The data schema, defined by the DDL, can easily induce unwanted complexity in subtle ways, and this might be a problem deliberately placed beyond the reach of the person attempting to read or change the data locked within.
As for complaints about parsers, that's not a deficiency of the language. It's a deficiency of the tools used. If the development environment isn't presenting problems to the developer in a convenient manner, then the utility sucks. Get a new one. Don't blame the text-mode clients though. They are just as bare-bones as a command line, and deliberately so. At a certain level, SQL can be regarded the same way a shell script might be regarded, in terms of possessing the convenience of (or lack thereof) a basic TTY command interpretter. But there's no real reason why it should be impossible to create a SQL script authoring tool, which provides syntax validation as convenient as any compiled language might employ, when communicating compilation errors to the developer.
I honestly love SQL...I consider it a noble language:) It is the only declarative language out there that is still used today, and there are some solid reasons for that.
However, I believe the biggest mistake that was made in the creation of SQL was to try to emulate natural language flow patterns. This makes it hard to format and edit, and when people optimize towards writability, they kill readability (such as the use of leading commas. The lack of any real standard indentation practice makes it cooperative work frustrating. And the natural language flow actually confuses the writer/reader into thinking that order of operations is linear, as opposed to the FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY->LIMIT order. This makes for really annoying logical bugs, such as how filter conditions in the where clause on a left-joined table can effectively turn your left join into an inner join.
This is actually a big issue because your ability to have complex declarative constraints goes up with table width, so breaking off potentially nullable columns also removes them from being available for cross-column check constraints.
The reason that you don't notice is that most of the times this stuff does not matter at all. Not because ORMs solve anything.
doing Map reduce with non of your fancy pants hadoop malarky
Ps and we had to build our own build system using CPL (prime JCL)