sqllogictest

[mathiasrw]

alaSQL src should incooperate test cases from sqllogictest [1] to identify areas where alaSQL does not comply with expectations to traditional SQL statements.

The goal (for now) is not to pass all the tests, but to help us describe (in the documentation) what alaSQL does not support - and by that identify areas to improve for the alaSQL library.

[1] https://github.com/oriherrnstadt/SQLlogictest/tree/master/test

[mathiasrw]

Im forking the project to work on this part.

The format of the tests are desribed here: http://www.sqlite.org/sqllogictest/doc/trunk/about.wiki

A working regext for the format of the files could be

var re = /(?:(?:\n|^)(statement) (ok|fail)((?:\r?\n.+)+))|(?:\n(?:(skipif) (\w+)\r?\n)?(?:(onlyif) (\w+)\r?\n)?(query) (\w+) (\w+)? ?(\w+)?\r?\n([\s\S]+?)\n----(?:(?:\r?\n([0-9]+) values hashing to ([0-z0-9]+))|(?:((?:\r?\n.+)+))))|(?:(?:\n|^)(hash-threshold) (\d+))|(?:\n(halt))/gmi; 

ok, one cant "onlyif" or "skipif" a statement - but have not met that yet in the test files...

Please use the PEG.JS parser at https://github.com/mathiasrw/alasql-logictest/blob/master/sqllogic/parser.peg instead of this regex

The regex cuts up the data into arrays. Runs this as a small reminder of what gows where:

var str = 'statement ok\nCREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER)\n\n\nstatement ok\nINSERT INTO t1(e,d,c,b,a) VALUES(246,248,247,249,245)\n\n\nstatement ok\nINSERT INTO t1(e,d,c,b,a) \nVALUES(246,248,247,249,245)\n\nskipif dbnameA\nquery I nosort\nSELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END\n  FROM t1\n ORDER BY 1\n----\n30 values hashing to 3c13dee48d9356ae19af2515e05e6b54\n\nonlyif dbnameB\nquery II nosort\nSELECT a+b*2+c*3+d*4+e*5,\n       (a+b+c+d+e)/5\n  FROM t1\n ORDER BY 1,2\n----\n60 values hashing to 808146289313018fce25f1a280bd8c30\n\nhalt\n\nquery IIIII nosort\nSELECT a+b*2+c*3+d*4+e*5,\n       CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222\n        WHEN a<b+3 THEN 333 ELSE 444 END,\n       abs(b-c),\n       (a+b+c+d+e)/5,\n       a+b*2+c*3\n  FROM t1\n WHERE (e>c OR e<d)\n   AND d>e\n   AND EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)\n ORDER BY 4,2,1,3,5\n----\n80 values hashing to f588aa173060543daffc54d07638516f\n\nhash-threshold 12\n\nquery I nosort\nSELECT CASE WHEN c>(SELECT avg(c) FROM t1) THEN a*2 ELSE b*10 END\n  FROM t1\n WHERE e+d BETWEEN a+b-10 AND c+130\n   AND c>d\n ORDER BY 1\n----\n1000\n1180\n1240';
var m;

while ((m = re.exec(str)) != null) {
    if (m.index === re.lastIndex) {
        re.lastIndex++;
    }
    console.log(m)
}

[mathiasrw]

A large part of the sqllogictest fails. Thats ok as its identifying new areas of improvements.

Normally I would always keep one test script that ran everything, but have a feeling it would be better to seperate them so you can run the sqllogictest seperate.

What do you think?

[agershun]

Mathias, hi! Thank you for tests and your effort. Sorry, I have some work in the office this month and very slow with answers.

I think it is better to split original test one-by-one and add them to test directory to simplify the testing process with mocha library.

[mathiasrw]

What about the test-sql folder? Imagine its a good place to put it - next to the other sql tests...

[agershun]

Yes, I created this especially for SQL conformity tests, and collected
there all SQL databases. Unfortunately, I do not know is it legal copyright
to put these databases here on GitHub... Now this folder is listed in
.gitignore file.

2015-03-12 17:45 GMT+03:00 Mathias Rangel Wulff notifications@github.com:

What about the test-sql folder? Imagine its a good place to put it - next
to the other sql tests...

—
Reply to this email directly or view it on GitHub
#87 (comment).

[mathiasrw]

.gitignore only limits new uploads - so if you want to get the folders off github you need a little more work - check out http://stackoverflow.com/questions/7927230/remove-directory-from-remote-repository-after-adding-them-to-gitignore

[mathiasrw]

Ran all the tests from sqllogic on version 0.40

Ignoring the returned values (effectively testing the SQL parser of alaSQL) I got:

Out of 2.504.249 tests 909.494 succeeded (36%)

In the moment I am idenfitying the main issues failing so we can get better results with alaSQL.

[agershun]

Great! I think if we will add ORDER BY 1,2 it will give ten percent more.

Mathias, did you run all five test files, or just first one? See files from this link

[mathiasrw]

I ran all of them + all the other .test files located in https://github.com/oriherrnstadt/SQLlogictest/tree/master/test - in total 622 files

I am still working on the test script to run the .test files - for the moment I keep it here
https://github.com/mathiasrw/alasql/tree/version-0.0.40/test/sqllogic

When I feel it has a good quality ill make a pull request to merge it into your repo...

[agershun]

Mathias, hi. I added numbers in ORDER BY clause:

    SELECT a,b FROM one ORDER BY 2,1

May I ask you to run tests again? Some of these tests were wrong by this reason. Thank you! (and sorry for my delays with answers)

[mathiasrw]

Sure.

I ran it this night but for some reason it went dead during the execution of https://github.com/mathiasrw/alasql/blob/version-0.0.40/test/sqllogic/test/random/aggregates/slt_good_10.test

I will look into why one of the next days

[mathiasrw]

Have not found a solution yet. Will look at it again as the next thing