I've just added this project grammar to https://mingodad.github.io/parsertl-playground/playground/ an Yacc/Lex compatible online editor/tester (select JsSql parser from Examples then click Parse to see the parse tree for the content in Input source).

From there we can also generate an EBNF understood by (IPV6) https://www.bottlecaps.de/rr/ui or (IPV4) https://rr.red-dove.com/ui to generate a nice navigable railroad diagram.

I hope it can help develop/debug/document this project grammar.
Any feedback is welcome !

Unlike other functions LEFT() and RIGHT() are not working in the selection of a query:

`sqlParser.parse('SELECT LEFT(a, 3) FROM b')`

returns a parseError

请原谅我英语不好

select
t2.xxx，
t2.xxx,
t2.xxx
from
(select t1.xxx,t1xxx,t1.xxx from tables t1 FETCH FIRST 100 rows only）t2
left join
tables t3
on t2.xxx = t3.xxx

var sqlParser = windows.sqlparser;
var qst = sqlParser.parse(querySql);
报错信息如下：
Uncaught Error :Parser error on line 1:
...tables t1 FETCH F1

Excepting 'EOF'，',' ,

不支持DB2 的FETCH

LEFT(string, charcters length), RIGHT(string, characters length) not getting exact result as we expect instead throw error

Hi this is a great lib.

I found out that the GROUP BY clause is parsed value identical to ORDER BY clause which has sortOpt ASC or DESC

i think this is a bug because the group by clause also accepting sortOpt

Thank you

A little more coverage of sql queries would be very useful.

update TABLE_NAME set COLUMN_NAME=0 where ID_COLUMN=1

when I parse sql like
select * from test where a<>1
I got this error:

sqlParser.js:414 Uncaught Error: Parse error on line 1:
...* from test where a<>1
-----------------------^
Expecting 'ALL', 'NUMERIC', 'IDENTIFIER', 'QUOTED_IDENTIFIER', 'STRING', 'EXPONENT_NUMERIC', 'HEX_NUMERIC', 'TRUE', 'FALSE', 'NULL', '(', 'CASE', '+', '-', '~', '!', 'BINARY', 'ROW', 'EXISTS', '{', 'ANY', got '>

It seems unsupported to call functions from within the FROM clause. Working example for SQLite:

SELECT one.name, group_concat(j.value, ', ') FROM one, json_each(one.stringArray) AS j GROUP BY one.id

Table one:

CREATE TABLE one (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    stringArray TEXT CHECK(json_valid(stringArray))
)

Data:

INSERT INTO "one" ("id", "name", "stringArray") VALUES ('1', 'John Doe',    '["apple","banana","cherry"]');
INSERT INTO "one" ("id", "name", "stringArray") VALUES ('2', 'Alice Smith', '["banana","grape","cherry"]');
INSERT INTO "one" ("id", "name", "stringArray") VALUES ('3', 'Bob Johnson', '["banana","apple","grape"]');

Update 1: also does not work with the json_table function supported by MySQL (see https://dev.mysql.com/blog-archive/json_table-the-best-of-both-worlds/):

SELECT people.* 
FROM t1, 
     JSON_TABLE(json_col, '$.people[*]' COLUMNS (
                name VARCHAR(40)  PATH '$.name',
                address VARCHAR(100) PATH '$.address')
     ) people;

We are scanning the SQL from a PowerBI to Postgres. PowerBI really likes to double quote things.
A query example is select "name" from "q_sample"."airports"

In the code I see references to IDENTIFIER and QUOTED_IDENTIFIER.
There was some discusssion regarding single quoting aliases here
#33

The module's syntax is based on
https://dev.mysql.com/doc/refman/5.7/en/select.html

and I read about double quotes being valid identifier characters at
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

ANSI_QUOTES
Treat " as an identifier quote character (like the quote character) and not as a string quote character. You can still use to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot
use double quotation marks to quote literal strings because they are interpreted as identifiers.

The following changes in sqlParser.jison parsed the query without complaining, but I am not sure if it is correct or the best approach.

1. Moved the following lines above the STRING declaration line to give them priority

['"][a-zA-Z_\u4e00-\u9fa5][a-zA-Z0-9_\u4e00-\u9fa5]*["']          return 'QUOTED_IDENTIFIER'
[`].+[`]                                                          return 'QUOTED_IDENTIFIER'

2. Below identifier_list I added

quoted_identifier
  : QUOTED_IDENTIFIER { $$ = { type: 'Identifier', value: $1 } }
  | quoted_identifier DOT QUOTED_IDENTIFIER { $$ = $1; $1.value += '.' + $3 }
  ;

3. At the bottom of the file, I reference the new quoted_identifier

table_factor
  : quoted_identifier partitionOpt aliasOpt index_hint_list_opt { $$ = { type: 'TableFactor', value: $1, partition: $2, alias: $3.alias, hasAs: $3.hasAs, indexHintOpt: $4 } }
  | '(' selectClause ')' aliasOpt { $$ = { type: 'TableFactor', value: { type: 'SubQuery', value: $2 }, alias: $4.alias, hasAs: $4.hasAs} }
  | '(' table_references ')' { $$ = $2; $$.hasParentheses = true }
  ;

Hi there
This is amazing lib but, there is a simply way to obtain a list of tables?
And in the same way, ¿there is a simply way to insert a table in FROM statement?

Thanks a lot

code

select
concat(dsw_cluster_name,',',logic_pod_name) as event_obj,
concat(dsw_cluster_name,',',logic_pod_name, ' PFC STORM. 详情: ',
'流量突跃 ', cast(FLOW_RX_BPS_RATE100 as int), '%(RX)',
'; ', cast(FLOW_TX_BPS_RATE100 as int), '%(TX)',
'; PFC 突增 ', cast(PFC_RX_PPS_RATE*100 as int), '%(RECV)') as brief
from SOURCE_BASIC_EVENT_POD_FLOW_AND_PFC_TREND
where win_end >= '${yyyy-MM-dd HH:mm:ss - 2m}'
and win_end < '${yyyy-MM-dd HH:mm:ss}'
and PFC_RX_PPS_RATE >= 100
and (FLOW_RX_BPS_RATE <= -0.2 and FLOW_TX_BPS_RATE <= -0.2)

error like this
{message: "Parse error on line 4:↵...LOW_RX_BPS_RATE*100 as i…,', ')', 'AND', '&&', '||', 'OR', 'XOR', got 'AS'", hash: {…}}

please support (INTERSECT | UNION | EXCEPT | SETMINUS) clause

只支持select，不支持 update,delete这些语法吗？

See this as a TODO on the readme, just wanted to create an issue to track progress and so folks could +1 if desired. Thanks!

Hi
I found something that maybe this is a bug. So when i tried the SQL query like in the picture below, it creates the AST, shouldn't it throw an error since only the Identifier specified without operator?

Thanks a lot

你好！
在使用js-sql-parser时发现一个问题:
ast = sqlParser.parse("SELECT stime, A.names, B.names FROM (SELECT stime, names FROM iaas_data.iaas_d3c0d0681cc1900) AS A LEFT JOIN (SELECT stime, names FROM iaas_data.iaas_1071f89feaa0e100) AS B ON A.stime = B.stime")

sqlParser.stringify(ast) 结果:
SELECT stime, A.names, B.names FROM (SELECT stime, names FROM iaas_data.iaas_d3c0d0681cc1900) LEFT JOIN (SELECT stime, names FROM iaas_data.iaas_1071f89feaa0e100) ON A.stime = B.stime

问题:
sqlParser.stringify(ast)后的SQL 没有 AS A 和 AS B 导致SQL错误.

#22

More and more SQL engines start supporting streaming functions, like

TUMBLE(rowtime, INTERVAL '1' HOUR)

described here https://calcite.apache.org/docs/stream.html#tumbling-windows-improved

Is it possible to support parameter list like "rowtime, INTERVAL '1' HOUR"?

Other functions like:
DATE_ADD(input_value, INTERVAL quantity_expr unit_of_time)
DATE_SUB(input_value, INTERVAL quantity_expr unit_of_time)

https://dev.mysql.com/doc/refman/8.0/en/union.html

I think we need to add the alias code to Sql.prototype.travelIdentifier function

Throws on

const parser = require('js-sql-parser');
const ast = parser.parse('WITH t as ( delete from foo ) Delete from bar;')
console.log(JSON.stringify(ast, null, 2));

SELECT TOP 3 (C.customerName) FROM Customers C

ERROR Error: Parse error on line 1:
SELECT TOP 3 (C.customerName) FROM Customers C
---------^
Expecting '(', 'ALL', 'DISTINCT', 'DISTINCTROW', 'HIGH_PRIORITY', 'MAX_STATEMENT_TIME', 'NUMERIC', 'STRAIGHT_JOIN', 'SQL_SMALL_RESULT', 'SQL_BIG_RESULT', 'SQL_BUFFER_RESULT', 'SQL_CACHE', 'SQL_NO_CACHE', 'SQL_CALC_FOUND_ROWS', '*', 'SELECT_EXPR_STAR', 'IDENTIFIER', 'QUOTED_IDENTIFIER', 'STRING', 'EXPONENT_NUMERIC', 'HEX_NUMERIC', 'TRUE', 'FALSE', 'NULL', 'CASE', '+', '-', '~', '!', 'BINARY', 'ROW', 'EXISTS', '{', 'NOT', got 'INVALID'
    at Parser.parseError (sqlParser.js:426)
    at Parser.parse (sqlParser.js:493)

Hi, this looks like a nice library. I've been looking for something like this for a long time. Thank you!

I noticed a small spelling error in the example code:

"type": "TableRefrence" should be TableReference.

Example
select * from some_table where userId=$userId

dollar sign variable is currently causing an error.