Skip to content

QueryPlanGuidance.md

Latest commit

 

History

History
66 lines (63 loc) · 16.5 KB

QueryPlanGuidance.md

File metadata and controls

66 lines (63 loc) · 16.5 KB

Query Plan Guidance

Query Plan Guidance (QPG) is a test case generation method that attempts to explore unseen query plans. Given a database state, we mutate it after no new unique query plans have been observed by randomly-generated queries on the database state aiming to cover more unique query plans for exposing more logics of DBMSs. Here, we document all mutators in which we choose the most promising one that may help covering more unique query plans to execute.

Mutators

All mutators are listed below and implemented in the enumeration variables Action in the XXDBProvider.java file of each DBMS. The Mutator column includes the items in the Action enumeration variable. The Example column includes an example of a realistic statement generated by this mutator. The Description column includes an explanation of what the mutator does. The More unique query plans... column explains why applying this mutator may help covering more unique query plans.

DBMS Mutator Example Description More unique query plans may be covered because of
SQLite PRAGMA PRAGMA automatic_index true; It modifies database options. different options that decide how to execute statements.
SQLite CREATE_INDEX CREATE INDEX i0 ON t0 WHERE c0 ISNULL; It adds a new index on a table. subsequent differnt logic of querying data.
SQLite CREATE_VIEW CREATE VIEW v0(c0) AS SELECT DISTINCT ABS(t0.c2) FROM t0; It adds a new view from existing tables. more possible execution logics on the view.
SQLite CREATE_TABLE CREATE TABLE t0 (c0 INT CHECK ((c0) BETWEEN (1) AND (10)) ); It adds a new table. more possible execution logics on the table.
SQLite CREATE_VIRTUALTABLE CREATE VIRTUAL TABLE vt1 USING fts5(c0 UNINDEXED); It adds a new table with fts5 feature. more possible execution logics on the table with fts5.
SQLite CREATE_RTREETABLE CREATE VIRTUAL TABLE rt0 USING rtree_i32(c0, c1, c2, c3, c4); It adds a new table with rtree feature. more possible execution logics on the table with rtree.
SQLite INSERT INSERT INTO t0(c0, c1) VALUES ('lrd+a*', NULL); It inserts a new row to a table. subsequent different logic of querying data.
SQLite DELETE DELETE FROM t0 WHERE (c0>3); It deletes specific rows from a table. subsequent different logic of querying data.
SQLite ALTER ALTER TABLE t0 ADD COLUMN c39 REAL; It changes the schema of a table. more possible execution logics on the changed table.
SQLite UPDATE UPDATE t0 SET (c2, c0)=(-944, 'L((xA') WHERE t0.c1; It updates specific data of a table. subsequent different logic of querying data.
SQLite DROP_INDEX DROP INDEX i0; It drops an index. subsequent different logic of querying data.
SQLite DROP_TABLE DROP TABLE t0; it drops an table. subsequent different logic of querying data.
SQLite DROP_VIEW DROP VIEW v0; It drops a view. subsequent different logic of querying data.
SQLite VACUUM VACUUM main; It rebuilds the database file. subsequent different logic of querying data.
SQLite REINDEX REINDEX t0; It drops and recreates indexes from scratch. subsequent different logic of querying data.
SQLite ANALYZE ANALYZE t0; It gathers statistics about tables to help make better query planning choices. subsequent different logic of querying data.
SQLite EXPLAIN EXPLAIN SELECT * FROM t0; It obtains query plan of a query. subsequent different logic of querying data.
SQLite CHECK_RTREE_TABLE SELECT rtreecheck('rt0'); It runs an integrity check on a table. subsequent different logic of querying data.
SQLite VIRTUAL_TABLE_ACTION INSERT INTO vt0(vt0) VALUES('rebuild'); It changes the options of a virtual table. subsequent different logic of querying data.
SQLite MANIPULATE_STAT_TABLE INSERT INTO sqlite_stat1 VALUES('rt0', 't1', '2'); It changes the table that stores statistics of all tables. subsequent different logic of querying data.
SQLite TRANSACTION_START BEGIN TRANSACTION; All statements after this will not be committed. subsequent different logic of querying data.
SQLite ROLLBACK_TRANSACTION ROLLBACK TRANSACTION; All statements after last BEGIN are dropped. subsequent different logic of querying data.
SQLite COMMIT COMMIT; All statements after last BEGIN are committed subsequent different logic of querying data.
TiDB CREATE_TABLE CREATE TABLE t1(c0 INT); It adds a new table. more possible execution logics on the table.
TiDB CREATE_INDEX CREATE INDEX i0 ON t0(c0(250) ASC) KEY_BLOCK_SIZE 1564693810209727437; It adds a new index on a table. subsequent differnt logic of querying data.
TiDB VIEW_GENERATOR CREATE VIEW v0(c0, c1) AS SELECT t1.c0, ((t1.c0)REGEXP('8')) FROM t1; It adds a new view from existing tables. more possible execution logics on the view.
TiDB INSERT INSERT INTO t0(c0) VALUES (-16387); It inserts a new row to a table. subsequent different logic of querying data.
TiDB ALTER_TABLE ALTER TABLE t1 ADD PRIMARY KEY(c0); It changes the schema of a table. more possible execution logics on the changed table.
TiDB TRUNCATE TRUNCATE t0; It drops all rows of a table. subsequent different logic of querying data.
TiDB UPDATE UPDATE t0 SET c0='S' WHERE t0.c0; It updates specific data of a table. subsequent different logic of querying data.
TiDB DELETE DELETE FROM t0 ORDER BY CAST(t0.c0 AS CHAR) DESC; It deletes specific rows from a table. subsequent different logic of querying data.
TiDB SET set @@tidb_max_chunk_size=8864; It modifies database options. different options that decide how to execute statements.
TiDB ADMIN_CHECKSUM_TABLE ADMIN CHECKSUM TABLE t0; it calculate the checksum for a table. subsequent different logic of querying data.
TiDB ANALYZE_TABLE ANALYZE TABLE t1 WITH 174 BUCKETS; It gathers statistics about tables to help make better query planning choices. subsequent different logic of querying data.
TiDB DROP_TABLE DROP TABLE t0; it drops an table. subsequent different logic of querying data.
TiDB DROP_VIEW DROP VIEW v0; It drops a view. subsequent different logic of querying data.
CockroachDB CREATE_TABLE CREATE TABLE t1 (c0 INT4, c1 VARBIT(44) UNIQUE DEFAULT (B'000'), CONSTRAINT "primary" PRIMARY KEY(c1 ASC, c0 ASC)); It adds a new table. more possible execution logics on the table.
CockroachDB CREATE_INDEX CREATE INDEX ON t0(rowid); It adds a new index on a table. subsequent differnt logic of querying data.
CockroachDB CREATE_VIEW CREATE VIEW v0(c0) AS SELECT DISTINCT MIN(TIMETZ '1970-01-11T12:19:44') FROM t0; It adds a new view from existing tables. more possible execution logics on the view.
CockroachDB CREATE_STATISTICS CREATE STATISTICS s0 FROM t2; It gathers statistics about tables to help make better query planning choices. subsequent different logic of querying data.
CockroachDB INSERT INSERT INTO t1 (rowid, c0) VALUES(NULL, true) ON CONFLICT (c0) DO NOTHING ; It inserts a new row to a table. subsequent different logic of querying data.
CockroachDB UPDATE UPDATE t0@{FORCE_INDEX=t0_pkey} SET c0=t0.c0; It updates specific data of a table. subsequent different logic of querying data.
CockroachDB SET_SESSION SET SESSION BYTEA_OUTPUT=escape; It changes session configurations. different options that decide how to execute statements.
CockroachDB SET_CLUSTER_SETTING SET CLUSTER SETTING sql.query_cache.enabled=true; It changes cluster configurations. different options that decide how to execute statements.
CockroachDB DELETE DELETE from t0; It deletes specific rows from a table. subsequent different logic of querying data.
CockroachDB TRUNCATE TRUNCATE TABLE t1 CASCADE; It drops all rows of a table. subsequent different logic of querying data.
CockroachDB DROP_TABLE DROP TABLE t0; it drops an table. subsequent different logic of querying data.
CockroachDB DROP_VIEW DROP VIEW v0; It drops a view. subsequent different logic of querying data.
CockroachDB COMMENT_ON COMMENT ON INDEX t0_c0_key IS '|?'; It changes schema of a table. subsequent different logic of querying data.
CockroachDB SHOW SHOW LOCALITY; It lists detailed information of active queries. subsequent different logic of querying data.
CockroachDB EXPLAIN EXPLAIN SELECT * FROM t0; It obtains query plan of a query. subsequent different logic of querying data.
CockroachDB SCRUB EXPERIMENTAL SCRUB table t0; It checks data corruption of a table. subsequent different logic of querying data.
CockroachDB SPLIT ALTER INDEX t0@t0_c0_key SPLIT AT VALUES (NULL); It changes the indexes. subsequent different logic of querying data.