Adding support for NATURAL and LATERAL joins in Snowflake

[teej]

I'm getting started fleshing out the Snowflake dialect. I'm unsure how I should approach support for these two join types.

NATURAL joins

The Snowflake spec for natural joins is as follows:

SELECT ...
FROM <object_ref1> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                       | CROSS
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

My questions are:

1. When I add new tokens like NATURAL, should I add them to the adapter or the global TokenType config? I assume the latter.
2. To add new join support, I'm not sure if I should be specifying a custom Parser._parse_join function in my config or if I should be using a transformer, or something else. I'm also not sure if I should be piggybacking on the exp.Join expression or adding a new expression type.
3. Both Snowflake and Oracle use the same syntax for NATURAL joins. Should I try to share the definition between the two somehow?
4. Should I enforce the fact that NATURAL joins in Snowflake don't have an ON condition?

LATERAL joins

Similar to NATURAL joins, I started LATERAL with a dialect-specific Parser._parse_lateral to exclude the requirement for a VIEW token which Snowflake does not use. But that approach won't cover all variants. I need to support these cases:

SELECT a FROM x, LATERAL (SELECT * FROM y) AS z
SELECT a FROM x INNER JOIN LATERAL (SELECT * FROM y) AS z
SELECT a, z.value AS z_val FROM x, LATERAL FLATTEN(input => x.b) AS z

5. Should I approach this from a dialect-specific re-working of joins, or should this go through transformers somehow?
6. To support joins in the style of FROM x, y be supported in the core parser or should I stick to the dialect?

I'm using these as a reference to understand how I should add support for complex dialect modifications, I appreciate any advice you have.

Thanks for making sqlglot, it's great to work with.

[tobymao]

Hey @teej.

In general, you should always add things to the generic code as the default path is supposed to be a super set of of SQL. Only go to dialect specific code when it's necessary.

1. New token Natural should be in global.
2. To add the new join, just put it into the global parser as natural join seems to be generic enough.
3. nothing should be needed here because you'll add everything to the generic code
4. i don't know the semantics of this, but do you mean oracle supports on for natural but snowflake doesn't? if so the only thing i would add is in snowflake a warning that on is not supported and then it would omit it on generation

For lateral joins, again, try to do it as generically as possible in the core parser.

Happy to chat more if you're interested.

[teej]

This is helpful, I'll take this approach.