A Critique of SQL, 40 Years Later
carlineng.com
carlineng.com
Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise.
SQL is on of my favorite tools to use and I don't see it getting replaced by anything any time soon.
The complaints about SQL composability are real. The grammar is fundamentally pretty irregular. Acceptable for humans to type ad-hoc, crappy for computers to generate.
You can like what SQL does for you (I do!) but it's also easy to imagine something a bit better. I thought the article was spot-on. I hope some future SQL x.0 will fix these issues, but also be similar enough to present-day SQL that I don't have to learn a whole new language from scratch.
If SQL is to Javascript then something akin to WASM would be nice. Lower level primitives that allow query systems to be built. SQL could be one such system, but not limited to.
EDIT : thanks all for your reply. I now understand that it is an IDE related thing not something fundamental to the language.
FROM tablename t SELECT t.<press tab>
and some form of autocomplete mechanism, either prefills all the column names from table "t" or suggests the list of columns and/or types associated with it.
This is much better than having to: 1. Run a SELECT with LIMIT statement just to get an idea of the layout. 2. Point and click through the IDE treeview.
Honestly, I don't think it helps a whole lot beyond this functionality, but I can see why folks who are accustomed to thinking in functional pipelines (from -> select -> map -> filter -> collect) can prefer this way of querying.
I think PRQL is one attempt at building something this way.[1]
But I think that’s not a big deal at all, and the SQL approach has a certain advantage in putting the type of action (select/update/delete/drop etc) front and centre, which is really quite helpful.
If you use PostgreSQL then you can use \d instead.
I'm sure the other RDBMSes have their own equivalent (except for maybe SQLite but if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong).
.schema
> if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong
That's an uninformed statement. SQLite is extremely solid production quality code. Of course it's not the universally applicable database solution, nothing is. Sometimes you need Cassandra or its kind, often MySQL|Postgres, other times SQLite is the correct answer.
https://www.sqlite.org/mostdeployed.html
> Every Android device
> Every iPhone and iOS device
> Every Mac
> Every Windows 10 machine
> Every Firefox, Chrome, and Safari web browser
> Every instance of Skype
> Every instance of iTunes
> Every Dropbox client
> Every TurboTax and QuickBooks
> PHP and Python
> Most television sets and set-top cable boxes
> Most automotive multimedia systems
> Countless millions of other applications
Look at all those toys.
If you ponder the importance of proper (robust, reliable, dependable) data management for data that keeps nuclear plants going, for farmaceutical research data, for anything happening on the financial markets, for medical records, for data concerning payroll and the like, etc. etc. then you might appreciate that all the stuff mentioned in the list is indeed really "just toys".
FROM table SELECT
And when done switch the code to be correct?
WITH (combine a whole bunch of stuff) as dataset SELECT a, b, c FROM dataset
This approach just says:
FROM tables... WHERE ... SELECT a, b, c
To me, it does make a lot of sense. This is also the paradigm that some of the graph databases use.
Exactly! This is where SQL hurts me the most: not being able to store (partial) query expressions in variables for later reuse. The only way to do this is by creating explicit views (requires DDL permissions) or executing the partial query into a temporary table (which is woefully inefficient for obvious reasons).
This is what I'd what to do if common table expression really were common:
SELECT c1, c2
FROM DifficultJoinStructure
AS myCte;
WITH myCte
SELECT c1, c2
WHERE SomeCondition(c1);
WITH myCte
SELECT c1, c2
WHERE SomeCondition(c2);Granted most environments effectively treat views as DBA/Sysadmin owned objects, especially where end users/apps are effectively sharing one, or a small number, of user accounts.
But given user=schema aspect several of the traditional databases, I get the impression the original intent might have been a little more laissez fair?
Of course the same can be said for tables, and that was perhaps a little idealistic!
Views aren't always quite as composable as you'd like either, or maybe I'm just scarred by the particular DB engines I use most.
So I actually agree with you, but unfortunately SQL requires that the "WELL AKSHWELLY" be followed by one or more "BUT" clauses.
No, is fundamental issue to the language!
The relational model is clear. You START with a relation and then compose with relational operators that return relations.
ie:
rel | project
And this fundamental issue with the language goes deeper. The rules are ad-hoc for each sub-operator despite the fact using relational model MUST make it simply to compose.
So, you have rules for HAVING, GROUP BY, ORDER BY, WHERE, SELECT and so on and none are like the others, are different in small but annoy ways...
Also when reading a statement, you're mostly interested in what the returned fields are rather details like where they came from or how they're ordered. It kind of makes sense to put it at the start.
Maybe other syntax forms have their benefits, specially when writing, but I don't think SQL's choice is completely senseless either.
Only *IN SQL*.
You don't need it on the relational model, heck, no even in any other paradigm:
1
So this:
SELECT * FROM foo
foo
foo WHERE .id = 1
foo ORDER BY .id
foo ORDER BY .id WHERE .id = 1 //Note this is not valid in SQL, but because SQL is wrong!
But now, when you see it this way, you note how MUCH nicer and simpler it could have been, because then each "fragment" of a SQL query could become *composable*.
But not on SQL, where the only "composition" is string concatenation, that is bad as you get.
It's actually useful to the person reading the code. It clearly defines where a statement starts, what it does and makes reading a query close to reading English. Show a SELECT FROM WHERE query to someone who does not know SQL and the person will understand it. It might be a bit harder if you remove the SELECT.
And this is another example of the ad-hoc problems of SQL: query terminators (;) are optional. If they weren't, there would be no abiguity where a statement would start: it's the first word after the previous terminator.
Oh! Is super-ambiguous! Make the parser and enjoy it!
Lets make this more concrete:
city SELECT id
city ORDER id
city id <-- Order or select???
city city city
id FROM city ORDER BY id
id FROM (city WHERE elevation > 1000)Following their stupid English syntax, but using the proper verbs it should rightfully be "PROJECT x, y FROM foo SELECT WHERE a = b"
`FooId, name, date, favorite_color, active`
Now, you want to pull the ID for the latest `foo` for a specific date, but you don't know any of the column names.
The modern workflow looks like this
You write
`SELECT * FROM Foo`
then you say, "Ok, now I can get autocomplete"
`SELECT FooId FROM Foo`
"Ok, now I can write the where clause"
`Select FooId From Foo WHERE date=?`
It becomes an exercise in moving the cursor around just to get the autocomplete going.
If you are really familiar with the schema, not a problem. But if you just remember a few details about it, then you are stuck in this weird back and forth cursor moving thing.
That's why it'd be more ergonomic to have something like
`From Foo Select FooId where date=?`
Because you never need to move your cursor and you could get all the autocomplete you need at the right times.
This becomes especially true when writing joining statements
FROM Foo f
JOIN Bar b ON f.FooId = b.FooId
SELECT f.FooId
WHERE b.active = 1you start to type SELECT some, columns and can't get autocomplete until you add FROM afterwards, so you either type the query inside out (SELECT FROM table and go back to after SELECT) or just give up.
It is fundamental to the language. The evaluation order is from,where,group by, having, select, order by, limit.
Everything in perfect order is, select except.
All the FROM-first examples I've ever seen are almost universally the simplest query in the world where autocomplete is not a large hurdle anyways because you aren't even bothering with table aliasing. As soon as you do anything even moderately complex (multiple joins, subqueries, calculations, etc.) the advantage of putting FROM first vanishes, and if you're adding a table alias or hard reference to the table you can already see what is in the SELECT list AND in many cases get autocomplete.
No misunderstanding at all. It’s just more natural to first describe what the sources of the data are, joins etc, and then afterwards which columns you’d like to retrieve, or what calculations you’d like to perform etc.
The current way of having select first is just plain dumb. When it comes to reading order, you never actually know what is selected until you’ve read through the from, where, group by and having clauses anyway, so you constantly have to jump back and forth between dart and end to see the context. And it also better matches the sql evaluation order to have from first and select is after all of these.
Select also just better fits with order_by and limit since it’s also about restricting results after you’ve gather them all up and stitched them together etc.
Let's assume this was accurate, and reframe it to discuss putting FROM first in the query:
When it comes to reading order, you never actually know where what you've selected is coming from until you've read through the WHERE, GROUP BY, HAVING, and SELECT clauses anyways, so you constantly have to jump back and forth between start and end to see the context.
I would be interested in seeing what a complex WITH or subselect would look like if you're putting FROM first, and just how much actual clarity you're getting out of reordering it.
I think the benefit is nonexistent and the harm is that you're going to further fragment an already fragmented syntax space for that nonexistent benefit.
When you get to the point where you see customer_name, you know what it came from because everything you read prior to that.
Imagine going into a shop to order three burgers and saying “the first one is without onion, the second is without onions and the third without picked, I’d like a big Mac, a quarter pounder and a hamburger please.” It’s just not the natural reading order.
> I would be interested in seeing what a complex WITH or subselect would look like if you're putting FROM first
The premis for this seems to be that your arguing that moving SELECT to the start of the query even though this isn’t he natural evaluation order nor reading order somehow helps to increase readability. But I don’t believe the there’s any reason to assume that. Just because it’s how things are currently and how you learned it doesn’t mean it’s better, it just means you’ve grown accostumed to it and therefore you feel it’s simpler. But everyone learning SQL from scratch who don’t initially learn actively that select isn’t applied in the order it’s written ends up making the same silly mistakes and being confused about the same things that would be obvious if we just put it where it actually belongs.
FROM a JOIN b JOIN c
SELECT b._
FROM a JOIN (FROM b WHERE b._IDEs were never a favorite (they were quite limited then).
Today, the situation is a lot better.
Thus by the time you are writing the column expression after the SELECT, the FROM table expression would be filled already (enabling scope-dependent column completion).
I wonder if it’s time for a transpiler renaissance in SQL? It would be trivial to convert “select id from users” into “from users select id”. I’m sure theres a lot of other cool feautures/sugar you could add while still outputting valid SQL.
I wish more languages compiling to SQL were more common.
(Not ORMs though they just miss the point entirely..)
In my experience when you need to escape to raw SQL there is still some kind of query builder employed, which too becomes another "compile to SQL" source.
I would also love to be able to specify the WHERE just after the FROM but before the JOINs. And also after them: - the first one to filter the rows before joining - the second one to filter after joining
In general, this isn't done. But I don't see any technical reason it can't be done.
You could suggest all columns up front, and then afterwards tell the user “this suggestion doesn’t exist”, which would just erode trust in the autocomplete.
This is done already by Jetbrain’s datagrip for example.
I am not sure how the author has missed the copious amount of material that Date (& Darwin) wrote on this topic? The obvious one being the Third Manifesto (whole book available here [1] since it's out of print now). But even "Database in Depth" [2] has extensive discussion about this despite being a less "opinionated" and "ranty" book (highly recommended BTW)
Yes, not explicitly updated since 2014, but there's really nothing new to say since then?
Date is definitely concerned about the departures from the relational model in SQL. But it's (EDIT) not just about purism. He's also pretty in general peaved off at SQL for similar concerns that the author presents here about syntax and expressiveness. Third Manifesto's "tutorial D" mandates the addition of a richer type system, including higher level types that match on relation constraints, and "operators" (similar to OO methods) on those types; basically a kind of predicate dispatch.
I imagine he's pretty sick of being ignored.
[1] https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf
[2] https://www.oreilly.com/library/view/database-in-depth/05961...
> Date is definitely concerned about the departures from the relational model in SQL. But it's just about purism.
Perhaps you meant to say "But it's not just about purism"?
NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN.
If I run:
SELECT x.a, y.b FROM x LEFT JOIN y on x.a = y.a
[5, NULL]
Obviously there are lots of workarounds, but to me this is a truly fundamental design flaw. SQL should have specified another NULL-like value, call it EMPTY, used only for joins that find no data. And analagous to IS NULL, it would be checked using IS EMPTY.
NULL only has one meaning: NULL. This is roughly analogous to unknown.
The one that his a lot of people is WHERE <value> NOT IN (<set>) where <set> contains a NULL. Because NOT IN unrolls to “<value> <> <s1> AND <value> <> <s2> AND … AND <value> <> <sx>” any NULL values in the set makes one predicate NULL which makes the whole expression NULL even if one or more of the other values match.
> I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5.
Not directly, but you can infer the difference going by the value of (in your example) y.a - if it is NULL then there was no match, otherwise a NULL for y.b is a NULL from the source not an indication of no match.
> SQL should have specified another NULL-like value
This sort of thing causes problems of its own. Are the unknowns equivalent? Where are they relevant? How do they affect each other? Do you need more to cover other edge cases? I have memories of VB6's four constants of the apocalypse (null, empty, missing, nothing).
This is one of the reasons some purists argue against NULL existing in SQL at all, rather than needing a family of NULL-a-likes.
Which is what I'm arguing against, because it's used in two unrelated ways in SQL -- as a data value, and to express no matching row found in a JOIN. So no matter how it's defined formally, in practice it has two meanings that have nothing whatsoever to do with each other. One is a value and can be stored, the other says 'not found' and results only from expressions and isn't for storage.
> but you can infer the difference
Yes, as I said there are lots of workarounds. But they're still workarounds.
> This is one of the reasons some purists argue against NULL existing in SQL at all
Sure, but the reality is that NULL is used in a practical sense to mean "no data entered". It's so ridiculously common that only some columns have no data for a particular row, and so e.g. you need the 'time_finished' column to either have a valid date, or it's NULL if the activity hasn't finished yet. The alternative is to have an additional boolean column 'is_finished' and for 'time_finished' to be arbitrarily '1970-01-01T00:00:00Z' whenever is_finished is false, which is clunky and redundant.
Purists can argue what they want, but NULL is so ridiculously useful as a stored data value it doesn't really matter.
Rust’s Diesel has an interesting way of dealing with this. “None” means missing while “Some(None)” means the value null. So when updating a record, “None” makes no change while “Some(None)” sets the value to null.
This distinction is obviously lost when retrieving from the database, but it’s an interesting concept.
NULL values makes business logic far more compact and intuitive. For enumerated values in fields it's easy enough to define another value in the same field to mean 'unknown' or 'not entered', but you can't do that for strings, numbers, datetimes, etc. -- you have to throw in a bunch of unwieldy additional boolean fields instead.
In contrast to what SQL has, Sum Types combined with Product Types (which is basically what a row is) are actually a universal way to model all possible data[0]. (Of course you may want syntax sugar, etc. on top of that, but Sums and Products at the bottom is sufficient.)
[0] I'm actually not sure if I should qualify that -- I believe Sums+Products can actually model anything, assuming you allow recursive type definitions -- which might be hard to make perform well. Storing a linked list in a database field, e.g. might not be the best idea.
e.g.
select questions.Id , questions.Text , answers.Id , answers.Text
from questions left join answers on answers.QuestionId = Questions.Id
answers.Id is non-nullable as a primary key, so if answers.Text is null but answers.Id is not null they've declined to answer.
In your example it is very likely that the primary key is the clustering key, so will be present in the non-clustered index that I assume will be on answers.questionId, making my point moot, but if for some unusual reason neither Id nor questionId were the clustering key checking Id may result in extra reads being needed.
In DBMSs without clustering keys implemented similarly to SQL Server, there may be such concerns in all cases.
So then are there no optional values? What happens when OUTER JOINs don't match?
Or perhaps the purists would remove outer joins too, it isn't since University that I've read around the discussion, but given alternate syntax to do the same thing can sometimes be convoluted that might be a bad idea itself.
Even Codd often hinted (quite a bit of material in the 1990 book gives such hints) that "if a row has a null somewhere, it SHOULDN'T BE CONSIDERED AS BELONGING TO THE RELATION". E.g. if an attribute of a FK is null, no FK checking should be done at all on account of that null. The FK checking should be done only when the FK value is "complete".
As for outer join, one problem with it is that its result is (intended to be) the extension of a disjuctive predicate, the disjuncts in question being exactly the predicate for the "matching" case and the other one the predicate for the "no match" case. Now if you see a row in the result, how can you tell from that row which of the two disjuncts made that row appear in the result ?
Outer join is problematic by definition precisely because of the possibly ambiguous interpretation that derives from its disjunctive predicate.
(Would you do that with base tables ??? I mean design a base table that can hold a row for strictly more than one possible reason ??? I mean where the user then sees a row in that table and subsequently cannot tell whether that row is there for reason/meaning 1 or for reason/meaning 2 ??? I'll tell what you -and anyone else- would do in such a case : you'd also add the indicator telling whether it's meaning 1 or 2 (and then the problem of 'what if it's both' is also solved). Now think back about what could be done in outer join to resolve that very same kind of disjunctive ambiguity.)
The key is to realize that submitting a query is to ask a question. So how many queries to you think you have to issue if the number of questions you have is five ?
It is technically perfectly possible to "ask five questions at the same time" to any given truly relational DBMS. Said DBMS will give five answers at the same time (and not leave you to guess which answer corresponds to which question). That SQL doesn't do it, does not mean it's impossible.
You've got a list of countries and are pulling each country's national flower, national bird, largest port city etc.
Without outer joins, Liechtenstein with no ports doesn't show in the list at all. Sad news for people who want to know all countries, or Liechtenstein's national bird (eagle).
My response was just to point out the absurdity of your premise.
From the re-published version of "Much ado about nothing, part 2" (re-published in "Database Dreaming, Vol II) :
"An outer join produces nulls because we ask the DBMS to show us data from one table even when no matching data can be found in another table. Such nulls have no meaning, no semantic content."
Personally I'd add to that : and even if it's the case that they do, that meaning is patently not "simply unknown" but rather a very clear indication of non-existence.
But I have no doubt the apologists will obviously always apologize no matter what and handwave arguments such as these away with "So what ? What's the difference." Well, it's the difference between knowing of non-existence and not knowing at all.
Sorry, but this is FALSE.
"Even if one of the other values match" will make that particular INequality test FALSE and one FALSE conjunct will make the entire conjunction FALSE, even in 3VL.
If I CREATE TABLE foo (bar CHAR(1) PRIMARY KEY, baz char(1) UNIQUE), then different things happen on different databases.
In Microsoft SQL server, only one insert of a null into the baz column is allowed, and the null value is indexed.
In Oracle, null is never indexed in this context, so any number of null insertions into baz are allowed.
On a composite index, I believe that nulls are always indexed, on any database (they must be).
As far as I understand it, this is implementation-defined:
SQL> select * from dual where null=null;
no rows selectedSQL Server's handling of NULLs with in unique indexes/constraints is not standard. You can get better using a filtered unique index (“CREATE UNIQUE INDEX more_compliant_unique_index ON dbo.some_table(this_column) WHERE this_column IS NOT NULL;”), but be aware that filtered indexes have their own collection of gotchas⁴.
----
[1] MS Access IIRC, MS SQL Server if the ANSI_NULLS option set to off, MS² SSIS's default (consider NULLS equal) behaviour for merge steps, and so on.
[2] You may be noticing a pattern here… MS are not the only culprit though.
[3] I'm making a distinction here between places where behaviour is not defined in the standards, so implementations are free to go their own way, and where implementations go against what is expected by the standards.
[4] particularly with respect to the collection of unfixed/wontfix gnarly edge cases that is the MERGE statement.
The thing that really bugs me about NULL is the default assumptions - 99.9% of the time I want NULL in a string context to be the empty string and NULL in a numeric context to be 0, but I have to use ISNULL or COALESCE to get that. I wish it were the other way round where NULL is treated as '' or 0 by default, but I can do something special if I really want to test for NULL'ness.
I've come up against the distinction lots of times (and yes have to retrieve additional fields in order to address it), while I don't ever want to confuse 0 with NULL. Tons of things are legitimately zero but crucially non-null, like an inventory count. (Empty strings, on the other hand, do seem much more interchangeable with NULL in probably the vast majority of contexts.)
SELECT x.Id,x.a,y.ID,y.b
FROM x
LEFT JOIN y
on x.a = y.a
On a side not, I use the SQL below all the time to identify missing rows on the "comparetable".
SELECT #basetable.columnname
FROM #basetable
LEFT OUTER JOIN #comparetable
ON #basetable.columnname1 = #comparetable.columnname1
and #basetable.columnname2 = #comparetable.columnname2
and #basetable.columnname3 = #comparetable.columnname3
WHERE #comparetable.columnname1 is nullNotably NULL of any kind is completely absent from the relational algebra. There are other ways to express absence. Notably, this is all supposed to be first-order predicate logic <hand wave hand wave> stuff, and... in that world "null" is also not a "thing".
Also SQL insists on allowing duplicate "rows" in its "tables"; whereas the relational model is very clear that the contents of a relation ("table") is a set, not a bag.
These two confusions in SQL actually lead to many many data integrity problems as follow-on effects and also complicate query planning and optimization.
You can always add SELECT y.a, which will allow you to disambiguate between the two options (it will be non-NULL in the first case and NULL in the second).
SELECT x.a, y.a IS NOT NULL, y.b
FROM x
LEFT JOIN y ON x.a = y.a;
In PG you could:
SELECT x.a, y IS DISTINCT FROM NULL, y.b
FROM x
LEFT JOIN y ON x.a = y.a;EMPTY implies a known, 0-byte value whereas NULL can imply either an unknown value or the "unknowability" of a value (i.e. the in-existence of a value).
In practical terms, this would be like equating the statements "I don't know whether that dog has a name" (i.e. a NULL name) and "I do not know the name of that dog" (an EMPTY name). The former does not assert the existence of a proper noun to represent "that dog", whereas the latter implicitly asserts that there exists a proper noun which nominates "that dog".
UPDATE x SET a = NULL WHERE b IS NULL;
Like... wat?
If your database is well designed, joining on a nullable column should be a relatively exotic use case, and can be handled by writing a tiny bit more code to check for NULLs before performing your join.
Assume y.a is the non-nullable primary key, while y.b is the column that may be null.
But in this example there might not be a row where y.a = 5. Or there might be. But you can't tell.
I have seen many times in my career where a rogue delete just truncates a table, a simple statement of intent (e.g. LIMIT 1) would tell the query planner that if it is about update/delete more than 1 row, it should error. In fact MySQL actually returns a warning if you do this.
TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything.
There were also definitely some bad paradigms invented as a result of SQL (e.g. all business logic lives in the database as stored procedures or stored functions), but that was bound to happen whenever a shiny new tool comes out and engineers want to use it.
I think the other way around.
“DELETE Something” to me means get rid of the whole of Something. “TRUNCATE Something” (in general English, not SQL) to me means to make something shorter, which probably doesn't mean removing it completely.
Perhaps “EMPTY Table” would have been a better choice than “TRUNCATE Table”, but by the time it reached the standard¹ TRUNCATE had long since been picked and until then it was not the responsibility of the SQL standard: you only had DELETE and without a filter it makes sence that this deletes all.
----
[1] note that TRUNCATE was not in the SQL standards until SQL2008, long after it was common in various DMBSs as a minimally logged alternative to DELETE-without-filtering-clauses.
btw. they are completly different.
TRUNCATE basically ignores all transaction semantices for the sake of performance (which can be really really bad) in mysql it can't be rolled back, in postgres it's not mvcc safe. TRUNCATE most often uses either an exclusive lock or some other mechanism.
if you know what you are doing truncate can be helpful, but delete has its benefits as well and truncate should be avoided by beginners.
Truncate in MSSQL requires alter permissions, so a user that can wipe a table can also change its definition. Not true with delete.
I hope you don't mean by that that integrity enforcement (checking the compliance of the data with ALL declared business rules) does not belong in the DBMS. Because the exact opposite is true : inside the DBMS is the only place where it belongs. Or if not the only, then certainly still the primary.
Some UPDATEs don't allow me to specify a limit!
Infuriating stuff.
And really, projects like this are fighting the general ignorance the industry as a whole has about what a relational database actually is. It's better since the NoSQL wave crested and we stopped hearing stupid shit like "my data isn't tabular and doesn't fit in a schema", but there's still a preponderance of people who haven't stopped to learn the lessons that Date and Codd tried to teach decades ago, and constantly reach for graph/network/hierarchical databases without understanding why we originally tried to move beyond those back in the 70s.
Unfortunately because of this most attempts to do "better than SQL" end up not working from first principles and look like a dogs breakfast of ill-formed "that'd be neat" ideas.
And, yeah, as I said the "pure relational" projects that are out there are usually either academic projects or hobby. (Yes I've had a few of my own)
I think there might be hope in the Datalog-type systems that have emerged recently.
And those that are built on "first principles" end up looking like a dog's breakfast just as well because people expect to not have to learn a novel language.
People don't want to be told to forget everything they thought they knew. People don"t want to be told they've been wrong for 40 yrs. Even if you add that it's not entirely their own fault because they've been consistently misled by an entire industry.
You can crack the problem of supporting CREATE ASSERTION, they'll still dismiss it (handwave handwave) believing you're just bluffing.
And I mostly disagree with the optimism built on Datalog.
The other alternative might be to implement one's new thing as a patch to alter the frontend of Postgres. I looked at this many years ago and the engineering effort was immense. But it might be easier now.
1) SQL's type model has no concept of nested relations or rich datatypes; it's limited to basic scalar types <tho there are vendor additions to this>, so implementing e.g. Date's concept of "relation valued attributes" <essentially tables-in-tables> for example would be impractical
2) SQL's storage model doesn't map 1:1 with e.g. a relational model because SQL tables are bags instead of sets (duplicate rows are possible). This has a couple downsides. a) whatever more expressive and relational algebraic language you built would have to inherit that model and and have some way of dealing with bags instead of proper relational ranges <which are sets> and b) the presence of the possibility of dupes actually makes some query planner optimizations impossible to perform. <CJ Date goes into more depth explaining this, I don't have room for it here>
3) Likewise similar problems with SQL NULLs. NULLs are not in the "pure" relational model, so your language can't be pure. NULLs mess with the type system, and you end up inheriting a bunch of the weird casting/identity/comparison issues that come with NULLS. And NULLs, again mess with the query planner.
Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement.
(1) is a function of what SQL you might transpile to. Any SQL w/ rich types (like PG) or w/ JSON support (most others) will be able to meet (1) with varying levels of hackiness.
(2) even if some SQL RDBMS allows you to have tables w/o primary keys, the thing transpiling to SQL wouldn't be obligated to have tables w/o primary keys. Some SQL RDBMSes do not allow tables to lack PKs (e.g., SQLite3 doesn't), but even this wouldn't be a problem -if one wanted tables-with-dups- because one could use hidden row IDs to make up for that.
(3) just as with (2), the new thing transpiling to SQL wouldn't have to allow NULLs, and could generate SQL that uses `IS [NOT] NULL` / `IS [NOT] DISTINCT FROM NULL` to obtain the desired semantics if the new thing kept any notion of NULL.
(4) is not a semantics issue, and anyways, some SQL RDBMSes let you implement tables yourself, and for the others there's CTEs and TEMP tables.
Of these only (1) is a problem if you want to target pure standard SQL, though it depends on which SQL standard you target.
We're definitely not talking about transpiling to 1980s SQL!
So, while there might some semantic impedance mismatches for a transpiling approach, and the choice of RDBMS to transpile to matters, on the whole I'm unconvinced that there are such problems that are very serious. My own imagination is failing me in my attempts to come with such semantic impedance mismatches.
> (3) just as with (2), the new thing transpiling to SQL wouldn't have to allow NULLs, and could generate SQL that uses `IS [NOT] NULL` / `IS [NOT] DISTINCT FROM NULL` to obtain the desired semantics if the new thing kept any notion of NULL.
You may disallow nulls stored, but anything like a non-inner join will produce nulls and then you're almost back to where you started.
> (4) is not a semantics issue, and anyways, [where 4 is "Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement"].
Spot on and great catch. I missed that.
> I'm unconvinced that there are such problems that are very serious
Let me help you - simple sqlNG commands could transpile into more much complex normal sql target statements, breaking the optimiser. That's a very real risk.
Good post though
Breaking the optimizer is a big deal, yes. I have this fantasy that we could have an out-of-band hinting system for SQL, like "start the query plan with table source <alias>", or "use index <index-name> for table source <alias>", or "materialize table source <alias> and create these indices for it", or "index the CTEs by..." (the last one should really be automatically done by the optimizer, but when you can save it thinking time, that can be a good thing). In-band hinting has generally been problematic. Out-of-band hinting could either follow the query itself, or be provided in a SQL exec API as a separate argument, or as explicit method calls in query setup in LINKQ-like systems.
> You may disallow nulls stored, but anything like a non-inner join will produce nulls and then you're almost back to where you started.
Now that just points out that the problem arises naturally and isn't entirely SQL's fault. Null values are as much a problem for SQL as for C and many other languages.
The way modern programming languages deal with nulls is by insisting on full pattern matching on algebraic data types. So it follows that one might want to do the same in a new query language, and/or possibly as an extension to SQL. So that this would be OK because at the top-level, but not as a subquery:
SELECT a.name, b.thing
FROM a LEFT JOIN b USING (id)
> Good post though
Ditto.
> I have this fantasy that we could have an out-of-band hinting system for SQL,
I think not breaking the optimiser, and out of band hinting appear somewhat orthogonal but I'd be very glad to be wrong. I'm looking at OOB hinting for a different reason, but what you're thinking is very close to mine, and I'm struck by the fact you see hinting the same way I do (actual, and extensive, logical vs physical separation, inc. indexes as hints).
> and one would be forced to check for [nulls]
Even more interesting. I will shortly have something of relevance. Do you have a contact e-mail?
If you're writing a transpiler, you might as well make it an optimizer and use a solid hinting system to guide the target RDBMS' optimizer so as not to be at its mercy. If you were implementing from scratch rather than as a transpiler, you'd have to write an optimizer anyways.
Also, the reason optimizers can suck is precisely that there can be many ways to plan a query.
E.g., if you have 10 table sources INNER JOINed in sequence, you could start the query plan with any one of them, but not every one of those 10 tables will yield a good query plan when used as the starting table! You want to start such a query plan with the table that has a PK or index key that is most fully determined by the WHERE/ON constraints, by which I mean: the table source that will have the smallest cardinality given the constraints imposed on it by the query.
Sometimes the query that one has to optimize is a sub-query deeply buried in another, possibly in a VIEW, and the hinting needed may be specific to the context outside the view. Now inline hinting leads to having to inline sub-queries (repetition).
The many table sources INNER JOINed case is one I've actually run into.
Part of the problem seems to be that pushing constraints is a fairly advanced sort of query algebra: you need a good AST in order to be able to do it, and even then, it's not all you have to do to build a decent optimizer. If you look at the SQLite3 optimizer, it's gotten much better over the years at pushing constraints.
With every table source named, you can address hints about them, and you can do it out of band.
That or pick a target whose optimizer you can either work on or already is good enough for the transpiler's purposes. However, keeping things general is a good idea, especially when it comes to SQL (there's so much incompatible variation between SQLs!), so as to keep the transpiler generic and portable. The lack of a standard and solid hinting system is a real problem. Also, sometimes optimizers get worse.
> Do you have a contact e-mail?
${HN_username} @ gmail2) Supose you're writing sqlng, compiling it to sql. sqlng simply mandates a primary key on everything. Not a problem.
3) nulls can be a horror but the alternatives aren't pretty. Removing nulls means tables have to be broken up, see https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without.... I believe there are better ways to handle this issue.
(final line) Makes no sense, a tab;e doesn't have to be disk-resident. Once read, with sufficient memory, the file sits in ram cache.
I think it also depends on how the 'model objects' are implemented. Is it Active Record or Domain Model? I think most of the complaints about ORM are actually complaints about Active Record / DAO, or just a crappy implementation. Also nothing wrong with using SQL in an otherwise ORMed application. Just harder to unit test, refactor etc.
My application code just calls stored procedures. It's unaware of the tables and underlying data model.
1) SPs usually mix persistence concerns with business logic. Making it harder to understand business intent. I find objects much more expressive than raw data. Sometimes you want to concentrate on the plain logic, without worrying about how something gets saved. Also you will not have to rewrite everything if you ever want to change how something is stored. Sometimes people switch from mysql to pg, or even doc or kv database. Keeping business logic separate enables this. And good ORM enables this separation.
2) Refactoring tooling and unit tests. SPs are lacking here significantly compared to general purpose languages.
3) Business logic outside of DB allows easier horizontal scaling.
PHP, ASP, ASP.NET, Angular, React, Django, whatever. Pick a year, pick a framework. The database stays steady.
I wrote about it at https://sive.rs/pg
and posted my SQL shopping cart at https://github.com/sivers/store
Please contact me if you'd like to share tips: https://sive.rs/contact
I read your blog post and I agree 100%
The database is the easiest place to code business logic that pertains to the data. Write it once, it's available for all client applications.
I think the staying power of SQL comes from its broader audience - programmers, analysts, and executives all use it. It's much easier to motivate programmers to learn a new, superior language than it is to motivate executives to learn a new technology that only gives ergonomic improvements.
https://wiki.c2.com/?TqlRoadmap
It's more API-like to reduce the need for specialized keywords. And you can "calculate" column lists via queries instead of have to list columns. For example, if a table has 60 columns and you want to SELECT all 60 minus two columns, you can't without listing all 58. With SMEQL you can use a query to return a list (table) of those 58 and feed it to the equivalent of a SELECT clause.
Things like CREATE TABLE are fed a table if you desire so you can "compute" your schema. You can generate entire schemas from data dictionaries. Anything that can be done with a table is. You can create in-line (virtual) tables if you want it command-driven, but it's still "using tables to do everything". You can use textual code to create such virtual tables or a table editor as it fits the situation. SMEQL is as close to "everything is a table" as I've seen. Even your FROM list can be table-ized. I used to do similar with dBASE/xBASE, and it was really nice, especially for ad-hoc work such as one-off research requests.
And as somebody mentioned here, null handling needs a serious revisit in SQL. I could rant all day about SQL null handling, especially for strings.
The only reason ever why people engage into theory, is precisely because of a deeply rooted desire to address "practical concerns". Hawking literally stated that his aim was to understand everything about the entire universe.
People who "like to focus on practical concerns" see an apple fall and (after they've seen it happen often enough and never ever otherwise) ultimately conclude that "apples fall and that's just how it is". Newton saw an apple fall and ultimately unveiled the concept of gravity. In doing so, Newton also unveiled the (admittedly extremely exceptional) circumstances in which it might be possible for us to see an apple "fall upward into the skies" after all.
One wrinkle: computed columns would interfere with query optimization. That said (and here I speak heresy) there are times when syntactic convenience trumps performance.
One performance trick is for the query optimizer assume the calculation will be the same as the last time the same query or sub-query was issued, and simply dump the results and start over if that assumption is false. If it keeps having to dump, then it assumes you are editing a lot and stops guessing for a day or so. (You could issue an optimizer command to resume guessing if need be.)
I wish the "truly awful" stuff I come up with was 0.1% as successful as SQL.
But at any rate, you could also try and ponder how unbelievably exceptionally awful it must have been that existed before SQL if something as truly awful as SQL could still be as successful as it has been.
https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with...
Even pandas (with all its faults) is more flexible as a language than SQL[1]. I'm of the opinion that there's a better together story in the end, but I guess we will see.
[1] https://ponder.io/pandas-vs-sql-food-court-michelin-style-re...
Kdb the system that qSQL is ran within, allows full use of variables and all builtin functions with tables/functions/variable/columns. It really is a case of less is more. What this allows is functional form queries. Imagine being able to query: ?[tableName;lessThan(age;10)] and have perfect functional form representation for all queries. No ORM, no string concatenation. It seems some other database creators are at least becoming area of these things and integrating parts.
On the other hand, SQL is set-based. But being set-based is weird in the world of computers because in memory everything is ordered. Sets are a fiction in programming, as they're always ordered in some way, and the set abstraction can only try to hide that order.
And that order can be very useful.
But there can be many orders that can be useful, but only one in which things are stored in memory -- the others can only be extra indexes.
So the general purpose thing (SQL) has to be set-based, offering explicit ordering, and taking advantage of actual order for optimization.
However there are a lot of pushback from 'traditional' dataengineers who were trained on spark/scala. Its bit of hardsell to go from a highly typed language to a free for all text based logic.
I think the following is needed for sql to be finally accepted as 'serious' contender.
create compiled sql language ( not pandas)
1. that compiles to sql and addresses some of the issues bought up in the post like nested aggregations.
2. make code reusable. Eg: apply year over year growth to a table that has the requisite columns. Compiler should check this in ide.
3. make materializations first class concept in the language. No seperate dbt layer.
4. crate a way to package and distribute libraries that you can import into your project .
5. a unit testing framework that makes it easy to test the logic without having to setup test tables in the database.
Whatever way you slice it, SQL is one of the most used languages today [0].
[0] https://spectrum.ieee.org/top-programming-languages-2022
I do unit testing in SQL, and something I'm working on and use extensively myself (https://www.npmjs.com/package/sql-watch) indirectly supports unit tests.
There are also SQL testing frameworks available.
However what we are doing here isn't 'unit testing' its a black box integration testing. When I write equivalent code in scala, i just test the logic via unit tests,
eg: logic to filter out some orders that don't qualify, I extract method in scala code and just test that logic as part of development lifecycle. There is no dependency on a database.
Analog here is using selenium or some ui testing framework to test if button turns blue if order exceeds limit. Thats not unit testing.
Unit testing seems to depend on where the unit of code is which is being tested. At the middle tier, you may mock out parts of the code so the tests aren't reliant on external sources (apis, databases, libraries, etc.).
It seems that unit testing database code would happen at the database layer: it's still a unit test as the test isn't dependent on external sources.
Its not database code though. "Orders over 100$ should be marked as vip" is domain logic.
Database code like interactions with database, connection pools, primary/secondary switching ect yes they should be tested with a database.
It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL.
2 & 3 are my biggest pain points. I can't just extract functions like I can with a regular programming language. Instead, SQL queries get increasingly complex with no great tools to manage that.
For 3, products like https://materialize.com/ look interesting for being able to create derived materialized views that can efficiently be kept up to date.
For instance, a "top 10" function that could be re-used on any table (apologies for my pseudo types and code):
selectTop10 : Column -> Table -> Table
selectTop10 orderByColumn table =
SQL.selectAllFrom table
|> orderDescBy orderByColumn
|> limit 10
limit : Int -> Table -> Table
limit n rows =
SQL.limitBy n rows
orderDescBy : Column -> Table -> Table
orderDescBy orderByColumn rows =
SQL.orderBy [orderByColumn] SQL.Ordering.Desc rowsAnd that's exactly what you're able to do in most of the modern data warehouse services such as Snowflake. Inferences can be contained within internal/external user defined functions.
This is very reminiscent of made the big-data/map-reduce movement so notable, sending your query to the data instead of moving your data to the query. Sending your model to the data, instead of sending the data to the model.
SELECT Id,col1,col2
FROM
(
SELECT
Id
,col1
,col2
,row_number () over (partition by columnkey1, columnkey2 order by anycolumnwilldohere desc) as _row
FROM _table
) as anytablealiaswilldohere
WHERE _row <=10I'm saying this as with most of my experience being in SQL.
Not exactly but sort of this:
http://blog.hydromatic.net/2020/02/25/morel-a-functional-lan...
>Mismatch with Host Language
But to me, i feel like this is the biggest problem with sql. Yes every language is different, but having different mental models nonetheless causes friction, and leads to things like ORMs which are often even worse.
>A Critique of SQL, 40 Years Later 08.11.2022
>The SQL language made its first appearance in 1974, as part of IBM’s System R database. It is now over 50 years later, and SQL is the de facto language for
1974 isn't 50 years ago yet :)
So the title is correct.
SQL is just a language for submitting Relational Calculus to the database (+ DDL statements).
If you wanted to, for instance, you could add a language alongside SQL in Postgres, submit the results to the internal RC optimizer, execute the optimized query, and get back the results.
In your new language, you can address all of the issues Carlin Eng/Chris Date identify in the article.
Go figure about the rest ...
For example if I want to pick a single row by id, with SQL I must send a query string, which results in parsing, which means lower latency.
If I want to randomly select 100k rows among a database of 1 million entries, I need to build 10k query strings (I think?), which won't be fast to parse. I don't think this happens when using C pointers in C, C++ or arrays in other languages.
SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;
SELECT TOP (100000) * FROM tblNm ORDER BY newid()
We use tables because they help us to massively simplify the processing of data.
Do you want to deal with graphs? The algorithms involved are harder than what your intuition is telling you.
In fact, the first databases, before relational databases were in vogue, were hierarchical/network databases.
https://en.wikipedia.org/wiki/CODASYL
They were obsoleted and replaced when RDMS appeared.
CODASYL is probably not remotely close to what the GP is talking about w.r.t. how humans think in graphs.
I've also recently been [re]introduced to graph databases (which are highly similar to the pre-relational network database paradigm)
You can simulate graph relationships with an RDBMS or noSQL - but you shouldn't
You can simulate an RDBMS with a graph db or noSQL - but you shouldn't
You can simulate noSQL with graph and RDBMS tools - but, again, you shouldn't
They all have their place - and ca even work quite well together, if you use them as they're intended
What is the feature which makes the noSQL which you shouldn't do in a relational database? To me noSQL always looks like a subset of relational database. The only thing, maybe, is that you can truly put everything in, but with modern JSON features and all the other things I don't see a downside in using relational. (Except a little learning curve, while that can be hidden behind some ORM or something if you really want for the beginning)
done. If that's really what you want.
No
And then having to figure out what all the fields are after the fact?
No
That's why noSQL exists
What do you mean? I can think of many silos containing connected information: municipal residence records, marriage registries, birth records, police reports, tax records, medical records (for each hospital) are all silos connected by a citizen's identity.
Many would counter by saying basic index lookups comprise a vast majority of DB processing time.
agreed, the implication is that the engine should do everything well. rdbms is a product of being pulled in every direction, there's generally an index strategy for everything including fully connected hyper graphs, and that's how they ought to be because we can write wildly complex recursive sql queries and the goal is an efficient ideally optimal execution plan
I don't think you mean "everything, equally well".
But if you mean that database vendors feel pressure to have their "RDBMS" be able to "be competitive" across a wide variety of use cases and metrics, I tend to agree.
Perhaps you don't mean storage but rather a conceptual model?
Because it’s useful and that last part isn’t true? Databases do more than indexes and you have trade offs regarding the impact of adding too many indexes.
It's also not only not a problem for scalability but in reality an important way to _improve_ scalability. Consider a simple example where we do "SELECT SUM(price) FROM orders GROUP BY customer_id". Removing SUM() from the language would massively increase the amount of data which needs to be processed into the response — which makes anything else you're doing like sorting harder – and it prevents various optimizations the database engine might make. For example, on AWS RDS Aurora the database pushes all of that down to the storage nodes so each storage node will return the sums for the records which are on that node and the main node can sum those intermediate values without needing to transfer a single source row over the network. Since SUM() is part of the language and well-defined, that's safe for one database team to implement without the risk of their results not matching a competing database.
Other things to think about are computed views and stored procedures: in both cases, there are situations where these are dramatic performance improvements or otherwise desirable and having a richer language means that those constructs can solve more problems.
It sounds like you don’t want a general purpose database. Something like what you describe might be more efficient on certain queries but only because it’s giving up a lot of flexibility - consider what happens if you add any filtering or grouping changes and those partial sums can no longer be used.
This preference for general systems rather than optimizing for a single query shows through in the tools used for this kind of task: materialized views and indexes using expressions. Those would allow the kind of precomputed aggregates you’re talking about without the kinds of challenges around flexibility and correctness your approach would encounter.
> Transferring large amounts of data in the response is solvable by using shared memory
That’s a common optimization but it’s not a panacea any more than indexes are. For example, relying on that falls over badly once you need a network and the problems I mentioned before are still important — even if your shared memory implementation is perfectly efficient, you’re still looking at keeping data in memory longer so your client can aggregate it when the database engine could do that for you and discard the memory immediately.
Another way of looking at this is to ask why a field with billions in R&D hasn’t already done it, and especially why various big data tools have been significantly less impactful than promised. If you see what seems like an easy way to beat the current options, it’s usually a good time to ask why everyone working in that space hasn’t seen it.
If you're doing a query that is not backed by an index, it's not scalable, so you could also do it in Excel.
> This preference for general systems rather than optimizing for a single query shows through in the tools used for this kind of task: materialized views and indexes using expressions.
Materialized views totally suck (at least in postgresql), because you cannot update them incrementally (yet). A better option most of the time are triggers and aggregate columns, but it requires a lot of micromanagement.
> It sounds like you don’t want a general purpose database
I don't think I want a special feature, map-reduce indexes seem pretty general "mathematically" sound thing.
> Another way of looking at this is to ask why a field with billions in R&D hasn’t already done it, and especially why various big data tools have been significantly less impactful than promised.
Well that's what's exiting about research, that some things haven't been done yet. Even though they may seem fairly obvious.