详解 Sqllogictest
写作背景
之前的文章《如何为 Databend 添加新的测试》介绍了 Databend 如何进行测试，其中 SQL 的测试方法中提到了 sqllogictest，大家对这种新引入的测试方法比较感兴趣，但当前介绍这个的中文资料很少，因此我们整理下近期的一些工作和思考，跟大家分享一下 sqllogictest 的设计、实现及应用。
关于 sqllogictest
数据库质量保证
测试维度和测试覆盖率是保证数据库质量的关键，测试维度包括 单元测试、模糊测试、功能测试（sqllogictest 在这里）、端到端（e2e）测试、性能测试等。数据库功能测试方案核心是通过执行 SQL 语句获得返回值，将返回值与预期进行对比，通常存在几个需要考虑的问题：
- 如何设计用例的格式？
- 如何比对结果？多数方案直接保存结果文件，无法区分具体 SQL 的执行结果，只能通过在用例之间增加输出的方式，导致用例不直观；
- 不同客户端或者数据库的差异如何解决？如不同的客户端对返回内容格式化方式有差异，不同的数据库对某些类型输出有差异；
发展简介
sqllogictest 最早是 SQLite 进行测试的工具，由 SQLite 的作者 D. Richard Hipp（理查德 希普）设计开发。关于相关的设计理念可以在 https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki 找到。
sqllogictest 的目标是保证数据库引擎执行结果是正确的。因此它不会关注其他方面的问题，诸如性能、索引优化、磁盘内存的使用情况、并发和锁等。
目前主流的数据库都有自己的 sqllogictest 测试工具和测试用例，测试用例的语法略有差异并且不能互相兼容，测试工具的实现方式也有所区别：
-
YDB 使用 python 实现
-
CockroachDB使用 go 实现
Databend 为何引入 sqllogictest
Databend 原来有一套功能测试工具，借鉴 clickhouse 的测试方法，将功能测试用例分为 stateless 测试和 stateful 测试。通过 Databend-test（python 实现）来执行，用例通过脚本的方式编写（或者一个 SQL 文件），用例的预期结果写成同名不同后缀名的文件并将两者的输出进行 diff 对比。如果相同则认为结果正确。这种测试方法对错误用例的编写和修改不友好外，此外 Databend 支持多套不同的 handler（如 mysql、http、clickhouse）这些 handler 都有被测试的需求，有点像测试不同的数据库。但原来的测试方法没办法解决这个问题，因此我们开始寻找一种能解决这些问题的测试方法和工具。
Databend 如何实现 sqllogictest
虽然都叫 sqllogictest，但实现差异很大，这种差异不仅在用例语法的支持上，实现使用的技术栈及整个工具的实现程度区别也很大。导致不管是测试集还是工具本身，很难开箱即用。经过对不同实现方案的分析对比，我们发现 sqllogictest 的核心功能需求不多、整个开源社区实现分裂无法满意的直接用、本身随着测试工作的推进越来越多的需求会加入进来导致大量的定制化开发。最终我们选择使用 python 自己造轮子。
sqllogictest 包含多个不同的 Runner 负责与不同的数据库或者 handler 交互，每个 Runner 要实现基类 SuiteRunner 中的方法，包括
-
execute_ok
-
execute_error
-
execute_query
-
batch_execute
这些方法是执行 sqllogictest 的核心，除此之外 SuiteRunner 类还会保存执行过程中的一些状态和控制变量。
以 Httprunner 的实现为例，实现了必要的接口 execute_ok 、execute_error、execute_query、batch_execute，除此之外还有两个函数 get_connection 和 reset_connection 主要用来重置连接和会话。
通过 Statement 类去解析用例文件，目前没有考虑实现一个解释器的方案，而采用简单的逐行读取文件通过正则匹配的方式实现语法解析。这么做的好处是可以快速实现；缺点是后续要添加语法支持比较麻烦。通过 LogicError 来输出错误信息，包含错误出现的 runner 名称、错误的消息（包含出错的 statement 的详情）及错误的类型。此外还实现了一个 LogicTestStatistics 类，记录每一个 SQL 执行的时间开销，最终输出的统计信息还比较简单，后续可以补充完善。
如何编写 sqllogictest
基础功能
可以通过这个实例快速入门: https://github.com/datafuselabs/databend/blob/main/tests/logictest/suites/select_0 当前支持的执行器: mysql handler, http handler, clickhouse handler。支持注释语法 ，使用 -- 来注释特定的行。statement类型：
-
ok
-
- 语句正确执行，无错误返回
-
error
-
- 语句执行错误，且返回的错误信息包含指定预期的内容，通常使用返回码，也可以使用消息文本（但不直观）
-
query
-
- B Boolean 布尔类型
- T text 文本类型
- F floating point 浮点类型
- I integer 整形
- 语句执行返回带有结果集, 通过 options 和 labels 区分结果集的对比方式
- options由字符组成，每个字符代表结果集中的一个列，支持的字符有：
- labels 不同的数据库（handler）对结果的处理存在差异通过 labels 区分开，对于存在多个差异的，通过逗号分隔开
相对而言 ok 和 error 比较好理解，query 相对复杂一些，以下是一个 query 类型用例的示例（仅供参考不代表实际结果）:
statement query III label(mysql)
select number, number + 1, number + 999 from numbers(10);
----
0 1 999
1 2 1000
2 3 1001
3 4 1002
4 5 1003
5 6 1004
6 7 1005
7 8 1006
8 9 1007
9 10 1008.0
---- mysql
0 1 999
1 2 1000
2 3 1001
3 4 1002
4 5 1003
5 6 1004
6 7 1005
7 8 1006
8 9 1007
9 10 1008
测试流程控制语法
1.支持 skipif 用于跳过指定的 runner
skipif clickhouse
statement query I
select 1;
----
1
2.支持 onlyif 用于仅执行指定的 runner
onlyif mysql
statement query I
select 1;
----
1
3.如果遇到一些偶发的测试失败，无法短期解决的。可以通过 skipped 跳过这个用例，也可以选择注释掉。
statement query skipped I
select 1;
----
1
执行输出
成功样例：
Logic Test Summary
Runner mysql test 237 suites, avg time cost of suites is 822.25 ms
Runner mysql test 4302 statements, avg time cost of statements is 45.3 ms
Runner http test 231 suites, avg time cost of suites is 341.56 ms
Runner http test 4222 statements, avg time cost of statements is 18.69 ms
Runner clickhouse test 231 suites, avg time cost of suites is 336.48 ms
Runner clickhouse test 4219 statements, avg time cost of statements is 18.42 ms
All tests pass! Logic test success!
当前的 summary 中包含了对测试执行过程的简单统计，包括执行的用例文件数、每个用例文件包含多少个语句、每个语句执行的平均时间及用例执行的平均时间。
失败样例 1：
ErrorType: statement query get result not equal to expected
Message:
Expected:
1
Actual:
Statement:
Parsed Statement
at_line: 4,
s_type: Statement: query, type:I, query_type: I, retry: False,
suite_name: base\15_query\alias\having_with_alias.test,
text:
select count(*) as count from (select * from numbers(1)) having count = 1;
results: [(<re.Match object; span=(0,4), match="------------------->>, 8, '1')],
runs_on: {'mysql", 'clickhouse", ‘http'}.
Start Line: 8, Result Label:
可以看出失败的用例为 base\15_query\alias\having_with_alias.test 中的第四行 ，返回的内容预期为 1 但实际是空。
失败样例 2：
Failed to execute. Collected info: Orig exception: Code: 2302, displayText = Table 'strings_oct_sample_u8' already exists.
Parsed Statement
at_line: 1,
s_type: Statement: ok, type: None,
suite_name: base\02_function\02_0017_function_strings_oct,
text:
CREATE TABLE strings_oct_sample_u8 (value UInt8 null) Engine = Fuse;
results:[],
runs_on: {'mysql', 'clickhouse', ‘http'}.
可以看出失败的用例为 base\02_function\02_0017_function_strings_oct 的第一行，返回的错误为表已存在。以上示例中我们发现从输出内容很容易就可以定位到具体的用例文件甚至哪一行哪个 SQL，对于需要对比结果的，也会把结果的预期和实际返回值打印出来，轻松的找出错误的问题。极大的改善了开发人员的使用体验，提升了排查问题的效率。
在流水线中使用 sqllogictest
当提交一个 PR（Pull Request）到 Databend 仓库时，会触发一系列的流水线；当构建部分完成后，会进入测试的部分。流水线会将构建产物在一个全新的环境上运行起来，同时执行各项测试，sqllogictest 是其中的一个重要环节。如图:只有当所有的测试都通过后，该提交才能合并到主干，保证了每次修订不会影响功能预期，而我们需要做的就是完善用例、提示用例的覆盖率。
运行 sqllogictest
贡献者：
直接在克隆 Databend 代码后，在 Databend 目录内执行 make sqllogic-test
使用者：
-
部署并运行 Databend，参考 https://databend.rs/doc/deploy/deploying-databend
-
拷贝与运行版本一致的 Databend 代码，进入 tests/logictest 目录
-
安装 python3(>=3.8)
-
安装 python3 依赖，通过目录下的 requirements.txt
pip3 install -r requirements.txt
- 执行 python3 main.py
运行参数
命令行参数：
- --suites other_dir 将会运行 ./other_dir 下的用例文件
- --run-dir ydb 将会运行 ./suites/ 下的目录名包含 ydb 的目录内的用例
- --skip-dir ydb 将会跳过 ./suites/ 下的目录名包含 ydb 的目录内的用例
- python main.py "03_0001" 指定执行名称中包含 03_0001 的用例
环境变量参数：
| SKIP_TEST_FILES | 包含指定文件名的用例会被跳过，通过逗号分隔 |
|---|---|
| DISABLE_MYSQL_LOGIC_TEST | 关闭 mysql handler 的测试，任意值 |
| DISABLE_CLICKHOUSE_LOGIC_TEST | 关闭 http handler 的测试，任意值 |
| DISABLE_CLICKHOUSE_LOGIC_TES | 关闭 clickhouse handler 的测试，任意值 |
| QUERY_MYSQL_HANDLER_HOST | mysql handler 地址 |
| QUERY_MYSQL_HANDLER_PORT | mysql handler 端口 |
| QUERY_HTTP_HANDLER_HOST | http handler 地址 |
| QUERY_HTTP_HANDLER_PORT | http handler 端口 |
| QUERY_CLICKHOUSE_HANDLER_HOST | clickhouse handler 地址 |
| QUERY_CLICKHOUSE_HANDLER_PORT | clickhouse handler 端口 |
| MYSQL_DATABASE | 默认数据库，通常是 default |
| MYSQL_USER | 默认用户，通常是 root |
| ADDITIONAL_HEADERS | 通常用于 http 协议的扩展需求，如身份认证 |
这些参数可以满足个性化的运行条件，比如不在本地部署的 Databend 或者测试 mysql、clickhouse（仅支持 http，不支持 clickhouse native 协议）
注意：由于 SQL 方言问题，我们的用例可能存在其他数据库不支持的语句，其他数据库的用例也存在类似情况。
编写技巧
- 不在意结果的用例使用 statement ok
- statement error 尽量使用错误码，message 是不稳定的
- statement query 的结果集里的空格仅用于区分不同的列，写多个空格除了影响外观外，不会影响测试结果
- statement query 对于返回结果中有空行的，需要用 /t tab 键占位
- 由于放弃了在用例里支持排序和重试语法（移到了测试工具中实现），必要时带上 order by 保证结果顺序始终一致
用例文件如何组织
测试套件的来源为第一层目录，如当前我们有 base、ydb 两部分套件；base 是自有用例、ydb 是从 ydb 引入的用例。在套件内的目录组织暂时还没形成明确的规范，通常以下组织方式：
-
根据语句来区分如 cockroachdb 的用例组织
-
根据语句类型或者设计到的模块来区分 如 DML、 DDL 或者 planner_v2，跟随功能开发走
拓展考虑
支持返回列的正则匹配，主要需求为当前 statement query 只支持精确匹配，无法满足部分模糊匹配的需求：匹配时间格式，这样就支持一些不返回固定时间的用例
后续计划
完善 sqllogictest 的使用体验及工具链
sqllogictest 的使用体验包括功能型的需求的完善、日志输出更加友好、用例迁移工具（从 SQL 文件或者第三方 sqllogictest 用例文件）等。
完善测试用例及覆盖率
各家测试数据集是宝贵的财富，往往是花费大量时间去设计和完善的，迁移用例为我所用对于加速测试覆盖率意义重大。同时我们也要完善自身的测试场景和功能的测试覆盖率。
Open-SQQL-Logictest
这是个畅想，重复造轮子并不是一个好习惯，除非轮子能造的更简单、更好用。如果有一天，对于sqllogictest的各方需求能整理清楚，定义出标准，这也许会成为可能。
参考资料
引用链接
[1] YDB: https://github.com/ydb-platform/ydb/tree/main/ydb/tests/functional/suite_tests
[2] CockroachDB: *https://github.com/cockroachdb/cockroach/tree/master/pkg/sql/logictest
关于 Databend
Databend 是一款开源、弹性、低成本，基于对象存储也可以做实时分析的新式数仓。期待您的关注，一起探索云原生数仓解决方案，打造新一代开源 Data Cloud。
-
Databend 文档：https://databend.rs/
-
Wechat：Databend
文章首发于公众号：Databend
