alibaba/nquery

Generic SQL engine for Web and Big-data.

nquery's Issues

Default install of should.js incompatible with method used in test suite

should.js deprecated .include and .includeEql on 2014-05-29. There are 6 uses of .include in test/unit/:

• adapter.js:23
• adapter.js:24
• column.js:75
• extractor.js:39
• template.js:183
• where.js:58

As a result, the default npm run test fails

doesn't support upsert-ish behaviour

the following query fails in test, but passes in mysql.

INSERT INTO `user_lauer_testing`
SET `user_id` = '123',
  `username` = 'mike'
ON duplicate KEY
UPDATE `user_id` = '123',
       `username` = 'bob'

Support for sub-queries?

This fails to parse: select i from (select MAX(i) AS i FROM foo:

module.exports.result.SyntaxError {name: "SyntaxError", expected: Array(3), found: "(", message: "Expected "$", [ \t\n\r] or [A-Za-z_] but "(" found.", offset: 14, …}

Add support with escaped FROM

If there is an escaped table in the FROM statement it throws an error.

Example:

var parse = require('node-sqlparser').parse;
var stringify = require('node-sqlparser').stringify;

// ERROR
var sql = 'SELECT `id` FROM `Tb`'; // escaped `Tb`
var astObj = parse(sql); // ERROR

// OK
var sqlOK = 'SELECT `id` FROM Tb';
var astObjOK = parse(sql); // OK

Example of the error:

      throw peg$buildException(null, peg$maxFailExpected, peg$maxFailPos);
      ^
SyntaxError: Expected "$", WHITE_SPACE or [A-Za-z_] but "`" found.

What happened to node-sqlparser?

I use node-sqlparser in a reverse proxy we're building at the City of Philadelphia, and I've noticed that the github repo seems to have been renamed to nquery and the readme suggests it's quite a different project?

Support for subqueries

I was unable to parse the following query

const Q3 = `
select
 dashboard.name,
 logs_count.ct
from
 dashobard
  join (
   select dashoboard_id,
    count(distinct user_id) as ct
   from time_on_site_logs
   group by dashboard_id
  ) as log_counts
  on log_counts.dashboard_id = dashboard.id
order by logcounts.ct desc
`; // fails

Exception thrown:

throw new this.SyntaxError(
^
SyntaxError: Expected "$", "`", [ \t\n\r] or [A-Za-z_] but "(" found.

I'm running the latest code pull from master

Parser Error

content;
我是一包香烟\u0014\\0\

and parse result below:

message: 'Expected "\'", "\\\\\'", "\\\\/", "\\\\\\"", "\\\\\\\\", "\\\\b", "\\\\f", "\\\\n", "\\\\r", "\\\\t", "\\\\u" or [^\\'\\\\\\\\\\\\0-\\\\x1F�] but "%14" found.',\n offset: 261,\n line: 2,\n column: 52

Cannot run demo

The demo sql cannot be parsed at my test:
webfan.de/cdn/application/test/nquery.html

"id" in the query throws an error:

module.exports.result.SyntaxError
column
:
29
expected
:
Array[10]
0
:
"",""
1
:
""GROUP""
2
:
""INNER""
3
:
""JOIN""
4
:
""LEFT""
5
:
""LIMIT""
6
:
""ORDER""
7
:
""UNION""
8
:
""WHERE""
9
:
"[ \t\n\r]"
length
:
10
proto
:
Array[0]
found
:
"i"
line
:
1
message
:
"Expected ",", "GROUP", "INNER", "JOIN", "LEFT", "LIMIT", "ORDER", "UNION", "WHERE" or [ \t\n\r] but "i" found."
name
:
"SyntaxError"
offset
:
28

Support free text search of all fields

node-sqlparser does parse FREETEXT(foo, 'bar') but it does not parse FREETEXT(*, 'bar') (with the wildcard)

(ref timwis/node-soda2-parser#1)

SyntaxError as being parsed

SyntaxError: Expected "FROM", "GROUP", "LIMIT", "ORDER", "UNION", "WHERE", WHITE_SPACE, or end of input but "A" found;
e.g. mysql is:
SELECT dd, Max(IF(stat_key = 'yys', stat_us, 0)) AS 'yys_users' FROM waf.t_cpkg WHERE stat_ty = 'waf_ty' GROUP BY dd;
could you please take a look, and update to fix the bug?

Breaking changes from 0.3.3->0.3.4

This package works as expected when you install it through npm(v0.3.3) but when I cloned the repo and tried to run demo.js I get the following error when it tries to evaluate $c := ...

Error: no registered db info found...
    at load (/Users/root/nquery/demo/loader.js:44:8)

The problem seems to be with the AST creation. In the newer version the AST doesn't show the tables a and b used for evaluation as 'var' type. Rolling back to the commit 395487a fixed the issue.

Support NOT BETWEEN

node-sqlparser supports BETWEEN but doesn't support NOT BETWEEN

SELECT * WHERE salary not between '40000' and '150000'

yields Expected "CONTAINS", "IN", "LIKE" or WHITE_SPACE but "b" found.

If this isn't expected in standard SQL, feel free to disregard, but I assume it is

Doesn't support Alter, Rename and many other commands.

Why doesn't the package support commands like Alter, Rename, Drop, Truncate etc. What issues will someone face if he tries to add support for these commands?

Trying to run a basic query and its not working

SELECT MONTH(endTime) FROM table

results in:

/Users/maltepeter/Documents/repos/node-query-api/node_modules/node-sql-parser/pegjs-parser.js:9800
      throw peg$buildException(
      ^
SyntaxError: Expected [A-Za-z0-9_] but end of input found.

Is there a way to get it to support this functionality?