Semicolon ";" at end of queries given error - [ER_PARSE_ERROR]

[amitkarpe]

Hi,

Using sql-lint on Mac & Linux (version 0.0.13). I found that sql-lint gave [ER_PARSE_ERROR] when it found semicolon.

With_semicolon

$ /bin/cat create.sql
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
$ sql-lint -f create.sql
create.sql:1 [ER_PARSE_ERROR] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,             day I' at line 1

Without_semicolon

$ /bin/cat without_semicolon_create.sql
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL)
$ sql-lint -f without_semicolon_create.sql -v

Same for "--query/-q" option

With_semicolon

$ sql-lint -q "$(/bin/cat create.sql )"
query:1 [ER_PARSE_ERROR] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,             day I' at line 1

Without_semicolon

$ sql-lint -q "$(sed -e "s/;//" create.sql)" -v

Using sed, I have removed ";" from create.sql.

[joereynolds]

Interesting, thank you. I'll have a look into this

[amitkarpe]

Hi @joereynolds ,

I have tested version 0.0.12 and 0.0.11.

$ git clone https://github.com/joereynolds/sql-lint
Cloning into 'sql-lint'...
remote: Enumerating objects: 527, done.
remote: Counting objects: 100% (527/527), done.
remote: Compressing objects: 100% (353/353), done.
remote: Total 3663 (delta 252), reused 395 (delta 170), pack-reused 3136
Receiving objects: 100% (3663/3663), 6.34 MiB | 2.02 MiB/s, done.
Resolving deltas: 100% (2211/2211), done.
$ cd sql-lint
$ ls
Dockerfile  LICENSE  Makefile  README.MD  build  conf.py  dist  docker-compose.yml  docs  index.rst  package.json  schema.sql  src  test  tests.sh  tsconfig.json  tslint.json
$ sql-lint -f test/test-files/create.sql
test/test-files/create.sql:1 [ER_PARSE_ERROR] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,             day I' at line 1
$ /bin/cat test/test-files/create.sql
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
$ sql-lint -V
0.0.12
$ # Added "\" at the end of first line
$ vim test/test-files/create.sql
$ cat test/test-files/create.sql
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, \
             day INT(2) UNSIGNED ZEROFILL);
$ sql-lint -f test/test-files/create.sql
test/test-files/create.sql:1 [ER_PARSE_ERROR] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, \             day' at line 1
$ # Removed "/" and ";"
$ vim test/test-files/create.sql
$ # Success
$ sql-lint -f test/test-files/create.sql
$ # Success
$ sql-lint -f test/test-files/create.sql -v
$ cat test/test-files/create.sql
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL)
$ # replaced with older version i.e. 0.0.11
$ sql-lint -V
0.0.11
$ # Success even with ";"
$ # Semicolon ";" at end of queries given error might have introduced after version 0.0.11
$ sql-lint -f test/test-files/create.sql -v
$ # Success even with ";"
$ cat test/test-files/create.sql
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);

[joereynolds]

Thank you for this @amitkarpe, I will have to git bisect this when I get the chance to look at the error. I did not realise it's a regression

[joereynolds]

Okay bisected. Here's the offending commit

01d38f3bdca62d06db06826510b35889567e59a7 is the first bad commit

commit 01d38f3bdca62d06db06826510b35889567e59a7           
Author: Joe Reynolds <[email protected]>   
Date:   Fri May 17 12:04:32 2019 +0100                    
                                                          
    Use Database checks                                   

[Code-Hex]

@joereynolds What's the progress of this?

[joereynolds]

Hi @Code-Hex,

There's been no movement on this recently. Happy to assist if someone else wants to take a go at it. I'm looking into some bigger driver support issues