> SELECT extract(day from timestamp '2001-02-16 20:38:40');
SQL is messy because all the syntax was decided on before there was a community that really understood what good syntax is. The 'from' in that extract does nothing and I can't easily identify if extract is a function or some sort of crazy parsing construct - what are the arguments? Is "day from timestamp '2001-02-16 20:38:40'" the argument? Are the arguments "day", "timestamp" and "'2001-02-16 20:38:40'"?
Are these annoyances crippling? Yes. Yes they are crippling. It should be possible for an amateur to quickly write an SQL validator as a starting project; relational algebra is not complicated. Any fool can write a validator for lisp. Relational algebra isn't that much more complicated - we don't have loops or flow control to contend with here.
Tidyverse's dplyr [0] implements the relational model for real dirty data and, as might be expected for something implemented this century, does a much better job than SQL. Not because the operations are that much different (although gather() & spread() are welcome additions) but through ingenious innovations like, as mentioned, functions having arguments instead of I-don't-even-know-what-that-is.
And the pipe operator which is legitimately ingenious. Great operator for data.
...why? SQL has been wildly paradigm-definingly useful for decades. It has driven hundreds of billions, perhaps trillions, of dollars of value. None of this hinges on the ability for an amateur to be able to write a validator for the language. It just seems like such a non-sequitur to me, such a strange thing to call out as a criticism.
SQL hasn't been wildly successful either because of or despite its syntax, it has been wildly successful because organizing data in the relational model and querying it declaratively is extremely powerful. The syntax is just not the interesting part. Any syntax that meets those criteria would do.
Also FWIW, I'm not sure that writing a sql validator _is_ all that hard. The grammars for any dialect are readily available and really not so hard to turn into an AST with any of your favorite tools (ANTLR, lex/yacc/bison, any of a million PEG parser generators). In many cases you don't even need to, we do static analysis of spark SQL using their own parser, for postgres can use the parser straight out of libpg.
I guess the OPs point is that if you can validate it easily, you can understand it. I'm certain I could write a lisp parser in 10 minutes, but I don't think that would mean I immediately understand all Clojure code (which is often pretty obtuse). You still need to learn all the underlying concepts and data structures.
Then often the storage and retrieval of data is an after thought during the development of an application. This is definitely true for CRUD apps. Then maybe the person used a framework to handle database interactions and now after a year or so, it’s not scaling well because the data access was not really given priority during development. So then the problem is the database engine or the syntax instead of proper design and planning.
That’s not to say there are no drawbacks to writing most dialects of SQL. I just believe most of the hate over SQL is coming from a procedural mindset to a relational one.
I think the point he is trying to make here is the same as the post was making concerning orthogonality. Having a smaller set of special syntax, and therefore an easier validator to write, means easier queries to write for the user. I don’t think he was implying that everyone who makes use of the language should know how to write a lexer for it.
> The syntax is just not the interesting part. Any syntax that meets those criteria would do.
I have to disagree with this. If this was the case we would still be programming everything in BASIC or C because they’re just another imperative programming language and it gets the job done. Having sugar syntax, a consistent language, etc.. all makes it easier for a programmer (or data analyst) to get the job he needs quicker (and therefore reduces cost), makes a program easier to maintain, and so on.
<snarky> Ability to manage persistency and correctness of SQL engines is very usefull while language interface not so much. </snarky>
The same can (almost) be said for Javascript. A language with a lot of foibles can still be successful if it is the only realistic mechanism to interact with the system.
> ...why?
I don't know about amateurs, but if the typical intended user of the language does not have a good grasp of what is and is not valid, they will often be reduced to trying one thing after another until they hit upon something that seems to work, with no real understanding of what it does. This is not how robust, correct software is written.
I do think this way of utilizing SQL is a problem, but I would lay the blame for it at the feet of a lamentably widespread anti-intellectualism in the field. It is true that the relational model is not trivial and must be learned before SQL makes much sense. But it is not too much to expect from someone who wants to be a practitioner in this field.
Here you got me thinking: do we even understand that now? There was quite a bit of contemplating about of semantics, data types and different kinds of abstractions in the programming languages for the last couple of generations already, but the general consensus about the syntax is that "nice syntax is nice, but it isn't really that important". Most modern languages loosely follow either something C-like or Algol-like, most people like what they are familiar with. There are sometimes claims that a good syntax should be successfully parsed by some relatively simple parser (which is totally not obvious, TBH, because while it is clear why C++ is a good counter-example, we don't really have that many good examples: classic LR-parsers or something like that are really not that powerful, and most real-world programming languages implement something very non-generic of their own). Some people claim that "good syntax is no syntax" (Lisp), and some say that Haskell has a good syntax (ikr). The bottom line being that this is just a matter of taste, unlike most of what we can say about types and data structures.
So, to summarize: I never actually heard a compelling general theory of good syntax.
This is completely offtopic, BTW, I agree that SQL is trash and it is even kinda funny that somebody tries to defend it like that, since for a long time it was sort of a textbook example of "why a language created with the idea to be used by non-technical people is a failure from the very beginning".
I started to think along these lines when I got serious about learning a foreign language. Humans appear to have some innate language ability that’s reflected, among other things, in commonalities between disparate languages. As far as I can tell, there’s been no serious effort to design a computer language to take advantage of this.
My pet theory about why the object.method(args) call syntax won is that it really is the most natural to read, as evidenced by the fact that most human languages follow subject-verb-object word order.
Most western languages maybe, but according to wikipedia[1] the most common is actually SOV rather than SVO.
[1] https://en.wikipedia.org/wiki/Word_order#Distribution_of_wor...
Nope. [1]
[1] https://wals.info/chapter/81 (Note that while this is the language count only, the same comment applies to the population using such word order as a native tongue: SOV and SVO are both common.)
https://docs.microsoft.com/en-us/dotnet/fsharp/language-refe...
Yes it works best when you have one argument to pass, as the first argument is passed implicitly.
Now, yes, there are languages with multimethods, where the distinction is less obvious. For those that always dispatch on all arguments, a uniform syntax makes more sense. CLOS is a good example.
The object.method(args) "won" because it is a low barrier to entry from English to Programming indeed, but as you become more and more experienced and you shake off the imperative way of thinking in favour of declarative thinking functional languages becomes more expressive than imperative languages. They become better at managing complexity because they allow you to build higher and higher towers of abstraction.
And then homoiconicity [2] shines because of its 'universally grammatic' property.
I’m thinking about basic stuff like improper nouns, denoting a clause’s role by inflection or adposition instead of word order, and having a clearly defined spoken representation- none of this is specific to which abstract structures the language is describing.
BASIC and C didn't have a concept of objects* , which didn't seem to hinder adoption at all. object.method() became popular when OOP languages became popular, and that was because IDEs offered better autocompletion with that syntax.
* We could emulate objects with C in some gnarly ways. Almost no one did at the time, excluding C-with-classes/C++.
Also there's a non-language related advantage of dot notation. Type the name of the object, press dot and IDE will show you what things you can do with that object. You don't have that with other notations.
Which goes to your point-- form follows function, in syntax as with many other things.
It's not that they didn't understand it. There are languages predating SQL that have better syntax.
But SQL was one of the so-called "4th generation programming languages", that were supposed to be operating at a much higher level. And because of that, there was the notion that, if they also have a more "natural" syntax, it would allow people who are not programmers to use them effectively. Hence why SQL was originally SEQUEL - Structured English Query Language. It was meant to be written by domain experts, not database experts.
Of course, that failed (like countless other similar attempts since then), and so now we're stuck with this syntax for no good reason whatsoever.
No, the relational model is beautiful and consistent! SQL is messy because the syntax is not consistent and elegantly composable. It could have those properties and still present the same underlying data relationships.
See Linq in C# as an example for how a more composable query syntax can expose the same data model.
For example in Linq you can chain arbitrary many select/join/where/group by in arbitrary order. In SQL you need nested subqueries to achieve the same which is a much more convoluted syntax.
The only criticism I have of Linq is that it is a more general language targeting different back-ends including plain old objects. If Linq did not allow things that do not make sense in SQL this would be a little less jarring.
I also wish it supported full outer joins, unions, and window functions.
I was able to hire a four or five contractors who never used Mongo just by requiring Entity Framework/Linq experience and less than 4 hours of training.
WITH statements alleviate some of this issue by allowing you to write subqueries in any order.
[1] https://thoughtbot.com/blog/advanced-postgres-performance-ti...
Exceptions:
1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call.
2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized.
https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case.
After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime.
Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.
What is beautiful and consistent is the relational algebra. The relation model relies on this formalism to model data by making some rather strong assumptions about how tuples and relations represent things and how relational operations are used to process them. And these assumptions are precisely what propagates to SQL and what some authors (see references in the article) consider controversial and messy. Then the question is whether and how these controversies can be fixed (or whether they are bugs or features).
A radically different approach to fix these controversies is to introduce a different formalism and different data model (as opposed to fixing only syntax) which is based on using functions. In other words, instead of using sets and set operations, we use in addition functions and function operations [1]. Here you can find an implementation of this approach:
https://github.com/prostodata/prosto Prosto is a data processing toolkit radically changing how data is processed by using both sets and functions and being a major alternative to map-reduce, join-groupby and other set-oriented approaches
[1] Concept-oriented model: Modeling and processing data using functions: https://www.researchgate.net/publication/337336089_Concept-o... -> Read introduction (two pages) for why having only sets is not enough and why functions are important
FWIW, I find the relational model to be much nicer than typical object models. Even in Java, which I write every day at work, I've found it to be much clearer to use immutable objects representing records. Not just for interacting with the database, but just for handling information in general.
Of course, it would be extremely painful to do without Lombok's @Value, @Builder, and @With annotations.
I mean, many RDBMS's have support for JSON and/or XML, so you can sort of get it.
However, I wonder if we'd be better of as an industry if we dealt with our data in code in a more relation-y way than an object-y way.
I do hate most ORMs. The only oneS I like are Linq based ORMs. The “ORM” is actually well integrated into the language.
You might as well use something like Dapper.
But yes agreed about ActiveRecord.
Already, we're introducing a weird inversion of syntax that, in my experience, trips up people learning it: data in SQL is stored as "rows" with "columns" inside "tables". More formally, we've got a hierarchical relationship where Tables > Rows > Columns, yet we write the query as Columns > Table > Rows.
There are far more consistent and beautiful querying languages than SQL: I would point to MongoDB's query language, which is less of a query language and more of a static javascript-interpretable library, but is still far easier to learn and more consistent than SQL. The same query in MongoDB: "db.users.find({ foo: "bar" });". How is this better? It embeds the operation in the statement ("find"); reading it hierarchically follows how the data is stored (Collection > Rows); the filtering operation is the same shape as the data being stored; and it naturally disallows most injection attacks.
I don't know MongoDB query language, but gah! that looks horrible. It uses three different syntaxes; dot notation, curlies/brackets and colon key value. Full of punctuation and doesn't read like english.
There is no distinction between noun "users" and verb "find". There's extraneous "db". does foo: "bar" mean equal or is it find() that determines the operator, maybe combo of both? how do I do other operations.
.Only if you are familiar with programing language that has that same syntax does any of it make sense. Otoh even educated non-programmers are gonna be able to read the SQL as SELECT "these things" FROM "this table" WHERE "these conditions are true".
I'd argue that most relational DB users are familiar with a programming language, and therefore most likely familiar with the C-style syntax. It's better to build on something that most of the potentials users are familiar with already.
> There is no distinction between noun "users" and verb "find".
There is no such distinction in natural language either (if you see words purely as sequences of characters), you have to know what is what and infer it from the context.
> even educated non-programmers are gonna be able to read the SQL as SELECT "these things" FROM "this table" WHERE "these conditions are true".
Yeah SQL looks a bit more like natural language at first glance, but that's about it. That familiarity is a false friend, it doesn't really help with the learning curve.
This kind of thinking reminds me of the ruby community trend a decade ago when DSLs were created to look beautiful and like written language. It's useless and confusing for long-term, practical purposes. Same with BDD style testing languages. The promise that non-technical people will feel right at home and can start contributing rarely lives up to reality.
Is it an issue? using symbols makes it easier to understand syntax.
> There is no distinction between noun "users" and verb "find".
Weird criticism from SQL. It can be distinguish by call with parenthesis.
IMO SQL keyword is harder to recognize.
MongoDB is absolutely still capable of being vulnerable to injection; its just harder, because it requires the client to provide an object which is parsed by your application with no data validation. In other words, SQL is vulnerable to injection by-default, because everything is a string, while you have to opt-in to being vulnerable with MongoDB, by writing your application to parse user input with no schema.
In reality, do applications do this? Hell yeah. Wire up a basic Express API, have it auto-parse any JSON its given, pass it straight to mongo, you'll be vulnerable. But, a backend which has any kind of type safety or API schema or GraphQL or something like that will be safer on mongodb than one with all that, on a SQL database with no ORM or parameterized queries or prepared statements.
seriuosly?
db.orders.aggregate([
{
$lookup:
{
from: "warehouses",
let: { order_item: "$item", order_qty: "$ordered" },
pipeline: [
{ $match:
{ $expr:
{ $and:
[
{ $eq: [ "$stock_item", "$$order_item" ] },
{ $gte: [ "$instock", "$$order_qty" ] }
]
}
}
},
{ $project: { stock_item: 0, _id: 0 } }
],
as: "stockdata"
}
}
])
SELECT *, stockdata
FROM orders
WHERE stockdata IN (SELECT warehouse, instock
FROM warehouses
WHERE stock_item= orders.item
AND instock >= orders.ordered );Doing the same in JavaScript is possible, but it's slow and cumbersome by comparison.
MongoDB queries, while being interpretable by javascript, aren't really javascript. You can't interact with the data using javascript (well, you can, using eval, but you shouldn't). You interact with the data via the query language, which is, again, expressed in JS, just like SQL is expressed in English.
It's more accurate to consider the Aggregation Pipeline as being the "composable" system to get at data in MongoDB. And its exceedingly composable; far more than SQL. It's literally a pipeline; a series of steps which fetch, mutate, filter, map, limit, calculate, correlate, relate, and otherwise interact with the data in a database. Each step operates on the output of the previous step, in series. You can programmatically swap steps in-and-out, in production, with no string manipulation or ORM, debug each step in series, remove steps, see the output, get performance characteristics on each step. There's no complex black-boxed query execution planner or compiler, because the query plan is the pipeline.
OR, another way to look at it, using MongoDB's ORM, which is quite bad if you ask me.
That query is not my invention, it comes directly from MongoDB documentation
If it looks bad, it means it is bad by design
https://docs.mongodb.com/manual/reference/operator/aggregati...
An "idealized" NoSQL schema is far, far more complex than anything anyone used to SQL would arrive at ([1]), but most of that is because in a "pure" NoSQL/Document-oriented database, the query engine simply isn't that powerful (think Dynamo). MongoDB has an inordinately powerful array of tools to get at data in a performant way, and $lookup is available as one of those tools. Can it be misused? Yeah; just look at the parent comment to see clear misuse. But generally, it's very common to see.
Modern thinking around MongoDB schema design is closer to SQL than NoSQL/Dynamo. Arrays are bad, denormalization can be valuable but use sparingly, that kind of stuff.
[1] https://docs.aws.amazon.com/amazondynamodb/latest/developerg...
One example: We used mongodb to track the state of a general CSV import system. So, we'd have a document for each csv file a user imported, and on that document, we were storing errors which occurred during the import to later display to the user. Of course, in an array. Worked great for years, until one day, a user uploaded a very bad CSV, non-maliciously, with hundreds of thousands of lines, with dozens of errors on each line, generating an array millions of items large. The failure condition here was wild: the import just got slower, and slower, and slower, until eventually the (modestly provisioned) db cluster started failing. We immediately normalized that array into its own collection, re-ran the import, still generated millions of errors, but with no problem.
As always, a general rule doesn't apply in every situation, but in my experience, unless you have a really strong grasp on how a system's use will scale, years into the future, unbounded arrays are icky. Lookups across two collections are only a modest performance loss over a direct query, and if the arrays get up there in size, they can actually be faster.
In our case someone uploaded a _huge_ CSV with some misalignment in the columns so every row had an error.
The resulting the mongo document was larger the max document size (16MB?), so it couldn't even save to the database.
Mongo is painful to work with and I feel like I keep finding more reasons to hate it.
Not sure that is true. You can imagine having a smaller query language with clean semantics able to capture messy data relationships. Small functional expression languages come to mind. Where it lies on the spectrum from purely-table-form to can-hold-everything is a design choice.
Your query language doesn’t need to capture the underlying data model exactly. SQL is an example of this itself.
I’ve run into the orthogonality issue myself several times. You don’t need to drop SQL today, but for some data domains more expressive query models can work really well.
Are there really any SQL libraries in the traditional sense (i.e. reusable/composable SQL code with a well specified API)? SQL "libraries" typically focus on hiding the inconsistencies and the abhorrent syntax under the carpet.
And the user base is there, mostly because of the database engine properties and features. The query language itself is just a bad side-effect. And TFA does make a point that NoSQL abandoning the underlying RDBMS model is in fact a regression.
The article presents several examples where SQL's messiness cannot be plausibly attributed to underlying data relationship messiness.
Instead, perhaps a second format should be standardized, which is machine readable/writable, that more or less represents relational algebra + whatever extra features SQL supports. It can be clunky and verbose, as long as it's straight-forward and easily composable. It would effectively be like a compiler intermediate representation.
Once you have something like that, you can have as many front-ends as you want with whatever syntax you prefer.
Of course, getting all of the RDBMS vendors to agree to it is still a problem, and they'll probably all still include their own vendor-specific extensions and differences, because they want to lock you in to their system.
But at least maybe the open source ones could agree, which would still be quite beneficial.
Also that is only one of the things they are hoping to fix. It sounds like you're saying "We have 5 variants of SQL already so nobody is allowed to write any other database query languages ever. We must use SQL forever." which is stupid.
At least we don't have to change our whole data model or give up consistent data to try it!
You want flat result, aggregated result. Why does this have to be blamed on the data relationship and not SQL language itself?
For any graphical type of relationships, I find SQL utterly hard to express my queries properly. It feels like a assembly language at that point.
This is a common misunderstanding of what the relational in "relational database" means. It's not (primarily) about relationships, except insofar that relationships can be described using relations and queried using relational algebra. But the key concept is that of the mathematical relation, as per Wikipedia:
"In mathematics, an n-ary relation on n sets, is any subset of Cartesian product of the n sets (i.e., a collection of n-tuples)"
So the goal of relational db languages is to provide tools for querying these sets of sets. Joins, Unions, Selections (Restrictions), and Projections are some of the tools that are provided. SQL provides some of these, though often calls them confusing names.
For example, in SQL the "select" keyword begins the query statement, but selection proper (as per relational algebra, also called restriction) is actually what is expressed in the "where" clause. What comes after "select" in your query is technically "projection" (choosing which tuples/columns exist in the output). Many ORMs and similar tools get this messed up, because the authors of them know SQL, but not the relational algebra on which it is nominally based.
Aside from DDL (which I'm not sure how you would do without distinguishing between base relvars [tables], derived relvars [views], and other classes of relations), SQL doesn't particularly treat different kinds of relation differently beyond what is minimally necessary (you can't write, via insert or update, to a relation that isn't a relvar, for instance.)
Try Sparql.
At least the old ideas are resurging in the form of Neo4j (essentially a triplestore) and Cypher (essentially SPARQL).
What do you mean here? The difference between a table and a dataframe is that a dataframe is a construct held in memory, while a table is persisted storage written to a database.
Selecting columns before tables always felt weird to me. Doesn't it make more sense if you had a graphical view this way? (imagine boxes around the below items where you can drag lines to make connections between tables/inputs)
USERS
--user_id-- [data processing] => ...
GROUPS
SELECT * ,groups.* from users INNER JOIN groups ON users.user_id = groups.user_id
SQL is so messy and full of details that can be much better represented via dataflow diagrams.
Either way, queries need to be composed at runtime a lot of the time, so for many uses of SQL you can't just have the queries as blobs that you prepare ahead of time in some other tool - they must be objects you can work with programmatically.
Then you can use git and optionally build custom tools like a visual diff of diagram v1 vs v2.
The dev still needs to type the code first, which does not address any problems with SQL.
> Most devs use straight SQL as far as I know because once you get into more complex queries the graphical interface becomes a hassle.
Those interfaces may be limited, or the devs may not have enough experience writing "code" via a higher-level graphical interface. Going from writing SQL to creating dataflow diagrams may be a bit like switching from imperative to functional programming.
The "Selection" is the set of predicates that restrict the resulting relation. Projection is choosing which tuples ("columns") to use in it.
The relational algebra has no "tables", this is, again, a SQL thing. It has relations (sets of sets) and operations on them. In SQL "tables" are one kind of relation, and views are another.
EdgeQL looks... interesting, but I need to see more to decide for sure. Would be cool if they built out a translator, so you could pass in SQL and get back EdgeQL.
I don't think translating from SQL to take a look at the EdgeQL it produces makes much sense, since it'd result in very unidiomatic queries. SQL prefers joins and flat rows, while EdgeQL is based on following links and has great support for nested data output.
Obligatory XKCD https://xkcd.com/927/
SQL is messy because describing the underlying data relationships are messy. The orthogonality example is a great illustration of this. What exactly should the result be if there are multiple dept heads? Should the result rows be duplicated? It's not clear how edgeQl would handle either (their edgQl orthogonality examples were constructed to only have result per sub query), but it seems like they would be kept together as sets. In that case, the result set is no longer a table, it's a dataframe, which is a useful data structure but is also not what relational databases do.