关于sql:详解-Sqllogictest

写作背景

之前的文章《如何为 Databend 增加新的测试》介绍了 Databend 如何进行测试，其中 SQL 的测试方法中提到了 sqllogictest，大家对这种新引入的测试方法比拟感兴趣，但以后介绍这个的中文材料很少，因而咱们整顿下近期的一些工作和思考，跟大家分享一下 sqllogictest 的设计、实现及利用。

对于 sqllogictest

数据库质量保证

测试维度和测试覆盖率是保障数据库品质的要害，测试维度包含 单元测试、含糊测试、功能测试（sqllogictest 在这里）、端到端（e2e）测试、性能测试等。数据库功能测试计划外围是通过执行 SQL 语句取得返回值，将返回值与预期进行比照，通常存在几个须要思考的问题：

1. 如何设计用例的格局？
2. 如何比对后果？少数计划间接保留后果文件，无奈辨别具体 SQL 的执行后果，只能通过在用例之间减少输入的形式，导致用例不直观；
3. 不同客户端或者数据库的差别如何解决？如不同的客户端对返回内容格式化形式有差别，不同的数据库对某些类型输入有差别；

倒退简介

sqllogictest 最早是 SQLite 进行测试的工具，由 SQLite 的作者 D. Richard Hipp（理查德 希普）设计开发。对于相干的设计理念能够在 https://www.sqlite.org/sqllog… 找到。

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/datafusela… 以后反对的执行器: mysql handler, http handler, clickhouse handler。反对正文语法，应用 — 来正文特定的行。statement 类型：

• ok
• • 语句正确执行，无谬误返回
• error <error regex>
• • 语句执行谬误，且返回的错误信息蕴含指定预期的内容，通常应用返回码，也能够应用音讯文本（但不直观）
• query <options> <labels>
• • 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

使用者 ：

1. 部署并运行 Databend，参考 https://databend.rs/doc/deplo…
2. 拷贝与运行版本统一的 Databend 代码，进入 tests/logictest 目录
3. 装置 python3(>=3.8)
4. 装置 python3 依赖，通过目录下的 requirements.txt

pip3 install -r requirements.txt

5. 执行 python3 main.py

运行参数

命令行参数 ：

1. –suites other_dir 将会运行 ./other_dir 下的用例文件
2. –run-dir ydb 将会运行 ./suites/ 下的目录名蕴含 ydb 的目录内的用例
3. –skip-dir ydb 将会跳过 ./suites/ 下的目录名蕴含 ydb 的目录内的用例
4. python main.py “03_0001” 指定执行名称中蕴含 03_0001 的用例

环境变量参数 ：

这些参数能够满足个性化的运行条件，比方不在本地部署的 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 的各方需要能整顿分明，定义出规范，这兴许会成为可能。

参考资料

https://www.sqlite.org/sqllog…

对于 Databend

Databend 是一款开源、弹性、低成本，基于对象存储也能够做实时剖析的旧式数仓。期待您的关注，一起摸索云原生数仓解决方案，打造新一代开源 Data Cloud。

• Databend 文档：https://databend.rs/
• Twitter：https://twitter.com/Datafuse_…
• Slack：https://datafusecloud.slack.com/
• Wechat：Databend
• GitHub：https://github.com/datafusela…

文章首发于公众号：Databend