-
Notifications
You must be signed in to change notification settings - Fork 30
Expand file tree
/
Copy pathJoinQueryTest.php
More file actions
161 lines (145 loc) · 5.66 KB
/
JoinQueryTest.php
File metadata and controls
161 lines (145 loc) · 5.66 KB
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
<?hh // strict
namespace Slack\SQLFake;
use function Facebook\FBExpect\expect;
use type Facebook\HackTest\HackTest;
use namespace HH\Lib\Vec;
final class JoinQueryTest extends HackTest {
private static ?AsyncMysqlConnection $conn;
public async function testInnerJoin(): Awaitable<void> {
$conn = static::$conn as nonnull;
$expected = vec[
dict[
'id' => 1,
'group_id' => 12345,
'name' => 'name1',
'table_3_id' => 1,
'table_4_id' => 1000,
'description' => 'association 1',
],
dict[
'id' => 1,
'group_id' => 12345,
'name' => 'name1',
'table_3_id' => 1,
'table_4_id' => 1001,
'description' => 'association 2',
],
dict[
'id' => 2,
'group_id' => 12345,
'name' => 'name2',
'table_3_id' => 2,
'table_4_id' => 1000,
'description' => 'association 3',
],
dict[
'id' => 3,
'group_id' => 12345,
'name' => 'name3',
'table_3_id' => 3,
'table_4_id' => 1003,
'description' => 'association 4',
],
];
$results = await $conn->query('SELECT * FROM table3 JOIN association_table ON id = table_3_id');
expect($results->rows())->toBeSame($expected, 'with no aliases and column names inferred from table schema');
$results =
await $conn->query('SELECT * FROM table3 JOIN association_table ON table3.id = association_table.table_3_id');
expect($results->rows())->toBeSame($expected, 'with columns using explicitly specified table names');
$results = await $conn->query(
'SELECT * FROM table3 f JOIN association_table s ON f.id = s.table_3_id AND f.group_id = s.group_id',
);
// there is (intentionally) one row here where the group_ids don't match, so we should see that filtered out here too
$expected = Vec\slice($expected, 0, 3);
expect($results->rows())->toBeSame($expected, 'with aliases and explicit group_id filter');
}
public async function testInnerJoinThreeTables(): Awaitable<void> {
$conn = static::$conn as nonnull;
$results = await $conn->query(
'SELECT table3.id as table_3_id, table4.description as descr, table4.group_id FROM table3 JOIN association_table on id=table_3_id JOIN table4 ON table_4_id = table4.id',
);
expect($results->rows())->toBeSame(vec[
dict['table_3_id' => 1, 'descr' => 'desc1', 'group_id' => 12345],
dict['table_3_id' => 1, 'descr' => 'desc2', 'group_id' => 12345],
dict['table_3_id' => 2, 'descr' => 'desc1', 'group_id' => 12345],
dict['table_3_id' => 3, 'descr' => 'desc1', 'group_id' => 7],
]);
}
public async function testStraightJoinThreeTables(): Awaitable<void> {
$conn = static::$conn as nonnull;
$results = await $conn->query(
'SELECT table3.id as table_3_id, table4.description as descr, table4.group_id FROM table3 STRAIGHT_JOIN association_table on id=table_3_id STRAIGHT_JOIN table4 ON table_4_id = table4.id',
);
expect($results->rows())->toBeSame(vec[
dict['table_3_id' => 1, 'descr' => 'desc1', 'group_id' => 12345],
dict['table_3_id' => 1, 'descr' => 'desc2', 'group_id' => 12345],
dict['table_3_id' => 2, 'descr' => 'desc1', 'group_id' => 12345],
dict['table_3_id' => 3, 'descr' => 'desc1', 'group_id' => 7],
]);
}
public async function testLeftJoin(): Awaitable<void> {
$conn = static::$conn as nonnull;
$results =
await $conn->query('SELECT id, table_4_id FROM table3 LEFT OUTER JOIN association_table ON id=table_3_id');
expect($results->rows())->toBeSame(vec[
dict['id' => 1, 'table_4_id' => 1000],
dict['id' => 1, 'table_4_id' => 1001],
dict['id' => 2, 'table_4_id' => 1000],
dict['id' => 3, 'table_4_id' => 1003],
dict['id' => 4, 'table_4_id' => null],
dict['id' => 6, 'table_4_id' => null],
]);
}
public async function testRightJoin(): Awaitable<void> {
$conn = static::$conn as nonnull;
$results = await $conn->query(
'SELECT table_3_id, table4.id as table_4_id FROM association_table RIGHT OUTER JOIN table4 ON id=table_4_id',
);
expect($results->rows())->toBeSame(vec[
dict['table_3_id' => 1, 'table_4_id' => 1000],
dict['table_3_id' => 2, 'table_4_id' => 1000],
dict['table_3_id' => 1, 'table_4_id' => 1001],
dict['table_3_id' => null, 'table_4_id' => 1002],
dict['table_3_id' => 3, 'table_4_id' => 1003],
dict['table_3_id' => null, 'table_4_id' => 1004],
]);
}
public async function testCrossJoin(): Awaitable<void> {
$conn = static::$conn as nonnull;
$results =
await $conn->query('SELECT table_3_id, id as table_4_id FROM association_table, table4 WHERE table4.id=1003');
expect($results->rows())->toBeSame(vec[
dict['table_3_id' => 1, 'table_4_id' => 1003],
dict['table_3_id' => 1, 'table_4_id' => 1003],
dict['table_3_id' => 2, 'table_4_id' => 1003],
dict['table_3_id' => 3, 'table_4_id' => 1003],
]);
}
public async function testNaturalJoin(): Awaitable<void> {
$conn = static::$conn as nonnull;
$results = await $conn->query('SELECT id, table_4_id FROM table3 NATURAL JOIN association_table');
expect($results->rows())->toBeSame(vec[
dict['id' => 1, 'table_4_id' => 1000],
dict['id' => 1, 'table_4_id' => 1001],
dict['id' => 1, 'table_4_id' => 1000],
dict['id' => 2, 'table_4_id' => 1000],
dict['id' => 2, 'table_4_id' => 1001],
dict['id' => 2, 'table_4_id' => 1000],
dict['id' => 3, 'table_4_id' => 1000],
dict['id' => 3, 'table_4_id' => 1001],
dict['id' => 3, 'table_4_id' => 1000],
]);
}
<<__Override>>
public static async function beforeFirstTestAsync(): Awaitable<void> {
static::$conn = await SharedSetup::initAsync();
// block hole logging
Logger::setHandle(new \HH\Lib\IO\MemoryHandle());
}
<<__Override>>
public async function beforeEachTestAsync(): Awaitable<void> {
restore('setup');
QueryContext::$strictSchemaMode = false;
QueryContext::$strictSQLMode = false;
}
}