Support for LATERAL queries

[arizonatribe]

Hello, I ran across a database view whose syntax doesn't seem to be supported by pg-mem and the console error suggested opening up an issue and providing an example query.

I'm binding this SQL view in the @ViewEntity decorator for a TypeORM model, whose expression is just this raw SQL:

SELECT my.email AS user_id,
  jsonb_each.value AS something
FROM
  my_table my, LATERAL jsonb_each(mt.my_nested_json) jsonb_each(key, value)
WHERE (jsonb_each.key ~~ 'something/%'::text);

And the error I was seeing is essentially just this:

💔 Your query failed to parse.
This is most likely due to a SQL syntax error. However, you might also have hit a bug, or an unimplemented feature of pg-mem.
If this is the case, please file an issue at https://github.com/oguimbal/pg-mem along with a query that reproduces this syntax error.

👉 Failed query:

    SELECT my.email AS user_id,
      jsonb_each.value AS something
    FROM
      my_table my, LATERAL jsonb_each(mt.my_nested_json) jsonb_each(key, value)
    WHERE (jsonb_each.key ~~ 'something/%'::text);

💀 Syntax error at line 6 col 20:

        my_table my,
                     ^
Unexpected comma token: ",". Instead, I was expecting to see one of the following:

    - A "kw_left" token
    - A "kw_right" token
    - A "kw_full" token
    - A "kw_inner" token
    - A "kw_where" token
    - A "kw_group" token
    - A "kw_join" token
    - A "kw_order" token
    - A "kw_limit" token
    - A "kw_offset" token
    - A "kw_fetch" token
    - A "kw_union" token
    - A "kw_with" token
    - A "semicolon" token

Am I interpreting this correctly that the error is just because the LATERAL keyword is unexpected?

[oguimbal]

Hi, thanks for the feedback.

You're right, this syntax is not supported as of today :( To be honnest, I didnt know it existed !

Adding that to the todo list, but I think this ought to be a pretty low priority compared to other issues (I'm not sure if this keyword is used much).

Sorry ...

To others:👍 the thread if you find this issue important

[arizonatribe]

I've never seen the syntax before encountering it on that database view. I've found a way to avoid using that database view, so I'm able to continue using pg-mem on the project now.

[abenhamdine]

I'm not sure if this keyword is used much. I've never seen the syntax before encountering it on that database view

It's a very common pattern. Frankly, I don't know any large application with sql queries that does not use LATERAL JOIN.

For us, it makes pg-mem totally useless, even it's a brillant idea and a great project 👍

Is there anything we can do to help to implement it @oguimbal ?

Corresponding issue in the parser repo : https://github.com/oguimbal/pgsql-ast-parser/issues/122

[oxilor]

@oguimbal I agree with @abenhamdine. JOIN LATERAL is used rather often in large applications. I had to abandon this wonderful library when testing my CRM system just because it doesn't support this feature :(