SQLacner for Databend 使用说明

_hys

已于 2022-10-02 11:29:29 修改

阅读量1.2k

点赞数

分类专栏：数据库文章标签：数据库 oracle 功能测试

于 2022-10-02 11:28:40 首次发布

本文链接：https://blog.csdn.net/hys__handsome/article/details/127141285

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SQLacner for Databend 数据库逻辑测试

一、运行

1、启动databend-query并创建用户

./databend/bin/databend-query &
mysql -uroot -h127.0.0.1 -P3307 -e "CREATE USER 'sqlancer' IDENTIFIED BY 'sqlancer'; GRANT ALL ON *.* TO sqlancer;"

2、打包sqlancer

git clone https://github.com/sqlancer/sqlancer.git
cd sqlancer
mvn package -DskipTests

3、运行sqlancer

#方式一，运行test单元
DATABEND_AVAILABLE=true mvn -Dtest=TestDatabend test
#方式二，运行jar包，可指定参数
cd target
java -jar sqlancer-*.jar --num-threads 4 --random-string-generation ALPHANUMERIC databend --oracle WHERE
#运行成功每5s输出一条信息
[2022/10/01 19:33:12] Executed 1037 queries (207 queries/s; 0.80/s dbs, successful statements: 100%). Threads shut down: 0.
[2022/10/01 19:33:17] Executed 2133 queries (219 queries/s; 0.00/s dbs, successful statements: 100%). Threads shut down: 0.
[2022/10/01 19:33:22] Executed 3248 queries (223 queries/s; 0.00/s dbs, successful statements: 100%). Threads shut down: 0.
[2022/10/01 19:33:27] Executed 4351 queries (220 queries/s; 0.00/s dbs, successful statements: 100%). Threads shut down: 0.

4、列举以下常用options（主可选项定义在MainOptions类，另外一个定义在DatabendOptions类）

-h：查看帮助

–num-threads：线程数

–timeout-seconds：运行时间，单位秒，默认-1无限执行

–num-tries：指定多少次异常后停止测试，默认100

–database-prefix：数据库的前缀名

–random-string-generation：随机字符串模式

–host：主机，默认localhost

–port：端口，默认3307

–username：用户名，默认sqlancer

–password：密码，默认sqlancer

databend：指定的DBMS

–oracle：测试的方式

Non-optimizing Reference Engine Construction (NoREC)

NoREC

Ternary Logic Partitioning (TLP)

QUERY_PARTITIONING
WHERE
GROUP_BY
HAVING
DISTINCT
AGGREGATE

二、排查

当sqlancer停止测试后，会先清除原来的日志文件（注意备份），然后将所有错误写入到新的日志文件中 ./sqlancer/logs/databend/*.log 其中 *-cur.log 为某个db执行的sql记录。

1、TLP检测出logic bug

若TLP检测出logic bug，日志文件会包含以下信息（错误的 query sql与重现db的sql）：其中cardinality不一致说明发生了logic bug。

--java.lang.AssertionError: the size of the result sets mismatch (3 and 6)!
-- Time: 2022/08/29 16:57:50
-- Database: databend1
-- Database version: 8.0.26-v0.8.12-nightly-74d0287-simd(rust-1.64.0-nightly-2022-08-27T03:09:50.519081067Z)
-- seed value: 1
DROP DATABASE IF EXISTS databend1;
CREATE DATABASE databend1;
USE databend1;
CREATE TABLE t0(c0BOOLEAN BOOLEAN NULL DEFAULT(true));
CREATE TABLE t1(c0FLOAT DOUBLE NULL DEFAULT(0.8522535562515259), c1VARCHAR VARCHAR NULL);
INSERT INTO t1(c0float) VALUES (0.48751503229141235);
INSERT INTO t1(c0float, c1varchar) VALUES (0.8522535562515259, NULL);
INSERT INTO t1(c1varchar) VALUES ('2'), ('78');
INSERT INTO t0(c0boolean) VALUES (false), (false), (true);
INSERT INTO t1(c0float) VALUES (0.8522535562515259), (0.48751503229141235);
INSERT INTO t1(c0float, c1varchar) VALUES (0.48751503229141235, '7555834'), (0.7298239469528198, '8');
-- SELECT t0.c0boolean FROM t0;
-- cardinality: 3
-- SELECT t0.c0boolean FROM t0 WHERE (NULL BETWEEN NULL AND NULL) UNION ALL SELECT t0.c0boolean FROM t0 WHERE (NOT (NULL BETWEEN NULL AND NULL)) UNION ALL SELECT t0.c0boolean FROM t0 WHERE (((NULL BETWEEN NULL AND NULL)) IS NULL);
-- cardinality: 6

TLP发现的bug例如：sqlancer: expression expansion error · Issue #7360 · datafuselabs/databend (github.com)

2、NoREC检测出logic bug

若NoREC检测出logic bug，日志文件会包含以下信息（错误的query sql与重现db的sql）：

第一条 query sql 返回的是结果的row数，第二条 query sql 返回的是count的和，若两数不一致则出现logic bug。

java.lang.AssertionError: SELECT t1.c0float, t1.c1varchar, t0.c0boolean FROM t1 RIGHT  JOIN t0 ON true WHERE (NOT -1257754687); -- 3
SELECT SUM(count) FROM (SELECT (((NOT -1257754687) IS NOT NULL AND (NOT -1257754687)) ::BIGINT)as count FROM t1 RIGHT JOIN t0 ON true) as res -- 0
-- Time: 2022/09/24 09:27:50
-- Database: databend1
-- Database version: 8.0.26-v0.8.46-nightly-f524701-simd(rust-1.66.0-nightly-2022-09-23T16:20:13.611527635Z)
-- seed value: 1
DROP DATABASE IF EXISTS databend1;
CREATE DATABASE databend1;
USE databend1;
CREATE TABLE t0(c0BOOLEAN BOOLEAN NULL DEFAULT(true));
CREATE TABLE t1(c0FLOAT DOUBLE NULL DEFAULT(0.8522535562515259), c1VARCHAR VARCHAR NULL);
INSERT INTO t1(c0float) VALUES (0.48751503229141235);

NoREC发现的bug例如：bug: select error · Issue #7863 · datafuselabs/databend (github.com)