SQLLOGICTEST First results

[agershun]

@mathiasrw I modified slightly the test procedure, found the source of the 'frozen' bug (from select4.test) and select subset of sqllogictest files to be the standard for future development.

So, May 15, 2016. version 0.1.16

• tests processed 142538,
• tests successfully parsed 140039
• results ok -8515
• unclear results* - 55467
• time spent in seconds - 557 sec
  1. I do not worry to much about such low starting performance, because in some cases it is a problem of sqllogictest file parser and result checker (it is not trivial task).
  2. I skipped some queries with joins of 4 or more tables, because AlaSQL stalled on them. I will add new issue for this problem, but here they excluded.
  3. I used 'oracle' compatibility mode, but or future I will decide which model to choose.

I will run this test for all new versions and publish the progress.

[agershun]

For future tests I will use Postgres and SQLite versions.

[agershun]

I changed a SLT parser a little bit. Now I want to focus only on the first file select1.test.

Current results are (May 17,2015 8:07)

• tests - 1031
• parsed ok - 1031 (100%)
• run ok - 866 (84%)
• unclear - 46
• time - 6.4s

PS. 84% is not so bad :+1:

[agershun]

Current state (May 17, 2015 @ 11:45) - KPI1 = 100% (select1.js passed)!!!

It is a time to start with select2.test - KPI2 tests - 1031 parsed ok - 1031 (100%) run ok - 105 (10%) time - 5.9s

[agershun]

First results for all files:

Test log

Date: May 17, 2015 @ 17-40 version 0.1.6

This file contains a log of tests runned on the SQLLOGICTEST package.

KPI1 (select1.test) - queries

• tests - 1031
• parsed ok - 1031 (100%)
• run ok - 1031 (100%)
• time - 5.8s

KPI2 (select2.test) - NULLs

• tests - 1031
• parsed ok - 1031 (100%)
• run ok - 257 (26%)
• time - 5.2s

KPI3 (select3.test) - nested queries and exists

• tests - 3351
• parsed ok - 3351 (100%)
• run ok - 1043 (31%)
• time - 17.0s

KPI4 (select4.test) - cross-joins

• Test failed (freezes at line 29187) - cross-join of 30 tables...
• Verifier should be rewritten

KPI5 (select5.test) - cross-joins

• Test failed (freezes at line 3388) - cross-joins of 7 tables
• Verifier should be rewritten

OTHER TESTS (tests, parsed, ok, time)

• evidence/in1.test - 216, 214, 94, 0.3
• evidence/in2.test - 45, 45, 31, 0.1
• evidence/slt_lang_aggfunc.test - 80,68,38,0.1
• evidence/slt_lang_createtrigger.test - 26,5,5,0
• evidence/slt_lang_createview.test - 25, 24, 16, 0.05
• evidence/slt_lang_dropindex.test - 8, 8, 5, 0
• evidence/slt_lang_droptable.test - 12, 12, 9, 0
• evidence/slt_lang_droptrigger.test - 12, 6, 8, 0
• evidence/slt_lang_dropview.test - 13, 13, 11, 0
• evidence/slt_lang_reindex.test - 7, 5, 5, 0
• evidence/slt_lang_replace.test - 14, 10, 8, 0
• evidence/slt_lang_update.test - 27, 27, 18, 0
• index/between/1/slt_good_0.test - 10022, 10022, 6622, 73.7
• index/commute/10/slt_good_0.test - 10034, 10034, 3492, 22.9
• index/delete/1/slt_good_0.test - 10907, 10907, 7690, 15.0
• index/in/10/slt_good_0.test - 10035, 10035, 4799, 75.2
• index/orderby/10/slt_good_0.test - 10053, 10053, 2539, 24.2
• index/orderby_nosort/10/slt_good_0.test - 10053, 10053, 3375, 24.6
• index/random/10/slt_good_0.test - 12172, 12132, 9105, 16.7
• index/view/10/slt_good_1.test - 8200, 8200, 2760, 12.4
• random/aggregates/slt_good_0.test - 13922, 13221, 6875, 18.7
• random/expr/slt_good_0.test - 15426, 14198, 10372, 24.0
• random/groupby/slt_good_0.test -10559, 10221, 4790, 14.2
• random/select/slt_good_0.test - 11084, 10279, 2012, 12.4

[agershun]

Almost passed for 1,2, and 3

KPI1 (select1.test) - queries

• tests - 1031
• parsed ok - 1031 (100%)
• run ok - 1031 (100%)
• time - 4.6s

KPI2 (select2.test) - NULLs

• tests - 1031
• parsed ok - 1031 (100%)
• run ok - 970 (94%)
• time - 4.9s

KPI3 (select3.test) - nested queries and exists

• tests - 3351
• parsed ok - 3351 (100%)
• run ok - 3069 (92%)
• time - 16.3s

[agershun]

Overall results for May 19, 2015 15:45

Result for all test files without select4.test and select5.test in total

• tests - 138365
• parsed ok - 135205 (98%)
• run ok - 86330 (62%) <----- this is the most important number in the test
• time - 452s

[mathiasrw]

Im so glad the test have been pushing you and the quality further. I have been a bit offline - im back on track :)

[agershun]

:+1: AlaSQL definitely needs you! Especially SQLLOGIC and NIST SQL test part.

[mathiasrw]

Good to hear that all the work is not done yet ;-)

What is NIST ?

[agershun]

NIST - American National Institute for Standardization (http://www.nist.gov/)

They have SQL Test Suite. I took some sql files from this suite and prepare interpreter. Al these files located here:

https://github.com/agershun/alasql/tree/master/test-sql/nisttest

[mathiasrw]

nice - you already looked into converting it.

Is it working? what is missing? What do you need help with?

[agershun]

A little bit later, I just test all these files in general (unfortunately AlaSQL can not pass select4 and select5), but for other files we can understand what was the reason of errors. Give me a couple days, I will prepare a plan

[mathiasrw]

I have updated https://github.com/agershun/alasql/blob/develop/TESTLOG.md with a full test run.

The overall quality is growing!

• Skipped tests: 56317
• Failed tests: 688726
• Total tested: 5945362
• Final score: 87 % was OK :+1:

Total script time: 21219236ms (5.9 hours)