Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Top 10 SQL Dialect Emulations Implemented in jOOQ (Part 2)

DZone's Guide to

Top 10 SQL Dialect Emulations Implemented in jOOQ (Part 2)

jOOQ standardizes SQL into a type-safe, embedded internal DSL in Java. With jOOQ 3.9+, we’ve added a parser that removes the need to express everything in the jOOQ API.

· Database Zone ·
Free Resource

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

Before reading, be sure to check out Part 1!

6. VALUES Clause

Did you know that VALUES() is a clause that can be used outside of INSERT statements? Yes. In PostgreSQL, you can just write:

X

And you’re getting the following result:

column1  column2
----------------
Hello    World  
Cool     eh?    

Of course, we should never rely on such generated column names; thus again, derived column lists. In PostgreSQL, this is only possible when actually using a derived table in this context:

X

Do all the databases support this clause? Of course not! But at least it can be emulated in all databases:

ACCESS    : ... like Oracle
ASE       : ... like PostgreSQL
CUBRID    : ... like PostgreSQL
DB2       : ... like PostgreSQL
DERBY     : ... like PostgreSQL
FIREBIRD  : ... like Sybase SQL Anywhere
H2        : select "v"."c1", "v"."c2" from (
              (select null "c1", null "c2" where 1 = 0) 
               union all 
              (select * from (
                 values ('Hello', 'World'), ('Cool', 'eh?')
               ) "v")
            ) "v"
HANA      : ... like Oracle
HSQLDB    : ... like PostgreSQL
INFORMIX  : ... like Sybase SQL Anywhere
INGRES    : ... like Oracle
MARIADB   : ... like Oracle
MYSQL     : ... like Oracle
ORACLE    : select "v"."c1", "v"."c2" from (
              (select null "c1", null "c2" from dual where 1 = 0) 
               union all 
              (select * from (
                (select 'Hello', 'World' from dual)
                 union all 
                (select 'Cool', 'eh?' from dual)
              ) "v")
            ) "v"
POSTGRES  : select "v"."c1", "v"."c2" from (
              values ('Hello', 'World'), ('Cool', 'eh?')
            ) as "v"("c1", "c2")
REDSHIFT  : ... like PostgreSQL
SQLITE    : ... like H2
SQLSERVER : ... like PostgreSQL
SYBASE    : select [v].[c1], [v].[c2] from (
              (select 'Hello', 'World' from [SYS].[DUMMY]) 
               union all 
              (select 'Cool', 'eh?' from [SYS].[DUMMY])
            ) [v]([c1], [c2])
VERTICA   : ... like PostgreSQL

There are four flavors of how this is supported:

  1. PostgreSQL and others: Support both VALUES and derived column lists.
  2. H2 and others: Support only VALUES, not derived column lists.
  3. Sybase SQL Anywhere and others: Do not support VALUES, but derived column lists.
  4. Oracle and others: Support neither feature.

Clearly, this is only syntactic sugar for other, more verbose SQL, but it’s really cool when you don’t actually need any real table. In fact, the whole optional FROM clause discussion from the beginning of this article is unnecessary, once you have VALUES(), which would be the standard way to “avoid” the FROM clause.

7. Predicates Using Row Value Expressions

Once you’ve started using these, you will not want to miss them: row value expressions. They’re essentially just tuple expressions, like:

X

Or, according to the standard and to PostgreSQL, also:

X

The functionality doesn’t seem very useful when using equality predicates, but it is much more interesting when using IN predicates:

X

Or, when doing keyset pagination, through non-equality predicates:

X

Again, not all databases support these. And those that do have various levels of support. PostgreSQL is again the only database that goes “all in” on all the predicates, including funky things like the DISTINCT predicate:

X

But luckily, again, these things can be emulated. Let’s look at all three of the above examples and save ourselves the DISTINCT predicate for the next list item.

1. Equality on Row Value Expressions

This is trivial. Either it’s supported or it isn’t. Or you’re Oracle and require a special syntax to prevent ORA-00920 invalid relational operator (I would really love to hear that story... must be funky):

ACCESS    : ... like SQL Server
ASE       : ... like SQL Server
CUBRID    : ... like PostgreSQL
DB2       : ... like PostgreSQL
DERBY     : ... like SQL Server
FIREBIRD  : ... like SQL Server
H2        : ... like PostgreSQL
HANA      : ... like SQL Server
HSQLDB    : ... like PostgreSQL
INFORMIX  : select * from customer 
            where row (first_name, last_name) = row ('MARY', 'SMITH')
INGRES    : ... like SQL Server
MARIADB   : ... like PostgreSQL
MYSQL     : ... like PostgreSQL
ORACLE    : select * from customer 
            where (first_name, last_name) = (('MARY', 'SMITH'))
POSTGRES  : select * from customer 
            where (first_name, last_name) = ('MARY', 'SMITH')
REDSHIFT  : ... like PostgreSQL
SQLITE    : ... like SQL Server
SQLSERVER : select * from customer 
            where (first_name = 'MARY' and last_name = 'SMITH')
SYBASE    : ... like SQL Server
VERTICA   : ... like PostgreSQL

Note that Informix requires the ROW() constructor, which should be optional. And again, Oracle is… Oracle.

2. IN Predicate

Emulating this is much more tricky if it is not supported. Remember that IN and EXISTS predicates can be equivalent, so there’s always a way to transform them into each other.

ACCESS    : ... like SQLite
ASE       : ... like SQL Server
CUBRID    : ... like SQL Server
DB2       : ... like SQL Server
DERBY     : ... like SQL Server
FIREBIRD  : ... like SQL Server
H2        : select * from customer where (first_name, last_name) in (
              select (first_name, last_name) from actor
            )
HANA      : ... like SQLite
HSQLDB    : ... like PostgreSQL
INFORMIX  : ... like SQL Server
INGRES    : ... like SQLite
MARIADB   : ... like PostgreSQL
MYSQL     : ... like PostgreSQL
ORACLE    : select * from customer where (first_name, last_name) in ((
              select first_name, last_name from actor
            ))
POSTGRES  : select * from customer where (first_name, last_name) in (
              select first_name, last_name from actor
            )
REDSHIFT  : ... like PostgreSQL
SQLITE    : select * from customer where exists (
              select x.c1, x.c2 from (
                (select null c1, null c2 where 1 = 0) 
                 union all 
                (select first_name, last_name from actor)
              ) x 
              where (first_name = x.c1 and last_name = x.c2)
            )
SQLSERVER : select * from customer where exists (
              select x.c1, x.c2 
              from (select first_name, last_name from actor) x(c1, c2) 
              where (first_name = x.c1 and last_name = x.c2)
            )
SYBASE    : ... like SQL Server
VERTICA   : ... like SQL Server

Observations:

  • At this point, it’s worth mentioning that these things work “by accident” in H2. H2, unfortunately, decided to use the (a, b, …, n) syntax for arrays, which are similar to tuples, but not the same thing. You can see in the H2 syntax that we have to wrap the two columns of the subquery in parentheses as well for the IN predicate to work as expected.
  • The transformation to an EXISTS() predicate requires the derived column list feature again. This is why some emulations are even more verbose than others.

3. Non-Equality Predicate

This predicate can be expanded to its standard definition again, easily, if it is not natively supported:

ACCESS    : ... like Oracle
ASE       : ... like PostgreSQL
CUBRID    : ... like Oracle
DB2       : ... like PostgreSQL
DERBY     : ... like Oracle
FIREBIRD  : ... like Oracle
H2        : ... like PostgreSQL
HANA      : ... like Oracle
HSQLDB    : ... like PostgreSQL
INFORMIX  : ... like Oracle
INGRES    : ... like Oracle
MARIADB   : ... like PostgreSQL
MYSQL     : ... like PostgreSQL
ORACLE    : select * from customer where (
              first_name >= 'JENNIFER' and (
                first_name > 'JENNIFER' or (
                  first_name = 'JENNIFER' and last_name > 'DAVIS'
                )
              )
            )
POSTGRES  : select * from customer 
              where (first_name, last_name) > ('JENNIFER', 'DAVIS')
REDSHIFT  : ... like Oracle
SQLITE    : ... like Oracle
SQLSERVER : ... like Oracle
SYBASE    : ... like Oracle
VERTICA   : ... like PostgreSQL

Observation: Strictly speaking, it is not necessary to have one of the two predicates redundant in the emulation, but unfortunately, many databases have trouble when the top boolean operator of a boolean expression is OR rather than AND.

8. The DISTINCT Predicate

In the previous section, we briefly mentioned the DISTINCT predicate, a useful predicate that helps handling NULL values as we’s mostly expect.

A quick summary in PostgreSQL:

X

This yields:

v1  v2  d
-----------------
1   1   false    
1   2   true     
1       true     
2   1   true     
2   2   false    
2       true     
    1   true     
    2   true     
        false    

Conveniently, this never returns NULL when comparing anything with NULL, so simply spoken,NULL IS NOT DISTINCT FROM NULL is TRUE. Quite some syntax, but hey, it’s SQL.

Regrettably, only few databases support the standard syntax, and MySQL and SQLite have a much more concise, non-standard operator. Let’s emulate the query from the previous section in our databases:

X

Observe that there’s a really cool way to emulate this operation using INTERSECT because interestingly, set operations also treat two NULL values as “the same,” i.e. non-DISTINCT. We have:

ACCESS    : ... like SQL Server (plus, add the dual table)
ASE       : ... like SQL Server (plus, add the dual table)
CUBRID    : ... like SQL Server (plus, add the dual table)
DB2       : ... like SQL Server (plus, add the dual table)
DERBY     : ... like SQL Server (plus, add the dual table)
FIREBIRD  : ... like PostgreSQL
H2        : ... like PostgreSQL
HANA      : ... like SQL Server (plus, add the dual table)
HSQLDB    : ... like PostgreSQL
INFORMIX  : ... like SQL Server (plus, add the dual table)
INGRES    : ... like SQL Server (plus, add the dual table)
MARIADB   : ... like MySQL
MYSQL     : select * from customer where (not((first_name, last_name) 
                                         <=> ('JENNIFER', 'DAVIS')))
ORACLE    : ... like SQL Server (plus, add the dual table)
POSTGRES  : select * from customer where (first_name, last_name) 
                        is distinct from ('JENNIFER', 'DAVIS')
REDSHIFT  : ... like PostgreSQL
SQLITE    : select * from customer where ((first_name, last_name) 
                                   is not ('JENNIFER', 'DAVIS'))
SQLSERVER : select * from customer where not exists (
              (select first_name, last_name) 
               intersect 
              (select 'JENNIFER', 'DAVIS')
            )
SYBASE    : ... like SQL Server (plus, add the dual table)
VERTICA   : ... like SQL Server

Want to try it yourself? The original PostgreSQL truth-table producing query can be transformed to this one:

X

It produces the same truth table. Cool, eh?

9. DDL Statements

This is one of the main reasons why we’re doing all of this. We want to allow for SQL text-based migration scripts (i.e. run with Flyway) to be translatable to any kind of SQL dialect. Because DDL is really the most boring part of SQL to keep vendor-agnostic.

Just two short examples.

1. Copying a Table Structure Into a New Table

A quick and dirty way to copy a table structure is this:

X

Looks cool, hm? Unfortunately, there is some trouble with the syntax, as you will see in the emulations:

DB2       : create table x as (select 1 one from "SYSIBM"."DUAL") 
            with no data
H2        : ... like Oracle
MARIADB   : ... like Oracle
MYSQL     : ... like Oracle
ORACLE    : create table x as select 1 one from dual where 1 = 0
POSTGRES  : create table x as select 1 as one with no data
SQLSERVER : select 1 one into x where 1 = 0

I’ve left out a couple of dialects, as this hasn’t been integration tested everywhere yet, being a work in progress. There are 4 flavors:

  1. PostgreSQL: Actual support for the WITH [ NO ] DATA clause.
  2. DB2: Actual support for the WITH [ NO ] DATA clause (but funky requirement to wrap the source query in parentheses.
  3. Oracle: No support for the clause (easy to emulate with dummy predicate), but at least support for CTAS (CREATE TABLE AS SELECT).
  4. SQL Server: Vendor specific alternative to CTAS.

The inverse is equally fun to emulate, let’s actually add the data:

X

And we’re getting:

DB2       : begin 
              execute immediate '
                create table x as (select 1 one from "SYSIBM"."DUAL") 
                with no data 
              '; 
              execute immediate '
                insert into x select 1 one from "SYSIBM"."DUAL" 
              '; 
            end
ORACLE    : create table x as select 1 one from dual
POSTGRES  : create table x as select 1 as one with data
SQLSERVER : select 1 one into x

Let’s focus on the interesting bits only.

  • Oracle, PostgreSQL, SQL Server as before.
  • DB2 actually cannot copy the data with the table structure. Huh!

As can be seen above, in cases like this, we might need to split a single DDL statement in a statement batch or anonymous block containing several statements. This is a work in progress, as not all databases support anonymous blocks or statement batches.

There are many other interesting DDL emulations, and a lot of it is not yet implemented. Just play around with them here and send us your feature requests/ideas here.

10. Built-In Functions

Last but not least, there are a ton of built-in functions, such as the boring LPAD()function. (Left pad is known for various things.) Migrating these between databases is really really tedious. We’re here to help! Let’s emulate:

X

Translation:

ACCESS    : replace(space(' ' - len('abc')), ' ', 5) & 'abc'
ASE       : (replicate(5, (' ' - char_length('abc'))) || 'abc')
CUBRID    : lpad('abc', ' ', 5)
DB2       : lpad('abc', ' ', 5)
DERBY     : lpad('abc', ' ', 5)
FIREBIRD  : lpad('abc', ' ', 5)
H2        : lpad('abc', ' ', 5)
HANA      : lpad('abc', ' ', 5)
HSQLDB    : lpad('abc', ' ', 5)
INFORMIX  : lpad('abc', ' ', 5)
INGRES    : lpad('abc', ' ', 5)
MARIADB   : lpad('abc', ' ', 5)
MYSQL     : lpad('abc', ' ', 5)
ORACLE    : lpad('abc', ' ', 5)
POSTGRES  : lpad('abc', ' ', 5)
REDSHIFT  : lpad('abc', ' ', 5)
SQLITE    : substr(replace(replace(substr(quote(zeroblob(((' ' - length('abc') - 1 + length("5")) / length("5") + 1) / 2)), 3), '''', ''), '0', "5"), 1, (' ' - length('abc'))) || 'abc'
SQLSERVER : (replicate(5, (' ' - len('abc'))) + 'abc')
SYBASE    : (repeat(5, (' ' - length('abc'))) || 'abc')
VERTICA   : lpad('abc', ' ', 5)

Forgive me again for the SQLite version. It was a suggestion made by an unknown user on Stack Overflow, the place where I tend to nerd-snipe people into solving such problems for me for free.

Conclusion

jOOQ standardizes SQL into a type-safe, embedded internal DSL in Java. With jOOQ 3.9+, we’ve added a parser (which is also publicly available here) that removes the need to express everything in the jOOQ API. Just parse your random SQL string and translate it to some other SQL dialect. This list could easily be extended to 50 items or more, but it is much more fun to play around with our website and try this on your own.

Please, if you do, do report any issue, feature request that you’d like to see here to help us make this new tool even more valuable for you. In the near future, we’re going to more closely integrate this parser with other tools such as Flyway, as we think there’s a lot of value in vendor-agnostic, standardized SQL.

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

Topics:
database ,tutorial ,clauses ,jooq ,dialect ,emulations

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}