Hacker News Reader

Comment by jfb | Hacker News Reader

I thought this was a very good article, unusually so for a blog post about databases. I do take issue, however, with the idea that there's a beauty to SQL. There's a beauty in the conceptual model, but you have to squint sideways to see it through the deeply hostile syntax.

View on HN

I'm not sure the "Relational Model" is even a great conceptual model for common business or analytical processing. I think SQL is ugly because the model is a poor fit; if the model was solid, a clean syntax would follow naturally. As for alternatives, MDX has superseded it for core "pivot table" analytics. For application processing, it seems modern document/hierarchical databases are good for many transactional needs.

What modern relational databases have... is a very intuitive abstract storage model. That's awesome. We've been working on an alternative "navigational model" (inspired from CODASYL and ORMs) based on this storage model. Our experimental query language is at http://htsql.org

Our critique of SQL is at http://htsql.org/doc/overview.html#why-not-sql

I'm sure you'll agree with Stonebraker's recent (actually, not so recent) claims that the relational model is "all wrong":

http://blog.jooq.org/2013/08/24/mit-prof-michael-stonebraker...

htsql seems very interesting. I should soon blog about your product and critique. Have you been publishing elsewhere?

That's not complaining about the relational model at all, he's complaining about implementation details of common RDBMSes being obsolete, as they're based on row-oriented storage, slow disks, small memory, and limited concurrency.

Things like Amazon Redshift are still relational but make radically different decisions on most of those points.

Edit: that talk is well worth reading, btw. I wonder why he completely ignores flash though?

Thanks for linking to that talk, it deserves a submission all of its own.

Absolutely. Very interesting talk. Definitely deserves a submission.

BTW, the person asking the last couple of questions is Ed Bugnion, one of the co-founders of VMWare. He is a faculty now at EPFL.

Interesting, I didn't know that!

I actually submitted it, but it didn't get too much traction...

Actually the title is "The Traditional RDBMS Wisdom is All Wrong".

He's arguing against multithreaded systems in favour of a partitioned OLTP platform like H-Store (VoltDB). I didn't see anything about the relational model being "all wrong". It works well for lots of scenarios, and turning it into a KV store is also straightforward.

As far as SQL, I think people agree it's not the best query language. QUEL may have won, but Stonebraker says[1]: "The only reason SQL won in the marketplace was because IBM released DB2 in 1984 without changing the System R query language."

1: http://iggyfernandez.wordpress.com/2011/12/23/nocoug-journal...

Are you criticising the relational model, or SQL? You seem to be contradicting yourself with your own language, which you claim works well with relational databases (ergo, works well with the relational model).

The relational query model (it's operators and set algebra) is a poor fit for common business inquiries. I view SQL as a practical syntax for encoding relational queries; SQL has deviated from the pure model when the model failed to address a real world business problems. I see SQL as a success, in that it works and applies the relational model to real world concerns.

What is intuitive to users is the idea of related files/tables, and this really doesn't have much to do with the relational algebra. While we have implemented HTSQL using SQL, this is an implementation choice -- our experiment is syntax/semantics, we didn't want to be in the business of data storage and query optimization, and, we use (the incredibly awesome) PostgreSQL. HTSQL's logical query model depends upon is a tabular based abstract storage.

Oh, the relational query model based on relational algebra! I thought you wrote "relational model".

For what it's worth, a join is indeed part of relational algebra - it's a composition of binary relations.

I have found SQL to be cumbersome for expressing temporal relationships, eg find all the event As that happen within one week of event B. There's not necessarily a data schema link between the table for event A and the table for event B.

how does htsql do with this?

Let's say you wish to list all students in a university, and then, show all other students with the same birth year and month. So, you'd start with ``/student`` and then you could select all columns using the asterisk. Next, you could use the ``fork()`` operation to join to the same table based on a set of columns that are correlated.

/student{, /fork(year(dob),month(dob))}

http://demo.htsql.org/student%7B*,%20/fork%28year%28dob%29,m...

You could wrap this up in a definition to make it a bit easier to understand:

/student .define(students_same_month := fork(year(dob),month(dob))) .select(, /students_same_month )

http://demo.htsql.org/student%0A.define%28students_same_mont...

With the more general case, let's say you're interested in listing per semester, which students started during that semester. In this case, you start with ``/semester`` and then define the correlated set, ``starting_students`` which uses the ``@`` attachment operator to link the two sets. Then, you'd select columns from semester and the list of correlated starting students.

/semester .define(starting_students:= (student.start_date>=begin_date& student.start_date<=end_date)@student) {, /starting_students }

http://demo.htsql.org/semester.define%28starting_students:=%...

Typically, you'd include the new link definition, ``starting_students`` in your configuration file so that you don't have to define it again... then the query is quite intuitive:

/semester{, /starting_students }

While this all may seem complex, it's not an easy problem. More importantly, HTSQL has the notion of "navigation". You're not filtering a cross product of semesters and students, instead, you're defining a named linkage from each semester to a set of students. The rows returned in the top level of query are semesters and the rows returned in the nested level are students.

Thank you for the examples.

I made some queries that are analogous to my temporal query needs

Here I'm looking for every student, other students with DOB within 2 weeks of the given student: http://demo.htsql.org/student.define(similar_students:=%20(s...

Here for every semester with at least one student, I find the oldest and the youngest students enrolled: http://demo.htsql.org/semester%20.define(starting_students:=...

No being a computer scientist I have to admit I do not appreciate the intricacies of the 'problems with SQL' blog entries. But working with htsql I gotta say it seems a lot more intuitive than SQL. It feels like the logic correspond much better to my mental model. And that there is much less of the jumping up and down the code to nest my SQL code logic that I find myself doing all the time.

Is there a way to install this on a PostgresQL instance on Win8?

HTSQL requires Python 2.7 and psycopg2 binary. As long as those install, I don't see any problems. We have tested it with older versions of Windows without any issues. If you run into a problem, you could report an issue: https://bitbucket.org/prometheus/htsql/issues

I found the relational beauty in being able to "store every unique value just once." It resonated with the programming concept of "write a function so that you don't need to change things in many places." This in turn gave me a lens to viewing how businesses operate independent of what systems you put on top of the data.

Does this mean SQL is pretty? Not really, but it gets the job done. Though I did have to spend a lot of time optimizing SQL to force certain query optimizations.

Does this mean relational should be used everywhere? No, but it has a distinct place. I think beer is beautiful too, but I won't drink it for breakfast with the in-laws.

I disagree. There are issues with SQL (how many things can NULL mean?) but there is also beauty in it. Every language has issues.

You don't see the beauty in SQL until you try to get as much bang for the buck out of specific queries. Then the idea that you can think in terms of math and sets really shines. You can do a lot in SQL which would require a lot more app code to accomplish, and the SQL will be more elegant and easier to maintain.

> There are issues with SQL (how many things can NULL mean?)

Please elaborate, I can only think of one.

The two main meanings of NULL are "Does not exist" and "Exists but is unknown", though there are more.

Also, `we don't know if it exists'.

NULL is a marker that means this data value does not exist in the database.

Your second, and any other 'meanings' are a misuse of NULL, which is your fault, not SQLs

> NULL is a marker that means this data value does not exist in the database.

Both of the uses I stated are data values that do not exist in the database.

> Your second, and any other 'meanings' are a misuse of NULL

No, they aren't. They are different, and substantively different, reasons why data does not exist in a database. One is the value does not exist in the domain of which the DB is model, and one is that the value exists in that domain but it is not known.

(Others have pointed out the additional case of "we do not know if it exists", which is ambiguity between those two cases.)

These two substantially different meanings of NULL were recognized fairly early in the history of the relational model by the creator of the model (E.F. Codd), who proposed refining the model by having separate markers for each of those two cases.

NULL can mean a bunch of things:

1. Value is unknown

2. Value does not exist (for example, outer join results).

These have ery different implications. For example:

somestring || does_not_exist you would think would equal somestring, but with unknown we dont know.

It doesn't exactly mean that the value does not exist, it's that it's unknown whether it exists. NULL means nothing at all, not the absence of a value (though applications will often abuse it to mean that, and pragmatically that's usually OK).

The conceptual model is great, but IMHO a little impractical. Anyone who has ever had to trick the optimiser into choosing a better plan by changing the semi join from WHERE EXISTS to NOT IN (or vice versa) will appreciate what I'm saying :-)

Lovely conceptual models always founder on the shoals of terrible implementation.

Not necessarily. It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions. But the relational model is still beautiful, I find its limitations are minor in the grand scheme of things.

"It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions."

It's also impossible for a human to do it. At least the optimizer will be awake at 3 in the morning and able to respond to data changes; the human will be asleep.

I'm not disagreeing :-)

Although the poor DBA on pager may not agree with you.

That really depends on the database. With MySQL? Yes, you have to apply hundreds of tricks. With Oracle? Chances are, that the CBO knows much better than you how to transform your SQL.

Tell that to all those people still adding rule hints to their queries :-)

Good point. It's been some time though, since I've last been using anything below Oracle 12c ;-)

The problem of a query optimizer doesn't go away by not having one.

They were invented for a reason: data changes. Data volume changes and data distribution changes. If your algorithms don't change to suit the inputs, performance will still fall off a cliff. That's a fundamental problem and not unique to SQL.

And this really only matters for somewhat "interesting" queries anyway. It's not like the database will all of a sudden change an "UPDATE foo SET x = x + 1 WHERE id = 13" into a 14-table join.

In practice, you have many queries adapting nicely to changing data, and then a couple where they get some estimates wrong and drive you crazy. But just because you are able to fix the plans in some of those instances doesn't make it a valid comparison. You have to compare it against hand crafting all of the plans (or writing equivalent algorithms) and the results of doing that. I suspect that in most cases, more plans will drive you crazy if you choose the plans rather than letting an optimizer do it.

Remember, the data is going to change once you get in production.

Of course. Sorry, I didn't mean to imply CBOs are bad or unnecessary. They are fundamental and necessary. Tuning queries is of course something that needs to be reviewed as data changes - pruning unnecessary indexes is often as importent as adding new ones!

Hopefully 95% or more of queries don't need to be nudged :-)

Reply on news.ycombinator.com