4
die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n"
5
unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH};
6
unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib";
10
use warnings FATAL => 'all';
11
use English qw(-no_match_vars);
12
use Test::More tests => 87;
17
use QueryAdvisorRules;
21
# This test should just test that the QueryAdvisor module conforms to the
23
# - It has a get_rules() method that returns a list of hashrefs:
24
# ({ID => 'ID', code => $code}, {ID => ..... }, .... )
25
# - It has a load_rule_info() method that accepts a list of hashrefs, which
26
# we'll use to load rule info from POD. Our built-in rule module won't
27
# store its own rule info. But plugins supplied by users should.
28
# - It has a get_rule_info() method that accepts an ID and returns a hashref:
29
# {ID => 'ID', Severity => 'NOTE|WARN|CRIT', Description => '......'}
30
my $p = new PodParser();
31
my $qar = new QueryAdvisorRules(PodParser => $p);
33
my @rules = $qar->get_rules();
36
'Returns array of rules'
40
foreach my $rule ( @rules ) {
43
|| (ref $rule->{code} ne 'CODE') )
51
'All rules are proper'
54
# QueryAdvisorRules.pm has more rules than mqa-rule-LIT.001.pod so to avoid
55
# "There is no info" errors we remove all but LIT.001.
56
@rules = grep { $_->{id} eq 'LIT.001' } @rules;
58
# Test that we can load rule info from POD. Make a sample POD file that has a
59
# single sample rule definition for LIT.001 or something.
62
file => "$trunk/t/lib/samples/pod/mqa-rule-LIT.001.pod",
66
# We shouldn't be able to load the same rule info twice.
71
file => "$trunk/t/lib/samples/pod/mqa-rule-LIT.001.pod",
75
qr/Rule \S+ is already defined/,
76
'Duplicate rule info is caught'
79
# Test that we can now get a hashref as described above.
81
$qar->get_rule_info('LIT.001'),
84
description => "IP address used as string. The string literal looks like an IP address but is not used inside INET_ATON(). WHERE ip='127.0.0.1' is better as ip=INET_ATON('127.0.0.1') if the column is numeric.",
86
'get_rule_info(LIT.001) works',
89
# Test getting a nonexistent rule.
91
$qar->get_rule_info('BAR.002'),
93
"get_rule_info() nonexistent rule"
97
$qar->get_rule_info(),
99
"get_rule_info(undef)"
102
# Add a rule for which there is no POD info and test that it's not allowed.
105
code => sub { return },
107
$qar->_reset_rule_info(); # else we'll get "cannot redefine rule" error
110
$qar->load_rule_info(
112
file => "$trunk/t/lib/samples/pod/mqa-rule-LIT.001.pod",
116
qr/There is no info for rule FOO.001/,
117
"Doesn't allow rules without info",
120
# ###########################################################################
121
# Test cases for the rules themselves.
122
# ###########################################################################
124
{ name => 'IP address not inside INET_ATON, plus SELECT * is used',
125
query => 'SELECT * FROM tbl WHERE ip="127.0.0.1"',
126
advice => [qw(COL.001 LIT.001)],
129
{ name => 'Date literal not quoted',
130
query => 'SELECT col FROM tbl WHERE col < 2001-01-01',
131
advice => [qw(LIT.002)],
133
{ name => 'Aliases without AS keyword',
134
query => 'SELECT a b FROM tbl',
135
advice => [qw(ALI.001 CLA.001)],
137
{ name => 'tbl.* alias',
138
query => 'SELECT tbl.* foo FROM bar WHERE id=1',
139
advice => [qw(ALI.001 ALI.002 COL.001)],
141
{ name => 'tbl as tbl',
142
query => 'SELECT col FROM tbl AS tbl WHERE id=1',
143
advice => [qw(ALI.003)],
145
{ name => 'col as col',
146
query => 'SELECT col AS col FROM tbl AS `my tbl` WHERE id=1',
147
advice => [qw(ALI.003)],
149
{ name => 'Blind INSERT',
150
query => 'INSERT INTO tbl VALUES(1),(2)',
151
advice => [qw(COL.002)],
153
{ name => 'Blind INSERT',
154
query => 'INSERT tbl VALUE (1)',
155
advice => [qw(COL.002)],
157
{ name => 'SQL_CALC_FOUND_ROWS',
158
query => 'SELECT SQL_CALC_FOUND_ROWS col FROM tbl AS alias WHERE id=1',
159
advice => [qw(KWR.001)],
161
{ name => 'All comma joins ok',
162
query => 'SELECT col FROM tbl1, tbl2 WHERE tbl1.id=tbl2.id',
165
{ name => 'All ANSI joins ok',
166
query => 'SELECT col FROM tbl1 JOIN tbl2 USING(id) WHERE tbl1.id>10',
169
{ name => 'Mix comman/ANSI joins',
170
query => 'SELECT col FROM tbl, tbl1 JOIN tbl2 USING(id) WHERE tbl.d>10',
171
advice => [qw(JOI.001)],
173
{ name => 'Non-deterministic GROUP BY',
174
query => 'select a, b, c from tbl where foo="bar" group by a',
175
advice => [qw(RES.001)],
177
{ name => 'Non-deterministic LIMIT w/o ORDER BY',
178
query => 'select a, b from tbl where foo="bar" limit 10 group by a, b',
179
advice => [qw(RES.002)],
181
{ name => 'ORDER BY RAND()',
182
query => 'select a from t where id=1 order by rand()',
183
advice => [qw(CLA.002)],
185
{ name => 'ORDER BY RAND(N)',
186
query => 'select a from t where id=1 order by rand(123)',
187
advice => [qw(CLA.002)],
189
{ name => 'LIMIT w/ OFFSET does not scale',
190
query => 'select a from t where i=1 limit 10, 10 order by a',
191
advice => [qw(CLA.003)],
193
{ name => 'LIMIT w/ OFFSET does not scale',
194
query => 'select a from t where i=1 limit 10 OFFSET 10 order by a',
195
advice => [qw(CLA.003)],
197
{ name => 'Leading %wildcard',
198
query => 'select a from t where i like "%hm"',
199
advice => [qw(ARG.001)],
201
{ name => 'Leading _wildcard',
202
query => 'select a from t where i LIKE "_hm"',
203
advice => [qw(ARG.001)],
205
{ name => 'Leading "% wildcard"',
206
query => 'select a from t where i like "% eh "',
207
advice => [qw(ARG.001)],
209
{ name => 'Leading "_ wildcard"',
210
query => 'select a from t where i LIKE "_ eh "',
211
advice => [qw(ARG.001)],
213
{ name => 'GROUP BY number',
214
query => 'select a from t where i <> 4 group by 1',
215
advice => [qw(CLA.004)],
217
{ name => '!= instead of <>',
218
query => 'select a from t where i != 2',
219
advice => [qw(STA.001)],
221
{ name => "LIT.002 doesn't match",
222
query => "update foo.bar set biz = '91848182522'",
225
{ name => "LIT.002 doesn't match",
226
query => "update db2.tuningdetail_21_265507 inner join db1.gonzo using(g) set n.c1 = a.c1, n.w3 = a.w3",
229
{ name => "LIT.002 doesn't match",
230
query => "UPDATE db4.vab3concept1upload
231
SET vab3concept1id = '91848182522'
232
WHERE vab3concept1upload='6994465'",
235
{ name => "LIT.002 at end of query",
236
query => "select c from t where d=2006-10-10",
237
advice => [qw(LIT.002)],
239
{ name => "LIT.002 5 digits doesn't match",
240
query => "select c from t where d=12345",
243
{ name => "LIT.002 7 digits doesn't match",
244
query => "select c from t where d=1234567",
247
{ name => "SELECT var LIMIT",
248
query => "select \@\@version_comment limit 1 ",
251
{ name => "Date with time",
252
query => "select c from t where d > 2010-03-15 09:09:09",
253
advice => [qw(LIT.002)],
255
{ name => "Date with time and subseconds",
256
query => "select c from t where d > 2010-03-15 09:09:09.123456",
257
advice => [qw(LIT.002)],
259
{ name => "Date with time doesn't match",
260
query => "select c from t where d > '2010-03-15 09:09:09'",
263
{ name => "Date with time and subseconds doesn't match",
264
query => "select c from t where d > '2010-03-15 09:09:09.123456'",
267
{ name => "Short date",
268
query => "select c from t where d=73-03-15",
269
advice => [qw(LIT.002)],
271
{ name => "Short date with time",
272
query => "select c from t where d > 73-03-15 09:09:09",
273
advice => [qw(LIT.002)],
276
{ name => "Short date with time and subseconds",
277
query => "select c from t where d > 73-03-15 09:09:09.123456",
278
advice => [qw(LIT.002)],
280
{ name => "Short date with time doesn't match",
281
query => "select c from t where d > '73-03-15 09:09:09'",
284
{ name => "Short date with time and subseconds doesn't match",
285
query => "select c from t where d > '73-03-15 09:09:09.123456'",
288
{ name => "LIKE without wildcard",
289
query => "select c from t where i like 'lamp'",
290
advice => [qw(ARG.002)],
292
{ name => "LIKE without wildcard, 2nd arg",
293
query => "select c from t where i like 'lamp%' or i like 'foo'",
294
advice => [qw(ARG.002)],
296
{ name => "LIKE with wildcard %",
297
query => "select c from t where i like 'lamp%'",
300
{ name => "LIKE with wildcard _",
301
query => "select c from t where i like 'lamp_'",
304
{ name => "Issue 946: LIT.002 false-positive",
305
query => "delete from t where d in('MD6500-26', 'MD6500-21-22', 'MD6214')",
308
{ name => "Issue 946: LIT.002 false-positive",
309
query => "delete from t where d in('FS-8320-0-2', 'FS-800-6')",
312
# This matches LIT.002 but unless the regex gets really complex or
313
# we do this rule another way, this will have to remain an exception.
314
# { name => "Issue 946: LIT.002 false-positive",
315
# query => "select c from t where c='foo 2010-03-17 bar'",
319
{ name => "IN(subquer)",
320
query => "select c from t where i in(select d from z where 1)",
321
advice => [qw(SUB.001)],
325
query => "select c from `w_chapter` INNER JOIN `w_series` AS `w_chapter__series` ON `w_chapter`.`series_id` = `w_chapter__series`.`id`, `w_series`, `auth_user` where id=1",
326
advice => [qw(JOI.001 JOI.002)],
328
{ name => "JOI.002 ansi self-join ok",
329
query => "select c from employees as e join employees as s on e.supervisor = s.id where foo='bar'",
332
{ name => "JOI.002 ansi self-join with other joins ok",
333
query => "select c from employees as e join employees as s on e.supervisor = s.id join employees as r on s.id = r.foo where foo='bar'",
336
{ name => "JOI.002 comma self-join ok",
337
query => "select c from employees as e, employees as s where e.supervisor = s.id",
340
{ name => "CLA.005 ORDER BY col=<constant>",
341
query => "select col1, col2 from tbl where col3=5 order by col3, col4",
342
advice => [qw(CLA.005)],
344
# Now col3 is not a constant, it's the string '5'.
345
{ name => "CLA.005 not tricked by '5'",
346
query => "select col1, col2 from tbl where col3='5' order by col3, col4",
350
query => "select c from L left join R using(c) where L.a=5 and R.b=10",
351
advice => [qw(JOI.003)],
353
{ name => "JOI.003 ok with IS NULL",
354
query => "select c from L left join R using(c) where L.a=5 and R.c is null",
357
{ name => "JOI.003 ok without outer table column",
358
query => "select c from L left join R using(c) where L.a=5",
361
{ name => "JOI.003 RIGHT",
362
query => "select c from L right join R using(c) where R.a=5 and L.b=10",
363
advice => [qw(JOI.003)],
365
{ name => "JOI.003 RIGHT ok with IS NULL",
366
query => "select c from L right join R using(c) where R.a=5 and L.c is null",
369
{ name => "JOI.003 RIGHT ok without outer table column",
370
query => "select c from L right join R using(c) where R.a=5",
373
{ name => "JOI.003 ok with INNER JOIN",
374
query => "select c from L inner join R using(c) where R.a=5 and L.b=10",
377
{ name => "JOI.003 ok with JOIN",
378
query => "select c from L join R using(c) where R.a=5 and L.b=10",
382
query => "select c from L left join R on a=b where L.a=5 and R.c is null",
385
L => { name => 'L', is_col => { a => 1 } },
386
R => { name => 'R', is_col => { b => 1, c => 1 } },
389
advice => [qw(JOI.004)],
391
{ name => "JOI.004 USING (b)",
392
query => "select c from L left join R using(b) where L.a=5 and R.c is null",
393
advice => [qw(JOI.004)],
395
{ name => "JOI.004 without table info",
396
query => "select c from L left join R on a=b where L.a=5 and R.c is null",
397
advice => [qw(JOI.004)],
399
{ name => "JOI.004 good exclusion join",
400
query => "select c from L left join R on a=b where L.a=5 and R.b is null",
403
L => { name => 'L', is_col => { a => 1 } },
404
R => { name => 'R', is_col => { b => 1, c => 1 } },
409
{ name => "JOI.004 RIGHT",
410
query => "select c from L right join R on a=b where R.a=5 and L.c is null",
413
L => { name => 'L', is_col => { a => 1, c => 1 } },
414
R => { name => 'R', is_col => { b => 1, } },
417
advice => [qw(JOI.004)],
419
{ name => "JOI.004 can table-qualify cols from WHERE",
420
query => "select c from L left join R on a=b where a=5 and c is null",
423
L => { name => 'L', is_col => { a => 1 } },
424
R => { name => 'R', is_col => { b => 1, c => 1 } },
427
advice => [qw(JOI.004)],
429
{ name => "CLA.006 GROUP BY different tables",
430
query => "select id from tbl1 join tbl2 using(a) where 1 group by tbl1.id, tbl2.id",
431
advice => [qw(CLA.006)],
433
{ name => "CLA.006 ORDER BY different tables",
434
query => "select id from tbl1 join tbl2 using(a) where 1 order by tbl1.id, tbl2.id",
435
advice => [qw(CLA.006)],
437
{ name => "CLA.006 GROUP BY tbl_a ORDER BY tbl_b",
438
query => "select id from tbl1 join tbl2 using(a) where 1 group by tbl1.id order by tbl2.id",
439
advice => [qw(CLA.006)],
441
{ name => "CLA.006 GROUP BY tbl_a ORDER BY tbl_b (2)",
442
query => "select id, foo from tbl1 join tbl2 using(a) where 1 group by tbl1.id order by tbl2.id, tbl1.foo",
443
advice => [qw(CLA.006 RES.001)],
445
{ name => "CLA.006 GROUP BY tbl_a ORDER BY tbl_b (3)",
446
query => "select id,foo from tbl1 join tbl2 using(a) where 1 group by tbl1.id, tbl2.foo order by tbl2.id",
447
advice => [qw(CLA.006)],
449
# CLA.006 cannot be detected without table qualifications for every column
450
{ name => "CLA.006 without full table qualifications",
451
query => "select id from tbl1 join tbl2 using(a) where 1 group by id order by tbl1.id",
455
name => 'Issue 1163, ARG.001 false-positive',
456
query => "SELECT COUNT(*) FROM foo WHERE meta_key = '_edit_lock' AND post_id = 488",
460
name => 'Issue 1163, RES.001 false-positive',
461
query => "SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM foo_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC",
465
name => 'CLA.007 ORDER BY ASC and DESC',
466
query => "select col1, col2 from tbl where i=1 order by col1, col2 desc",
467
advice => [qw(CLA.007)],
471
# Run the test cases.
472
$qar = new QueryAdvisorRules(PodParser => $p);
473
$qar->load_rule_info(
474
rules => [ $qar->get_rules() ],
475
file => "$trunk/bin/pt-query-advisor",
479
my $adv = new Advisor(match_type => "pos");
480
$adv->load_rules($qar);
481
$adv->load_rule_info($qar);
483
my $sp = new SQLParser();
485
foreach my $test ( @cases ) {
486
my $query_struct = $sp->parse($test->{query});
488
arg => $test->{query},
489
query_struct => $query_struct,
490
tbl_structs => $test->{tbl_structs},
492
my ($ids, $pos) = $adv->run_rules(
501
if ( $test->{pos} ) {
505
"$test->{name} matched near pos"
510
die if $test->{stop};
513
# #############################################################################
515
# #############################################################################
519
open STDERR, '>', \$output;
520
$p->_d('Complete test coverage');
524
qr/Complete test coverage/,
added
removed
t/lib/QueryAdvisorRules.t
