A map of all those new NoSQL, NewSQL, post-SQL, structured, unstructured database options that came out over the past year


So you want to go with the flow and implement your next application on top of some NoSQL, NotJustSQL, NewSQL, AlmostSQL, SQL++, NextGenSQL, and what not, just to be sure not to miss out on some of the latest developments in the data business? Here’s a little map to guide you through the jungle of choices:

http://gigaom.com/cloud/confused-by-the-glut-of-new-databases-heres-a-map-for-you/

… or you just stick with the relational data model and some decent RDBMS like Oracle, SQL Server, or Postgres and wait until things settle a little bit 🙂

Row value expressions and the BETWEEN predicate


Now this is a simple example of how SQL clause simulation can get nasty if you want to make use of some more advanced SQL clauses that aren’t supported in all databases. Consider the following predicate and equivalent transformations thereof:

The BETWEEN predicate

The BETWEEN predicate is a convenient form of expressing the fact that one expression A should be in BETWEEN two other expressions B and C. This predicate was defined already in §8.4 of SQL-1992, and then refined in SQL-1999 (adding ASYMMETRIC/SYMMETRIC):

8.3 <between predicate>

Function
    Specify a range comparison.

Format
    <between predicate> ::=
        <row value expression> [ NOT ] BETWEEN 
          [ ASYMMETRIC | SYMMETRIC ]
          <row value expression> AND <row value expression>

While ASYMMETRIC is just a verbose way of expressing the default behaviour of the BETWEEN predicate, SYMMETRIC has the useful property of indicating that the order of B and C is irrelevant. Knowing this, the following transformations can be established:

BETWEEN predicate transformations

The following statements are all equivalent:

A BETWEEN SYMMETRIC B AND C
(A BETWEEN B AND C) OR (A BETWEEN C AND B)
(A >= B AND A <= C) OR (A >= C AND A <= B)

While this is still somewhat readable, try adding row value expressions:

-- The original statement
(A1, A2) BETWEEN SYMMETRIC (B1, B2) AND (C1, C2)

-- Transforming away BETWEEN SYMMETRIC
   (     (A1, A2) >= (B1, B2) 
     AND (A1, A2) <= (C1, C2) )
OR (     (A1, A2) >= (C1, C2) 
     AND (A1, A2) <= (B1, B2) )

-- Transforming away the row value expressions
   (     ((A1 > B1) OR (A1 = B1 AND A2 > B2) OR (A1 = B1 AND A2 = B2))
     AND ((A1 < C1) OR (A1 = C1 AND A2 < C2) OR (A1 = C1 AND A2 = C2)) )
OR (     ((A1 > C1) OR (A1 = C1 AND A2 > C2) OR (A1 = C1 AND A2 = C2))
     AND ((A1 < B1) OR (A1 = B1 AND A2 < B2) OR (A1 = B1 AND A2 = B2)) )

In the lowest expression, some parts could’ve been factored out for “simplicity”. The example is just to give you a picture of what the BETWEEN [SYMMETRIC] predicate really does to row value expressions.

Native SQL support for row value expressions and BETWEEN SYMMETRIC

Here’s a comprehensive list of the 14 SQL dialects supported by jOOQ, and what is natively supported by them:

Database BETWEEN SYMMETRIC RVE = RVE RVE < RVE RVE BETWEEN
CUBRID [1] no yes no no
DB2 no yes yes yes
Derby no no no no
Firebird no no no no
H2 [2] no yes yes yes
HSQLDB yes yes yes yes
Ingres yes no no no
MySQL no yes yes no
Oracle no yes no no
Postgres yes yes yes yes
SQL Server no no no no
SQLite no no no no
Sybase ASE no no no no
Sybase SQL Anywhere no no no no

Explanation:

  • The BETWEEN SYMMETRIC column indicates, whether the database supports the SYMMETRIC keyword in general
  • The RVE = RVE column indicates, whether the database supports row value expressions in general (e.g. in equal comparison predicates)
  • The RVE < RVE column indicates, whether the database supports “ordering” comparison predicates (<, <=, >, >=) along with row value expressions
  • The RVE BETWEEN column indicates, whether the database supports the BETWEEN predicates along with row value expressions

Footnotes:

  • [1]: CUBRID doesn’t really support row value expressions. What looks like a RVE is in fact a SET in CUBRID
  • [2]: H2 doesn’t really support row value expressions. What looks like a RVE is in fact an ARRAY in H2

Row value expressions and the NULL predicate


Row value expressions are something very powerful in SQL. They have been around since the early days of standard SQL, e.g. in SQL 1992, even if not all databases implement them correctly, still today.

As always, the NULL predicate is one that is a bit tricky to understand, also in the context of row value expressions. Have a look at the following expressions:

(A, B) IS NULL
(A, B) IS NOT NULL

The SQL 1992 standard defines that:

8.6  

General Rules

1) Let R be the value of the <row value constructor>.

2) If all the values in R are the null value, then "R IS NULL" is
   true; otherwise, it is false.

3) If none of the values in R are the null value, then "R IS NOT
   NULL" is true; otherwise, it is false.

   Note: For all R, "R IS NOT NULL" has the same result as "NOT
   R IS NULL" if and only if R is of degree 1. Table 12, "<null
   predicate> semantics", specifies this behavior.

Pay some special attention to paragraph 3). Yes, the following two predicates are NOT equivalent!

    (A, B) IS NOT NULL
NOT((A, B) IS     NULL)

This is easy to understand, when factoring out the rules of equivalency:

(A, B) IS NOT NULL                -- equivalent to...
A IS NOT NULL AND B IS NOT NULL   -- equivalent to...
NOT(A IS NULL) AND NOT(B IS NULL) -- equivalent to...
NOT(A IS NULL OR B IS NULL)

-- whereas...
NOT((A, B) IS NULL)               -- equivalent to...
NOT(A IS NULL AND B IS NULL)

The truth table also nicely documents this:

+----------------+-------+-------------+------------+--------------+
|                | R IS  | R IS NOT    | NOT R IS   | NOT R IS NOT |
| Expression     | NULL  | NULL        | NULL       | NULL         |
+----------------+-------+-------------+------------+--------------+
| degree 1: null | true  | false       | false      |  true        |
| degree 1: not  | false | true        | true       |  false       |
| null           |       |             |            |              |
| degree > 1:    | true  | false       | false      |  true        |
| all null       |       |             |            |              |
| degree > 1:    | false | false       | true       |  true        |
| some null      |       |             |            |              |
| degree > 1:    | false | true        | true       |  false       |
| none null      |       |             |            |              |
+----------------+-------+-------------+------------+--------------+

More on row value expressions

jOOQ 3.0 will introduce formal and typesafe support for row value expressions and predicates based thereupon. Stay tuned for more interesting insight and articles about row vlaue expressions and how they’re supported (and/or simulated) in various SQL dialects

Big Fail. How to recognise bad software


There’s some subtlety in how “bad software” can be recognised from error messages as these… 😉

big-fail

JDEclipse: A must-have Java Decompiler


I have recently discovered this new, must-have Eclipse plugin, which is very useful for those third-party libraries whose source-code you can’t easily find or link into your Eclipse: JDEclipse.

It doesn’t only decompile byte code into Java code, it does so extremely well, keeping line numbers at the correct place, such that you can actually debug through the decompiled source code and set breakpoints that actually work.

Read more about JDEclipse here:
http://mchr3k.github.com/jdeclipse-realign/

Note that of course you shouldn’t decompile any code whose license doesn’t allow you to do so…