florajs / sql-parser Goto Github PK

I'm having difficulty parsing a simple delete query. Is there something I'm doing wrong here?

const { Parser } = require("flora-sql-parser")

new Parser().parse('delete from "user" where id = 1')

Output:

peg$SyntaxError: Expected "$", "(", "--", "/*", "INSERT", "REPLACE", "SELECT", "UPDATE", "WITH", "return", [ \t\n\r], or end of input but "d" found.

I have the example running here on RunKit.

I'm finding unexpected behavior if my column is name type:

const { Parser } = require("flora-sql-parser")
const parser = new Parser();
const ast = parser.parse("SELECT type FROM t");

Result in error:
peg$SyntaxError: Expected [A-Za-z0-9_:] or [A-Za-z0-9_] but " " found.

Changing the column name to something else, results in no error. However, type is a fairly common property/column name. I'm happy to try to PR this if someone can give me an indication where to start or any other hints.

UPDATE: this works if you wrap type in double quotes. Any thoughts about automating double quote wrapping for reserved words?

I am getting quotes around CURRENT_TIMESTAMP in my where clause after converting a query to AST and back. This is causing an invalid identifier error when executing the query.

Parsing integers with leading zeros does not work:

const { Parser } = require('flora-sql-parser');
const parser = new Parser();

//Follwing statements all throw SyntaxError
parser.parse('SELECT 042 FROM t');
parser.parse('SELECT * FROM t WHERE attr < 042');
parser.parse('SELECT * FROM t WHERE attr BETWEEN 24 AND 042');

it will throw error when i use LIKE and IS NOT NULL at the same time.
example: select * from tab where username like '%something%' and img is not null
throw error: SyntaxError: Expected [A-Za-z0-9_:] or [A-Za-z0-9_] but " " found.

https://www.npmjs.com/package/flora-sql-parser?activeTab=versions says there is a v0.9.3 version that has been published a couple days ago. Releases do not show this version and the latest commit correspond to tagging to v0.9.2.
Was that accidental and v0.9.2 === v0.9.3, or was there an uncommitted change added to v0.9.3?

Thanks! 🙏

When upgrading to v0.9.0, we get:

ERROR in ./node_modules/flora-sql-parser/lib/parser.js
Module not found: Error: Can't resolve './../build/pegjs-parser'

Looking at https://unpkg.com/[email protected]/ vs. https://unpkg.com/[email protected]/, it seems that the build folder is missing.
Is that something that was forgotten at publish time?

record column not supported

x=parser.parse('SELECT cv,i FROM voucher cv JOIN item i ON i.dbid=cv.dbid AND i.cvoid=cv.void AND i.deleted=0')

In version 0.7.4, How to parse string when there is single quote?
For example:
parser.parse("select * from t where firstName=Hu's")
parser.parse("select * from t where firstName=Hu\'s")
both are not working.

Support date/time literals in the where clause:

DATE '1999-12-25' 
TIME '08:23:16' 
TIME '14:23:16.5' 
TIMESTAMP '1999-12-25 08:23:16' 

e.g.

SELECT * WHERE x = TIMESTAMP '1999-12-25 08:23:16' 

const { Parser } = require('flora-sql-parser');
const parser = new Parser();

//Following statement throws SyntaxError
parser.parse('SELECT .5 * SUM FROM t');

while
parser.parse('SELECT 0.5 * SUM FROM t');
works fine

Error:

"SyntaxError: Expected "!", "$", "'", "(", "", "+", "-", "--", "/", ":", "ALL", "AVG", "CASE", "CAST", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "DATE", "DISTINCT", "EXISTS", "FALSE", "MAX", "MIN", "NOT", "NULL", "SELECT", "SESSION_USER", "SQL_BIG_RESULT", "SQL_BUFFER_RESULT", "SQL_CACHE", "SQL_CALC_FOUND_ROWS", "SQL_NO_CACHE", "SQL_SMALL_RESULT", "SUM", "SYSTEM_USER", "TIME", "TIMESTAMP", "TRUE", "USER", "WITH", """, "`", [ \t\n\r], [0-9], or [A-Za-z_] but "." found."

can not parse some sqls. would you like help me to solve this problem
example
"SELECT *, (SELECT group_concat(inner_url) inner_url FROM t_media_info b WHERE a.id = b.msg_id GROUP BY msg_id) inner_url WHERE f_step = 1 AND business_id IN (6, 3, 4, 8, 9, 12, 14, 15, 38, 39, 5) ORDER BY create_time DESC

The parser has an issue parsing a statement with a string with escaped single quotes.

In PostgreSQL, the single quote in string can be escaped with an extra single quote before it. For example, this SQL works in PostgreSQL

SELECT * FROM restaurants WHERE name = 'wendy''s'

but the parser throws an error for it

SyntaxError: Expected "!=", "%", "*", "+", "-", "--", "/", "/*", "<", "<=", "<>", "=", ">", ">=", "AND", "GROUP", "HAVING", "LIMIT", "OR", "ORDER", "UNION", [ \t\n\r], or end of input but "'" found.

The workaround is to change the way of escaping from '' to \'. So this SQL works in the parser

SELECT * FROM restaurants WHERE name = 'wendy\'s'

I noticed this project has an MIT license:
https://github.com/godmodelabs/flora-sql-parser/blob/master/LICENSE

However, it is "based on the SQL parser extracted from Alibaba's nquery module". The nquery module uses the GPLv2 license:

https://github.com/alibaba/nquery/blob/master/package.json#L34

That would mean this project may contain GPL code and could be mislabeled with the MIT license.

I'm curious, how the SQL parser was extracted from nquery?

First, wanted to say thanks for the great library, it's been very useful!

I've run into a problem when converting an ast selected column like this:

{
	expr: {
		type: ‘column_ref’,
		table: ‘tableid’,
		column: ‘columnname’
	},
	as: ‘column alias’
}

Where it'll be translated as a sql string to something like:
SELECT "tableid"."columnname" as 'column alias' from "tableid"

When sending that string to execute with PRESTO I get an error like:

No viable alternative at input 'column alias'

This is because PRESTO expects double quotes instead of single quotes for column aliases. Would y'all mind if I create a pull request to change https://github.com/godmodelabs/flora-sql-parser/blob/master/lib/sql.js#L143 to use double quotes instead of single quotes?

Thanks again!

wondering if you can add node position, and or if there is any reason for not to have it. similar to this https://github.com/redotjs/redot/blob/b5abff784e9952d12d3713f27839bed12ee173df/packages/redot-parse/dot.pegjs#L13
thanks!

Crashes on a query such as:

SELECT tablename.* FROM tablename

with

SyntaxError: Expected "'", "--", "/*", "\"", "`", [ \t\n\r] or [A-Za-z_] but "*" found.

For example, you may want to augment a row with information from another table, as in:

SELECT
    preferences.*,
    user.displayName
FROM
    preferences,
    user
WHERE
    preferences.userid = user.id

The parser is able to parse SELECT * FROM db.table just fine, but it fails to parse SELECT * FROM db."table" and SELECT * FROM "db"."table" properly. It fails with the error message Expected "--", "/*", [ \t\n\r], or [A-Za-z_] but "\"" found.

Planned bigger and backward incompatible changes for next major version:

• options for multiple SQL dialects (implemented using PEG.js "predicates")
• cleanup quote/identifier handling (double quotes, single quotes and backticks) - implement correctly depending on SQL dialect
• As a start differentiate between ANSI SQL and MySQL. Further SQL dialects might follow.
• AST format changes
  • param: change "value" to "name"

when i input 'GROUP_CONCAT' to parse AST it would throw error.
exp:
'SELECT GROUP_CONCAT(DISTINCT id) AS ids FROM t'
throw error:
message: 'Expected [A-Za-z0-9_:] or [A-Za-z0-9_] but " " found.',
expected: [
{
type: 'class',
parts: [Array],
inverted: false,
ignoreCase: false
},
{
type: 'class',
parts: [Array],
inverted: false,
ignoreCase: false
},
{
type: 'class',
parts: [Array],
inverted: false,
ignoreCase: false
}
],
found: ' ',
location: {
start: { offset: 28, line: 1, column: 29 },
end: { offset: 29, line: 1, column: 30 }
},
name: 'SyntaxError'
}

by the way
how can i do if i want to add a new expression parsing rule with plugin-ins

Firstly, thanks for this tool/module...it helps a lot.

Given the following SQL statement, the parser works fine:

SELECT m.userId, m.username, m.email, a.isAdmin
FROM member m
LEFT JOIN admin a ON m.id = a.id

The following, however, fails.

SELECT m.userId, m.username, m.email, a.isAdmin
FROM member m
LEFT OUTER JOIN admin a ON m.id = a.id

My code is as follows

var sql = "SELECT m.id, m.username, m.email, a.isAdmin ";
sql += "FROM member m "
sql += "LEFT OUTER JOIN admin a ON m.id = a.id"

const Parser = require('flora-sql-parser').Parser;
const parser = new Parser();
const ast = parser.parse(sql);

console.log(ast);

The error is as follows:

/home/user/Projects/node/mysqltest/node_modules/flora-sql-parser/pegjs-parser.js:10194
    throw peg$buildStructuredError(
    ^
SyntaxError: Expected "--", "/*", "JOIN", or [ \t\n\r] but "O" found.
    at peg$buildStructuredError (/home/user/Projects/node/mysqltest/node_modules/flora-sql-parser/pegjs-parser.js:949:12)
    at peg$parse (/home/user/Projects/node/mysqltest/node_modules/flora-sql-parser/pegjs-parser.js:10194:11)
    at Parser.parse (/home/user/Projects/node/mysqltest/node_modules/flora-sql-parser/lib/parser.js:7:16)
    at Object.<anonymous> (/home/user/Projects/node/mysqltest/parser.js:7:20)
    at Module._compile (module.js:643:30)
    at Object.Module._extensions..js (module.js:654:10)
    at Module.load (module.js:556:32)
    at tryModuleLoad (module.js:499:12)
    at Function.Module._load (module.js:491:3)
    at Function.Module.runMain (module.js:684:10)

解析hive类型的sql语句时，语法正确但是解析失败
select row_number() over(A by B order by C desc) as col1
from table1

解析不了row_number()

> [email protected] install F:\proj\htrack\src\client-static\node_modules\flora-sql-parser
> pegjs -o pegjs-parser.js sql.pegjs

'pegjs' is not recognized as an internal or external command,
operable program or batch file.
npm WARN optional SKIPPING OPTIONAL DEPENDENCY: fsevents@^1.0.0 (node_modules\chokidar\node_modules\fsevents):
npm WARN notsup SKIPPING OPTIONAL DEPENDENCY: Unsupported platform for [email protected]: wanted {"os":"darwin","arch":"any"} (current: {"os":"win32","arch":"x64"})
npm WARN [email protected] No repository field.
npm WARN [email protected] No license field.

npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] install: `pegjs -o pegjs-parser.js sql.pegjs`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] install script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

We are using a project that uses this a dep and we can not build it for deploy with npm pack (requires no symlinks)

Given the following T-SQL Statement as input

const SQL = 'SELECT syscolumns.name, ROW_NUMBER() OVER(PARTITION BY id ORDER BY colid) rowNo from sysColumns';

When parsed into ast with the Default option:

const ast = parser.astify(SQL);

ast:

{
    "columns": [
        {
            "as": null,
            "expr": {
                "column": "name",
                "table": "syscolumns",
                "type": "column_ref"
            }
        },
        {
            "as": "rowNo",
            "expr": {
                "args": {
                    "type": "expr_list",
                    "value": [
                    ]
                },
                "name": "ROW_NUMBER",
                "over": {
                    "as_window_specification": {
                        "parentheses": true,
                        "window_specification": {
                            "name": null,
                            "orderby": [
                                {
                                    "expr": {
                                        "column": "colid",
                                        "table": null,
                                        "type": "column_ref"
                                    },
                                    "type": "ASC"
                                }
                            ],
                            "partitionby": [
                                {
                                    "as": null,
                                    "expr": {
                                        "column": "id",
                                        "table": null,
                                        "type": "column_ref"
                                    }
                                }
                            ],
                            "window_frame_clause": null
                        }
                    },
                    "type": "window"
                },
                "type": "function"
            }
        }
    ],
    "distinct": null,
    "for_update": null,
    "from": [
        {
            "as": null,
            "db": null,
            "table": "sysColumns"
        }
    ],
    "groupby": null,
    "having": null,
    "into": {
        "position": null
    },
    "limit": null,
    "options": null,
    "orderby": null,
    "type": "select",
    "where": null,
    "window": null,
    "with": null
}

Converted back to T-SQL:
const sql = parser.sqlify(ast, {database: 'TransactSQL'});

sql:
SELECT [syscolumns].[name], ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [colid] ASC) AS [rowNo] FROM [sysColumns]

However, when the SQL is parsed into ast with TransactSQL option, the produced ast is missing the "as_window_specification" object and attempting to parse it back into SQL fails,

const ast = parser.astify(SQL, {database: 'TransactSQL'});

ast:

{
    "columns": [
        {
            "as": null,
            "expr": {
                "column": "name",
                "table": "syscolumns",
                "type": "column_ref"
            }
        },
        {
            "as": "rowNo",
            "expr": {
                "args": {
                    "type": "expr_list",
                    "value": [
                    ]
                },
                "name": "ROW_NUMBER",
                "over": {
                    "orderby": [
                        {
                            "expr": {
                                "column": "colid",
                                "table": null,
                                "type": "column_ref"
                            },
                            "type": "ASC"
                        }
                    ],
                    "partitionby": [
                        {
                            "as": null,
                            "expr": {
                                "column": "id",
                                "table": null,
                                "type": "column_ref"
                            }
                        }
                    ]
                },
                "type": "function"
            }
        }
    ],
    "distinct": null,
    "from": [
        {
            "as": null,
            "db": null,
            "table": "sysColumns"
        }
    ],
    "groupby": null,
    "having": null,
    "limit": null,
    "options": null,
    "orderby": null,
    "top": null,
    "type": "select",
    "where": null,
    "with": null
}

Converted back to T-SQL:
const sql = parser.sqlify(ast, {database: 'TransactSQL'});

SError: unknown over type
    at B (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1085075)
    at Object.V [as function] (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1086132)
    at wr (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1095810)
    at C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1101662
    at C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1101974
    at Array.map (<anonymous>)
    at $r (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1101558)
    at lr (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1092014)
    at Zr (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1111238)
    at Jr (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1112115)

Hey there,

It looks like you just published v0.8.1, but the tag and release are missing from: https://github.com/godmodelabs/flora-sql-parser/releases

Those 2 things are very useful for things like the wonderful Renovate which opens PRs on consuming repos and adds release notes for the new versions.

Thanks again!