Snowflake (LEFT JOIN) LATERAL: Unsupported subquery type cannot be evaluated

user_5070879 0
Viewed 2906

Lateral Join

In a FROM clause, the LATERAL keyword allows an in-line view to reference columns from a table expression that precedes that in-line view.

A lateral join behaves more like a correlated subquery than like most JOINs.

Let's tweak the code provided in documentation a bit:

CREATE TABLE departments (department_id INTEGER, name VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR,
                        department_ID INTEGER, project_names ARRAY);

INSERT INTO departments (department_ID, name) VALUES 
    (1, 'Engineering'), 
    (2, 'Support'),
    (3, 'HR');  -- adding new row

INSERT INTO employees (employee_ID, last_name, department_ID) VALUES 
    (101, 'Richards', 1),
    (102, 'Paulson',  1),
    (103, 'Johnson',  2);

Query:

SELECT * 
FROM departments AS d,
LATERAL (SELECT * FROM employees AS e 
         WHERE e.department_ID = d.department_ID 
         ORDER BY employee_id DESC LIMIT 1) AS iv2  -- adding ORDER BY ... LIMIT ...
ORDER BY employee_ID;

SQL compilation error: Unsupported subquery type cannot be evaluated

Yes, I am aware I could rewrite this query with ROW_NUMBER() or other ways.

1) Why the usage of TOP/LIMIT is not possible in this particular scenario?

2) Is there a syntax to achieve LEFT JOIN LATERAL/OUTER APPLY?

I would like to be able too get all source rows in the resultset even if the LATERAL subquery produces no rows for them. To get as final result:

┌────────────────┬──────────────┬──────────────┬────────────┬────────────────┬───────────────┐
│ department_id  │    name      │ employee_id  │ last_name  │ department_id  │ project_names │
├────────────────┼──────────────┼──────────────┼────────────┼────────────────┼───────────────┤
│             1  │ Engineering  │ 102          │ Paulson    │ 1              │ null          │
│             2  │ Support      │ 103          │ Johnson    │ 2              │ null          │
│             3  │ HR           │ null         │ null       │ null           │ null          │
└────────────────┴──────────────┴──────────────┴────────────┴────────────────┴───────────────┘

db<>fiddle demo

snowflake-cloud-data-platform
1 Answers

So even though we have previously discussed that you know you can rewrite it, here is it rewrite

WITH departments AS (
    SELECT * FROM VALUES
        (1, 'Engineering'), 
        (2, 'Support'),
        (3, 'HR')
        v(department_ID, name)
), employees AS (
    SELECT * FROM VALUES 
        (101, 'Richards', 1),
        (102, 'Paulson',  1),
        (103, 'Johnson',  2)
        v(employee_ID, last_name, department_ID)
), dep_emp AS (
  SELECT * 
  FROM employees 
  QUALIFY ROW_NUMBER() OVER (PARTITION BY department_ID ORDER BY employee_id) = 1
)
SELECT * 
FROM departments AS d
LEFT JOIN dep_emp AS e ON d.department_ID = e.department_ID
ORDER BY employee_ID;

gives as you wished:

DEPARTMENT_ID    NAME           EMPLOYEE_ID    LAST_NAME    DEPARTMENT_ID
1                Engineering    101            Richards     1
2                Support        103            Johnson      2
3                HR             null           null         null

By moving from a LATERAL to a CTE with the QUALIFY to implement the LIMIT/TOP and then using a LEFT JOIN to get the empty matches, you have the steps you want.

To the unasked question of why is it this way. Snowflake is not really a per-row Database, it's more a Map/Reduce/MergeJoin processes, and simple correlated subqueries it can rewrite as multi-steps (aka like CTE/joins) but complex stuff it cannot. They are improving it all the time. But given you know your data and you know your model, it just makes most sense to express things in batches of operations and let the power of MergeJoin give you wins.

Is there a syntax to achieve LEFT JOIN LATERAL/OUTER APPLY? this is done via parameter , OUTER => TRUE in the FLATTEN command

user_439920
Related