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';
12
use Test::More tests => 137;
13
use English qw(-no_match_vars);
19
$Data::Dumper::Indent = 1;
20
$Data::Dumper::Sortkeys = 1;
21
$Data::Dumper::Quotekeys = 0;
23
my $sp = new SQLParser();
25
# ############################################################################
26
# Should throw some errors for stuff it can't do.
27
# ############################################################################
29
sub { $sp->parse('drop table foo'); },
30
qr/Cannot parse DROP queries/,
31
"Dies if statement type cannot be parsed"
34
# ############################################################################
36
# ############################################################################
38
my ( $in, $expect, %args ) = @_;
39
my $got = $sp->_parse_csv($in, %args);
44
) or print Dumper($got);
66
q{'hello, world!','hi'},
67
[q{'hello, world!'}, q{'hi'}],
73
[q{'a'}, q{"b"}, q{c}],
78
q{"x, y", "", a, 'b'},
79
[q{"x, y"}, q{""}, q{a}, q{'b'}],
83
# ############################################################################
85
# ############################################################################
86
sub test_is_identifier {
87
my ( $thing, $expect ) = @_;
89
$sp->is_identifier($thing),
91
"$thing is" . ($expect ? "" : " not") . " an ident"
96
test_is_identifier("tbl", 1);
97
test_is_identifier("`tbl`", 1);
98
test_is_identifier("'tbl'", 0);
99
test_is_identifier("\"tbl\"", 0);
100
test_is_identifier('db.tbl', 1);
101
test_is_identifier('"db.tbl"', 0);
102
test_is_identifier('db.tbl.col', 1);
103
test_is_identifier('1', 0);
105
# #############################################################################
106
# WHERE where_condition
107
# #############################################################################
109
my ( $where, $struct ) = @_;
111
$sp->parse_where($where),
113
"WHERE " . substr($where, 0, 60)
114
. (length $where > 60 ? '...' : ''),
131
'i=1 or j<10 or k>100 or l != 0',
173
right_arg => '"bar"',
179
'(i=1 and foo="bar")',
191
right_arg => '"bar"',
197
'(i=1) and (foo="bar")',
209
right_arg => '"bar"',
215
'i= 1 and foo ="bar" or j = 2',
227
right_arg => '"bar"',
239
'i=1 and foo="i have spaces and a keyword!"',
251
right_arg => '"i have spaces and a keyword!"',
257
'i="this and this" or j<>"that and that" and k="and or and" and z=1',
263
right_arg => '"this and this"',
269
right_arg => '"that and that"',
275
right_arg => '"and or and"',
287
'i="this and this" or j in ("and", "or") and x is not null or a between 1 and 10 and sz="the keyword \'and\' is in the middle or elsewhere hidden"',
293
right_arg => '"this and this"',
299
right_arg => '("and", "or")',
304
operator => 'is not',
310
operator => 'between',
311
right_arg => '1 and 10',
317
right_arg => '"the keyword \'and\' is in the middle or elsewhere hidden"',
323
"(`ga_announcement`.`disabled` = 0)",
327
left_arg => '`ga_announcement`.`disabled`',
347
"1 and foo not like '%bar%'",
358
operator => 'not like',
359
right_arg => '\'%bar%\'',
377
right_arg => 'false',
383
"TO_DAYS(column) < TO_DAYS(NOW()) - 5",
387
left_arg => "TO_DAYS(column)",
389
right_arg => 'TO_DAYS(NOW()) - 5',
395
"id <> CONV(ff, 16, 10)",
401
right_arg => 'CONV(ff, 16, 10)',
407
"edpik.input_key = input_key.id",
411
left_arg => 'edpik.input_key',
413
right_arg => 'input_key.id'
419
"((`sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`) and (`sakila`.`country`.`country` = 'Brazil') and (`sakila`.`city`.`city` like 'A%'))",
423
left_arg => '`sakila`.`city`.`country_id`',
425
right_arg => '`sakila`.`country`.`country_id`'
429
left_arg => '`sakila`.`country`.`country`',
431
right_arg => '\'Brazil\''
435
left_arg => '`sakila`.`city`.`city`',
437
right_arg => '\'A%\''
442
# #############################################################################
443
# Whitespace and comments.
444
# #############################################################################
446
$sp->clean_query(' /* leading comment */select *
447
from tbl where /* comment */ id=1 /*trailing comment*/ '
449
'select * from tbl where id=1',
450
'Remove extra whitespace and comment blocks'
457
*/ select * from tbl where /* another
458
silly comment */ id=1
460
also on mutiple lines*/ '
462
'select * from tbl where id=1',
463
'Remove multi-line comment blocks'
467
$sp->clean_query('-- SQL style
476
'Remove multiple -- comment lines and blank lines'
480
# #############################################################################
481
# Normalize space around certain SQL keywords. (This makes parsing easier.)
482
# #############################################################################
484
$sp->normalize_keyword_spaces('insert into t value(1)'),
485
'insert into t value (1)',
486
'Add space VALUE (cols)'
490
$sp->normalize_keyword_spaces('insert into t values(1)'),
491
'insert into t values (1)',
492
'Add space VALUES (cols)'
496
$sp->normalize_keyword_spaces('select * from a join b on(foo)'),
497
'select * from a join b on (foo)',
498
'Add space ON (conditions)'
502
$sp->normalize_keyword_spaces('select * from a join b on(foo) join c on(bar)'),
503
'select * from a join b on (foo) join c on (bar)',
504
'Add space multiple ON (conditions)'
508
$sp->normalize_keyword_spaces('select * from a join b using(foo)'),
509
'select * from a join b using (foo)',
510
'Add space using (conditions)'
514
$sp->normalize_keyword_spaces('select * from a join b using(foo) join c using(bar)'),
515
'select * from a join b using (foo) join c using (bar)',
516
'Add space multiple USING (conditions)'
520
$sp->normalize_keyword_spaces('select * from a join b using(foo) join c on(bar)'),
521
'select * from a join b using (foo) join c on (bar)',
522
'Add space USING and ON'
525
# ###########################################################################
527
# ###########################################################################
529
$sp->parse_group_by('col, tbl.bar, 4, col2 ASC, MIN(bar)'),
531
{ column => 'col', },
532
{ table => 'tbl', column => 'bar', },
533
{ position => '4', },
534
{ column => 'col2', sort => 'ASC', },
535
{ function => 'MIN', expression => 'bar' },
537
"GROUP BY col, tbl.bar, 4, col2 ASC, MIN(bar)"
540
# ###########################################################################
542
# ###########################################################################
544
$sp->parse_order_by('foo'),
549
$sp->parse_order_by('foo'),
554
$sp->parse_order_by('foo, bar'),
562
$sp->parse_order_by('foo asc, bar'),
564
{column => 'foo', sort => 'ASC'},
567
'order by foo asc, bar'
570
$sp->parse_order_by('1'),
575
$sp->parse_order_by('RAND()'),
576
[{function => 'RAND'}],
580
# ###########################################################################
582
# ###########################################################################
584
$sp->parse_limit('1'),
589
$sp->parse_limit('1, 2'),
596
$sp->parse_limit('5 OFFSET 10'),
599
explicit_offset => 1,
605
# ###########################################################################
606
# FROM table_references
607
# ###########################################################################
610
my ( $from, $struct ) = @_;
611
my $got = $sp->parse_from($from);
616
) or print Dumper($got);
621
[ { tbl => 'tbl', } ],
626
[ { tbl => 'tbl', alias => 'ta', } ],
673
't1 JOIN t2 ON t1.id=t2.id',
689
right_arg => 't2.id',
699
't1 a JOIN t2 as b USING (id)',
712
condition => 'using',
721
't1 JOIN t2 ON t1.id=t2.id JOIN t3 ON t1.id=t3.id',
737
right_arg => 't2.id',
754
right_arg => 't3.id',
764
't1 AS a LEFT JOIN t2 b ON a.id = b.id',
793
't1 a NATURAL RIGHT OUTER JOIN t2 b',
804
type => 'natural right outer',
811
# http://pento.net/2009/04/03/join-and-comma-precedence/
813
'a, b LEFT JOIN c ON c.c = a.a',
847
'a, b, c CROSS JOIN d USING (id)',
873
condition => 'using',
883
'tbl FORCE INDEX (foo)',
887
index_hint => 'FORCE INDEX (foo)',
893
'tbl USE INDEX(foo)',
897
index_hint => 'USE INDEX(foo)',
903
'tbl FORCE KEY(foo)',
907
index_hint => 'FORCE KEY(foo)',
913
'tbl t FORCE KEY(foo)',
918
index_hint => 'FORCE KEY(foo)',
924
'tbl AS t FORCE KEY(foo)',
930
index_hint => 'FORCE KEY(foo)',
935
# Database-qualified tables.
953
'`ryan likes`.`to break stuff`',
956
tbl => 'to break stuff',
961
'`db`.`tbl` LEFT JOIN `foo`.`bar` USING (glue)',
972
condition => 'using',
980
'tblB AS dates LEFT JOIN dbF.tblC AS scraped ON dates.dt = scraped.dt AND dates.version = scraped.version',
1000
left_arg => 'dates.dt',
1002
right_arg => 'scraped.dt',
1006
left_arg => 'dates.version',
1008
right_arg => 'scraped.version',
1016
# The parser needs to match the join condition verb ON or USING
1017
# but these table names have those words embedded in the full
1021
[ { db=>'db', tbl=>'version', } ],
1025
"db.like_using_odd_table_names",
1026
[ { db=>'db', tbl=>'like_using_odd_table_names', } ],
1030
"db.`on`", # don't name your table this :-(
1031
[ { db=>'db', tbl=>'on', } ],
1035
"db.`using`", # or this
1036
[ { db=>'db', tbl=>'using', } ],
1039
# #############################################################################
1040
# parse_table_reference()
1041
# #############################################################################
1042
sub test_parse_table_reference {
1043
my ( $tbl, $struct ) = @_;
1044
my $s = $sp->parse_table_reference($tbl);
1053
test_parse_table_reference('tbl',
1057
test_parse_table_reference('tbl a',
1058
{ tbl => 'tbl', alias => 'a', }
1061
test_parse_table_reference('tbl as a',
1062
{ tbl => 'tbl', alias => 'a', explicit_alias => 1, }
1065
test_parse_table_reference('tbl AS a',
1066
{ tbl => 'tbl', alias => 'a', explicit_alias => 1, }
1069
test_parse_table_reference('db.tbl',
1070
{ tbl => 'tbl', db => 'db', }
1073
test_parse_table_reference('db.tbl a',
1074
{ tbl => 'tbl', db => 'db', alias => 'a', }
1077
test_parse_table_reference('db.tbl AS a',
1078
{ tbl => 'tbl', db => 'db', alias => 'a', explicit_alias => 1, }
1082
test_parse_table_reference('`tbl`',
1086
test_parse_table_reference('`tbl` `a`',
1087
{ tbl => 'tbl', alias => 'a', }
1090
test_parse_table_reference('`tbl` as `a`',
1091
{ tbl => 'tbl', alias => 'a', explicit_alias => 1, }
1094
test_parse_table_reference('`tbl` AS `a`',
1095
{ tbl => 'tbl', alias => 'a', explicit_alias => 1, }
1098
test_parse_table_reference('`db`.`tbl`',
1099
{ tbl => 'tbl', db => 'db', }
1102
test_parse_table_reference('`db`.`tbl` `a`',
1103
{ tbl => 'tbl', db => 'db', alias => 'a', }
1106
test_parse_table_reference('`db`.`tbl` AS `a`',
1107
{ tbl => 'tbl', db => 'db', alias => 'a', explicit_alias => 1, }
1110
# #############################################################################
1112
# #############################################################################
1113
sub test_parse_columns {
1114
my ( $cols, $struct ) = @_;
1115
my $s = $sp->parse_columns($cols);
1124
test_parse_columns('tbl.* foo',
1125
[ { col => '*', tbl => 'tbl', alias => 'foo' } ],
1128
# #############################################################################
1130
# #############################################################################
1131
sub test_parse_set {
1132
my ( $set, $struct ) = @_;
1133
my $got = $sp->parse_set($set);
1138
) or print Dumper($got);
1143
[{col=>"col", value=>"'val'"}],
1147
'a.foo="bar", b.foo=NOW()',
1149
{tbl=>"a", col=>"foo", value=>'"bar"'},
1150
{tbl=>"b", col=>"foo", value=>'NOW()'},
1154
# #############################################################################
1156
# #############################################################################
1158
my $query = "DELETE FROM t1
1160
(SELECT COUNT(*) /* no hint */ FROM t2 WHERE NOT EXISTS
1161
(SELECT * FROM t3 WHERE ROW(5*t2.s1,77)=
1162
(SELECT 50,11*s1 FROM
1163
(SELECT * FROM t5) AS t5
1167
my @subqueries = $sp->remove_subqueries($sp->clean_query($query));
1171
'DELETE FROM t1 WHERE s11 > ANY (__SQ3__)',
1173
query => 'SELECT * FROM t5',
1174
context => 'identifier',
1178
query => 'SELECT 50,11*s1 FROM __SQ0__ AS t5',
1179
context => 'scalar',
1183
query => 'SELECT * FROM t3 WHERE ROW(5*t2.s1,77)= __SQ1__',
1188
query => 'SELECT COUNT(*) FROM t2 WHERE NOT EXISTS (__SQ2__)',
1192
'DELETE with nested subqueries'
1195
$query = "select col from tbl
1196
where id=(select max(id) from tbl2 where foo='bar') limit 1";
1197
@subqueries = $sp->remove_subqueries($sp->clean_query($query));
1201
'select col from tbl where id=__SQ0__ limit 1',
1203
query => "select max(id) from tbl2 where foo='bar'",
1204
context => 'scalar',
1207
'Subquery as scalar'
1210
$query = "select col from tbl
1211
where id=(select max(id) from tbl2 where foo='bar') and col in(select foo from tbl3) limit 1";
1212
@subqueries = $sp->remove_subqueries($sp->clean_query($query));
1216
'select col from tbl where id=__SQ1__ and col in(__SQ0__) limit 1',
1218
query => "select foo from tbl3",
1222
query => "select max(id) from tbl2 where foo='bar'",
1223
context => 'scalar',
1226
'Subquery as scalar and IN()'
1229
$query = "SELECT NOW() AS a1, (SELECT f1(5)) AS a2";
1230
@subqueries = $sp->remove_subqueries($sp->clean_query($query));
1234
'SELECT NOW() AS a1, __SQ0__ AS a2',
1236
query => "SELECT f1(5)",
1237
context => 'identifier',
1240
'Subquery as SELECT column'
1243
$query = "SELECT DISTINCT store_type FROM stores s1
1245
SELECT * FROM cities WHERE NOT EXISTS (
1246
SELECT * FROM cities_stores
1247
WHERE cities_stores.city = cities.city
1248
AND cities_stores.store_type = stores.store_type))";
1249
@subqueries = $sp->remove_subqueries(
1250
$sp->clean_query($sp->normalize_keyword_spaces($query)));
1254
'SELECT DISTINCT store_type FROM stores s1 WHERE NOT EXISTS (__SQ1__)',
1256
query => "SELECT * FROM cities_stores WHERE cities_stores.city = cities.city AND cities_stores.store_type = stores.store_type",
1261
query => "SELECT * FROM cities WHERE NOT EXISTS (__SQ0__)",
1265
'Two nested NOT EXISTS subqueries'
1268
$query = "select col from tbl
1269
where id=(select max(id) from tbl2 where foo='bar')
1270
and col in(select foo from
1271
(select b from fn where id=1
1272
and b > any(select a from a)
1275
@subqueries = $sp->remove_subqueries($sp->clean_query($query));
1279
'select col from tbl where id=__SQ3__ and col in(__SQ2__) limit 1',
1281
query => 'select a from a',
1286
query => 'select b from fn where id=1 and b > any(__SQ0__)',
1287
context => 'identifier',
1291
query => 'select foo from __SQ1__',
1295
query => 'select max(id) from tbl2 where foo=\'bar\'',
1296
context => 'scalar',
1299
'Mutiple and nested subqueries'
1302
$query = "select (select now()) from universe";
1303
@subqueries = $sp->remove_subqueries($sp->clean_query($query));
1307
'select __SQ0__ from universe',
1309
query => 'select now()',
1310
context => 'identifier',
1313
'Subquery as non-aliased column identifier'
1316
# #############################################################################
1317
# Test parsing full queries.
1318
# #############################################################################
1322
# ########################################################################
1324
# ########################################################################
1325
{ name => 'DELETE FROM',
1326
query => 'DELETE FROM tbl',
1329
clauses => { from => 'tbl', },
1330
from => [ { tbl => 'tbl', } ],
1334
{ name => 'DELETE FROM WHERE',
1335
query => 'DELETE FROM tbl WHERE id=1',
1342
from => [ { tbl => 'tbl', } ],
1354
{ name => 'DELETE FROM LIMIT',
1355
query => 'DELETE FROM tbl LIMIT 5',
1362
from => [ { tbl => 'tbl', } ],
1369
{ name => 'DELETE FROM ORDER BY',
1370
query => 'DELETE FROM tbl ORDER BY foo',
1377
from => [ { tbl => 'tbl', } ],
1378
order_by => [{column=>'foo'}],
1382
{ name => 'DELETE FROM WHERE LIMIT',
1383
query => 'DELETE FROM tbl WHERE id=1 LIMIT 3',
1391
from => [ { tbl => 'tbl', } ],
1406
{ name => 'DELETE FROM WHERE ORDER BY',
1407
query => 'DELETE FROM tbl WHERE id=1 ORDER BY id',
1415
from => [ { tbl => 'tbl', } ],
1424
order_by => [{column=>'id'}],
1428
{ name => 'DELETE FROM WHERE ORDER BY LIMIT',
1429
query => 'DELETE FROM tbl WHERE id=1 ORDER BY id ASC LIMIT 1 OFFSET 3',
1435
order_by => 'id ASC ',
1436
limit => '1 OFFSET 3',
1438
from => [ { tbl => 'tbl', } ],
1447
order_by=> [{column=>'id', sort=>'ASC'}],
1451
explicit_offset => 1,
1457
# ########################################################################
1459
# ########################################################################
1460
{ name => 'INSERT INTO VALUES',
1461
query => 'INSERT INTO tbl VALUES (1,"foo")',
1466
values => '(1,"foo")',
1468
into => [ { tbl => 'tbl', } ],
1469
values => [ '1', q{"foo"}, ],
1473
{ name => 'INSERT INTO VALUES with complex CSV values',
1474
query => 'INSERT INTO tbl VALUES ("hello, world!", "", a, \'b\')',
1479
values => '("hello, world!", "", a, \'b\')',
1481
into => [ { tbl => 'tbl', } ],
1491
{ name => 'INSERT VALUE',
1492
query => 'INSERT tbl VALUE (1,"foo")',
1497
values => '(1,"foo")',
1499
into => [ { tbl => 'tbl', } ],
1500
values => [ '1', q{"foo"}, ],
1504
{ name => 'INSERT INTO cols VALUES',
1505
query => 'INSERT INTO db.tbl (id, name) VALUE (2,"bob")',
1510
columns => 'id, name ',
1511
values => '(2,"bob")',
1513
into => [ { tbl => 'tbl', db => 'db' } ],
1514
columns => [ { col => 'id' }, { col => 'name' } ],
1515
values => [ '2', q{"bob"} ],
1519
{ name => 'INSERT INTO VALUES ON DUPLICATE',
1520
query => 'INSERT INTO tbl VALUE (3,"bob") ON DUPLICATE KEY UPDATE col1=9',
1525
values => '(3,"bob")',
1526
on_duplicate => 'col1=9',
1528
into => [ { tbl => 'tbl', } ],
1529
values => [ '3', q{"bob"} ],
1530
on_duplicate => ['col1=9',],
1534
{ name => 'INSERT INTO SET',
1535
query => 'INSERT INTO tbl SET id=1, foo=NULL',
1540
set => 'id=1, foo=NULL',
1542
into => [ { tbl => 'tbl', } ],
1544
{ col => 'id', value => '1', },
1545
{ col => 'foo', value => 'NULL', },
1550
{ name => 'INSERT INTO SET ON DUPLICATE',
1551
query => 'INSERT INTO tbl SET i=3 ON DUPLICATE KEY UPDATE col1=9',
1557
on_duplicate => 'col1=9',
1559
into => [ { tbl => 'tbl', } ],
1560
set => [{col =>'i', value=>'3'}],
1561
on_duplicate => ['col1=9',],
1565
{ name => 'INSERT ... SELECT',
1566
query => 'INSERT INTO tbl (col) SELECT id FROM tbl2 WHERE id > 100',
1572
select => 'id FROM tbl2 WHERE id > 100',
1574
into => [ { tbl => 'tbl', } ],
1575
columns => [ { col => 'col' } ],
1581
where => 'id > 100',
1583
columns => [ { col => 'id' } ],
1584
from => [ { tbl => 'tbl2', } ],
1598
{ name => 'INSERT INTO VALUES()',
1599
query => 'INSERT INTO db.tbl (id, name) VALUES(2,"bob")',
1604
columns => 'id, name ',
1605
values => '(2,"bob")',
1607
into => [ { tbl => 'tbl', db => 'db' } ],
1608
columns => [ { col => 'id' }, { col => 'name' } ],
1609
values => [ '2', q{"bob"} ],
1614
# ########################################################################
1616
# ########################################################################
1617
# REPLACE are parsed by parse_insert() so if INSERT is well-tested we
1618
# shouldn't need to test REPLACE much.
1619
{ name => 'REPLACE INTO VALUES',
1620
query => 'REPLACE INTO tbl VALUES (1,"foo")',
1625
values => '(1,"foo")',
1627
into => [ { tbl => 'tbl', } ],
1628
values => [ '1', q{"foo"} ],
1632
{ name => 'REPLACE VALUE',
1633
query => 'REPLACE tbl VALUE (1,"foo")',
1638
values => '(1,"foo")',
1640
into => [ { tbl => 'tbl', } ],
1641
values => [ '1', q{"foo"} ],
1645
{ name => 'REPLACE INTO cols VALUES',
1646
query => 'REPLACE INTO db.tbl (id, name) VALUE (2,"bob")',
1651
columns => 'id, name ',
1652
values => '(2,"bob")',
1654
into => [ { tbl => 'tbl', db => 'db' } ],
1655
columns => [ { col => 'id' }, { col => 'name' } ],
1656
values => [ '2', q{"bob"} ],
1661
name => 'REPLACE SELECT JOIN ON',
1662
query => 'REPLACE INTO db.tblA (dt, ncpc) SELECT dates.dt, scraped.total_r FROM tblB AS dates LEFT JOIN dbF.tblC AS scraped ON dates.dt = scraped.dt AND dates.version = scraped.version',
1666
columns => 'dt, ncpc ',
1668
select => 'dates.dt, scraped.total_r FROM tblB AS dates LEFT JOIN dbF.tblC AS scraped ON dates.dt = scraped.dt AND dates.version = scraped.version',
1670
columns => [ { col => 'dt' }, { col => 'ncpc' } ],
1671
into => [ { db => 'db', tbl => 'tblA' } ],
1675
columns => 'dates.dt, scraped.total_r ',
1676
from => 'tblB AS dates LEFT JOIN dbF.tblC AS scraped ON dates.dt = scraped.dt AND dates.version = scraped.version',
1679
{ tbl => 'dates', col => 'dt' },
1680
{ tbl => 'scraped', col => 'total_r' },
1686
explicit_alias => 1,
1691
explicit_alias => 1,
1701
left_arg => 'dates.dt',
1703
right_arg => 'scraped.dt',
1707
left_arg => 'dates.version',
1709
right_arg => 'scraped.version',
1721
# ########################################################################
1723
# ########################################################################
1725
query => 'SELECT NOW()',
1731
columns => [ { col => 'NOW()' } ],
1735
{ name => 'SELECT var',
1736
query => 'select @@version_comment',
1740
columns => '@@version_comment',
1742
columns => [ { col => '@@version_comment' } ],
1746
{ name => 'SELECT FROM',
1747
query => 'SELECT col1, col2 FROM tbl',
1751
columns => 'col1, col2 ',
1754
columns => [ { col => 'col1' }, { col => 'col2' } ],
1755
from => [ { tbl => 'tbl', } ],
1759
{ name => 'SELECT FROM JOIN WHERE GROUP BY ORDER BY LIMIT',
1760
query => '/* nonsensical but covers all the basic clauses */
1761
SELECT t1.col1 a, t1.col2 as b
1763
LEFT JOIN tbl2 AS t2 ON t1.id = t2.id
1768
ORDER BY t2.name ASC
1774
columns => 't1.col1 a, t1.col2 as b ',
1775
from => 'tbl1 t1 LEFT JOIN tbl2 AS t2 ON t1.id = t2.id ',
1776
where => 't2.col IS NOT NULL AND t2.name = "bob" ',
1777
group_by => 'a, b ',
1778
order_by => 't2.name ASC ',
1781
columns => [ { col => 'col1', tbl => 't1', alias => 'a' },
1782
{ col => 'col2', tbl => 't1', alias => 'b',
1783
explicit_alias => 1 } ],
1792
explicit_alias => 1,
1800
left_arg => 't1.id',
1802
right_arg => 't2.id',
1812
left_arg => 't2.col',
1813
operator => 'is not',
1814
right_arg => 'null',
1818
left_arg => 't2.name',
1820
right_arg => '"bob"',
1827
order_by => [{table=>'t2', column=>'name', sort=>'ASC'}],
1835
{ name => 'SELECT FROM JOIN ON() JOIN USING() WHERE',
1836
query => 'SELECT t1.col1 a, t1.col2 as b
1840
JOIN tbl2 AS t2 ON(t1.id = t2.id)
1842
JOIN tbl3 t3 USING(id)
1845
t2.col IS NOT NULL',
1849
columns => 't1.col1 a, t1.col2 as b ',
1850
from => 'tbl1 t1 JOIN tbl2 AS t2 on (t1.id = t2.id) JOIN tbl3 t3 using (id) ',
1851
where => 't2.col IS NOT NULL',
1853
columns => [ { col => 'col1', tbl => 't1', alias => 'a' },
1854
{ col => 'col2', tbl => 't1', alias => 'b',
1855
explicit_alias => 1 } ],
1864
explicit_alias => 1,
1872
left_arg => 't1.id',
1874
right_arg => 't2.id',
1886
condition => 'using',
1895
left_arg => 't2.col',
1896
operator => 'is not',
1897
right_arg => 'null',
1903
{ name => 'SELECT keywords',
1904
query => 'SELECT all high_priority SQL_CALC_FOUND_ROWS NOW() LOCK IN SHARE MODE',
1910
columns => [ { col => 'NOW()' } ],
1914
sql_calc_found_rows => 1,
1915
lock_in_share_mode => 1,
1920
{ name => 'SELECT * FROM WHERE',
1921
query => 'SELECT * FROM tbl WHERE ip="127.0.0.1"',
1927
where => 'ip="127.0.0.1"',
1929
columns => [ { col => '*' } ],
1930
from => [ { tbl => 'tbl' } ],
1936
right_arg => '"127.0.0.1"',
1942
{ name => 'SELECT with simple subquery',
1943
query => 'select * from t where id in(select col from t2)',
1949
where => 'id in(__SQ0__)',
1951
columns => [ { col => '*' } ],
1952
from => [ { tbl => 't' } ],
1958
right_arg => '(__SQ0__)',
1964
query => 'select col from t2',
1971
columns => [ { col => 'col' } ],
1972
from => [ { tbl => 't2' } ],
1978
{ name => 'Complex SELECT, multiple JOIN and subqueries',
1979
query => 'select now(), (select foo from bar where id=1)
1980
from t1, t2 join (select * from sqt1) as t3 using (`select`)
1981
join t4 on t4.id=t3.id
1982
where c1 > any(select col2 as z from sqt2 zz
1983
where sqtc<(select max(col) from l where col<100))
1984
and s in ("select", "tricky") or s <> "select"
1985
group by 1 limit 10',
1989
columns => 'now(), __SQ3__ ',
1990
from => 't1, t2 join __SQ2__ as t3 using (`select`) join t4 on t4.id=t3.id ',
1991
where => 'c1 > any(__SQ1__) and s in ("select", "tricky") or s <> "select" ',
1995
columns => [ { col => 'now()' }, { col => '__SQ3__' } ],
2011
explicit_alias => 1,
2016
columns => ['`select`'],
2017
condition => 'using',
2029
left_arg => 't4.id',
2031
right_arg => 't3.id',
2043
right_arg => 'any(__SQ1__)',
2049
right_arg => '("select", "tricky")',
2055
right_arg => '"select"',
2058
limit => { row_count => 10 },
2059
group_by => [ { position => '1' } ],
2064
columns => 'max(col) ',
2068
columns => [ { col => 'max(col)' } ],
2069
context => 'scalar',
2070
from => [ { tbl => 'l' } ],
2072
query => 'select max(col) from l where col<100',
2086
columns => 'col2 as z ',
2088
where => 'sqtc<__SQ0__'
2091
{ alias => 'z', explicit_alias => 1, col => 'col2' }
2094
from => [ { alias => 'zz', tbl => 'sqt2' } ],
2095
query => 'select col2 as z from sqt2 zz where sqtc<__SQ0__',
2103
right_arg => '__SQ0__',
2112
columns => [ { col => '*' } ],
2113
context => 'identifier',
2114
from => [ { tbl => 'sqt1' } ],
2115
query => 'select * from sqt1',
2125
columns => [ { col => 'foo' } ],
2126
context => 'identifier',
2127
from => [ { tbl => 'bar' } ],
2128
query => 'select foo from bar where id=1',
2143
{ name => 'Table joined twice',
2146
INNER JOIN `w_series` AS `w_chapter__series`
2147
ON `w_chapter`.`series_id` = `w_chapter__series`.`id`,
2150
WHERE `w_chapter`.`status` = 1",
2155
from => "`w_chapter` INNER JOIN `w_series` AS `w_chapter__series` ON `w_chapter`.`series_id` = `w_chapter__series`.`id`, `w_series`, `auth_user` ",
2156
where => "`w_chapter`.`status` = 1",
2158
columns => [{col => '*'}],
2164
alias => 'w_chapter__series',
2165
explicit_alias => 1,
2172
left_arg => '`w_chapter`.`series_id`',
2174
right_arg => '`w_chapter__series`.`id`',
2202
left_arg => '`w_chapter`.`status`',
2211
# ########################################################################
2213
# ########################################################################
2214
{ name => 'UPDATE SET',
2215
query => 'UPDATE tbl SET col=1',
2222
tables => [ { tbl => 'tbl', } ],
2223
set => [ { col =>'col', value => '1' } ],
2227
{ name => 'UPDATE SET WHERE ORDER BY LIMIT',
2228
query => 'UPDATE tbl AS t SET foo=NULL WHERE foo IS NOT NULL ORDER BY id LIMIT 10',
2232
tables => 'tbl AS t ',
2234
where => 'foo IS NOT NULL ',
2238
tables => [ { tbl => 'tbl', alias => 't', explicit_alias => 1, } ],
2239
set => [ { col => 'foo', value => 'NULL' } ],
2244
operator => 'is not',
2245
right_arg => 'null',
2248
order_by => [{column=>'id'}],
2249
limit => { row_count => 10 },
2254
# ########################################################################
2255
# EXPLAIN EXTENDED fully-qualified queries.
2256
# ########################################################################
2257
{ name => 'EXPLAIN EXTENDED SELECT',
2258
query => 'select `sakila`.`city`.`country_id` AS `country_id` from `sakila`.`city` where (`sakila`.`city`.`country_id` = 1)',
2262
columns => '`sakila`.`city`.`country_id` AS `country_id` ',
2263
from => '`sakila`.`city` ',
2264
where => '(`sakila`.`city`.`country_id` = 1)',
2269
col => 'country_id',
2270
alias => 'country_id',
2271
explicit_alias => 1,
2274
from => [ { db=>'sakila', tbl=>'city' } ],
2277
left_arg => '`sakila`.`city`.`country_id`',
2286
foreach my $test ( @cases ) {
2287
my $struct = $sp->parse($test->{query});
2292
) or print Dumper($struct);
2293
die if $test->{stop};
2296
# ############################################################################
2297
# Use Schema to achieve full awesomeness.
2298
# ############################################################################
2307
my $o = new OptionParser(description => 'SchemaIterator');
2308
$o->get_specs("$trunk/bin/pt-table-checksum");
2311
my $tp = new TableParser(Quoter => $q);
2312
my $fi = new FileIterator();
2313
my $file_itr = $fi->get_file_itr("$trunk/t/lib/samples/mysqldump-no-data/dump001.txt");
2314
my $schema = new Schema();
2315
my $schema_itr = new SchemaIterator(
2316
file_itr => $file_itr,
2324
1 while ($schema_itr->next_schema_object());
2326
# Notice how c3 and b aren't qualified.
2328
$sp->parse("select c3 from b where 'foo'=c3"),
2334
where => '\'foo\'=c3',
2336
columns => [ { col => 'c3' } ],
2337
from => [ { tbl => 'b' } ],
2339
left_arg => "'foo'",
2346
"Query struct without Schema"
2349
# Now they're qualified.
2350
$sp->set_Schema($schema);
2352
$sp->parse("select c3 from b where 'foo'=c3"),
2358
where => '\'foo\'=c3',
2360
columns => [ { db => 'test', tbl => 'b', col => 'c3' } ],
2361
from => [ { db => 'test', tbl => 'b' } ],
2363
left_arg => "'foo'",
2370
"Query struct with Schema"
2373
# #############################################################################
2375
# #############################################################################
added
removed
t/lib/SQLParser.t
