> I can get jOOq to pretty easily yield queries that won't work if I switch out database dialects.

In fact, you can do this with SQL. The following is perfectly valid in MySQL but not in any of the other dialects:

    SELECT a, b, COUNT(*)
    FROM t
    GROUP BY a

SQL is so heavy with implicit semantics that cannot be expressed in syntax alone...