Статьи

Сумасшедшие переводы простых выражений SQL на различные диалекты SQL

Май 28, 2018

SQL стандартизирован ISO / IEC. У нас есть замечательные стандарты , такие как SQL-92 , SQL: 1999 , SQL: 2003 , SQL: 2008 , верно? Теоретически. На практике многие вещи не имеют эквивалента между реальными реализациями SQL. Это очень верно для поддержки общих функций, большинство из которых не являются частью какого-либо стандарта. Вот интересное понимание эквивалентных выражений для очень простых функций.

В этой статье я использую jOOQ , библиотеку, которая моделирует SQL как внутренний предметно-ориентированный язык в Java. jOOQ  строит модель AST вашего оператора SQL в памяти Java, прежде чем отобразить его на выбранном диалекте SQL. Это помогает стандартизировать SQL, так что ваша модель программирования остается простой, и в то же время имеет доступ к полезным функциям.

Следующая маленькая Java-программа используется:

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();
    }
}

Теперь проверьте этот вывод!

Печать греха (3)

Это самая простая из протестированных функций. Это делает то же самое на каждом диалекте.

      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)

Обратите внимание, что SQLite не поддерживает эту функцию. В последующих списках перечислены только те диалекты, которые действительно могут подражать рассматриваемой функции.

В приведенном выше списке ASE — это Sybase ASE, а SYBASE — это Sybase SQL Anywhere.

Мощность печати (2, 4)

Это немного сложнее, так как функция питания поддерживается не во всех базах данных. Но его легко эмулировать, используя логарифмы и выражения.

      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)

Печать Синх (3)

ХОРОШО. Гиперболический синус точно не используется каждый день. Но когда вам это нужно, вам лучше запомнить формулу. Вы? Вот как эта функция отображает:

      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))

Печать lpad («abc», 3)

Вне строковых функций, они полезны время от времени. Но даже такие простые функции, как LPAD, поддерживаются не во всех базах данных. Вот пара креативных решений с использованием повтора.

      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')

Ик! Вы видели эту эмуляцию для SQLite? Ужасно. Некоторый фон можно увидеть здесь .

Печать даты ()

Теперь арифметика datetime, вероятно, является наименее совместимой с кросс-вендором областью в SQL. Вы можете проиграть, только если не используете хорошо протестированный инструмент интеграции, такой как jOOQ, для абстрагирования этих функций. Вот что составляет простая разница между двумя датами:

      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)

Хорошая новость заключается в том, что почти в каждой базе данных есть средства для ее реализации. За исключением SQLite, где деление на 86400 секунд на самом деле не экономит летнее время (!).

Печать bit_count (5)

Не в последнюю очередь, давайте посмотрим на этого монстра. Вышеуказанная функция подсчитывает количество битов, установленных в 1 в значении аргумента 5 . Это приведет к 2 . В этом примере рассматриваются только байтовые значения (TINYINT). Вы можете представить, как будет выглядеть поддержка больших целых чисел.

   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)

Самое ужасное в вышесказанном заключается в том, что не только функция bit_count () практически не поддерживается, но и отсутствует побитовая поддержка .

Обратите внимание, что в группе пользователей jOOQ также очень интересно обсудить альтернативные, более короткие способы эмуляции функции bit_count ().

Испуганный?

Не будь В SQL есть больше минных полей, чем совместимости функций между поставщиками. Если вы хотите знать это сами, скачайте jOOQ и запустите собственную эмуляцию функций с помощью вышеуказанной программы.

Следите за будущими сообщениями на эти темы!

43
Share: