CROSS APPLY - SQL Server to MariaDB Migration

SQL Server CROSS APPLY clause allows you to perform a correlated inner join (lateral join) often using a table-valued function or subquery. Correlated means that the right operand of a join clause contains a reference to the left operand.

In MariaDB you can use the CROSS JOIN LATERAL clause (still in progress, see https://jira.mariadb.org/browse/MDEV-19078).

Consider a sample data:

  CREATE TABLE colors (name VARCHAR(30), arr VARCHAR(30));
  CREATE TABLE chars (c CHAR(1));
 
  INSERT INTO colors VALUES ('Green', '["g","r","e","e","n"]');
  INSERT INTO colors VALUES ('Red', '["r","e","d"]');
  INSERT INTO colors VALUES ('Black', NULL);
 
  INSERT INTO chars VALUES ('r');
  INSERT INTO chars VALUES ('e');
  INSERT INTO chars VALUES ('n');

SQL Server:

  -- Duplicate each 'colors' line for every row returned by table-valued function
  SELECT name, value
  FROM colors 
    CROSS APPLY OPENJSON(colors.arr) WITH (value CHAR '$') t;

Result:

Note that 'Black' row is not returned by the query as the table-valued function returns NULL for it.

In MariaDB you can use CROSS JOIN or even INNER JOIN. Note that LATERAL keyword is not allowed for joins with table-valued functions:

MariaDB:

  -- Duplicate each 'colors' line for every row returned by table-valued function
  SELECT name, value
  FROM colors 
     CROSS JOIN JSON_TABLE(colors.arr, '$[*]' COLUMNS (value CHAR(1) PATH '$')) t; 
 
  -- INNER JOIN can be also used
  SELECT name, value
  FROM colors 
     INNER JOIN JSON_TABLE(colors.arr, '$[*]' COLUMNS (value CHAR(1) PATH '$')) t;

Result:

Now let's perform CROSS APPLY using a subquery:

SQL Server:

  SELECT name, c
  FROM colors 
    CROSS APPLY (SELECT c FROM chars WHERE CHARINDEX(chars.c, colors.name) <> 0) c;
 
  -- INNER JOIN returns the same result
  SELECT name, c
  FROM colors 
    INNER JOIN chars ON CHARINDEX(chars.c, colors.name) <> 0;

Result:

In MariaDB we now have to use LATERAL keyword for the correlated subquery:

MariaDB:

  -- Join fails without LATERAL keyword
  SELECT name, c
  FROM colors 
    CROSS JOIN (SELECT c FROM chars WHERE INSTR(colors.name, chars.c) <> 0) c;
  -- ERROR 1054 (42S22): Unknown column 'colors.name' in 'where clause'
 
  -- Now with LATERAL
  SELECT name, c
  FROM colors 
    CROSS JOIN LATERAL (SELECT c FROM chars WHERE INSTR(colors.name, chars.c) <> 0) c;

Result:

For more information, see SQL Server to MariaDB Migration.