packages/ztd-query-mysqli-adapter/tests/Integration/MysqliCteShadowingTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration;
6
7use Container\Endpoint;
8use Container\MySqlRelease;
9use mysqli;
10use mysqli_result;
11use PHPUnit\Framework\Attributes\Large;
12use PHPUnit\Framework\TestCase;
13use Testcontainers\Testcontainers;
14use ZtdQuery\Adapter\Mysqli\ZtdMysqli;
15
16#[\PHPUnit\Framework\Attributes\CoversClass(ZtdMysqli::class)]
17#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Session\ConnectionExecution::class)]
18#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\ZtdMysqliStatement::class)]
19#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Session\StatementExecution::class)]
20#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Driver\MysqliConnection::class)]
21#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Driver\MysqliResultStatement::class)]
22#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Driver\MysqliResultColumnExtractor::class)]
23#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Native\MysqliStatementBindingBridge::class)]
24#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\ZtdMysqliException::class)]
25#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Native\MysqliPropertyReader::class)]
26#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Session\MysqliResultProcessor::class)]
27#[Large]
28final class MysqliCteShadowingTest extends TestCase
29{
30 public function testUpdatesAndDeletesEveryListedTable(): void
31 {
32 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
33 $endpoint = $container->getData(Endpoint::class);
34 try {
35 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
36 $rawMysqli->set_charset('utf8mb4');
37 $users = 'users_' . bin2hex(random_bytes(8));
38 $orders = 'orders_' . bin2hex(random_bytes(8));
39 $rawMysqli->query("CREATE TABLE `{$users}` (id INT PRIMARY KEY, name VARCHAR(50))");
40 $rawMysqli->query("CREATE TABLE `{$orders}` (order_id INT PRIMARY KEY, user_id INT, status VARCHAR(50))");
41 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
42
43 self::assertNotFalse($ztdMysqli->query("INSERT INTO `{$users}` VALUES (1, 'Alice'), (2, 'Bob')"));
44 self::assertNotFalse($ztdMysqli->query("INSERT INTO `{$orders}` VALUES (10, 1, 'pending'), (20, 2, 'pending')"));
45 self::assertNotFalse($ztdMysqli->query(
46 "UPDATE `{$users}` u, `{$orders}` o SET u.name = 'Updated', o.status = 'done'"
47 . ' WHERE u.id = o.user_id AND u.id = 2',
48 ));
49
50 $updatedUsers = $ztdMysqli->query("SELECT id, name FROM `{$users}` ORDER BY id");
51 $updatedOrders = $ztdMysqli->query("SELECT order_id, status FROM `{$orders}` ORDER BY order_id");
52 self::assertInstanceOf(mysqli_result::class, $updatedUsers);
53 self::assertInstanceOf(mysqli_result::class, $updatedOrders);
54 self::assertSame(
55 [['id' => 1, 'name' => 'Alice'], ['id' => 2, 'name' => 'Updated']],
56 $updatedUsers->fetch_all(MYSQLI_ASSOC),
57 );
58 self::assertSame(
59 [['order_id' => 10, 'status' => 'pending'], ['order_id' => 20, 'status' => 'done']],
60 $updatedOrders->fetch_all(MYSQLI_ASSOC),
61 );
62
63 self::assertNotFalse($ztdMysqli->query(
64 "DELETE u, o FROM `{$users}` u JOIN `{$orders}` o ON u.id = o.user_id WHERE u.id = 2",
65 ));
66
67 $remainingUsers = $ztdMysqli->query("SELECT id FROM `{$users}` ORDER BY id");
68 $remainingOrders = $ztdMysqli->query("SELECT order_id FROM `{$orders}` ORDER BY order_id");
69 self::assertInstanceOf(mysqli_result::class, $remainingUsers);
70 self::assertInstanceOf(mysqli_result::class, $remainingOrders);
71 self::assertSame([['id' => 1]], $remainingUsers->fetch_all(MYSQLI_ASSOC));
72 self::assertSame([['order_id' => 10]], $remainingOrders->fetch_all(MYSQLI_ASSOC));
73
74 $physicalUsers = $rawMysqli->query("SELECT * FROM `{$users}`");
75 $physicalOrders = $rawMysqli->query("SELECT * FROM `{$orders}`");
76 self::assertInstanceOf(mysqli_result::class, $physicalUsers);
77 self::assertInstanceOf(mysqli_result::class, $physicalOrders);
78 self::assertSame([], $physicalUsers->fetch_all(MYSQLI_ASSOC));
79 self::assertSame([], $physicalOrders->fetch_all(MYSQLI_ASSOC));
80 } finally {
81 $container->stop();
82 }
83 }
84
85 public function testExecuteQueryReplaceRemovesExistingPrimaryKey(): void
86 {
87 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
88 $endpoint = $container->getData(Endpoint::class);
89 try {
90 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
91 $rawMysqli->set_charset('utf8mb4');
92 $table = 'prefix_' . bin2hex(random_bytes(8));
93 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, name VARCHAR(50))', $table));
94 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
95
96 $ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, 'original')", $table));
97 self::assertNotFalse($ztdMysqli->execute_query(
98 sprintf('REPLACE INTO `%s` VALUES (?, ?)', $table),
99 ['1', 'replaced'],
100 ));
101
102 $rows = $ztdMysqli->query(sprintf('SELECT * FROM `%s` WHERE id = 1', $table));
103 self::assertInstanceOf(mysqli_result::class, $rows);
104 self::assertSame([['id' => 1, 'name' => 'replaced']], $rows->fetch_all(MYSQLI_ASSOC));
105 } finally {
106 $container->stop();
107 }
108 }
109
110 public function testExecuteQueryOnDuplicateKeyUpdateReplacesExistingValues(): void
111 {
112 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
113 $endpoint = $container->getData(Endpoint::class);
114 try {
115 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
116 $rawMysqli->set_charset('utf8mb4');
117 $table = 'prefix_' . bin2hex(random_bytes(8));
118 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, name VARCHAR(50))', $table));
119 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
120
121 $ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, 'original')", $table));
122 self::assertNotFalse($ztdMysqli->execute_query(
123 sprintf('INSERT INTO `%s` VALUES (?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name)', $table),
124 ['1', 'updated'],
125 ));
126
127 $rows = $ztdMysqli->query(sprintf('SELECT * FROM `%s` WHERE id = 1', $table));
128 self::assertInstanceOf(mysqli_result::class, $rows);
129 self::assertSame([['id' => 1, 'name' => 'updated']], $rows->fetch_all(MYSQLI_ASSOC));
130 } finally {
131 $container->stop();
132 }
133 }
134
135 public function testUpdateReplacesExistingTextWithEmptyString(): void
136 {
137 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
138 $endpoint = $container->getData(Endpoint::class);
139 try {
140 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
141 $rawMysqli->set_charset('utf8mb4');
142 $table = 'prefix_' . bin2hex(random_bytes(8));
143 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, name VARCHAR(100), notes TEXT)', $table));
144 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
145
146 self::assertNotFalse($ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, 'Alice', 'some notes')", $table)));
147 self::assertNotFalse($ztdMysqli->query(sprintf("UPDATE `%s` SET notes = '' WHERE name = 'Alice'", $table)));
148
149 $result = $ztdMysqli->query(sprintf('SELECT notes FROM `%s` WHERE id = 1', $table));
150 self::assertInstanceOf(mysqli_result::class, $result);
151 self::assertSame([['notes' => '']], $result->fetch_all(MYSQLI_ASSOC));
152 } finally {
153 $container->stop();
154 }
155 }
156
157 public function testUpdatePreservesIntroducedHexLiteral(): void
158 {
159 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
160 $endpoint = $container->getData(Endpoint::class);
161 try {
162 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
163 $rawMysqli->set_charset('utf8mb4');
164 $table = 'prefix_' . bin2hex(random_bytes(8));
165 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, payload VARBINARY(255))', $table));
166 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
167
168 self::assertNotFalse($ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, X'48656C6C6F')", $table)));
169 self::assertNotFalse($ztdMysqli->query(sprintf("UPDATE `%s` SET payload = X'576F726C64' WHERE id = 1", $table)));
170
171 $result = $ztdMysqli->query(sprintf('SELECT payload FROM `%s` WHERE id = 1', $table));
172 self::assertInstanceOf(mysqli_result::class, $result);
173 self::assertSame([['payload' => 'World']], $result->fetch_all(MYSQLI_ASSOC));
174
175 $physical = $rawMysqli->query(sprintf('SELECT payload FROM `%s`', $table));
176 self::assertInstanceOf(mysqli_result::class, $physical);
177 self::assertSame([], $physical->fetch_all(MYSQLI_ASSOC));
178 } finally {
179 $container->stop();
180 }
181 }
182
183 public function testUpdatePreservesIntervalUnit(): void
184 {
185 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
186 $endpoint = $container->getData(Endpoint::class);
187 try {
188 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
189 $rawMysqli->set_charset('utf8mb4');
190 $table = 'prefix_' . bin2hex(random_bytes(8));
191 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, created_at DATETIME NOT NULL, due_at DATETIME)', $table));
192 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
193
194 self::assertNotFalse($ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, '2025-01-01 10:00:00', NULL)", $table)));
195 self::assertNotFalse($ztdMysqli->query(sprintf('UPDATE `%s` SET due_at = created_at + INTERVAL 30 DAY WHERE id = 1', $table)));
196
197 $result = $ztdMysqli->query(sprintf('SELECT due_at FROM `%s` WHERE id = 1', $table));
198 self::assertInstanceOf(mysqli_result::class, $result);
199 self::assertSame([['due_at' => '2025-01-31 10:00:00']], $result->fetch_all(MYSQLI_ASSOC));
200
201 $physical = $rawMysqli->query(sprintf('SELECT due_at FROM `%s`', $table));
202 self::assertInstanceOf(mysqli_result::class, $physical);
203 self::assertSame([], $physical->fetch_all(MYSQLI_ASSOC));
204 } finally {
205 $container->stop();
206 }
207 }
208
209 public function testUpdateAndDeleteRestrictRowsWithCaseExpression(): void
210 {
211 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
212 $endpoint = $container->getData(Endpoint::class);
213 try {
214 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
215 $rawMysqli->set_charset('utf8mb4');
216 $updates = 'prefix_' . bin2hex(random_bytes(8));
217 $deletes = 'prefix_' . bin2hex(random_bytes(8));
218 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, score INT)', $updates));
219 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, score INT)', $deletes));
220 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
221
222 $rows = 'VALUES (1, 85), (2, 60), (3, 95), (4, 45)';
223 self::assertNotFalse($ztdMysqli->query(sprintf('INSERT INTO `%s` %s', $updates, $rows)));
224 self::assertNotFalse($ztdMysqli->query(sprintf('INSERT INTO `%s` %s', $deletes, $rows)));
225 self::assertNotFalse($ztdMysqli->query(sprintf('UPDATE `%s` SET score = 0 WHERE CASE WHEN score > 80 THEN 1 ELSE 0 END = 1', $updates)));
226 self::assertSame(2, $ztdMysqli->lastAffectedRows());
227 self::assertNotFalse($ztdMysqli->query(sprintf('DELETE FROM `%s` WHERE CASE WHEN score > 80 THEN 1 ELSE 0 END = 1', $deletes)));
228 self::assertSame(2, $ztdMysqli->lastAffectedRows());
229
230 $updated = $ztdMysqli->query(sprintf('SELECT id, score FROM `%s` ORDER BY id', $updates));
231 $remaining = $ztdMysqli->query(sprintf('SELECT id, score FROM `%s` ORDER BY id', $deletes));
232 self::assertInstanceOf(mysqli_result::class, $updated);
233 self::assertInstanceOf(mysqli_result::class, $remaining);
234 self::assertSame([
235 ['id' => 1, 'score' => 0],
236 ['id' => 2, 'score' => 60],
237 ['id' => 3, 'score' => 0],
238 ['id' => 4, 'score' => 45],
239 ], $updated->fetch_all(MYSQLI_ASSOC));
240 self::assertSame([
241 ['id' => 2, 'score' => 60],
242 ['id' => 4, 'score' => 45],
243 ], $remaining->fetch_all(MYSQLI_ASSOC));
244
245 $physicalUpdates = $rawMysqli->query(sprintf('SELECT * FROM `%s`', $updates));
246 $physicalDeletes = $rawMysqli->query(sprintf('SELECT * FROM `%s`', $deletes));
247 self::assertInstanceOf(mysqli_result::class, $physicalUpdates);
248 self::assertInstanceOf(mysqli_result::class, $physicalDeletes);
249 self::assertSame([], $physicalUpdates->fetch_all(MYSQLI_ASSOC));
250 self::assertSame([], $physicalDeletes->fetch_all(MYSQLI_ASSOC));
251 } finally {
252 $container->stop();
253 }
254 }
255
256 public function testInsertWithoutColumnListIgnoresNamedForeignKeyConstraint(): void
257 {
258 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
259 $endpoint = $container->getData(Endpoint::class);
260 try {
261 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
262 $rawMysqli->set_charset('utf8mb4');
263 $parent = 'prefix_' . bin2hex(random_bytes(8));
264 $child = 'prefix_' . bin2hex(random_bytes(8));
265 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, name VARCHAR(50)) ENGINE=InnoDB', $parent));
266 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, parent_id INT NOT NULL, label VARCHAR(50) NOT NULL, CONSTRAINT `fk_parent` FOREIGN KEY (parent_id) REFERENCES `%s`(id)) ENGINE=InnoDB', $child, $parent));
267 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
268
269 self::assertNotFalse($ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, 'Parent')", $parent)));
270 self::assertNotFalse($ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (10, 1, 'Child')", $child)));
271
272 $result = $ztdMysqli->query(sprintf('SELECT * FROM `%s`', $child));
273 self::assertInstanceOf(mysqli_result::class, $result);
274 self::assertSame([['id' => 10, 'parent_id' => 1, 'label' => 'Child']], $result->fetch_all(MYSQLI_ASSOC));
275
276 $physical = $rawMysqli->query(sprintf('SELECT * FROM `%s`', $child));
277 self::assertInstanceOf(mysqli_result::class, $physical);
278 self::assertSame([], $physical->fetch_all(MYSQLI_ASSOC));
279 } finally {
280 $container->stop();
281 }
282 }
283
284 public function testSelfReferencingUpsertMatchesNativeMySql(): void
285 {
286 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
287 $endpoint = $container->getData(Endpoint::class);
288 try {
289 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
290 $rawMysqli->set_charset('utf8mb4');
291 $nativeTable = 'prefix_' . bin2hex(random_bytes(8));
292 $shadowTable = 'prefix_' . bin2hex(random_bytes(8));
293 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, quantity INT NOT NULL)', $nativeTable));
294 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, quantity INT NOT NULL)', $shadowTable));
295 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
296
297 $rawMysqli->query(sprintf('INSERT INTO `%s` VALUES (1, 100)', $nativeTable));
298 $ztdMysqli->query(sprintf('INSERT INTO `%s` VALUES (1, 100)', $shadowTable));
299 $rawMysqli->query(sprintf('INSERT INTO `%1$s` VALUES (1, 5), (1, 7) ON DUPLICATE KEY UPDATE quantity = `%1$s`.quantity + VALUES(quantity)', $nativeTable));
300 $ztdMysqli->query(sprintf('INSERT INTO `%1$s` VALUES (1, 5), (1, 7) ON DUPLICATE KEY UPDATE quantity = `%1$s`.quantity + VALUES(quantity)', $shadowTable));
301
302 $rawRows = $rawMysqli->query(sprintf('SELECT id, quantity FROM `%s`', $nativeTable));
303 $ztdRows = $ztdMysqli->query(sprintf('SELECT id, quantity FROM `%s`', $shadowTable));
304 $physicalShadowRows = $rawMysqli->query(sprintf('SELECT * FROM `%s`', $shadowTable));
305 self::assertInstanceOf(mysqli_result::class, $rawRows);
306 self::assertInstanceOf(mysqli_result::class, $ztdRows);
307 self::assertInstanceOf(mysqli_result::class, $physicalShadowRows);
308 self::assertEquals($rawRows->fetch_all(MYSQLI_ASSOC), $ztdRows->fetch_all(MYSQLI_ASSOC));
309 self::assertSame([], $physicalShadowRows->fetch_all(MYSQLI_ASSOC));
310 } finally {
311 $container->stop();
312 }
313 }
314
315 public function testAffectedRowsCountsOnlyChangedMySqlRows(): void
316 {
317 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
318 $endpoint = $container->getData(Endpoint::class);
319 try {
320 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
321 $rawMysqli->set_charset('utf8mb4');
322 $table = 'prefix_' . bin2hex(random_bytes(8));
323 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, score INT)', $table));
324 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
325
326 $ztdMysqli->query(sprintf('INSERT INTO `%s` VALUES (1, 10)', $table));
327 $ztdMysqli->query(sprintf('UPDATE `%s` SET score = 10 WHERE id = 1', $table));
328 self::assertSame(0, $ztdMysqli->lastAffectedRows());
329 $ztdMysqli->query(sprintf('UPDATE `%s` SET score = 11 WHERE id = 1', $table));
330 self::assertSame(1, $ztdMysqli->lastAffectedRows());
331 } finally {
332 $container->stop();
333 }
334 }
335
336 public function testTransactionsAndSavepointsRestoreShadowRows(): void
337 {
338 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
339 $endpoint = $container->getData(Endpoint::class);
340 try {
341 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
342 $rawMysqli->set_charset('utf8mb4');
343 $table = 'prefix_' . bin2hex(random_bytes(8));
344 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, name VARCHAR(20))', $table));
345 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
346
347 $ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, 'one')", $table));
348 $ztdMysqli->begin_transaction();
349 $ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (2, 'two')", $table));
350 $ztdMysqli->savepoint('nested');
351 $ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (3, 'three')", $table));
352 $ztdMysqli->query('ROLLBACK TO SAVEPOINT nested');
353 $ztdMysqli->commit();
354
355 $committed = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $table));
356 self::assertInstanceOf(mysqli_result::class, $committed);
357 self::assertSame([
358 ['id' => 1, 'name' => 'one'],
359 ['id' => 2, 'name' => 'two'],
360 ], $committed->fetch_all(MYSQLI_ASSOC));
361
362 $ztdMysqli->begin_transaction();
363 $ztdMysqli->query(sprintf("UPDATE `%s` SET name = 'changed'", $table));
364 $ztdMysqli->rollback();
365 $rolledBack = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $table));
366 self::assertInstanceOf(mysqli_result::class, $rolledBack);
367 self::assertSame([
368 ['id' => 1, 'name' => 'one'],
369 ['id' => 2, 'name' => 'two'],
370 ], $rolledBack->fetch_all(MYSQLI_ASSOC));
371 } finally {
372 $container->stop();
373 }
374 }
375
376 public function testRecursiveAndUserOwnedCteNamespacesRemainValid(): void
377 {
378 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
379 $endpoint = $container->getData(Endpoint::class);
380 try {
381 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
382 $rawMysqli->set_charset('utf8mb4');
383 $table = 'prefix_' . bin2hex(random_bytes(8));
384 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, parent_id INT)', $table));
385 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
386
387 $ztdMysqli->query(sprintf('INSERT INTO `%s` VALUES (1, NULL), (2, 1), (3, 2)', $table));
388 $recursive = $ztdMysqli->query(sprintf(
389 'WITH RECURSIVE tree AS (SELECT id, parent_id FROM `%1$s` WHERE parent_id IS NULL UNION ALL SELECT n.id, n.parent_id FROM `%1$s` n JOIN tree t ON n.parent_id = t.id) SELECT id FROM tree ORDER BY id',
390 $table,
391 ));
392 self::assertInstanceOf(mysqli_result::class, $recursive);
393 self::assertSame([['id' => 1], ['id' => 2], ['id' => 3]], $recursive->fetch_all(MYSQLI_ASSOC));
394
395 $owned = $ztdMysqli->query(sprintf('WITH `%1$s` AS (SELECT 9 AS id, NULL AS parent_id) SELECT id FROM `%1$s`', $table));
396 self::assertInstanceOf(mysqli_result::class, $owned);
397 self::assertSame([['id' => 9]], $owned->fetch_all(MYSQLI_ASSOC));
398 } finally {
399 $container->stop();
400 }
401 }
402
403 public function testCteDefinitionsRemainVisibleToSimulatedDml(): void
404 {
405 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
406 $endpoint = $container->getData(Endpoint::class);
407 try {
408 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
409 $rawMysqli->set_charset('utf8mb4');
410 $table = 'prefix_' . bin2hex(random_bytes(8));
411 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, value VARCHAR(20))', $table));
412 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
413
414 self::assertNotFalse($ztdMysqli->query(sprintf("WITH source AS (SELECT 1 AS id, 'one' AS value UNION ALL SELECT 2, 'two') INSERT INTO `%s` SELECT * FROM source", $table)));
415 self::assertNotFalse($ztdMysqli->query(sprintf("WITH chosen AS (SELECT id FROM `%1\$s` WHERE value = 'two') UPDATE `%1\$s` SET value = 'changed' WHERE id IN (SELECT id FROM chosen)", $table)));
416 self::assertNotFalse($ztdMysqli->query(sprintf("WITH chosen AS (SELECT id FROM `%1\$s` WHERE value = 'one') DELETE FROM `%1\$s` WHERE id IN (SELECT id FROM chosen)", $table)));
417
418 $result = $ztdMysqli->query(sprintf('SELECT * FROM `%s`', $table));
419 self::assertInstanceOf(mysqli_result::class, $result);
420 self::assertSame([['id' => 2, 'value' => 'changed']], $result->fetch_all(MYSQLI_ASSOC));
421 } finally {
422 $container->stop();
423 }
424 }
425
426 public function testOrderedLimitedUpdateKeepsOriginalIdentityAndSwapSnapshot(): void
427 {
428 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
429 $endpoint = $container->getData(Endpoint::class);
430 try {
431 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
432 $rawMysqli->set_charset('utf8mb4');
433 $table = 'prefix_' . bin2hex(random_bytes(8));
434 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, left_value VARCHAR(20), right_value VARCHAR(20))', $table));
435 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
436
437 $ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, 'a1', 'b1'), (2, 'a2', 'b2'), (3, 'a3', 'b3')", $table));
438 $ztdMysqli->query(sprintf('UPDATE `%s` SET id = 30, left_value = right_value, right_value = left_value ORDER BY id DESC LIMIT 1', $table));
439
440 $result = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $table));
441 self::assertInstanceOf(mysqli_result::class, $result);
442 self::assertSame([
443 ['id' => 1, 'left_value' => 'a1', 'right_value' => 'b1'],
444 ['id' => 2, 'left_value' => 'a2', 'right_value' => 'b2'],
445 ['id' => 30, 'left_value' => 'b3', 'right_value' => 'a3'],
446 ], $result->fetch_all(MYSQLI_ASSOC));
447 } finally {
448 $container->stop();
449 }
450 }
451
452 public function testInsertSelectPreservesStarJoinsAggregatesDistinctAndRollup(): void
453 {
454 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
455 $endpoint = $container->getData(Endpoint::class);
456 try {
457 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
458 $rawMysqli->set_charset('utf8mb4');
459 $source = 'prefix_' . bin2hex(random_bytes(8));
460 $target = 'prefix_' . bin2hex(random_bytes(8));
461 $orders = 'prefix_' . bin2hex(random_bytes(8));
462 $summary = 'prefix_' . bin2hex(random_bytes(8));
463 $regions = 'prefix_' . bin2hex(random_bytes(8));
464 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, region VARCHAR(20), amount INT)', $source));
465 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, region VARCHAR(20), amount INT)', $target));
466 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, source_id INT)', $orders));
467 $rawMysqli->query(sprintf('CREATE TABLE `%s` (region VARCHAR(20), order_count INT, total_amount INT)', $summary));
468 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))', $regions));
469 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
470
471 $ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, 'east', 100), (2, 'east', 200), (3, 'west', 300)", $source));
472 $ztdMysqli->query(sprintf('INSERT INTO `%s` VALUES (1, 1), (2, 1), (3, 3)', $orders));
473 $ztdMysqli->query(sprintf('INSERT INTO `%s` SELECT * FROM `%s`', $target, $source));
474 $ztdMysqli->query(sprintf(
475 'INSERT INTO `%s` (region, order_count, total_amount) SELECT s.region, COUNT(o.id), SUM(s.amount) FROM `%s` s JOIN `%s` o ON o.source_id = s.id GROUP BY s.region WITH ROLLUP',
476 $summary,
477 $source,
478 $orders,
479 ));
480 $ztdMysqli->query(sprintf('INSERT INTO `%s` (name) SELECT DISTINCT region FROM `%s` ORDER BY region', $regions, $source));
481
482 $targetRows = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $target));
483 $summaryRows = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY region', $summary));
484 $regionRows = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $regions));
485 self::assertInstanceOf(mysqli_result::class, $targetRows);
486 self::assertInstanceOf(mysqli_result::class, $summaryRows);
487 self::assertInstanceOf(mysqli_result::class, $regionRows);
488 self::assertEquals([
489 ['id' => 1, 'region' => 'east', 'amount' => 100],
490 ['id' => 2, 'region' => 'east', 'amount' => 200],
491 ['id' => 3, 'region' => 'west', 'amount' => 300],
492 ], $targetRows->fetch_all(MYSQLI_ASSOC));
493 self::assertEquals([
494 ['region' => null, 'order_count' => 3, 'total_amount' => 500],
495 ['region' => 'east', 'order_count' => 2, 'total_amount' => 200],
496 ['region' => 'west', 'order_count' => 1, 'total_amount' => 300],
497 ], $summaryRows->fetch_all(MYSQLI_ASSOC));
498 self::assertEquals([['id' => 1, 'name' => 'east'], ['id' => 2, 'name' => 'west']], $regionRows->fetch_all(MYSQLI_ASSOC));
499 } finally {
500 $container->stop();
501 }
502 }
503
504 public function testGroupedSelfReferencingSubqueryRestrictsUpdate(): void
505 {
506 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
507 $endpoint = $container->getData(Endpoint::class);
508 try {
509 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
510 $rawMysqli->set_charset('utf8mb4');
511 $table = 'prefix_' . bin2hex(random_bytes(8));
512 $definition = '(id INT PRIMARY KEY, name VARCHAR(50), dept_id INT, salary DECIMAL(10,2), active TINYINT DEFAULT 1)';
513 $rawMysqli->query(sprintf('CREATE TABLE `%s` %s', $table, $definition));
514 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
515
516 $rows = "VALUES (1, 'Alice', 1, 120000, 1), (2, 'Bob', 1, 110000, 1), (3, 'Charlie', 2, 90000, 1), (4, 'Diana', 3, 95000, 1), (5, 'Eve', 1, 130000, 1)";
517 $ztdMysqli->query(sprintf('INSERT INTO `%s` %s', $table, $rows));
518
519 $ztdMysqli->query(sprintf('UPDATE `%1$s` SET active = 0 WHERE dept_id IN (SELECT dept_id FROM `%1$s` GROUP BY dept_id HAVING AVG(salary) > 100000)', $table));
520
521 $result = $ztdMysqli->query(sprintf('SELECT id, active FROM `%s` ORDER BY id', $table));
522 self::assertInstanceOf(mysqli_result::class, $result);
523 self::assertSame([
524 ['id' => 1, 'active' => 0],
525 ['id' => 2, 'active' => 0],
526 ['id' => 3, 'active' => 1],
527 ['id' => 4, 'active' => 1],
528 ['id' => 5, 'active' => 0],
529 ], $result->fetch_all(MYSQLI_ASSOC));
530 } finally {
531 $container->stop();
532 }
533 }
534
535 public function testGroupedSelfReferencingSubqueryRestrictsDelete(): void
536 {
537 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
538 $endpoint = $container->getData(Endpoint::class);
539 try {
540 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
541 $rawMysqli->set_charset('utf8mb4');
542 $table = 'prefix_' . bin2hex(random_bytes(8));
543 $definition = '(id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2), status VARCHAR(20))';
544 $rawMysqli->query(sprintf('CREATE TABLE `%s` %s', $table, $definition));
545 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
546
547 $rows = "VALUES (1, 1, 50, 'completed'), (2, 1, 75, 'completed'), (3, 1, 30, 'cancelled'), (4, 2, 200, 'completed'), (5, 3, 10, 'completed'), (6, 3, 15, 'completed')";
548 $ztdMysqli->query(sprintf('INSERT INTO `%s` %s', $table, $rows));
549
550 $ztdMysqli->query(sprintf("DELETE FROM `%1\$s` WHERE customer_id IN (SELECT customer_id FROM `%1\$s` WHERE status = 'cancelled' GROUP BY customer_id HAVING COUNT(*) >= 1)", $table));
551
552 $result = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $table));
553 self::assertInstanceOf(mysqli_result::class, $result);
554 self::assertSame([
555 ['id' => 4, 'customer_id' => 2, 'amount' => '200.00', 'status' => 'completed'],
556 ['id' => 5, 'customer_id' => 3, 'amount' => '10.00', 'status' => 'completed'],
557 ['id' => 6, 'customer_id' => 3, 'amount' => '15.00', 'status' => 'completed'],
558 ], $result->fetch_all(MYSQLI_ASSOC));
559 } finally {
560 $container->stop();
561 }
562 }
563
564 public function testAutoIncrementUsesShadowCounterWithoutModifyingPhysicalTable(): void
565 {
566 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
567 $endpoint = $container->getData(Endpoint::class);
568 try {
569 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
570 $rawMysqli->set_charset('utf8mb4');
571 $table = 'prefix_' . bin2hex(random_bytes(8));
572 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL)', $table));
573 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
574
575 $ztdMysqli->query(sprintf("INSERT INTO `%s` (name) VALUES ('Alice'), ('Bob')", $table));
576
577 $ztdRows = $ztdMysqli->query(sprintf('SELECT id, name FROM `%s` ORDER BY id', $table));
578 $rawRows = $rawMysqli->query(sprintf('SELECT * FROM `%s`', $table));
579 self::assertInstanceOf(mysqli_result::class, $ztdRows);
580 self::assertInstanceOf(mysqli_result::class, $rawRows);
581 self::assertEquals([['id' => 1, 'name' => 'Alice'], ['id' => 2, 'name' => 'Bob']], $ztdRows->fetch_all(MYSQLI_ASSOC));
582 self::assertSame([], $rawRows->fetch_all(MYSQLI_ASSOC));
583 } finally {
584 $container->stop();
585 }
586 }
587
588 public function testOmittedExplicitAndDefaultOnlyValuesMatchMySql(): void
589 {
590 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
591 $endpoint = $container->getData(Endpoint::class);
592 try {
593 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
594 $rawMysqli->set_charset('utf8mb4');
595 $table = 'prefix_' . bin2hex(random_bytes(8));
596 $rawMysqli->query(sprintf(
597 "CREATE TABLE `%s` (id INT DEFAULT 7, status ENUM('new','active') DEFAULT 'active', note VARCHAR(20) DEFAULT NULL)",
598 $table,
599 ));
600 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
601
602 $rawMysqli->query(sprintf('INSERT INTO `%s` (id, status) VALUES (1, DEFAULT)', $table));
603 $rawMysqli->query(sprintf('INSERT INTO `%s` () VALUES ()', $table));
604 $ztdMysqli->query(sprintf('INSERT INTO `%s` (id, status) VALUES (1, DEFAULT)', $table));
605 $ztdMysqli->query(sprintf('INSERT INTO `%s` () VALUES ()', $table));
606
607 $raw = $rawMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $table));
608 $ztd = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $table));
609 self::assertInstanceOf(mysqli_result::class, $raw);
610 self::assertInstanceOf(mysqli_result::class, $ztd);
611 $rawRows = $raw->fetch_all(MYSQLI_ASSOC);
612 $ztdRows = $ztd->fetch_all(MYSQLI_ASSOC);
613 self::assertEquals($rawRows, $ztdRows);
614 } finally {
615 $container->stop();
616 }
617 }
618
619 public function testEnumUsesDeclarationRanksForOrderingAndComparison(): void
620 {
621 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
622 $endpoint = $container->getData(Endpoint::class);
623 try {
624 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
625 $rawMysqli->set_charset('utf8mb4');
626 $table = 'prefix_' . bin2hex(random_bytes(8));
627 $rawMysqli->query(sprintf("CREATE TABLE `%s` (id INT PRIMARY KEY, size ENUM('small','medium','large'))", $table));
628 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
629
630 $ztdMysqli->query(sprintf("INSERT INTO `%s` VALUES (1, 'large'), (2, 'small'), (3, 'medium')", $table));
631
632 $ordered = $ztdMysqli->query(sprintf('SELECT size FROM `%s` ORDER BY size', $table));
633 $compared = $ztdMysqli->query(sprintf("SELECT size FROM `%s` WHERE size > 'small' ORDER BY size", $table));
634 self::assertInstanceOf(mysqli_result::class, $ordered);
635 self::assertInstanceOf(mysqli_result::class, $compared);
636 self::assertSame(['small', 'medium', 'large'], array_column($ordered->fetch_all(MYSQLI_ASSOC), 'size'));
637 self::assertSame(['medium', 'large'], array_column($compared->fetch_all(MYSQLI_ASSOC), 'size'));
638 } finally {
639 $container->stop();
640 }
641 }
642
643 public function testSetExpressionsRemainSingleStatements(): void
644 {
645 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
646 $endpoint = $container->getData(Endpoint::class);
647 try {
648 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
649 $rawMysqli->set_charset('utf8mb4');
650 $users = 'prefix_' . bin2hex(random_bytes(8));
651 $vip = 'prefix_' . bin2hex(random_bytes(8));
652 $rawMysqli->query(sprintf('CREATE TABLE `%s` (name VARCHAR(255) PRIMARY KEY)', $users));
653 $rawMysqli->query(sprintf('CREATE TABLE `%s` (name VARCHAR(255) PRIMARY KEY)', $vip));
654 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
655
656 $ztdMysqli->query(sprintf("INSERT INTO `%s` (name) VALUES ('Alice'), ('Bob')", $users));
657 $ztdMysqli->query(sprintf("INSERT INTO `%s` (name) VALUES ('Alice')", $vip));
658
659 $except = $ztdMysqli->query(sprintf(
660 'SELECT name FROM `%s` EXCEPT SELECT name FROM `%s` ORDER BY name',
661 $users,
662 $vip,
663 ));
664 $intersect = $ztdMysqli->query(sprintf(
665 'SELECT name FROM `%s` INTERSECT SELECT name FROM `%s` ORDER BY name',
666 $users,
667 $vip,
668 ));
669 self::assertInstanceOf(mysqli_result::class, $except);
670 self::assertInstanceOf(mysqli_result::class, $intersect);
671 self::assertSame([['name' => 'Bob']], $except->fetch_all(MYSQLI_ASSOC));
672 self::assertSame([['name' => 'Alice']], $intersect->fetch_all(MYSQLI_ASSOC));
673 } finally {
674 $container->stop();
675 }
676 }
677
678 public function testSelectOnCleanShadowReturnsEmpty(): void
679 {
680 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
681 $endpoint = $container->getData(Endpoint::class);
682 try {
683 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
684 $rawMysqli->set_charset('utf8mb4');
685 $table = 'prefix_' . bin2hex(random_bytes(8));
686 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
687 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
688 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
689 $result = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $table));
690 self::assertNotFalse($result);
691 self::assertInstanceOf(mysqli_result::class, $result);
692
693 $rows = $result->fetch_all(MYSQLI_ASSOC);
694 self::assertCount(0, $rows);
695 } finally {
696 $container->stop();
697 }
698 }
699
700 public function testInsertDoesNotModifyPhysicalDatabase(): void
701 {
702 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
703 $endpoint = $container->getData(Endpoint::class);
704 try {
705 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
706 $rawMysqli->set_charset('utf8mb4');
707 $table = 'prefix_' . bin2hex(random_bytes(8));
708 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
709 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
710 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
711 $ztdMysqli->query(sprintf(
712 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
713 $table
714 ));
715
716 $result = $rawMysqli->query(sprintf('SELECT * FROM `%s`', $table));
717 self::assertNotFalse($result);
718 self::assertInstanceOf(mysqli_result::class, $result);
719
720 $rows = $result->fetch_all(MYSQLI_ASSOC);
721 self::assertCount(2, $rows);
722 } finally {
723 $container->stop();
724 }
725 }
726
727 public function testInsertIsVisibleViaZtdSelect(): void
728 {
729 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
730 $endpoint = $container->getData(Endpoint::class);
731 try {
732 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
733 $rawMysqli->set_charset('utf8mb4');
734 $table = 'prefix_' . bin2hex(random_bytes(8));
735 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
736 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
737 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
738 $ztdMysqli->query(sprintf(
739 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
740 $table
741 ));
742
743 $result = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $table));
744 self::assertNotFalse($result);
745 self::assertInstanceOf(mysqli_result::class, $result);
746
747 $rows = $result->fetch_all(MYSQLI_ASSOC);
748 self::assertCount(1, $rows);
749 self::assertSame('Charlie', $rows[0]['name']);
750 /**
751 * @var string|int $age
752 */
753 $age = $rows[0]['age'];
754 self::assertSame('35', (string) $age);
755 } finally {
756 $container->stop();
757 }
758 }
759
760 public function testMultipleInsertsAccumulate(): void
761 {
762 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
763 $endpoint = $container->getData(Endpoint::class);
764 try {
765 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
766 $rawMysqli->set_charset('utf8mb4');
767 $table = 'prefix_' . bin2hex(random_bytes(8));
768 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
769 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
770 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
771 $ztdMysqli->query(sprintf(
772 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
773 $table
774 ));
775 $ztdMysqli->query(sprintf(
776 "INSERT INTO `%s` (name, age) VALUES ('Diana', 28)",
777 $table
778 ));
779
780 $result = $ztdMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY name', $table));
781 self::assertNotFalse($result);
782 self::assertInstanceOf(mysqli_result::class, $result);
783
784 $rows = $result->fetch_all(MYSQLI_ASSOC);
785 self::assertCount(2, $rows);
786
787 $names = array_column($rows, 'name');
788 self::assertContains('Charlie', $names);
789 self::assertContains('Diana', $names);
790 } finally {
791 $container->stop();
792 }
793 }
794
795 public function testSelectWithWhereOnShadowData(): void
796 {
797 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
798 $endpoint = $container->getData(Endpoint::class);
799 try {
800 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
801 $rawMysqli->set_charset('utf8mb4');
802 $table = 'prefix_' . bin2hex(random_bytes(8));
803 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
804 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
805 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
806 $ztdMysqli->query(sprintf(
807 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
808 $table
809 ));
810 $ztdMysqli->query(sprintf(
811 "INSERT INTO `%s` (name, age) VALUES ('Diana', 28)",
812 $table
813 ));
814
815 $result = $ztdMysqli->query(sprintf('SELECT * FROM `%s` WHERE age > 30', $table));
816 self::assertNotFalse($result);
817 self::assertInstanceOf(mysqli_result::class, $result);
818
819 $rows = $result->fetch_all(MYSQLI_ASSOC);
820 self::assertCount(1, $rows);
821 self::assertSame('Charlie', $rows[0]['name']);
822 } finally {
823 $container->stop();
824 }
825 }
826
827 public function testPhysicalDatabaseRemainsUnchangedAfterMutations(): void
828 {
829 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
830 $endpoint = $container->getData(Endpoint::class);
831 try {
832 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
833 $rawMysqli->set_charset('utf8mb4');
834 $table = 'prefix_' . bin2hex(random_bytes(8));
835 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
836 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
837 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
838 $ztdMysqli->query(sprintf(
839 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
840 $table
841 ));
842 $ztdMysqli->query(sprintf(
843 "INSERT INTO `%s` (name, age) VALUES ('Diana', 28)",
844 $table
845 ));
846
847 $result = $rawMysqli->query(sprintf('SELECT * FROM `%s` ORDER BY id', $table));
848 self::assertNotFalse($result);
849 self::assertInstanceOf(mysqli_result::class, $result);
850
851 $rows = $result->fetch_all(MYSQLI_ASSOC);
852 self::assertCount(2, $rows);
853 self::assertSame('Alice', $rows[0]['name']);
854 self::assertSame('Bob', $rows[1]['name']);
855 } finally {
856 $container->stop();
857 }
858 }
859
860 public function testEnableDisableToggle(): void
861 {
862 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
863 $endpoint = $container->getData(Endpoint::class);
864 try {
865 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
866 $rawMysqli->set_charset('utf8mb4');
867 $table = 'prefix_' . bin2hex(random_bytes(8));
868 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
869 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
870 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
871 self::assertTrue($ztdMysqli->isZtdEnabled());
872
873 $ztdMysqli->disableZtd();
874 self::assertFalse($ztdMysqli->isZtdEnabled());
875
876 $ztdMysqli->enableZtd();
877 self::assertTrue($ztdMysqli->isZtdEnabled());
878 } finally {
879 $container->stop();
880 }
881 }
882
883 public function testDisableZtdBypassesRewriting(): void
884 {
885 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
886 $endpoint = $container->getData(Endpoint::class);
887 try {
888 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
889 $rawMysqli->set_charset('utf8mb4');
890 $table = 'prefix_' . bin2hex(random_bytes(8));
891 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
892 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
893 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
894 $ztdMysqli->disableZtd();
895
896 $ztdMysqli->query(sprintf(
897 "INSERT INTO `%s` (name, age) VALUES ('Direct', 40)",
898 $table
899 ));
900
901 $result = $rawMysqli->query(sprintf('SELECT * FROM `%s`', $table));
902 self::assertNotFalse($result);
903 self::assertInstanceOf(mysqli_result::class, $result);
904
905 $rows = $result->fetch_all(MYSQLI_ASSOC);
906 self::assertCount(3, $rows);
907
908 $rawMysqli->query(sprintf("DELETE FROM `%s` WHERE name = 'Direct'", $table));
909 } finally {
910 $container->stop();
911 }
912 }
913
914 public function testPreparedStatementSelectWithZtd(): void
915 {
916 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
917 $endpoint = $container->getData(Endpoint::class);
918 try {
919 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
920 $rawMysqli->set_charset('utf8mb4');
921 $table = 'prefix_' . bin2hex(random_bytes(8));
922 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
923 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
924 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
925 $ztdMysqli->query(sprintf(
926 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
927 $table
928 ));
929
930 $stmt = $ztdMysqli->prepare(sprintf('SELECT * FROM `%s` WHERE name = ?', $table));
931 self::assertNotFalse($stmt);
932
933 $name = 'Charlie';
934 $stmt->bind_param('s', $name);
935 $stmt->execute();
936 $result = $stmt->get_result();
937 self::assertNotFalse($result);
938
939 $rows = $result->fetch_all(MYSQLI_ASSOC);
940 self::assertCount(1, $rows);
941 self::assertSame('Charlie', $rows[0]['name']);
942 } finally {
943 $container->stop();
944 }
945 }
946
947 public function testPreparedStatementSelectNonExistent(): void
948 {
949 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
950 $endpoint = $container->getData(Endpoint::class);
951 try {
952 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
953 $rawMysqli->set_charset('utf8mb4');
954 $table = 'prefix_' . bin2hex(random_bytes(8));
955 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
956 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
957 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
958 $ztdMysqli->query(sprintf(
959 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
960 $table
961 ));
962
963 $stmt = $ztdMysqli->prepare(sprintf('SELECT * FROM `%s` WHERE name = ?', $table));
964 self::assertNotFalse($stmt);
965
966 $name = 'Alice';
967 $stmt->bind_param('s', $name);
968 $stmt->execute();
969 $result = $stmt->get_result();
970 self::assertNotFalse($result);
971
972 $rows = $result->fetch_all(MYSQLI_ASSOC);
973 self::assertCount(0, $rows);
974 } finally {
975 $container->stop();
976 }
977 }
978
979 public function testAffectedRowsAfterInsert(): void
980 {
981 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
982 $endpoint = $container->getData(Endpoint::class);
983 try {
984 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
985 $rawMysqli->set_charset('utf8mb4');
986 $table = 'prefix_' . bin2hex(random_bytes(8));
987 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
988 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
989 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
990 $ztdMysqli->query(sprintf(
991 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
992 $table
993 ));
994
995 self::assertSame(1, $ztdMysqli->lastAffectedRows());
996 } finally {
997 $container->stop();
998 }
999 }
1000
1001 public function testAffectedRowsAfterMultipleInserts(): void
1002 {
1003 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
1004 $endpoint = $container->getData(Endpoint::class);
1005 try {
1006 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
1007 $rawMysqli->set_charset('utf8mb4');
1008 $table = 'prefix_' . bin2hex(random_bytes(8));
1009 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
1010 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
1011 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
1012 $ztdMysqli->query(sprintf(
1013 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
1014 $table
1015 ));
1016 self::assertSame(1, $ztdMysqli->lastAffectedRows());
1017
1018 $ztdMysqli->query(sprintf(
1019 "INSERT INTO `%s` (name, age) VALUES ('Diana', 28)",
1020 $table
1021 ));
1022 self::assertSame(1, $ztdMysqli->lastAffectedRows());
1023 } finally {
1024 $container->stop();
1025 }
1026 }
1027
1028 public function testExecuteQuerySelect(): void
1029 {
1030 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
1031 $endpoint = $container->getData(Endpoint::class);
1032 try {
1033 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
1034 $rawMysqli->set_charset('utf8mb4');
1035 $table = 'prefix_' . bin2hex(random_bytes(8));
1036 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
1037 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
1038 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
1039 $ztdMysqli->query(sprintf(
1040 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
1041 $table
1042 ));
1043
1044 $result = $ztdMysqli->execute_query(
1045 sprintf('SELECT * FROM `%s` WHERE name = ?', $table),
1046 ['Charlie']
1047 );
1048 self::assertNotFalse($result);
1049 self::assertInstanceOf(mysqli_result::class, $result);
1050
1051 $rows = $result->fetch_all(MYSQLI_ASSOC);
1052 self::assertCount(1, $rows);
1053 self::assertSame('Charlie', $rows[0]['name']);
1054 } finally {
1055 $container->stop();
1056 }
1057 }
1058
1059 public function testRealQueryInsert(): void
1060 {
1061 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
1062 $endpoint = $container->getData(Endpoint::class);
1063 try {
1064 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
1065 $rawMysqli->set_charset('utf8mb4');
1066 $table = 'prefix_' . bin2hex(random_bytes(8));
1067 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL)', $table));
1068 $rawMysqli->query(sprintf("INSERT INTO `%s` (name, age) VALUES ('Alice', 30), ('Bob', 25)", $table));
1069 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
1070 $result = $ztdMysqli->real_query(sprintf(
1071 "INSERT INTO `%s` (name, age) VALUES ('Charlie', 35)",
1072 $table
1073 ));
1074 self::assertTrue($result);
1075
1076 $selectResult = $ztdMysqli->query(sprintf('SELECT * FROM `%s`', $table));
1077 self::assertNotFalse($selectResult);
1078 self::assertInstanceOf(mysqli_result::class, $selectResult);
1079
1080 $rows = $selectResult->fetch_all(MYSQLI_ASSOC);
1081 self::assertCount(1, $rows);
1082 } finally {
1083 $container->stop();
1084 }
1085 }
1086
1087 public function testPreparedBackslashesRoundTripWithoutMysqlEscapeCorruption(): void
1088 {
1089 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
1090 $endpoint = $container->getData(Endpoint::class);
1091 try {
1092 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
1093 $rawMysqli->set_charset('utf8mb4');
1094 $table = 'typed_' . bin2hex(random_bytes(8));
1095 $rawMysqli->query(sprintf('CREATE TABLE `%s` (id INT PRIMARY KEY, value VARCHAR(255))', $table));
1096 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
1097
1098 $insert = $ztdMysqli->prepare(sprintf('INSERT INTO `%s` (id, value) VALUES (?, ?)', $table));
1099 self::assertNotFalse($insert);
1100 $id = 1;
1101 $value = 'path\\to\\file';
1102 self::assertTrue($insert->bind_param('is', $id, $value));
1103 self::assertTrue($insert->execute());
1104
1105 $result = $ztdMysqli->query(sprintf('SELECT value FROM `%s` WHERE id = 1', $table));
1106 self::assertNotFalse($result);
1107 self::assertInstanceOf(mysqli_result::class, $result);
1108 self::assertSame([['value' => $value]], $result->fetch_all(MYSQLI_ASSOC));
1109 } finally {
1110 $container->stop();
1111 }
1112 }
1113
1114 public function testRangePartitionSelectionMatchesShadowRows(): void
1115 {
1116 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
1117 $endpoint = $container->getData(Endpoint::class);
1118 try {
1119 $rawMysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
1120 $rawMysqli->set_charset('utf8mb4');
1121 $rawMysqli->query('CREATE TABLE events (id INT NOT NULL, event_date DATE NOT NULL, '
1122 . 'PRIMARY KEY (id, event_date)) PARTITION BY RANGE (YEAR(event_date)) ('
1123 . 'PARTITION p2023 VALUES LESS THAN (2024), '
1124 . 'PARTITION p2024 VALUES LESS THAN (2025), '
1125 . 'PARTITION pmax VALUES LESS THAN MAXVALUE)');
1126 $ztdMysqli = ZtdMysqli::fromMysqli($rawMysqli, null);
1127
1128 self::assertNotFalse($ztdMysqli->query('INSERT INTO events VALUES '
1129 . "(1, '2023-06-01'), (2, '2024-01-15'), (3, '2024-11-20'), (4, '2025-02-01')"));
1130
1131 $selected = $ztdMysqli->query('SELECT id FROM events PARTITION (p2024) ORDER BY id');
1132 self::assertInstanceOf(mysqli_result::class, $selected);
1133 self::assertSame([['id' => 2], ['id' => 3]], $selected->fetch_all(MYSQLI_ASSOC));
1134
1135 $combined = $ztdMysqli->query('SELECT e.id FROM events PARTITION (p2023, pmax) e ORDER BY e.id');
1136 self::assertInstanceOf(mysqli_result::class, $combined);
1137 self::assertSame([['id' => 1], ['id' => 4]], $combined->fetch_all(MYSQLI_ASSOC));
1138
1139 $physical = $rawMysqli->query('SELECT COUNT(*) AS total FROM events');
1140 self::assertInstanceOf(mysqli_result::class, $physical);
1141 self::assertSame([['total' => '0']], $physical->fetch_all(MYSQLI_ASSOC));
1142 } finally {
1143 $container->stop();
1144 }
1145 }
1146 public function testYearShorthandMatchesNativeCoercionWithoutPhysicalWrites(): void
1147 {
1148 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
1149 $endpoint = $container->getData(Endpoint::class);
1150 try {
1151 $native = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
1152 $native->set_charset('utf8mb4');
1153 $native->query('CREATE TABLE years (id INT PRIMARY KEY, value YEAR)');
1154 $ztd = ZtdMysqli::fromMysqli($native);
1155 self::assertNotFalse($ztd->query("INSERT INTO years VALUES (1, 78), (2, 69), (3, 0), (4, '0')"));
1156 self::assertNotFalse($ztd->query('INSERT INTO years (id, value) SELECT 5, 93'));
1157 $result = $ztd->query('SELECT value FROM years ORDER BY id');
1158 self::assertInstanceOf(mysqli_result::class, $result);
1159 self::assertSame([['value' => 1978], ['value' => 2069], ['value' => 0], ['value' => 2000], ['value' => 1993]], $result->fetch_all(MYSQLI_ASSOC));
1160 $physical = $native->query('SELECT * FROM years');
1161 self::assertInstanceOf(mysqli_result::class, $physical);
1162 self::assertSame([], $physical->fetch_all(MYSQLI_ASSOC));
1163 } finally {
1164 $container->stop();
1165 }
1166 }
1167
1168 public function testNativeConstructorCreatesAnIsolatedDefaultSession(): void
1169 {
1170 $container = Testcontainers::run(MySqlRelease::fromEnvironment());
1171 $endpoint = $container->getData(Endpoint::class);
1172 try {
1173 $native = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
1174 $native->set_charset('utf8mb4');
1175 $database = 'test';
1176 $native->query('CREATE TABLE users (id INT)');
1177 $ztd = new ZtdMysqli($endpoint->host, $endpoint->username, $endpoint->password, $database, $endpoint->port);
1178 self::assertTrue($ztd->isZtdEnabled());
1179 self::assertNotFalse($ztd->query('INSERT INTO users VALUES (42)'));
1180 $simulated = $ztd->query('SELECT * FROM users');
1181 self::assertInstanceOf(mysqli_result::class, $simulated);
1182 self::assertSame([['id' => 42]], $simulated->fetch_all(MYSQLI_ASSOC));
1183 $physical = $native->query('SELECT * FROM users');
1184 self::assertInstanceOf(mysqli_result::class, $physical);
1185 self::assertSame([], $physical->fetch_all(MYSQLI_ASSOC));
1186 self::assertTrue($ztd->close());
1187 } finally {
1188 $container->stop();
1189 }
1190 }
1191
1192}
1193