Using Lateral Flatten and Join results in a syntax error

Question

I'm running into an issue where I get a syntax error if I try to join and lateral flatten in the same query. I've created the following example:

Setup


    CREATE TABLE "DEMO_DB"."PUBLIC"."MAIN_TABLE" (id number, department_id number, stuff variant);
    insert into "DEMO_DB"."PUBLIC"."MAIN_TABLE" (id, department_id, stuff) SELECT 1, 1, PARSE_JSON('[{ "json_id": 1, "name": "Thing 1-One" }, { "json_id": 2, "name": "Thing 1-Two" }]');
    insert into "DEMO_DB"."PUBLIC"."MAIN_TABLE" (id, department_id, stuff) SELECT 2, 1, PARSE_JSON('[{ "json_id": 1, "name": "Thing 2-One" }, { "json_id": 2, "name": "Thing 2-Two" }]');
    insert into "DEMO_DB"."PUBLIC"."MAIN_TABLE" (id, department_id, stuff) SELECT 3, 2, PARSE_JSON('[{ "json_id": 1, "name": "Thing 3-One" }, { "json_id": 3, "name": "Thing 3-Three" }]');

And setup a table to join to:


    CREATE TABLE "DEMO_DB"."PUBLIC"."DEPARTMENTS" (id number, name string);
    insert into "DEMO_DB"."PUBLIC"."DEPARTMENTS" (id, name) values (1, 'First Department');
    insert into "DEMO_DB"."PUBLIC"."DEPARTMENTS" (id, name) values (2, 'Second Department');

Examples

Basic lateral flatten works:


    SELECT
        MAIN_TABLE.id,
        MAIN_TABLE.department_id,
        my_stuff.value:json_id::number stuff_id,
        my_stuff.value:name::string stuff_name
    FROM
        "DEMO_DB"."PUBLIC"."MAIN_TABLE",
        lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff
    ;

The Problem

"SQL compilation error: error line 9 at position 59 invalid identifier 'MAIN_TABLE.DEPARTMENT_ID'"


    SELECT
        MAIN_TABLE.id,
        MAIN_TABLE.department_id,
        DEPARTMENTS.name department_name,
        my_stuff.value:json_id::number stuff_id,
        my_stuff.value:name::string stuff_name
    FROM
        "DEMO_DB"."PUBLIC"."MAIN_TABLE",
        lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff
    JOIN "DEMO_DB"."PUBLIC"."DEPARTMENTS" on (DEPARTMENTS.id = MAIN_TABLE.department_id);

More that works

-- basic join without lateral flatten

SELECT
    MAIN_TABLE.id,
    MAIN_TABLE.department_id,
    DEPARTMENTS.name department_name -- ,
--    my_stuff.value:json_id::number stuff_id,
--    my_stuff.value:name::string stuff_name
FROM
    "DEMO_DB"."PUBLIC"."MAIN_TABLE" -- ,
--    lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff
JOIN "DEMO_DB"."PUBLIC"."DEPARTMENTS" on (DEPARTMENTS.id = MAIN_TABLE.department_id);

-- Using a table in the from clause with a where
SELECT
    MAIN_TABLE.id,
    MAIN_TABLE.department_id,
    DEPARTMENTS.name department_name,
    my_stuff.value:json_id::number stuff_id,
    my_stuff.value:name::string stuff_name
FROM
    "DEMO_DB"."PUBLIC"."MAIN_TABLE",
    lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff,
    "DEMO_DB"."PUBLIC"."DEPARTMENTS"
 WHERE (DEPARTMENTS.id = MAIN_TABLE.department_id);



-- Using an inline view to pull in the join data
SELECT
    MAIN_TABLE.id,
    MAIN_TABLE.department_id,
    MAIN_TABLE.department_name,
    my_stuff.value:json_id::number stuff_id,
    my_stuff.value:name::string stuff_name
FROM
    (
      SELECT
        MAIN_TABLE.*,
        DEPARTMENTS.name department_name
      FROM
        "DEMO_DB"."PUBLIC"."MAIN_TABLE"
      JOIN "DEMO_DB"."PUBLIC"."DEPARTMENTS" on (DEPARTMENTS.id = MAIN_TABLE.department_id)
    ) MAIN_TABLE,
    lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff;

Does this answer your question? [Snowflake - Lateral cannot be on the left side of join](https://stackoverflow.com/questions/63397022/snowflake-lateral-cannot-be-on-the-left-side-of-join) — Danny Varod, Jun 28 '23 at 13:47

score 0 · Answer 1 · answered Jun 23 '20 at 00:32

0

This works:

    SELECT
    MAIN_TABLE.id,
    MAIN_TABLE.department_id,
    DEPARTMENTS.name department_name,
    my_stuff.value:json_id::number stuff_id,
    my_stuff.value:name::string stuff_name
FROM
     "DEMO_DB"."PUBLIC"."MAIN_TABLE"
JOIN "DEMO_DB"."PUBLIC"."DEPARTMENTS" on (DEPARTMENTS.id = MAIN_TABLE.department_id),
     lateral flatten ( input => MAIN_TABLE.stuff ) my_stuff ;

The order matters for lateral joins. Here is a usage note that discusses it:

https://docs.snowflake.com/en/sql-reference/constructs/join-lateral.html#usage-notes

answered Jun 23 '20 at 00:32

Greg Pavlik

10,089
2
12
29

Ah! "Just as INNER JOIN syntax can use either the comma or the words “INNER JOIN”, a lateral join can also use the comma or the words INNER JOIN." was the missing piece. I didn't realized that the comma was essentially inner joining. It might be helpful to have an example on that page that actually uses a JOIN to another table like my above. Thanks! – Chip Ach Jun 23 '20 at 14:05

Using Lateral Flatten and Join results in a syntax error

1 Answers1