Go-sqlancer

Inspired by Manuel Rigger's paper Testing Database Engines via Pivoted Query Synthesis.

Testing approaches

Go-sqlancer has supported Pivoted Query Synthesis (PQS), Non-optimizing Reference Engine Construction (NoREC) and Ternary Logic Partitioning (TLP). You can use -mode to specify the testing approach.

Quickstart

make
bin/go-sqlancer -dsn "root:@tcp(127.0.0.1:4000)/"

And other flags you can set:

Usage of go-sqlancer:
  -approach string
        use NoRec or PQS method or both, split by vertical bar (default "pqs|norec|tlp")
  -depth int
        sql depth (default 1)
  -dsn string
        dsn of target db for testing
  -duration duration
        fuzz duration (default 5h0m0s)
  -enable-expr-idx
        enable create expression index
  -enable-hint
        enable sql hint for TiDB
  -log-level string
        set log level: info, warn, error, debug [default: info] (default "info")
  -silent
        silent when verify failed
  -view-count int
        count of views to be created (default 10)

Supported Statement

Functions & Operators

XOR, AND, OR, NOT, GT, LT, NE, EQ, GE, LE, IF, CASE, IN, BETWEEN, etc.

• https://github.com/pingcap/tidb/issues/16716

create table t(a float);
insert t values(NULL);
select * from t where (!(a and a)) is null;

---
tidb> select * from t where (!(a and a)) is null;
Empty set (0.00 sec)
----
mysql> select * from t where (!(a and a)) is null;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
---

• https://github.com/pingcap/tidb/issues/16679

create table t0(c0 int);
insert into t0 values(null);

---
tidb> select * from t0 where ((!(1.5071004017670217e-01=t0.c0))) IS NULL;
Empty set (0.00 sec)

tidb> select ((!(1.5071004017670217e-01=null))) IS NULL;
+--------------------------------------------+
| ((!(1.5071004017670217e-01=null))) IS NULL |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

• https://github.com/pingcap/tidb/issues/16788

create table t(c int);
insert into t values(1), (NULL);

---
tidb> select c, c = 0.5 from t;
+------+---------+
| c    | c = 0.5 |
+------+---------+
|    1 |       0 |
| NULL |       0 |
+------+---------+
2 rows in set (0.01 sec)
---
mysql> select c, c = 0.5 from t;
+------+---------+
| c    | c = 0.5 |
+------+---------+
|    1 |       0 |
| NULL |    NULL |
+------+---------+
2 rows in set (0.00 sec)

• https://github.com/tidb-challenge-program/bug-hunting-issue/issues/64

mysql> desc table_int_float;
+-----------+---------+------+------+---------+----------------+
| Field     | Type    | Null | Key  | Default | Extra          |
+-----------+---------+------+------+---------+----------------+
| id        | int(16) | NO   | PRI  | NULL    | auto_increment |
| col_int   | int(16) | YES  |      | NULL    |                |
| col_float | float   | YES  | MUL  | NULL    |                |
+-----------+---------+------+------+---------+----------------+
3 rows in set (0.00 sec)
mysql> select col_float from table_varchar_float;
+-----------+
| col_float |
+-----------+
|      NULL |
+-----------+

---
tidb> SELECT * FROM table_varchar_float WHERE !(table_varchar_float.col_float and 1) IS NULL;
Empty set (0.00 sec)

View

Table partition

• https://github.com/pingcap/tidb/issues/16896

create table t(id int not null auto_increment, col_int int not null, col_float float, primary key(id, col_int)) partition by range(col_int) (partition p0 values less than (100), partition pn values less than (MAXVALUE));
insert into t values(1, 10, 1), (101, 100, 101);

---
tidb> SELECT /*+ use_cascades(TRUE)*/ * from t;
Empty set (0.00 sec)

tidb> SELECT * from t;
+-----+---------+-----------+
| id  | col_int | col_float |
+-----+---------+-----------+
| 101 |     100 |       101 |
|   1 |      10 |         1 |
+-----+---------+-----------+
2 rows in set (0.00 sec)

SQL Hint

• hash_agg
• stream_agg
• agg_to_cop
• read_consistent_replica
• no_index_merge
• use_toja
• enable_plan_cache
• use_cascades
• hash_join
• merge_join
• inl_join
• memory_quota
• max_execution_time
• use_index
• ignore_index
• use_index_merge
• qb_name
• time_range
• read_from_storage
• query_type
• inl_hash_join
• inl_merge_join

Issues found by go-sqlancer

Fuzz Issues

Notes

For experimental features in tidb, you need add some configs on tiup startup

[experimental]
allow-expression-index = true
allow-auto-random = true

tiup playground nightly --db.config path/to/config/file