forked from XiaoMi/soar
/
cases.go
213 lines (173 loc) · 14.6 KB
/
cases.go
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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
/*
* Copyright 2018 Xiaomi, Inc.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package common
// TestSQLs 测试SQL大集合
var TestSQLs []string
func init() {
// 所有的SQL都要以分号结尾，-list-test-sqls 参数会打印这个 list，以分号结尾可方便测试
// 如：./soar -list-test-sql | ./soar
TestSQLs = []string{
// single equality
"SELECT * FROM film WHERE length = 86;", // index(length)
"SELECT * FROM film WHERE length IS NULL;", // index(length)
"SELECT * FROM film HAVING title = 'abc';", // 无法使用索引
// single inequality
"SELECT * FROM sakila.film WHERE length >= 60;", // any of <, <=, >=, >; but not <>, !=, IS NOT NULL"
"SELECT * FROM sakila.film WHERE length >= '60';", // Implicit Conversion
"SELECT * FROM film WHERE length BETWEEN 60 AND 84;",
"SELECT * FROM film WHERE title LIKE 'AIR%';", // but not LIKE '%blah'",
"SELECT * FROM film WHERE title IS NOT NULL;",
// multiple equalities
"SELECT * FROM film WHERE length = 114 and title = 'ALABAMA DEVIL';", // index(title,length) or index(length,title)",
// equality and inequality
"SELECT * FROM film WHERE length > 100 and title = 'ALABAMA DEVIL';", // index(title, length)",
// multiple inequality
"SELECT * FROM film WHERE length > 100 and language_id < 10 and title = 'xyz';", // index(d, b) or index(d, c) 依赖数据",
"SELECT * FROM film WHERE length > 100 and language_id < 10;", // index(b) or index(c)",
// GROUP BY
"SELECT release_year, sum(length) FROM film WHERE length = 123 AND language_id = 1 GROUP BY release_year;", // INDEX(length, language_id, release_year) or INDEX(language_id, length, release_year)",
"SELECT release_year, sum(length) FROM film WHERE length >= 123 GROUP BY release_year;", // INDEX(length)",
"SELECT release_year, language_id, sum(length) FROM film GROUP BY release_year, language_id;", // INDEX(release_year, language_id) (no WHERE)",
"SELECT release_year, sum(length) FROM film WHERE length = 123 GROUP BY release_year,(length+language_id);", // INDEX(length) expression in GROUP BY, so no use including even release_year.",
"SELECT release_year, sum(film_id) FROM film GROUP BY release_year;", // INDEX(`release_year`)
"SELECT * FROM address GROUP BY address,district;", // INDEX(address, district)
"SELECT title FROM film WHERE ABS(language_id) = 3 GROUP BY title;", // 无法使用索引
// ORDER BY
"SELECT language_id FROM film WHERE length = 123 GROUP BY release_year ORDER BY language_id;", // INDEX(length, release_year) should have stopped with Step 2b",
"SELECT release_year FROM film WHERE length = 123 GROUP BY release_year ORDER BY release_year;", // INDEX(length, release_year) the release_year will be used for both GROUP BY and ORDER BY",
"SELECT * FROM film WHERE length = 123 ORDER BY release_year ASC, language_id DESC;", // INDEX(length) mixture of ASC and DESC.",
"SELECT release_year FROM film WHERE length = 123 GROUP BY release_year ORDER BY release_year LIMIT 10;", // INDEX(length, release_year)",
"SELECT * FROM film WHERE length = 123 ORDER BY release_year LIMIT 10;", // INDEX(length, release_year)",
"SELECT * FROM film ORDER BY release_year LIMIT 10;", // 不能单独给release_year加索引
"SELECT film_id FROM film ORDER BY release_year LIMIT 10;", // TODO: INDEX(release_year)，film_id 是主键查询列满足索引覆盖的情况才会使用到 release_year 索引
"SELECT * FROM film WHERE length > 100 ORDER BY length LIMIT 10;", // INDEX(length) This "range" is compatible with ORDER BY
"SELECT * FROM film WHERE length < 100 ORDER BY length LIMIT 10;", // INDEX(length) also works
"SELECT * FROM customer WHERE address_id in (224,510) ORDER BY last_name;", // INDEX(address_id)
"SELECT * FROM film WHERE release_year = 2016 AND length != 1 ORDER BY title;", // INDEX(`release_year`, `length`, `title`)
// "Covering" Rows
"SELECT title FROM film WHERE release_year = 1995;", // INDEX(release_year, title)",
"SELECT title, replacement_cost FROM film WHERE language_id = 5 AND length = 70;", // INDEX(language_id, length, title, replacement_cos film ), title, replacement_cost顺序无关，language_id, length顺序视散粒度情况.
"SELECT title FROM film WHERE language_id > 5 AND length > 70;", // INDEX(language_id, length, title) language_id or length first (that's as far as the Algorithm goes), then the other two fields afterwards.
// equalities and sort
"SELECT * FROM film WHERE length = 100 and title = 'xyz' ORDER BY release_year;", // 依赖数据特征，index(length, title, release_year) or index(title, length, release_year)需要评估
// inequality and sort
"SELECT * FROM film WHERE length > 100 and title = 'xyz' ORDER BY release_year;", // 依赖数据特征， index(title, release_year)，index(title, length)需要评估
"SELECT * FROM film WHERE length > 100 ORDER BY release_year;", // 依赖数据特征， index(length)，index(release_year)需要评估
// Join
// 内连接 INNER JOIN
// 在mysql中，inner join...on , join...on , 逗号...WHERE ，cross join...on是一样的含义。
// 但是在标准SQL中，它们并不等价，标准 SQL 中 INNER JOIN 与 ON共同使用, CROSS JOIN用于其他情况。
// 逗号不支持 on 和 using 语法, 逗号的优先级要低于INNER JOIN, CROSS JOIN, LEFT JOIN
// ON子句的语法格式为：tb1.col1 = tb2.col2列名可以不同，筛选连接后的结果，两表的对应列值相同才在结果集中。
// 当模式设计对联接表的列采用了相同的命名样式时，就可以使用 USING 语法来简化 ON 语法
// join, inner join, cross join等价，优先选择小结果集条件表为驱动表
// left [outer] join左表为驱动表
// right [outer] join右表为驱动表
// 驱动表连接列如果没其他条件可以不考虑加索引，反正是需要foreach
// 被驱动表连接列需要加索引。即:left [outer] join的右表连接列需要加索引，right [outer] join的左表连接列需要加索引，inner join结果集较大表的连接列需要加索引
// 其他索引添加算法与单表索引优化算法相同
// 总结：被驱动表列需要添加索引
// 建议：将无索引的表通常作为驱动表
"SELECT * FROM city a INNER JOIN country b ON a.country_id=b.country_id;",
// 左外连接 LEFT [OUTER] JOIN
"SELECT * FROM city a LEFT JOIN country b ON a.country_id=b.country_id;",
// 右外连接 RIGHT [OUTER] JOIN
"SELECT * FROM city a RIGHT JOIN country b ON a.country_id=b.country_id;",
// 左连接
"SELECT * FROM city a LEFT JOIN country b ON a.country_id=b.country_id WHERE b.last_update IS NULL;",
// 右连接
"SELECT * FROM city a RIGHT JOIN country b ON a.country_id=b.country_id WHERE a.last_update IS NULL;",
// 全连接 FULL JOIN 因为在mysql中并不支持，所以我们用union实现
"SELECT * FROM city a LEFT JOIN country b ON a.country_id=b.country_id " +
"UNION " +
"SELECT * FROM city a RIGHT JOIN country b ON a.country_id=b.country_id;",
// 两张表中不共同满足的数据集
"SELECT * FROM city a RIGHT JOIN country b ON a.country_id=b.country_id WHERE a.last_update IS NULL " +
"UNION " +
"SELECT * FROM city a LEFT JOIN country b ON a.country_id=b.country_id WHERE b.last_update IS NULL;",
// NATURAL JOIN 默认是同名字段完全匹配的INNER JOIN
"SELECT country_id, last_update FROM city NATURAL JOIN country;",
// NATURAL LEFT JOIN
"SELECT country_id, last_update FROM city NATURAL LEFT JOIN country;",
// NATURAL RIGHT JOIN
"SELECT country_id, last_update FROM city NATURAL RIGHT JOIN country;",
// STRAIGHT_JOIN 实际上与内连接 INNER JOIN 表现完全一致，
// 不同的是使用了 STRAIGHT_JOIN 后指定表载入的顺序，city 先于 country 载入
"SELECT a.country_id, a.last_update FROM city a STRAIGHT_JOIN country b ON a.country_id=b.country_id;",
// SEMI JOIN
// 半连接： 当一张表在另一张表找到匹配的记录之后，半连接（semi-join）返回第一张表中的记录。
// 与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。
// 另外，右节点的表一条记录也不会返回。半连接通常使用 IN 或 EXISTS 作为连接条件
"SELECT a.address, a.postal_code FROM sakila.address a WHERE a.city_id IN (SELECT c.city_id FROM sakila.city c);",
// Delayed Join
// https://www.percona.com/blog/2007/04/06/using-delayed-join-to-optimize-count-and-limit-queries/
`SELECT city FROM( SELECT city_id FROM city WHERE city = "A Corua (La Corua)" ORDER BY last_update DESC LIMIT 50, 10) I JOIN city ON (I.city_id = city.city_id) JOIN country ON (country.country_id = city.country_id) ORDER BY city DESC;`,
// DELETE
"DELETE city, country FROM city INNER JOIN country using (country_id) WHERE city.city_id = 1;",
"DELETE city FROM city LEFT JOIN country ON city.country_id = country.country_id WHERE country.country IS NULL;",
"DELETE a1, a2 FROM city AS a1 INNER JOIN country AS a2 WHERE a1.country_id=a2.country_id;",
"DELETE FROM a1, a2 USING city AS a1 INNER JOIN country AS a2 WHERE a1.country_id=a2.country_id;",
"DELETE FROM film WHERE length > 100;",
// UPDATE
"UPDATE city INNER JOIN country USING(country_id) SET city.city = 'Abha', city.last_update = '2006-02-15 04:45:25', country.country = 'Afghanistan' WHERE city.city_id=10;",
"UPDATE city INNER JOIN country ON city.country_id = country.country_id INNER JOIN address ON city.city_id = address.city_id SET city.city = 'Abha', city.last_update = '2006-02-15 04:45:25', country.country = 'Afghanistan' WHERE city.city_id=10;",
"UPDATE city, country SET city.city = 'Abha', city.last_update = '2006-02-15 04:45:25', country.country = 'Afghanistan' WHERE city.country_id = country.country_id AND city.city_id=10;",
"UPDATE film SET length = 10 WHERE language_id = 20;",
// INSERT
"INSERT INTO city (country_id) SELECT country_id FROM country;",
"INSERT INTO city (country_id) VALUES (1),(2),(3);",
"INSERT INTO city (country_id) VALUES (10);",
"INSERT INTO city (country_id) SELECT 10 FROM DUAL;",
// REPLACE
"REPLACE INTO city (country_id) SELECT country_id FROM country;",
"REPLACE INTO city (country_id) VALUES (1),(2),(3);",
"REPLACE INTO city (country_id) VALUES (10);",
"REPLACE INTO city (country_id) SELECT 10 FROM DUAL;",
// DEPTH
"SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM ( SELECT film_id FROM film ) film ) film ) film ) film ) film ) film ) film ) film ) film ) film ) film ) film ) film ) film ) film ) film;",
// SUBQUERY
"SELECT * FROM film WHERE language_id = (SELECT language_id FROM language LIMIT 1);",
// "SELECT COUNT(*) /* no hint */ FROM t2 WHERE NOT EXISTS (SELECT * FROM t3 WHERE ROW(5 * t2.s1, 77) = (SELECT 50, 11 * s1 FROM t4 UNION SELECT 50, 77 FROM (SELECT * FROM t5) AS t5 ) ) ;",
"SELECT * FROM city i left JOIN country o ON i.city_id=o.country_id union SELECT * FROM city i right JOIN country o ON i.city_id=o.country_id;",
"SELECT * FROM (SELECT * FROM actor WHERE last_update='2006-02-15 04:34:33' and last_name='CHASE') t WHERE last_update='2006-02-15 04:34:33' and last_name='CHASE' GROUP BY first_name;",
"SELECT * FROM city i left JOIN country o ON i.city_id=o.country_id union SELECT * FROM city i right JOIN country o ON i.city_id=o.country_id;",
"SELECT * FROM city i left JOIN country o ON i.city_id=o.country_id WHERE o.country_id is null union SELECT * FROM city i right JOIN country o ON i.city_id=o.country_id WHERE i.city_id is null;",
"SELECT first_name,last_name,email FROM customer STRAIGHT_JOIN address ON customer.address_id=address.address_id;",
"SELECT ID,name FROM (SELECT address FROM customer_list WHERE SID=1 order by phone limit 50,10) a JOIN customer_list l ON (a.address=l.address) JOIN city c ON (c.city=l.city) order by phone desc;",
// function in conditions
"SELECT * FROM film WHERE date(last_update)='2006-02-15';",
"SELECT last_update FROM film GROUP BY date(last_update);",
"SELECT last_update FROM film order by date(last_update);",
// CLA.004
"SELECT description FROM film WHERE description IN('NEWS','asd') GROUP BY description;",
// ALTER TABLE ADD INDEX
// 已经存在索引的列应该提醒索引已存在
"alter table address add index idx_city_id(city_id);",
"alter table inventory add index `idx_store_film` (`store_id`,`film_id`);",
"alter table inventory add index `idx_store_film` (`store_id`,`film_id`),add index `idx_store_film` (`store_id`,`film_id`),add index `idx_store_film` (`store_id`,`film_id`);",
// https://github.com/BruceDu521/soar/issues/47
`SELECT DATE_FORMAT(t.last_update, '%Y-%m-%d'), COUNT(DISTINCT (t.city)) FROM city t WHERE t.last_update > '2018-10-22 00:00:00' AND t.city LIKE '%Chrome%' AND t.city = 'eip' GROUP BY DATE_FORMAT(t.last_update, '%Y-%m-%d') ORDER BY DATE_FORMAT(t.last_update, '%Y-%m-%d');`,
// https://github.com/BruceDu521/soar/issues/17
"create table hello.t (id int unsigned);",
// https://github.com/BruceDu521/soar/issues/146
"select * from tb where data >= '';",
// https://github.com/BruceDu521/soar/issues/163
"alter table tb alter column id drop default;",
// explain extra info "Select tables optimized away"
"select maxId, minId from (select max(film_id) maxId, min(film_id) minId from film where last_update > '2016-03-27 02:01:01') as d;",
"select maxId, minId from (select max(film_id) maxId, min(film_id) minId from film) as d;",
}
}