The JDBC Boolean Compatibility List

Interestingly, boolean types have been introduced only late in the SQL standard, namely in SQL:1999. Even today, not all databases natively support BOOLEAN or BIT types. Most importantly, we can still wait for them in Oracle for a while. Here’s “Ask Tom”‘s point of view from 2002 on the subject:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6263249199595

Oracle. Why U No Boolean? tweet this

People have worked around this limitation by using numeric or string literals instead. For instance 1 / 0, Y / N, T / F or the SQL standard 'true' / 'false'.

Booleans in JDBC

From a JDBC API perspective, boolean values can be set as bind values through PreparedStatement.setBoolean() or fetched from result sets through ResultSet.getBoolean(), and similar methods. If your database supports booleans, the Java boolean type nicely maps to SQL BOOLEAN – even if Java’s Boolean wrapper type would have been a better fit to respect NULLs.

But if you’re storing boolean values in INTEGER, CHAR(1) or VARCHAR(1) columns, things look differently in various databases. Consider the following example:

CREATE TABLE booleans (
  val char(1)
);

And then, run this Java program (we’re using jOOQ to keep things concise)

try {
    DSL.using(configuration)
       .execute(
       "insert into boolean (val) values (?)", true);
}
catch (Exception e) {
    e.printStackTrace();
}

DSL.using(configuration)
   .fetch("select * from booleans");

Not all databases / JDBC drivers support the above. These databases will run the above program:

  • Firebird (inserts ‘Y’ or ‘N’)
  • HSQLDB (inserts ‘1’ or ‘0’)
  • IBM DB2 (inserts ‘1’ or ‘0’)
  • MariaDB (inserts ‘1’ or ‘0’)
  • Microsoft Access (inserts ‘1’ or ‘0’)
  • MySQL (inserts ‘1’ or ‘0’)
  • Oracle (inserts ‘1’ or ‘0’)
  • SQL Server (inserts ‘1’ or ‘0’)
  • Sybase (inserts ‘1’ or ‘0’)

… whereas these databases will throw an exception:

  • CUBRID
  • Derby
  • H2
  • Ingres
  • PostgreSQL
  • SQLite

Booleans in the SQL standard

It is worth mentioning, that the SQL standard specifies how to deal with boolean to string conversion in the specification of the CAST() function:

6.13 <cast specification>
[...]
10) If TD is fixed-length character string, 
   then let LTD be the length in characters
   of TD.
[...]
e) If SD is boolean, then
Case:
i) If SV is True and LTD is not less than 4, 
   then TV is 'TRUE' extended on the right by
   LTD–4
s.
ii) If SV is False and LTD is not less than 5,
   then TV is 'FALSE' extended on the right by
   LTD–5 <space>s.
iii) Otherwise, an exception condition is 
   raised: data exception — invalid character
   value for cast.

So, most Open Source databases show what could be interpreted as the “correct” behaviour, even if from a historic perspective, 1/0 should be accepted behaviours. Beware of this limitation when using an Open Source test database!

For more information about this and the H2 database, please refer to this thread on the H2 user group.

Crazy Translations of Simple SQL Expressions to Various SQL Dialects

SQL is standardised by ISO / IEC. We have wonderful standards, such as SQL-92, SQL:1999, SQL:2003, SQL:2008. Right? In theory. In practice, many things do not have an equivalence between the actual SQL implementations. This is very true for common function support – most of which are not part of any standard. Here’s an interesting insight into equivalent expressions for very simple functions.

For this article, I’m using jOOQ, a library that models SQL as an internal domain-specific language in Java. jOOQ builds an AST model of your SQL statement in Java memory, before rendering it to the chosen SQL dialect. This helps standardising SQL, such that your programming model stays simple, while being able to access useful functionality.

The following little Java program is used:

package org.jooq.compatibility;

import static org.jooq.SQLDialect.SQL99;
import static org.jooq.impl.DSL.*;

import java.sql.Date;
import java.util.EnumSet;

import org.jooq.QueryPart;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

public class Compatibility {

    // The main method prints a couple of simple
    // and common SQL functions
    public static void main(String[] args) {
        print(sin(3));
        print(power(2, 4));
        print(sinh(3));
        print(lpad(inline("abc"), 3));
        print(dateDiff(currentDate(),
                       Date.valueOf("2000-01-01")));
        print(bitCount((byte) 5));
    }

    private static void print(QueryPart part) {
        System.out.println("Printing " + part);
        System.out.println("---------------------");

        // Get only SQLDialect families, don't
        // distinguish between
        // SQL Server 2008 or SQL Server 20012
        EnumSet<SQLDialect> dialects =
            EnumSet.noneOf(SQLDialect.class);
        for (SQLDialect dialect:SQLDialect.values())
            if (dialect != SQL99)
                dialects.add(dialect.family());

        // Render the passed in SQL clause to
        // all supported SQL dialects
        for (SQLDialect dialect: dialects)
            System.out.println(
                String.format("%1$9s : %2$s",
                dialect, DSL.using(dialect)
                            .renderInlined(part)
            ));

        System.out.println();
        System.out.println();
    }
}

Now check out this output!

Printing sin(3)

This is the most straightforward among the tested functions. It renders the same in every dialect.

      ASE : sin(3)
   CUBRID : sin(3)
      DB2 : sin(3)
    DERBY : sin(3)
 FIREBIRD : sin(3)
       H2 : sin(3)
   HSQLDB : sin(3)
   INGRES : sin(3)
  MARIADB : sin(3)
    MYSQL : sin(3)
   ORACLE : sin(3)
 POSTGRES : sin(3)
   SQLITE : sin(3)
SQLSERVER : sin(3)
   SYBASE : sin(3)

Note that SQLite doesn’t really support this function, though. In subsequent listings, only those dialects are listed that can really emulate the function in question.

In the above list, ASE is Sybase ASE whereas SYBASE is Sybase SQL Anywhere.

Printing power(2, 4)

This gets a little trickier, as the power function isn’t supported in all databases. But it’s easy to emulate it using logarithms and exp.

      ASE : power(2, 4)
   CUBRID : power(2, 4)
      DB2 : power(2, 4)
    DERBY : exp((ln(2) * 4))
 FIREBIRD : power(2, 4)
       H2 : power(2, 4)
   HSQLDB : power(2, 4)
   INGRES : power(2, 4)
  MARIADB : power(2, 4)
    MYSQL : power(2, 4)
   ORACLE : power(2, 4)
 POSTGRES : power(2, 4)
SQLSERVER : power(2, 4)
   SYBASE : power(2, 4)

Printing sinh(3)

OK. Hyperbolic Sine isn’t exactly used every day. But when you need it, you better remember the formula. Do you? Here’s how this function renders:

      ASE : ((exp((3 * 2)) - 1) / (exp(3) * 2))
   CUBRID : ((exp((3 * 2)) - 1) / (exp(3) * 2))
      DB2 : sinh(3)
    DERBY : sinh(3)
 FIREBIRD : sinh(3)
       H2 : sinh(3)
   HSQLDB : ((exp((3 * 2)) - 1) / (exp(3) * 2))
   INGRES : ((exp((3 * 2)) - 1) / (exp(3) * 2))
  MARIADB : ((exp((3 * 2)) - 1) / (exp(3) * 2))
    MYSQL : ((exp((3 * 2)) - 1) / (exp(3) * 2))
   ORACLE : sinh(3)
 POSTGRES : ((exp((3 * 2)) - 1) / (exp(3) * 2))
   SQLITE : sinh(3)
SQLSERVER : ((exp((3 * 2)) - 1) / (exp(3) * 2))
   SYBASE : ((exp((3 * 2)) - 1) / (exp(3) * 2))

Printing lpad(‘abc’, 3)

Off to string functions, they’re useful every now and then. But even simple functions as LPAD aren’t supported in all databases. Here’s a couple of creative solutions using repeat.

      ASE : (replicate(' ', (3 - char_length('abc'))) || 'abc')
   CUBRID : lpad('abc', 3, ' ')
      DB2 : lpad('abc', 3, ' ')
 FIREBIRD : cast(lpad('abc', 3, ' ') as varchar(4000))
       H2 : lpad('abc', 3, ' ')
   HSQLDB : lpad('abc', 3, ' ')
   INGRES : lpad('abc', 3, ' ')
  MARIADB : lpad('abc', 3, ' ')
    MYSQL : lpad('abc', 3, ' ')
   ORACLE : lpad('abc', 3, ' ')
 POSTGRES : lpad('abc', 3, ' ')
   SQLITE : substr(replace(replace(substr(quote(zeroblob(((3 - length('abc') - 1 + length(' ')) / length(' ') + 1) / 2)), 3), '''', ''), '0', ' '), 1, (3 - length('abc'))) || 'abc'
SQLSERVER : (replicate(' ', (3 - len('abc'))) + 'abc')
   SYBASE : (repeat(' ', (3 - length('abc'))) || 'abc')

Eek! Did you see that emulation for SQLite? Awful. Some background can be seen here.

Printing datediff()

Now, datetime arithmetics is probably the least cross-vendor compatible area in SQL. You can only lose if you do not use a well integration-tested tool like jOOQ to abstract these functions. Here’s what the simple difference between two dates amounts to:

      ASE : datediff(day, '2000-01-01', current_date())
   CUBRID : current_date() - date '2000-01-01'
      DB2 : (days(current_date) - days(date '2000-01-01'))
    DERBY : {fn timestampdiff(sql_tsi_day, date('2000-01-01'), current_date) }
 FIREBIRD : datediff(day, date '2000-01-01', current_date)
       H2 : datediff('day', date '2000-01-01', current_date())
   HSQLDB : datediff('day', date '2000-01-01', current_date)
   INGRES : cast((current_date - date '2000-01-01') as integer)
  MARIADB : datediff(current_date(), date '2000-01-01')
    MYSQL : datediff(current_date(), date '2000-01-01')
   ORACLE : sysdate - date '2000-01-01'
 POSTGRES : current_date - date '2000-01-01'
   SQLITE : (strftime('%s', current_date) - strftime('%s', '2000-01-01')) / 86400
SQLSERVER : datediff(day, '2000-01-01', convert(date, current_timestamp))
   SYBASE : datediff(day, '2000-01-01', current date)

The good news is, almost every database has some means of actually implementing this. Except SQLite, where dividing by 86400 seconds is not really daylight savings save (!).

Printing bit_count(5)

Last but not least, let’s have a look at this monster. The above function counts the number of bits set to 1 in the argument value 5. It would result to 2. This example only deals with byte values (TINYINT). You can imagine what larger integer support would look like.

   CUBRID : cast(((5 & 1) + ((5 & 2) >> 1) + ((5 & 4) >> 2) + ((5 & 8) >> 3) + ((5 & 16) >> 4) + ((5 & 32) >> 5) + ((5 & 64) >> 6) + ((5 & -128) >> 7)) as int)
      DB2 : cast((bitand(5, 1) + (bitand(5, 2) / power(2, 1)) + (bitand(5, 4) / power(2, 2)) + (bitand(5, 8) / power(2, 3)) + (bitand(5, 16) / power(2, 4)) + (bitand(5, 32) / power(2, 5)) + (bitand(5, 64) / power(2, 6)) + (bitand(5, -128) / power(2, 7))) as integer)
 FIREBIRD : cast((bin_and(5, 1) + bin_shr(bin_and(5, 2), 1) + bin_shr(bin_and(5, 4), 2) + bin_shr(bin_and(5, 8), 3) + bin_shr(bin_and(5, 16), 4) + bin_shr(bin_and(5, 32), 5) + bin_shr(bin_and(5, 64), 6) + bin_shr(bin_and(5, -128), 7)) as integer)
       H2 : cast((bitand(5, 1) + (bitand(5, 2) / power(2, 1)) + (bitand(5, 4) / power(2, 2)) + (bitand(5, 8) / power(2, 3)) + (bitand(5, 16) / power(2, 4)) + (bitand(5, 32) / power(2, 5)) + (bitand(5, 64) / power(2, 6)) + (bitand(5, -128) / power(2, 7))) as int)
   HSQLDB : cast((bitand(5, 1) + (bitand(5, 2) / power(2, 1)) + (bitand(5, 4) / power(2, 2)) + (bitand(5, 8) / power(2, 3)) + (bitand(5, 16) / power(2, 4)) + (bitand(5, 32) / power(2, 5)) + (bitand(5, 64) / power(2, 6)) + (bitand(5, -128) / power(2, 7))) as int)
  MARIADB : bit_count(5)
    MYSQL : bit_count(5)
   ORACLE : cast((bitand(5, 1) + (bitand(5, 2) / power(2, 1)) + (bitand(5, 4) / power(2, 2)) + (bitand(5, 8) / power(2, 3)) + (bitand(5, 16) / power(2, 4)) + (bitand(5, 32) / power(2, 5)) + (bitand(5, 64) / power(2, 6)) + (bitand(5, -128) / power(2, 7))) as number(10))
 POSTGRES : cast(((5 & 1) + ((5 & 2) >> 1) + ((5 & 4) >> 2) + ((5 & 8) >> 3) + ((5 & 16) >> 4) + ((5 & 32) >> 5) + ((5 & 64) >> 6) + ((5 & -128) >> 7)) as int)
   SQLITE : cast(((5 & 1) + ((5 & 2) >> 1) + ((5 & 4) >> 2) + ((5 & 8) >> 3) + ((5 & 16) >> 4) + ((5 & 32) >> 5) + ((5 & 64) >> 6) + ((5 & -128) >> 7)) as int)
   SYBASE : cast(((5 & 1) + ((5 & 2) / power(2, 1)) + ((5 & 4) / power(2, 2)) + ((5 & 8) / power(2, 3)) + ((5 & 16) / power(2, 4)) + ((5 & 32) / power(2, 5)) + ((5 & 64) / power(2, 6)) + ((5 & -128) / power(2, 7))) as int)

The awful thing about the above is that not only the bit_count() function is hardly supported, but there’s a lot of lack of support for bitwise operations as well.

Note, there is also a very interesting discussion on the jOOQ User Group about alternative, shorter ways to emulate the bit_count() function.

Scared?

Don’t be. There’s bigger minefields than function cross-vendor compatibility in SQL. If you want to know for yourself, download jOOQ and run your own function emulations with the above program.

Stay tuned for future posts on these topics!