Handling differences in SQL dialects

Even though SQL is a standard, the SQL dialects supported by various SQL database vendors differ to a large degree. They can have different keywords, order of keywords, different datatypes and, of course, totally different functions, especially time and date functions. That makes it hard to port SQL scripts written for one SQL back end to another back end. This is especially true for behavioral analyses, since they typically use time and date functions, which are different for every database vendor. Rax shields the analyst from these differences. Let's take a look at the table from the previous example, and a very simple `print statement:

    {[#:deptId, #:empId, $:name, &:salary]} :
    employees :=
      import [
        (#)"DEPTID",
        (#)"EMPID",
        ($)"NAME",
        (&)"SALARY"
      ] "EMPLOYEES";

    `print employees[1..5];
       

When run on Rax/MySQL, Rax executes the following SQL query in the SQL (MySQL) back end:

    SELECT rownum, C01, C02, C03, C04
    FROM (
      SELECT 0 AS rownum, DEPTID AS C01, EMPID AS C02,
             NAME AS C03, SALARY AS C04
      FROM EMPLOYEES
    ) AS A
    ORDER BY C01, C02, C03, C04
    LIMIT 5 OFFSET 0;
       

On Rax/SQLServer, the query looks like this:

    INSERT INTO RAX$$Y89PIYGQVML8W9BYS529H2T2U
    SELECT rownum, C01, C02, C03, C04
    FROM (
      SELECT ROW_NUMBER() OVER(ORDER BY C01, C02, C03, C04) AS rownum,
             C01, C02, C03, C04
      FROM (
        SELECT 0 AS rownum, DEPTID AS C01, EMPID AS C02,
               NAME AS C03, SALARY AS C04
        FROM EMPLOYEES
      ) AS A
    ) AS B
    WHERE rownum >= 1 AND rownum <= 5;
       

Even in such a simple query one can see significant differences between the SQL dialects. SQLServer lacks support for the LIMIT and OFFSET keywords, therefore the query has to be written in a completely different way.

Another problem that we regularly encounter, is that certain back ends do not support certain functionality at all. For example, SQLite does not support the POWER function. In such situations, Rax reverts to its fallback mechanism: client-side processing described in the following section.