Currently on master:

In src/order.rs, the order by clause allows for trailing commas, which is technically invalid SQL.
Eg, "select * from users order by name ascage desc,\n" will parse, but should not

In the incoming bump nom to v5 PR, this is on line 76 of the same file:

76        many0(order_expr),

Hi, I'm working on implementing fmt::Display on SqlQuery and all the structs and enums underneath. I will make a PR sometime on the next few days.

Hi!
Thank you for this great library.
I am trying to use it to parse wikipedia dumps in my project wikipedia-externallinks-fast-extraction.
Unfortunately, they contain mysql escape characters that are currently not supported by this library.

Unsupported characters

The escape characters are:

\0
\'
\"
\b
\n
\r
\t
\Z
\\
\%
\_

Example

INSERT INTO externallinks VALUES (23481,120102,'http://home.arcor.de/jean-polmartin/aufsaetze/apliut.htm\'','http://de.arcor.home./jean-polmartin/aufsaetze/apliut.htm\'','http://de.arcor.home./jean-polmartin/aufsaetze/apliut.htm\'');

#![feature(test)]
extern crate test;
use test::Bencher;
use sqlparser::dialect::MySqlDialect;
use sqlparser::parser::Parser;

#[bench]
fn bench_sqlparser(b: &mut Bencher) {
    let pg_dialect = MySqlDialect {};
    let sql = r#"SELECT COUNT(*) FROM t"#;
    let _ = Parser::parse_sql(&pg_dialect, sql).unwrap();
    b.iter(||{
        Parser::parse_sql(&pg_dialect, sql).unwrap();
    });
}

#[bench]
fn bench_nomsql(b: &mut Bencher) {
    let sql = r#"SELECT COUNT(*) FROM t"#;
    let s=nom_sql::parser::parse_query(sql);
    if s.is_err(){
        panic!("{}",s.err().unwrap())
    }
    b.iter(||{
        nom_sql::parser::parse_query(sql).unwrap();
    });
}

• result

running 2 tests
test bench_nomsql    ... bench:      10,228 ns/iter (+/- 352)
test bench_sqlparser ... bench:       2,832 ns/iter (+/- 64)

Hi, your library seem to have a comprehensive structure of queries. I noticed that the DeleteStatement is lacking though.

MySQL supports the ability to choose which partitions of a given table will be selected from/changed. We will want to be able to parse this when they show up anywhere in for DELETE, INSERT, REPLACE, SELECT and UPDATE, so I believe it will work best to parse this within the table_reference and schema_table_reference parsers.

Is this comment still relevant ?

As of MySQL 8 Common Table Expressions are supported with the added keyword WITH (https://dev.mysql.com/doc/refman/8.0/en/with.html). It would be helpful to be able to parse this statement prior every place a SELECT statement shows up (I don't believe there are any exceptions from what I have seen in the documentation.

I originally opened this as a ticket relating to some INSERT changes but felt it would be better under it's own ticket/commit since it is its own command.

The join clauses appear to properly support nested selection but the from clause only looks for a table list. We will want to be able to support parsing these statements since nested selection shows up in the from clause fairly often from my experience.

Currently parsers that accept subqueries call the nested_selection parser which won't catch unions compound_selection will. I believe anywhere that a subquery or a filter section of DML commands should always permit a compound selection.

For some reason the parser isn't able to parse this fairly simple case statement: CASE WHEN country = 'USA' THEN state ELSE city END. I couldn't find any obvious reason that the case_when_column would miss this one.

I found the following comment // TODO(malte): support REPLACE, nested selection, DEFAULT VALUES in insert::insertion

As mentioned in the comments of arithmetic.rs, operator precedence is not currently supported and neither is nested arithmetic expressions.

I plan on eventually submitting a PR to support this but first wanted to open an issue so others may be aware of what I am working on in case they want to also contribute.

I have minimal experience with parsers, but after some research found that Pratt parsers are probably best for this purpose, and plan to implement it using this approach barring any comments/suggestions from other contributors

When running the tests for the first time with cargo test I noticed multiple warnings about unused doc comments:

warning: unused doc comment
    --> src/common.rs:1021:1
     |
1021 |   /// Parse rule for a comment part.
     |   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1022 | / named!(pub parse_comment<CompleteByteSlice, String>,
1023 | |     do_parse!(
1024 | |         opt_multispace >>
1025 | |         tag_no_case!("comment") >>
...    |
1029 | |     )
1030 | | );
     | |__- rustdoc does not generate documentation for macro expansions
     |
     = help: to document an item produced by a macro, the macro must produce the documentation as part of its expansion

Should we follow the help suggestion and include the documentation line(s) in the macro or remove the documentation lines for now?

It would be nice to be able to parse SQL statements with comments in them.

Example

create--blah blah
table--blah blah
tt--blah blah
(--blah blah
x--blah blah
)--blah blah
;

In standard SQL you may select literals without a table, eg SELECT 1 + 1;. This is not supported by nom-sql but in a perfect world it would be.

comment from @spazm (on the bump nom to v5 PR):
nested_selection could attempt to parse with something more specific than field_definition_expr like field_definition_literals(hypothetical) which would parse without the FROM (or FROM as optional).

The version of nom currently used by nom-sql is fairly old. In and of itself, this isn't so bad (though the newer nom presumably has performance and correctness fixes), but it causes some pain with dependencies. Specifically, the old version of num depends on regex 0.1, whereas regex is now at 0.2 (which is incompatible according to Rust's semver). regex 0.1 in turn has a bunch of outdated dependencies (compare 0.1 to current). These in turn pull in more old dependencies, etc. For crates that use nom-sql this causes many dependencies to be pulled in twice (one old, one new), which has a detrimental effect on compilation time and space usage.

I know fixing this is a bit annoying, as the nom api has changed a fair bit, but it's probably eventually worth doing.

MySQL table options can be written in any order, and be separated by a comma.

Example

CREATE TABLE t(x integer) AUTO_INCREMENT=1,ENGINE=,KEY_BLOCK_SIZE=8;

i stumbled across this line.

would consumers ever need to know about "IS NULL" vs. "= NULL"?

select 1 from t where id is null
select 1 from t where id = null

The result of parsing these queries is the same but is null is not an equivalent of = null. The latter query actually returns "wrong" results.
https://stackoverflow.com/questions/9581745/sql-is-null-and-null

Right now parsing a CREATE TABLE statement will fail:

• if there are comments on some field
• if there is a comment on the table itself

Exemple:

CREATE TABLE withcomments (
...
field VARCHAR(100) COMMENT 'some field',
...
) COMMENT='table description';

It would be nice to support those.
I'm not yet really familiar with nom, but I would gladly help on this feature.

In master (and on the incoming bump nom to v5 PR), compound_selection will match unbalanced paren pairs. We only want "(nested)" or "nested" to match, but using opt for both paren tags means that "nested)" and "(nested" will also match.

There may be other similar unbalanced issues with parens in other areas of the code or trailing commas.

This might work if the incoming bump nom to v5 PR is merged, but is definitely not pretty. Maybe it could be extracted into a "opt_parenthetical", ore more general opt_delimited :

alt(
    delimited(opt(tag("(")), nested_selection, opt(tag(")"))),
    nested_selection,
)

(line 110 of src/compound_select in the bump nom to v5 PR)

Per the following comment I stumbled upon for the creation parser, // TODO(malte): support types, TEMPORARY tables, IF NOT EXISTS, AS stmt I wanted to get an issue open to add these features along with a few other missing pieces. I will add comments as I figure out what missing pieces those may be.

Both ORDER BY AND GROUP BY can have a positional in place of a column name (the positional and column names can be used in the same list) and any conditional expression. Ideally, the parser would able to handle these situations.

CREATE TABLE statements without a space between the table name and the column definition fail to parse.

Example

CREATE TABLE x(b integer);

Hi,
I'm still trying to parse mysql dumps from wikipedia, and I'm having problems with mysql table options (table_option in the docs).

Example statement that fails to parse

CREATE TABLE x (b integer)  ENGINE=InnoDB AUTO_INCREMENT=413856661 DEFAULT CHARSET=binary;

Currently the parser has the HAVING clause parsed as part of the GROUP BY clause when it was valid, this is needed in cases where a condition on a calculated value is needed and a GROUP BY is inappropriate such as SELECT 1 + 2 as adder HAVING adder > 2.

SQL files can contain any byte sequence. However, this library only exposes a way to parse a rust string (that is, a sequence of unicode codepoints). This makes it impossible to parse some SQL files (such as the wikipedia dumps I am currently working with), as thay contain byte sequences that are not valid utf-8.

The api should expose a function that takes an &[u8] instead of an &str.

For handling byte sequences that are not valid utf8 in literal strings, I see two possibilities:

• Using the already existing Blob(Vec<u8>) (the information that the literal was a string and not a blob would be lost)
• Using a fault-tolerant utf8 decoder like rust-encoding (invalid characters would be lost).

Nom recently released v5 and nom-sql still uses v4. Just wondering if there is a plan to upgrade and what that plan might be.

One thing to note: nom v5's macros were rewritten to use the streaming version of the parsers. My assumption would be that nom-sql would only want to deal with complete parsers when upgrading to nom v5.

Nom has good documentation on upgrading to nom v5 here

MySQL 8 supports window functions (https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html) ideally the parser would be able to properly parse statements that use these in expressions.

Most of the Rust libraries for accessing MySQL allow named parameters using the :name syntax, it would be ideal to be able to parse these as an ItemPlaceholder::Named(String).

Currently, when parse fails, the user only gets a very uninformative error string as a result.
It would be nice to have a custom error type with useful functionality , such as knowing where the error happened.

These 2 queries

INSERT INTO user(name, password) VALUES('aaa','xxx');
INSERT INTO user (name, password) VALUES ('aaa','xxx');

are both valid, but nom-sql only parses the 2nd correctly.
I'm guessing this is a bit of complex to do in nom, where the multispace becomes optional when tag!('(') is in the next few bytes.

This placholder ? working but :1 and $1 not work.

Example

let sql = "DELETE FROM users WHERE user = $1 AND password = $2";
    let res = parse_query(sql);
    assert!(res.is_ok());
// or
 let sql = "DELETE FROM users WHERE user = :1 AND password = :2";
    let res = parse_query(sql);
    assert!(res.is_ok());

Result:

Err(
    "failed to parse query"
)