Sqllogictest Illustrated

Background

You might want to know that the Databend team is rolling out a sqllogictest to replace the stateless tests. In this post, we share our experience of designing, developing, and using the sqllogictest.

About sqllogictest

Database quality assurance

Test dimension and test coverage are the keys to ensure database quality. Test dimension includes unit test, fuzzy test, functional test (sqllogitest belongs to this category), end-to-end (e2e) test, performance test, etc.

The basic idea of functional tests for database is to compare the execution returns with expectations. Generally, the following issues need to be considered in advance:

1. How to prescribe the test-script format?
2. How to compare the results? In most solutions, the returns are saved as files, making detailed execution results invisible . We had to add additional output between cases to determine approximately where the cases were making errors.
3. How to bridge the differences between clients and databases? In most cases, different clients have different return formats, similarly different databases have different outputs for certain types.

What is sqllogictest?

Developed by SQLite's author D. Richard Hipp, sqllogictest was originally designed to test SQLite. See more about its design concepts in https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki.The sqllogictest program seeks to answer just one question: Does the database engine compute the correct answer? No attention is paid to performance, optimal use of index, disk and memory, transactional behavior, or concurrency and locking issues.

At present, mainstream databases have all implemented their own sqllogictest test tools and test cases, yet the syntaxes of test cases are slightly different and not compatible with each other. The implementation methods of test tools are also different:

• [YDB] implemented by Python
• [CockroachDB] implemented by Go

Why Databend needs sqllogictest?

There is already a set of functional test tools implemented in Databend, which divides the functional test cases into stateless tests and stateful tests, using Clickhouse for reference. The use cases are written as scripts (or SQL files), and the expected results are saved as files with the same name but different suffixes. We can run Databend-test(written in Python) to execute tests, and then use diff to compare the results.

This method, however, is not friendly to the compilation and modification of error cases. Moreover, Databend supports multiple sets of handlers (such as MySQL, HTTP, and Clickhouse), yet this method cannot conduct tests for every handler (kind of like testing different databases). Therefore, we looked for methods and tools that can solve these problems.

How to implement sqllogictest in Databend?

The implementation versions of sqllogictest differ greatly, which is not only reflected in the supported use case syntaxes, but also in the technical stacks used and the functions implemented. As a result, it is difficult to use the existing test cases or the tools directly.

After analyzing and comparing different implementation schemes, we found that the core function requirements of sqllogictest are in fact not too much. The existing versions in the community differed greatly from each other and can't be used directly. Moreover, with the advancement of testing practice, many new demands would be proposed, therefore the workload of customized development would certainly be quite arduous. Considering all these, we chose to start from scratch with Python.

In sqllogictest, there are multiple Runners interacting with different databases or handlers. Each runner should implement all the methods in the base class SuiteRunner, including:

• execute_ok
• execute_error
• execute_query
• batch_execute

These methods are the core of sqllogictest execution. Besides, the SuiteRunner class also stores some of the state and control variables during execution.

Take the typo of Http Runner as an example . In addition to the necessary interfaces like“execute_ok”，“execute_error”，“execute_query”，“batch_execute”, two functions are also realized for resetting connections and sessions.

The use case file is parsed by the Statement class. At present, a simple method is used for syntax parsing, that is, reading the file line by line and using regular expressions for matching. Compared with implementing an interpreter, the advantage of this approach is that it can be implemented quickly, yet the disadvantage is that it would be cumbersome to extend the syntax support.

Error information is output through logicError, including the name of the runner where the error occurred, the error message (including the details of the error statement) and the error type.

A LogicTestStatistics class is also implemented to record the time cost of each SQL execution. The final statistical output is relatively simple now but can be supplemented in the future.

How to write test scripts for sqllogictest ?

Basic functions

Here is a quick guidehttps://github.com/datafuselabs/databend/blob/main/tests/logictest/suites/select_0.The supported handlers: MySQL handler, HTTP handler, Clickhouse handlerAnnotation: The use of '--' to annotate specific lines is supported.Statement types：

• ok

  • The statement is executed correctly and no error is returned.

• error <error regex>

  • An error occurs when the statement is executed, and the returned error info contains expected content. Usually return codes are used, text messages are also feasible (but not intuitive).

• query <options> <labels>

  • The execution return includes a result set, and the comparison method of the result sets be specified by options and labels parameters.

• options: Composed of characters, and each character represents a column in the result set.

• The supported characters are:

  • B Boolean
  • T text
  • F floating point
  • I integer
  • labels: The differences in the processing of results by different databases (handlers) are distinguished by labels. Commas are used when there are multiple differences.

Compared to ok and error statements, query statements are more complicated. Here is a use case of query type (for reference only and may be inconsistent with the actual results):

statement query III label(mysql) 
select number, number + 1, number + 999 from numbers(10); 
----0     1   9991     2  10002     3  10013     4  10024     5  10035     6  10046     7  10057     8  10068     9  10079    10  1008.0 
----  mysql0     1   9991     2  10002     3  10013     4  10024     5  10035     6  10046     7  10057     8  10068     9  10079    10  1008

Control syntax in the testing process:

1.Use“ skipif” to skip specified runner

skipif clickhouse 
statement query I 
select 1; 
---- 
1

2.Use “onlyif” to execute the specified runner

onlyif mysql 
statement query I 
select 1; 
---- 
1

3.When encountering occasional test failures that cannot be solved easily, we can use skipped to skip the use case, or annotate it for now.

statement query skipped I 
select 1; 
---- 
1

Execution output

A SUCCESS example:

The current summary contains simple statistics on the test execution process, including the number of case files executed, the number of statements contained in each case file, the average time of each statement execution and the average time of case execution.

A FAILURE example:

he failed case is in the 4th line of “base/15_query/alias/having_with_alias.test”. The expected return is 1 but get null instead.

Another FAILURE example:

The failed case is in the 1st line of “base/02_function/02_0017_function_strings_oct”. The reported error is table already exists.

As can be seen from the above examples, it is easy to locate the specific use case file or even the index of line or SQL with the returned output. For those that need to compare the results, the expected and actual return values will also be printed out so users can easily find out the error. This greatly improves the use experience of developers and improves the efficiency of debugging.

Using sqllogictest in a pipeline

When a PR(Pull Request) is proposed to the Databend repository, a pipeline will be constructed. Then enter the testing process, the constructed product will be executed in a new environment, and various tests will be conducted at the same time. Sqllogictest is an important part of the process.

As shown in the figure:

Only after all the tests pass successfully can the submission be merged into the branch, ensuring that none revision will interfere with the expected functions. All we need to do is to extend the use cases and prompt the test coverage.

Running sqllogictest

For contributors

Directly execute “make sqllogic-test”in the Databend directory after cloning.

For users

1.Deploy and run Databend, refer to https://databend.rs/doc/deploy/deploying-databend.

2.Copy Databend code of corresponding version, then change the directory to tests/logictest.

3.Install python3(>=3.8).

4.Install dependencies.

pip3 install -r requirements.txt

5.Run “python3” main.py.

Run parameters

Command-line parameters

1. “--suites other_dir” will run all the case files in ."/other_dir"
2. “--run-dir ydb” will run all the case files from directories in."/suites/"with "ydb" contained in their names
3. "--skip-dir ydb" will skip all the case files from directories in."/suites/"with "ydb" contained in their names
4. "python main.py "03_0001""will run the specific case file with "03_0001" contained in its name

Environment variables parameters

A	B
SKIP_TEST_FILES	Cases containing the specified filename are skipped, separated by commas
DISABLE_MYSQL_LOGIC_TEST	Close the test of mysql handler, any value
DISABLE_HTTP_LOGIC_TEST	Close the test of http handler, any value
DISABLE_CLICKHOUSE_LOGIC_TEST	Close the test of clickhouse handler, any value
QUERY_MYSQL_HANDLER_HOST	mysql handler address
QUERY_MYSQL_HANDLER_PORT	mysql handler port
QUERY_HTTP_HANDLER_HOST	http handler address
QUERY_HTTP_HANDLER_PORT	http handler port
QUERY_CLICKHOUSE_HANDLER_HOST	clickhouse handler address
QUERY_CLICKHOUSE_HANDLER_PORT	clickhouse handler port
MYSQL_DATABASE	Default database,usually default
MYSQL_USER	Default user, usually root
ADDITIONAL_HEADERS	Usually used for the extension requirements of HTTP protocol, such as identity authentication

These parameters can meet personalized running conditions, such as Databend not deployed locally or testing MySQL and Clickhouse (when only HTTP is supported, and Clickhouse native protocol is not supported)Note: due to the SQL dialect problem, our use cases may have statements that are not supported by other databases, and the use cases of other databases may also have similar situations.

Tips

• Use ok statements for use cases where the results are less important.
• Prefer to use error codes in error statements, as messages are unstable.
• The spaces in the result sets of query statements are only used to distinguish different columns. Additional spaces will not affect the test results.
• When using query statements, a /t tab key is needed if there is a blank line in the returned result.
• Since we dropped the support for sorting and retry syntaxes in use cases (now implemented in the test tool), it is necessary to add an order by statement to ensure that the result order is always consistent

How to organize test cases?

Test modules are under the first level directory. For example, we currently have two modules: base and ydb, where base stores our own use cases, and ydb represents cases imported from YDB.As for organizations in the respective modules, there is no clear specification yet. These methods are generally followed:

• Organize cases by statements like cockroachdb
• Organize cases by statement types or related modules, such as DML, DDL and planner_v2, according to function development process

Extension

Regular matching of returned columns is required, for currently only precise matching is supported in query statements, and this can't meet the needs of some fuzzy matching.

• Time formats matching, thus use cases without a fixed time in its return would be supported.

Future plans

Improve the use experience and toolchain of sqllogictest

The use experience of sqllogictest includes the improvement of functional requirements, more friendly log output method, use case migration tools (from SQL files or third-party sqllogictest use case files), etc.

Extend test cases and raise test coverage

The test case sets are valuable assets, which often take a lot of time to design and perfect. It is of great significance to improve the test coverage by using migration cases. We also need to improve our own test scenarios and functional test coverage at the same time.

Open-SQL-Logictest?

Many database projects have included a sqllogictest. A sqllogictest is usually implemented based on a specified project and cannot work in another project. We need a set of standardized practical methods for the sqllogictest implementation that includes all the considerations of the requirements from sqllogictest.

If one day we can sort out all the requirements of sqllogictest and define certain standards, this can then be an option.

Reference

https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki

https://github.com/datafuselabs/databend/tree/main/tests/logictest