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.
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)
---
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)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)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
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
Notes
For experimental features in tidb, you need add some configs on tiup startup
[experimental]
allow-expression-index = true
allow-auto-random = truetiup playground nightly --db.config path/to/config/file