SE Radio 137: SQL with Jim Melton

5 comments

• Hey guys, excellent show, maybe you could do a mic check or help the guest fix their audio before the interview? the mic problems really made it hard to listen to some great info he was giving out.

  cheers.

  —
  “No good deed ever goes unpunished”

• This was another interview that left me behind wondering whether I had wasted my time. All nice and cozy. More feeling than understanding. There are quite a few tough questions about SQL and relational databases that could and should have been asked. Tough questions don’t imply being impolite, rather, they show that you’re taking the topic, the person you’re talking to, and finally the audience seriously. Asking tough questions politely requires that the interviewers know their stuff and are well-prepared.

  Here are some questions of the kind I have in mind

  • NULL did occur in the interview, but none of its problems were touched. Consider this query:
    SELECT * FROM booleans WHERE (value = true) OR (value = false)
    It does not return rows where value is NULL. That’s because in the ternary logic SQL uses, each sub-expression is evaluated individually to unknown (not false!) and so the whole condition evaluates to unknown. Assuming that NULL is meant to express missing knowledge (not some Heisenbergian state of nature), the above result is completely counter-intuitive as a definite boolean value (even if it is unknown) must either be true or false. — How did this kind of strange logic enter the SQL standard? Does Melton think it was a mistake?
  • Why is SQL so incredibly non-modular? It is all but impossible to compose SQL statements from generic, re-usable parts.
  • How is SQL meant to be used? Apparently, Melton doesn’t know much about ORM and he understandably dislikes sprinkling Embedded SQL all over the application code. He suggests shoving all application logic into the DB in the form of PSMs (aka stored procedures), thereby exemplifying that he knows little about the architecture (and the reasoning behind it) of current multi-tier systems. In fact, he acknowledges that he is not involved with application programming. In contrast, Arno does know all this architecture stuff — why not elaborate?
  • In the last few minutes of the interview, it sounded like the SQL standardization committee is desperate for input from people who actually use their stuff. Is the committee out of touch? How could it ever come to this?

• mschuerig,

  About NULLs: Your example isn’t confusing at all, as long as one is aware of ternary logic. Which the podcast explained, and which isn’t that hard to grasp. If I were to attack NULLs, it would be how it can sometimes be surprising in relation to aggregate queries.

  Modularity: Views, CTEs, functions, procedures. I find it hard to see how SQL is non-modular.

  ORMs/multi-tier: Your point of view represents one school. Meltons statements represent another. Your attitude about this seems immature.

  About the committee being out of touch: I agree with this one, in that I find the SQL standards committee very closed. I’ve never found a way to send suggestions to them, and their metting agendas and minutes are very hard to find. I think that the SQL committee should take a look at how the C++ group works. And also consider adding mailing lists and wikis to their organizational toolset. And/or at least one of them should once a while hang around the comp.databases and/or comp.databases.theory newsgroups, for example.

• Way to go, SE-Radio! Thanks for yet another good show.

• tarvin,

  thanks for taking the time for replying to my comment. However, I think I need to fix a few misunderstandings.

  As far as NULLs are concerned, I wasn’t trying to argue against their use and I’m well aware of three-valued logic. As you say yourself, 3VL can produce surprising results. I would have liked to hear a short explanation why this particular logic was chosen for SQL and how well this decision looks in retrospect.

  The same goes for architecture. I’m not faulting Melton for being old-school, but I was missing a discussion and comparison of these architectural styles. Would it have been too much to ask “Oh, you’re putting all the business logic into the database. We tend to handle these things in application code. Do you have any thoughts about these two approaches?”

  Regarding modulartiy, specifically of queries: Let’s take the Brad-Jennifer-Angelina query: Find all actors were married to another actor until they married a co-actor from a movie. How would you decompose this into simpler parts? How would you reuse some of these parts to compose a query to find consultants that changed their affiliation after a consulting gig? Can you construct queries from reusable parts at runtime? AFAICT, these things can be achieved with abstraction layers such as ORMs, but you can’t do them in plain SQL. At least it is not obvious. — Assuming I’m not comletely wrong, why doesn’t SQL provide suitable means for these kinds of modularization and abstraction?