Previously empty statements would result in parse errors. This can happen in cases where they do not initially appear empty, e.g. if the contained only comments. For example, something similar to this appears in mysqldump output:

/* some comment*/;
/* another comment */;
CREATE TABLE ...

Lex usually discards comments until it finds a token to return but in the above case that is nil since it is an empty statement.

This change extends the parser to handle the empty statement (after lex does its comment discarding in the above example).

Fixes #31.

This is a fantastically complete and fast parser, but I have one problem case to report. It doesn't seem to understand queries where the "using" shorthand is used in a join.

Reproduce with: Parsing: select * from t1 join t2 using(id) yields error: syntax error at position 31 near 'using'

sql := "SELECT * FROM table WHERE a = 'abc'"
stmt, err := sqlparser.Parse(sql)
if err != nil {
	panic(err)
}

I got this error

http: panic serving [::1]:50432: syntax error at position 20 near 'table'
goroutine 19 [running]:
net/http.(*conn).serve.func1(0xc0000a2960)
	/usr/local/go/src/net/http/server.go:1769 +0x139
panic(0x7624e0, 0xc000087020)
	/usr/local/go/src/runtime/panic.go:522 +0x1b5
github.com/flume-cloud-services/database/controllers.CreateQuery(0x8565a0, 0xc000136000, 0xc000112400)
	/home/rerwan/go/src/github.com/flume-cloud-services/database/controllers/query.go:25 +0x27b
net/http.HandlerFunc.ServeHTTP(0x7ece30, 0x8565a0, 0xc000136000, 0xc000112400)
	/usr/local/go/src/net/http/server.go:1995 +0x44
github.com/flume-cloud-services/database/middleware.AuthMiddleware.func1(0x8565a0, 0xc000136000, 0xc000112400)
	/home/rerwan/go/src/github.com/flume-cloud-services/database/middleware/auth.go:60 +0x351
net/http.HandlerFunc.ServeHTTP(0xc000090440, 0x8565a0, 0xc000136000, 0xc000112400)
	/usr/local/go/src/net/http/server.go:1995 +0x44
net/http.(*ServeMux).ServeHTTP(0xadf1a0, 0x8565a0, 0xc000136000, 0xc000112400)
	/usr/local/go/src/net/http/server.go:2375 +0x1d6
net/http.serverHandler.ServeHTTP(0xc0000932b0, 0x8565a0, 0xc000136000, 0xc000112400)
	/usr/local/go/src/net/http/server.go:2774 +0xa8
net/http.(*conn).serve(0xc0000a2960, 0x856ee0, 0xc00009e440)
	/usr/local/go/src/net/http/server.go:1878 +0x851
created by net/http.(*Server).Serve
	/usr/local/go/src/net/http/server.go:2884 +0x2f4

I am using the latest version of sqlparser And Golang 1.12

Hi, I got another one:

r := strings.NewReader(`SET NAMES utf8;`)

tokens := sqlparser.NewTokenizer(r)
for {
	stmt, err := sqlparser.ParseNext(tokens)
	if err == io.EOF {
		break
	}
	fmt.Println(sqlparser.String(stmt))
	if err != nil {
		fmt.Println(err)
	}
}

This results only in this output:

set

The rest is missing?

Discovered while looking at mysqldump's default output, which contains a couple things that currently cause syntax errors.

A mysqldump file consists of SQL statements that create tables and insert rows into them. By default it places LOCK TABLES and UNLOCK TABLES statements around its inserts, but currently these are not handled by the parser.

I create a table with

UpdateDatetime TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

it shows

syntax error at position 471 near 'current_timestamp'

Minimal example:

package main

import (
    "fmt"
    "github.com/xwb1989/sqlparser"
)

func main() {
    sql := "SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH)"
    tree, err := sqlparser.Parse(sql)
    if err != nil {
        fmt.Println("Error: ", err)
        panic("oh no")
    }
    fmt.Println(tree)
}

$ go run main.go
Error:  syntax error at position 34 near 1
panic: oh no

[trace info]

Looks like it's failing to handle the MONTH after INTERVAL.

I got an error for a query which contain OFFSET. It should be a valid query.

syntax error at position 42 near OFFSET for query - SELECT * FROM AccessToken LIMIT 10 OFFSET 13

Hi there!

Thank you so much for taking the time to extract this into its own package, it's awesome! I was wondering if you could provide any guidance if there were any functions exposed that takes a parsed query and can recompile it back into an SQL statement?

Thank you so much for your time!

HI. When I use sqlparser to parse sql with "?", and then call function stmt.Format, the "?" in the sql will be replaced with ":v1". Is this a bug? Here is an example.

sql := "select * from tbl where id = ?"
stmt, _ := sqlparser.Parse(sql)
buf := sqlparser.NewTrackedBuffer(nil)
stmt.Format(buf)
newSql := buf.String()
fmt.Println(newSql) // the newSql is "select * from tbl where id = :v1"

Thank you in advance for your answer.

Is it possible to obtain the position of each node in the AST? I believe it's not currently being tracked. Any sense of what would be involved to add support for that? I'd be happy to implement if someone could point me in the right direction.

Ideally, it would look something like this:

{
  Start: { Offset: 23, Line: 5, Column: 6 },
  End:   { Offset: 25, Line: 5, Column: 8 }
}

INSERT INTO `products` (`created_at`,`updated_at`,`deleted_at`,`code`,`price`) VALUES ("2022-12-13 03:09:51.724","2022-12-13 03:09:51.724",NULL,"D42",100) RETURNING `id`

syntax error at position 165 near 'RETURNING'

I have an SQL procedure in MariaDB called bookUPDATE such as this:

BEGIN
	UPDATE book
		SET
			book.Title= v_Title,
			book.ReleaseDate = v_ReleaseDate,
			book.Description = v_Description,
		WHERE book.ID = v_ID;
END

I have an SQL builder for this procedure which creates a string:

q := "CALL bookUPDATE(?, ?, ?, ?); CALL bookUPDATE(?, ?, ?, ?);"

When I do the sqlparser.Parse(q) I get an error syntax error at position 5 near 'call'.

How am I supposed to Parse calling the procedure?

I want to validate some SQL queries that are nested and have sub-queries at multiple depths. The sqlparser.Parse says the following query is invalid:-

SELECT ( SELECT ( SELECT ( SELECT * FROM table) FROM table_2 ) FROM table_1 WHERE column = ( SELECT * FROM table_3 ) ) FROM table

Am I doing something wrong or does the library not support sub-packages?

func main() { sql := "SELECT * FROM table WHERE a = 'abc'" stmt, err := sqlparser.Parse(sql) if err != nil { fmt.Printf("%s",err.Error()) }

// Otherwise do something with stmt
switch stmt := stmt.(type) {
case *sqlparser.Select:
	_ = stmt
case *sqlparser.Insert:
}

fmt.Printf("stmt :%v", stmt)

}

result err: syntax error at position 20 near 'table'

While using this library, I noticed that it does not add a backtick to MySQL tables named groups, which causes an SQL error when executing the generated queries.

This PR is a proposed fix for this.