taozhi8833998 / node-sql-parser

Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList and convert it back to SQL
https://taozhi8833998.github.io/node-sql-parser/
Apache License 2.0
780 stars 174 forks source link

Some JSONB operators return syntax error #2016

Closed mmollick closed 1 month ago

mmollick commented 1 month ago

Describe the bug I'm seeing syntax errors for both ?| and ?& operators.

Database Engine Postgres

To Reproduce

Node: v21.7.3 Package: v5.2.0 (also tested against latest commit from main)

For SELECT id, collection::jsonb ?| array['val1', 'val2'] FROM instances I get the following error:

SyntaxError: Expected "!", "$", "$$", "'", "(", "*", "+", "-", "--", ".", "/*", ":", "@", "@@", "ARRAY", "ARRAY_AGG", "AVG", "CASE", "CAST", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "DATE", "DATETIME", "DENSE_RANK", "E", "EXISTS", "EXTRACT", "FALSE", "FIRST_VALUE", "GROUP_CONCAT", "INTERVAL", "LAG", "LAST_VALUE", "LEAD", "MAX", "MIN", "NOT", "NTH_VALUE", "NTILE", "NULL", "RANK", "ROW_NUMBER", "SELECT", "SESSION_USER", "STRING_AGG", "SUM", "SYSTEM_USER", "TIME", "TIMESTAMP", "TRUE", "USER", "WITH", "\"", "`", "crosstab", "json_to_record", "json_to_recordset", "jsonb_to_record", "jsonb_to_recordset", "mode", "now", "percentile_cont", "percentile_disc", "trim", "~", [ \t\n\r], [0-9], or [A-Za-z_一-龥] but "|" found

For SELECT id, collection::jsonb ?& array['val1', 'val2'] FROM instances I get the following error:

SyntaxError: Expected "!", "$", "$$", "'", "(", "*", "+", "-", "--", ".", "/*", ":", "@", "@@", "ARRAY", "ARRAY_AGG", "AVG", "CASE", "CAST", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "DATE", "DATETIME", "DENSE_RANK", "E", "EXISTS", "EXTRACT", "FALSE", "FIRST_VALUE", "GROUP_CONCAT", "INTERVAL", "LAG", "LAST_VALUE", "LEAD", "MAX", "MIN", "NOT", "NTH_VALUE", "NTILE", "NULL", "RANK", "ROW_NUMBER", "SELECT", "SESSION_USER", "STRING_AGG", "SUM", "SYSTEM_USER", "TIME", "TIMESTAMP", "TRUE", "USER", "WITH", "\"", "`", "crosstab", "json_to_record", "json_to_recordset", "jsonb_to_record", "jsonb_to_recordset", "mode", "now", "percentile_cont", "percentile_disc", "trim", "~", [ \t\n\r], [0-9], or [A-Za-z_一-龥] but "&" found.

Expected behavior That the operator successfully parses into the AST.

Screenshots N/A

Additional context N/A