Skip to content

PingCAP-QE/go-sqlancer

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

74 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Go-sqlancer

Test Go Report Card

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

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