1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
--echo ####################################
--echo # EXECUTION
--echo ####################################
# -----------------------------------
# SQL Queries to test normalizations.
# -----------------------------------
SELECT 1 FROM t1;
SELECT 1 FROM `t1`;
SELECT 1,2 FROM t1;
SELECT 1, 2, 3, 4 FROM t1;
SELECT 1 FROM t2;
SELECT 1,2 FROM t2;
SELECT 1, 2, 3, 4 FROM t2;
# (NUM) => (#)
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
# (NUM,NUM) => (#,#)
INSERT INTO t3 VALUES (1, 2);
INSERT INTO t4 VALUES (1, 2);
# (NUM,NUM,NUM) => (#,#)
INSERT INTO t5 VALUES (1, 2, 3);
# (NUM),(NUM) => (#),(#)
INSERT INTO t1 VALUES (1), (2), (3);
# (NUM),(NUM),(NUM) => (#),(#)
INSERT INTO t1 VALUES (1), (2), (3), (4);
# (NUM,NUM),(NUM,NUM) => (#,#),(#,#)
INSERT INTO t3 VALUES (1, 2), (3, 4), (5, 6);
# (NUM,NUM,NUM),(NUM,NUM,NUM),(NUM,NUM,NUM) => (#,#),(#,#)
INSERT INTO t5 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
# IN_SYM TOK_ROW_SINGLE_VALUE => TOK_IN_GENERIC_VALUE_EXPRESSION
SELECT * FROM t1 WHERE a IN (1);
# IN_SYM TOK_ROW_MULTIPLE_VALUE => TOK_IN_GENERIC_VALUE_EXPRESSION
SELECT * FROM t1 WHERE a IN (1, 2, 3);
# -----------------------------------------------------------------------
# Test case to handle NULL. If alone, not normalized otherwise normalized.
# -----------------------------------------------------------------------
INSERT INTO t1 VALUES (NULL);
INSERT INTO t3 VALUES (NULL,NULL);
INSERT INTO t3 VALUES (1,NULL);
INSERT INTO t3 VALUES (NULL,1);
INSERT INTO t6 VALUES (NULL, NULL, NULL, NULL);
INSERT INTO t6 VALUES (1, NULL, NULL, NULL);
INSERT INTO t6 VALUES (NULL, 2, NULL, NULL);
INSERT INTO t6 VALUES (1, 2, 3, NULL);
INSERT INTO t6 VALUES (1, 2, NULL, 4);
# -----------------------------------------------------------------------
# Test case for handling spaces in statement.
# -----------------------------------------------------------------------
SELECT 1 + 1;
# -----------------------------------------------------------------------
# Test case for handling comments.
# -----------------------------------------------------------------------
# comment starting with "--"
# TODO : SELECT 1; -- This comment continues to the end of line
# comment starting from "#"
SELECT 1; # This comment continues to the end of line
# Inline comment
SELECT 1 /* This is an inline comment */ + 1;
# Multiple line comments
SELECT 1+
/*
this is a
multiple-line comment
*/
1;
# -----------------------------------------------------------------------
# Tests to show how the digest behaves with tokens that can have multiple
# names (such as DATABASE = "DATABASE" or "SCHEMA", SUBSTRING, STD_SYM,
# VARIANCE_SYM ... )
# -----------------------------------------------------------------------
--disable_warnings
CREATE SCHEMA statements_digest_temp;
DROP SCHEMA statements_digest_temp;
CREATE DATABASE statements_digest_temp;
DROP DATABASE statements_digest_temp;
# TODO : add more
--enable_warnings
# -----------------------------------------------------------------------
# Test case to show stats for statements giving ERRORS/WARNINGS, are also
# captured.
# -----------------------------------------------------------------------
--ERROR ER_NO_SUCH_TABLE
SELECT 1 FROM no_such_table;
CREATE TABLE dup_table (c char(4));
--ERROR ER_TABLE_EXISTS_ERROR
CREATE TABLE dup_table (c char(4));
DROP TABLE dup_table;
INSERT IGNORE INTO t11 VALUES("MySQL");
# -----------------------------------------------------------------------
# Tests to show sub-statements for following statements are not
# instrumented.
# - Prepared Statements
# - Stored Procedures/Functions.
# - Table Triggers
# -----------------------------------------------------------------------
PREPARE stmt FROM "SELECT * FROM t12";
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DELIMITER //;
CREATE PROCEDURE p1() BEGIN SELECT * FROM t12; END//
DELIMITER ;//
CALL p1();
CALL p1();
DROP PROCEDURE p1;
DELIMITER //;
CREATE FUNCTION `func`(a INT, b INT) RETURNS int(11) RETURN a+b //
DELIMITER ;//
select func(3,4);
select func(13,42);
DROP FUNCTION func;
CREATE TRIGGER trg BEFORE INSERT ON t12 FOR EACH ROW SET @a:=1;
INSERT INTO t12 VALUES ("abc");
INSERT INTO t12 VALUES ("def");
DROP TRIGGER trg;
# Tests to show more complex SELECT syntaxes
DELETE FROM t1;
INSERT INTO t1 VALUES (1),(2);
# Each pair of queries should go into one single class of digest:
SELECT * FROM (SELECT a,1,1 FROM t1) AS dt (x,y,z);
SELECT * FROM (SELECT a,2,2 FROM t1) AS dt (x,y,z);
WITH cte (x,y,z)AS (SELECT a,1,1 FROM t1) SELECT 1, cte.* FROM cte;
WITH cte (x,y,z)AS (SELECT a,2,2 FROM t1) SELECT 2, cte.* FROM cte;
--sorted_result
WITH cte (x,y,z)AS (SELECT a,1,1 FROM t1) SELECT 1, cte.*, cte2.* FROM cte, cte AS cte2;
--sorted_result
WITH cte (x,y,z)AS (SELECT a,2,2 FROM t1) SELECT 2, cte.*, cte2.* FROM cte, cte AS cte2;
--sorted_result
WITH RECURSIVE cte (x,y,z) AS
(SELECT a,1,1 FROM t1 UNION ALL SELECT a+1,a*1,11 FROM t1 WHERE 0)
SELECT 1, cte.*, cte2.* FROM cte, cte AS cte2;
--sorted_result
WITH RECURSIVE cte (x,y,z) AS
(SELECT a,2,2 FROM t1 UNION ALL SELECT a+2,a*2,22 FROM t1 WHERE 0)
SELECT 2, cte.*, cte2.* FROM cte, cte AS cte2;
|
