Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A Critique of SQL, 40 Years Later

carlineng.com

Post reply on HN
Print view View original

251–260 of 260 posts

Re: A Critique of SQL, 40 Years Later

#251

Post by ErwinSmout »

crazygringo wrote: post #37

I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me. 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 and I get back [5, NULL] 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…

The predicate corresponding to an outer join is fundamentally disjunctive in nature (any outer join is equivalent to a UNION of at least 2 SELECTs) meaning that "after the fact", there is no way to determine which particular one of the disjuncts made the row appear in the result.

Re: A Critique of SQL, 40 Years Later

#252

Post by ErwinSmout »

Koshkin wrote: post #71
vbezhenar wrote: post #57

Earlier quoted context omitted.

You don’t do outer joins.

Don’t these exist for a reason?

Yes. The reason is lazy people expect to be able to ask five questions in one go and expect one single answer to answer all five of them.

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.

Re: A Critique of SQL, 40 Years Later

#253

Post by ErwinSmout »

dspillett wrote: post #46
crazygringo wrote: post #37

I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me. 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 and I get back [5, NULL] 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…

> but it's the twin meanings of NULL that really kills me NULL only has one meaning: NULL. This is roughly analogous to unknown. The one that his a lot of people is WHERE NOT IN ( ) where contains a NULL. Because NOT IN unrolls to “ AND AND … AND ” 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…

"The one that his a lot of people is WHERE NOT IN () where contains a NULL. Because NOT IN unrolls to “ AND AND … AND ” 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."

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.

Re: A Critique of SQL, 40 Years Later

#254

Post by ErwinSmout »

cmrdporcupine wrote: post #226
zasdffaa wrote: post #190

Earlier quoted context omitted.

Why does it rule out 'more sophisticated and elegant behaviours'?

If your language has to interoperate with data in the SQL database, it right away has to deal with SQL's limitations and oddities: 1) SQL's type model has no concept of nested relations or rich datatypes; it's limited to basic scalar types , so implementing e.g. Date's concept of "relation valued attributes" for example would be impractical 2) SQL's storage model doesn't map 1:1 with e.g. a relational model because S…

SQL (the language that is, as defined by ISO) has COLLECT and UNNEST operators which come reasonably close (not 100% of course) to "Date's concept of relation-valued attributes". Moreover, I'm told those two operators are supported by at least one of the three big dogs.

Re: A Critique of SQL, 40 Years Later

#255

Post by cryptonector »

zasdffaa wrote: post #242
cryptonector wrote: post #241

Earlier quoted context omitted.

Thanks for listing these. It's very useful. (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 lac…

Interesting. Ok > (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 is…

> 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.

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 ", or "use index for table source ", or "materialize table source 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)
and one would be forced to check for absence (like nullity, but with the requirement that one must check every time it's possible that a value is absent). In SQL we have `coalesce()`, `IS [NOT] NULL`, `IS [NOT] DISTINCT FROM NULL`, and so on, so we can do this pattern matching, but we're not required to, and that might be the real problem with SQL and NULL values.

> Good post though

Ditto.

Re: A Critique of SQL, 40 Years Later

#256

Post by ErwinSmout »

cbm-vic-20 wrote: post #82

> "we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset)." I wish the "truly awful" stuff I come up with was 0.1% as successful as SQL.

And Tony Hoare probably wished he hadn't made his "billion dollar mistake".

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.

Re: A Critique of SQL, 40 Years Later

#257

Post by zasdffaa »

cryptonector wrote: post #255
zasdffaa wrote: post #242

Earlier quoted context omitted.

Interesting. Ok > (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 is…

> 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. 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 ", or "use index for table source ", or "materialize table source and create these indices for…

This is getting even more interesting.

> 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?

Re: A Critique of SQL, 40 Years Later

#258

Post by ErwinSmout »

emptyparadise wrote: post #235
matheusmoreira wrote: post #195

Earlier quoted context omitted.

> if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong 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 se…

everyone is doing it wrong :(

Not necessarily.

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".

Re: A Critique of SQL, 40 Years Later

#259

Post by cryptonector »

zasdffaa wrote: post #257
cryptonector wrote: post #255

Earlier quoted context omitted.

> 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. 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 ", or "use index for table source ", or "materialize table source and create these indices for…

This is getting even more interesting. > 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 extensi…

> I think not breaking the optimiser, and out of band hinting appear somewhat orthogonal but I'd be very glad to be wrong.

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} @ gmail

Re: A Critique of SQL, 40 Years Later

#260

Post by tabtab »

ErwinSmout wrote: post #250
tabtab wrote: post #43

Many of those complaints seem theoretical. I like to focus on practical concerns. The biggest problem I see is that the SQL language has grown too complex. It's related to the "Lack of Orthogonality" problem mentioned in the article, but I see different solutions. SQL is not based on combinations of simpler concepts, but hard-coded keywords. But how to orthogonize (factor) it gets into philosophical differences. My f…

"Many of those complaints seem theoretical. I like to focus on practical concerns." 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 eno…

Many "overly educated" (for lack of nice way to say it) just seem to often waste time on idealistic issues. It would take a while to give a decent example, but they just seem to not understand the business world and capitalism. The Newton example is not applicable because it's usually not about discovery, but weighing tradeoffs. I don't mind their personal R&D, just don't make the customer pay for or wait too long for the R&D. After work, they can invent all they want.
Post reply on HN