packages/ztd-query-mysql/tests/Unit/Rewrite/Transformer/SelectTransformerTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite\Transformer;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use RuntimeException;
11use ZtdQuery\Platform\CastRenderer;
12use ZtdQuery\Platform\IdentifierQuoter;
13use ZtdQuery\Platform\MySql\Rewrite\FullText\MySqlFullTextSearchRewriter;
14use ZtdQuery\Platform\MySql\Rewrite\Partition\MySqlPartitionSelectionRewriter;
15use ZtdQuery\Platform\MySql\Rewrite\Transformer\SelectTransformer;
16use ZtdQuery\Platform\MySql\Rewrite\Type\MySqlTypeSemantics;
17use ZtdQuery\Platform\MySql\Sql\MySqlIdentifierQuoter;
18use ZtdQuery\Platform\MySql\Sql\Value\MySqlCastRenderer;
19use ZtdQuery\Platform\ValueRenderer;
20use ZtdQuery\Schema\ColumnDeclaration;
21use ZtdQuery\Schema\ColumnTypeFamily;
22use ZtdQuery\Schema\Partition\TablePartitioning;
23
24#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Cte\HeaderParser::class)]
25#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Cte\IdentifierReferences::class)]
26#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\ReferenceReader::class)]
27#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\FullText\ExpressionEditor::class)]
28#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\SelectionReader::class)]
29#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\SourceProjection::class)]
30
31#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\CastTypeResolver::class)]
32#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Type\RankEdits::class)]
33#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\ScalarExpression::class)]
34#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\StringCoercion::class)]
35#[CoversClass(SelectTransformer::class)]
36#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\MySqlSelectRelationParser::class)]
37#[UsesClass(MySqlCastRenderer::class)]
38#[UsesClass(MySqlIdentifierQuoter::class)]
39#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\MySqlValueRenderer::class)]
40#[UsesClass(MySqlTypeSemantics::class)]
41#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Cte\MySqlCteShadowComposer::class)]
42#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\GeneratedColumn\MySqlGeneratedColumnProjector::class)]
43#[UsesClass(MySqlPartitionSelectionRewriter::class)]
44#[UsesClass(MySqlFullTextSearchRewriter::class)]
45#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
46#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Set\OrderRewriter::class)]
47#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Set\ValueNormalizer::class)]
48#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Shadow\CteRows::class)]
49final class SelectTransformerTest extends TestCase
50{
51 public function testTransformsPartitionSelectionBeforeComposingShadowCte(): void
52 {
53 $result = (new SelectTransformer())->transform('SELECT id FROM events PARTITION (p0)', [
54 'events' => [
55 'rows' => [['id' => 1], ['id' => 11]],
56 'columns' => ['id'],
57 'columnTypes' => [],
58 'partitioning' => new TablePartitioning(['p0' => 'id < 10']),
59 ],
60 ]);
61
62 self::assertStringStartsWith('WITH `events` AS', $result);
63 self::assertStringContainsString(
64 'SELECT id FROM (SELECT * FROM events WHERE (id < 10)) AS events',
65 $result,
66 );
67 }
68
69 public function testGeneratedColumnsAreRecomputedFromBaseRow(): void
70 {
71 $result = (new SelectTransformer())->transform('SELECT total FROM orders', [
72 'orders' => [
73 'rows' => [['qty' => 5, 'unit_price' => 10, 'total' => null]],
74 'columns' => ['qty', 'unit_price', 'total'],
75 'columnTypes' => [],
76 'generatedExpressions' => ['total' => '(qty * unit_price)'],
77 ],
78 ]);
79
80 self::assertStringContainsString('(qty * unit_price) AS `total`', $result);
81 self::assertStringContainsString('AS `__ztd_generated_source`', $result);
82 }
83
84 public function testTransformMaterializesViewAfterItsShadowTable(): void
85 {
86 $tables = [
87 'users' => [
88 'rows' => [['id' => 1]],
89 'columns' => ['id'],
90 'columnTypes' => [],
91 ],
92 'active_users' => [
93 'viewSql' => 'SELECT * FROM users WHERE id > 0',
94 ],
95 'active_user_count' => [
96 'viewSql' => 'SELECT count(*) AS total FROM active_users',
97 ],
98 ];
99
100 self::assertSame(
101 "WITH `users` AS (SELECT CAST(1 AS SIGNED) AS `id`),\n`active_users` AS (SELECT * FROM users WHERE id > 0),\n`active_user_count` AS (SELECT count(*) AS total FROM active_users)\nSELECT * FROM active_user_count",
102 (new SelectTransformer())->transform('SELECT * FROM active_user_count', $tables),
103 );
104 }
105
106 public function testUsesInjectedValueRenderer(): void
107 {
108 $valueRenderer = self::createStub(ValueRenderer::class);
109 $valueRenderer->method('renderValue')->willReturn('CUSTOM_VALUE');
110 $transformer = new SelectTransformer(null, null, $valueRenderer);
111 $tables = [
112 'users' => [
113 'rows' => [['id' => 1]],
114 'columns' => ['id'],
115 'columnTypes' => [],
116 ],
117 ];
118
119 self::assertStringContainsString('CUSTOM_VALUE AS `id`', $transformer->transform('SELECT * FROM users', $tables));
120 }
121
122
123
124
125
126
127
128
129
130 public function testTransformWithNoTablesReturnsOriginalSql(): void
131 {
132 $transformer = new SelectTransformer();
133 $sql = 'SELECT 1';
134 $result = $transformer->transform($sql, []);
135 self::assertSame($sql, $result);
136 }
137
138 public function testTransformWithShadowDataAddsCte(): void
139 {
140 $transformer = new SelectTransformer();
141 $sql = 'SELECT * FROM users';
142 $tables = [
143 'users' => [
144 'rows' => [['id' => '1', 'name' => 'Alice']],
145 'columns' => ['id', 'name'],
146 'columnTypes' => [],
147 ],
148 ];
149
150 $result = $transformer->transform($sql, $tables);
151 self::assertStringStartsWith('WITH `users` AS (SELECT', $result);
152 self::assertStringContainsString('AS `id`', $result);
153 self::assertStringContainsString('AS `name`', $result);
154 self::assertStringEndsWith('SELECT * FROM users', $result);
155 }
156
157 public function testTransformWithEmptyTableProducesEmptyCte(): void
158 {
159 $transformer = new SelectTransformer();
160 $sql = 'SELECT * FROM users';
161 $tables = [
162 'users' => [
163 'rows' => [],
164 'columns' => ['id', 'name'],
165 'columnTypes' => [],
166 ],
167 ];
168
169 $result = $transformer->transform($sql, $tables);
170 self::assertStringContainsString('`users` AS (SELECT', $result);
171 self::assertStringContainsString('FROM DUAL WHERE 0)', $result);
172 self::assertStringContainsString('AS `id`', $result);
173 self::assertStringContainsString('AS `name`', $result);
174 }
175
176 public function testTransformSkipsUnreferencedTables(): void
177 {
178 $transformer = new SelectTransformer();
179 $sql = 'SELECT 1';
180 $tables = [
181 'orders' => [
182 'rows' => [['id' => '1']],
183 'columns' => ['id'],
184 'columnTypes' => [],
185 ],
186 ];
187
188 $result = $transformer->transform($sql, $tables);
189 self::assertSame($sql, $result);
190 }
191
192 public function testTransformNullValueProducesNull(): void
193 {
194 $transformer = new SelectTransformer();
195 $sql = 'SELECT * FROM users';
196 $tables = [
197 'users' => [
198 'rows' => [['id' => 1, 'name' => null]],
199 'columns' => ['id', 'name'],
200 'columnTypes' => [],
201 ],
202 ];
203
204 $result = $transformer->transform($sql, $tables);
205 self::assertStringContainsString('NULL AS `name`', $result);
206 }
207
208 public function testTransformBoolValueProducesTrueOrFalse(): void
209 {
210 $transformer = new SelectTransformer();
211 $sql = 'SELECT * FROM flags';
212 $tables = [
213 'flags' => [
214 'rows' => [['active' => true, 'deleted' => false]],
215 'columns' => ['active', 'deleted'],
216 'columnTypes' => [],
217 ],
218 ];
219
220 $result = $transformer->transform($sql, $tables);
221 self::assertStringContainsString('TRUE AS `active`', $result);
222 self::assertStringContainsString('FALSE AS `deleted`', $result);
223 }
224
225 public function testTransformFloatValueProducesStringRepresentation(): void
226 {
227 $transformer = new SelectTransformer();
228 $sql = 'SELECT * FROM prices';
229 $tables = [
230 'prices' => [
231 'rows' => [['amount' => 3.14]],
232 'columns' => ['amount'],
233 'columnTypes' => [],
234 ],
235 ];
236
237 $result = $transformer->transform($sql, $tables);
238 self::assertStringContainsString('3.14 AS `amount`', $result);
239 }
240
241 public function testTransformIntValueProducesCastExpression(): void
242 {
243 $transformer = new SelectTransformer();
244 $sql = 'SELECT * FROM nums';
245 $tables = [
246 'nums' => [
247 'rows' => [['val' => 42]],
248 'columns' => ['val'],
249 'columnTypes' => [],
250 ],
251 ];
252
253 $result = $transformer->transform($sql, $tables);
254 self::assertStringContainsString('42', $result);
255 self::assertStringContainsString('AS `val`', $result);
256 }
257
258 public function testTransformStringValueProducesQuotedCast(): void
259 {
260 $transformer = new SelectTransformer();
261 $sql = 'SELECT * FROM names';
262 $tables = [
263 'names' => [
264 'rows' => [['val' => 'hello']],
265 'columns' => ['val'],
266 'columnTypes' => [],
267 ],
268 ];
269
270 $result = $transformer->transform($sql, $tables);
271 self::assertStringContainsString("'hello'", $result);
272 self::assertStringContainsString('AS `val`', $result);
273 }
274
275 public function testTransformWithColumnTypesAppliesCast(): void
276 {
277 $transformer = new SelectTransformer();
278 $sql = 'SELECT * FROM typed';
279 $tables = [
280 'typed' => [
281 'rows' => [['val' => '42']],
282 'columns' => ['val'],
283 'columnTypes' => ['val' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT')],
284 ],
285 ];
286
287 $result = $transformer->transform($sql, $tables);
288 self::assertStringContainsString('CAST', $result);
289 self::assertStringContainsString('AS `val`', $result);
290 }
291
292 public function testTransformWithEmptyRowsAndColumnTypesUsesNullCast(): void
293 {
294 $transformer = new SelectTransformer();
295 $sql = 'SELECT * FROM typed';
296 $tables = [
297 'typed' => [
298 'rows' => [],
299 'columns' => ['val'],
300 'columnTypes' => ['val' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT')],
301 ],
302 ];
303
304 $result = $transformer->transform($sql, $tables);
305 self::assertStringContainsString('CAST(NULL', $result);
306 self::assertStringContainsString('AS `val`', $result);
307 self::assertStringContainsString('WHERE 0', $result);
308 }
309
310 public function testTransformMultipleRowsUsesUnionAll(): void
311 {
312 $transformer = new SelectTransformer();
313 $sql = 'SELECT * FROM users';
314 $tables = [
315 'users' => [
316 'rows' => [
317 ['id' => 1, 'name' => 'Alice'],
318 ['id' => 2, 'name' => 'Bob'],
319 ],
320 'columns' => ['id', 'name'],
321 'columnTypes' => [],
322 ],
323 ];
324
325 $result = $transformer->transform($sql, $tables);
326 self::assertStringContainsString('UNION ALL', $result);
327 self::assertStringContainsString('`users` AS (SELECT', $result);
328 self::assertStringContainsString('SELECT * FROM users', $result);
329 self::assertSame(2, substr_count($result, 'AS `id`'));
330 self::assertSame(2, substr_count($result, 'AS `name`'));
331 }
332
333 public function testTransformEmptyColumnsAndRowsSkipsTable(): void
334 {
335 $transformer = new SelectTransformer();
336 $sql = 'SELECT * FROM users';
337 $tables = [
338 'users' => [
339 'rows' => [],
340 'columns' => [],
341 'columnTypes' => [],
342 ],
343 ];
344
345 $result = $transformer->transform($sql, $tables);
346 self::assertStringNotContainsString('WITH', $result);
347 }
348
349 public function testTransformColumnsInferredFromRowsWhenEmpty(): void
350 {
351 $transformer = new SelectTransformer();
352 $sql = 'SELECT * FROM users';
353 $tables = [
354 'users' => [
355 'rows' => [['id' => 1, 'name' => 'Alice']],
356 'columns' => [],
357 'columnTypes' => [],
358 ],
359 ];
360
361 $result = $transformer->transform($sql, $tables);
362 self::assertStringContainsString('AS `id`', $result);
363 self::assertStringContainsString('AS `name`', $result);
364 }
365
366 public function testTransformWithExistingWithClausePrependsCtesCorrectly(): void
367 {
368 $transformer = new SelectTransformer();
369 $sql = 'WITH cte AS (SELECT 1) SELECT * FROM users, cte';
370 $tables = [
371 'users' => [
372 'rows' => [['id' => 1]],
373 'columns' => ['id'],
374 'columnTypes' => [],
375 ],
376 ];
377
378 $result = $transformer->transform($sql, $tables);
379 self::assertStringStartsWith('WITH `users` AS', $result);
380 self::assertStringContainsString('cte AS (SELECT 1)', $result);
381 self::assertStringContainsString('SELECT * FROM users, cte', $result);
382 }
383
384 public function testTransformSetColumnOrderByRewriting(): void
385 {
386 $transformer = new SelectTransformer();
387 $sql = 'SELECT * FROM items ORDER BY `colors`';
388 $tables = [
389 'items' => [
390 'rows' => [['id' => 1, 'colors' => 'red,blue']],
391 'columns' => ['id', 'colors'],
392 'columnTypes' => [
393 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
394 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue','green')"),
395 ],
396 ],
397 ];
398
399 $result = $transformer->transform($sql, $tables);
400 self::assertStringContainsString('FIND_IN_SET', $result);
401 }
402
403 public function testTransformOrderByWithNoSetColumnsUnchanged(): void
404 {
405 $transformer = new SelectTransformer();
406 $sql = 'SELECT * FROM items ORDER BY `name`';
407 $tables = [
408 'items' => [
409 'rows' => [['id' => 1, 'name' => 'Alice']],
410 'columns' => ['id', 'name'],
411 'columnTypes' => [
412 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
413 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'),
414 ],
415 ],
416 ];
417
418 $result = $transformer->transform($sql, $tables);
419 self::assertStringNotContainsString('FIND_IN_SET', $result);
420 }
421
422 public function testTransformSetValueNormalization(): void
423 {
424 $transformer = new SelectTransformer();
425 $sql = 'SELECT * FROM items';
426 $tables = [
427 'items' => [
428 'rows' => [['colors' => 'blue,red']],
429 'columns' => ['colors'],
430 'columnTypes' => [
431 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue','green')"),
432 ],
433 ],
434 ];
435
436 $result = $transformer->transform($sql, $tables);
437 self::assertStringContainsString("'red,blue'", $result);
438 }
439
440 public function testTransformEmptySetValuePreserved(): void
441 {
442 $transformer = new SelectTransformer();
443 $sql = 'SELECT * FROM items';
444 $tables = [
445 'items' => [
446 'rows' => [['colors' => '']],
447 'columns' => ['colors'],
448 'columnTypes' => [
449 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
450 ],
451 ],
452 ];
453
454 $result = $transformer->transform($sql, $tables);
455 self::assertStringContainsString("''", $result);
456 }
457
458 public function testTransformMultipleTablesProducesMultipleCtes(): void
459 {
460 $transformer = new SelectTransformer();
461 $sql = 'SELECT * FROM users JOIN orders ON users.id = orders.user_id';
462 $tables = [
463 'users' => [
464 'rows' => [['id' => 1]],
465 'columns' => ['id'],
466 'columnTypes' => [],
467 ],
468 'orders' => [
469 'rows' => [['id' => 10, 'user_id' => 1]],
470 'columns' => ['id', 'user_id'],
471 'columnTypes' => [],
472 ],
473 ];
474
475 $result = $transformer->transform($sql, $tables);
476 self::assertStringContainsString('`users` AS', $result);
477 self::assertStringContainsString('`orders` AS', $result);
478 }
479
480 public function testTransformUnsupportedValueTypeThrows(): void
481 {
482 $transformer = new SelectTransformer();
483 $sql = 'SELECT * FROM data';
484 $tables = [
485 'data' => [
486 'rows' => [['val' => [1, 2, 3]]],
487 'columns' => ['val'],
488 'columnTypes' => [],
489 ],
490 ];
491
492 self::expectException(RuntimeException::class);
493 self::expectExceptionMessage('Unsupported value type');
494 $transformer->transform($sql, $tables);
495 }
496
497 public function testTransformWithFallbackNullCastWhenNoColumnType(): void
498 {
499 $transformer = new SelectTransformer();
500 $sql = 'SELECT * FROM items';
501 $tables = [
502 'items' => [
503 'rows' => [],
504 'columns' => ['name'],
505 'columnTypes' => [],
506 ],
507 ];
508
509 $result = $transformer->transform($sql, $tables);
510 self::assertStringContainsString('CAST(NULL', $result);
511 self::assertStringContainsString('WHERE 0', $result);
512 }
513
514 public function testTransformEscapesSingleQuotesInValues(): void
515 {
516 $transformer = new SelectTransformer();
517 $sql = 'SELECT * FROM users';
518 $tables = [
519 'users' => [
520 'rows' => [['name' => "O'Brien"]],
521 'columns' => ['name'],
522 'columnTypes' => [],
523 ],
524 ];
525
526 $result = $transformer->transform($sql, $tables);
527 self::assertStringContainsString("O''Brien", $result);
528 }
529
530 public function testTransformQualifiedSetOrderByWithDirection(): void
531 {
532 $transformer = new SelectTransformer();
533 $sql = 'SELECT * FROM items ORDER BY `items`.`colors` DESC';
534 $tables = [
535 'items' => [
536 'rows' => [['id' => 1, 'colors' => 'red']],
537 'columns' => ['id', 'colors'],
538 'columnTypes' => [
539 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
540 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
541 ],
542 ],
543 ];
544
545 $result = $transformer->transform($sql, $tables);
546 self::assertStringContainsString('FIND_IN_SET', $result);
547 self::assertStringContainsString('DESC', $result);
548 }
549
550 public function testTransformRowsWithNoColumnsAndRowsExistInfersFromRows(): void
551 {
552 $transformer = new SelectTransformer();
553 $sql = 'SELECT * FROM data';
554 $tables = [
555 'data' => [
556 'rows' => [
557 ['a' => 1],
558 ['a' => 2, 'b' => 3],
559 ],
560 'columns' => [],
561 'columnTypes' => [],
562 ],
563 ];
564
565 $result = $transformer->transform($sql, $tables);
566 self::assertStringContainsString('AS `a`', $result);
567 self::assertStringContainsString('AS `b`', $result);
568 }
569
570 public function testTransformSingleRowCteFormat(): void
571 {
572 $transformer = new SelectTransformer();
573 $sql = 'SELECT * FROM t';
574 $tables = [
575 't' => [
576 'rows' => [['a' => 'x']],
577 'columns' => ['a'],
578 'columnTypes' => ['a' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)')],
579 ],
580 ];
581
582 $result = $transformer->transform($sql, $tables);
583 self::assertMatchesRegularExpression('/^WITH `t` AS \(SELECT .+ AS `a`\)\nSELECT \* FROM t$/', $result);
584 }
585
586 public function testTransformEmptyCteContainsDualAndWhere0(): void
587 {
588 $transformer = new SelectTransformer();
589 $sql = 'SELECT * FROM t';
590 $tables = [
591 't' => [
592 'rows' => [],
593 'columns' => ['a'],
594 'columnTypes' => ['a' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT')],
595 ],
596 ];
597
598 $result = $transformer->transform($sql, $tables);
599 self::assertMatchesRegularExpression('/`t` AS \(SELECT .+ AS `a` FROM DUAL WHERE 0\)/', $result);
600 }
601
602 public function testTransformCteWithMultipleTablesJoinedByComma(): void
603 {
604 $transformer = new SelectTransformer();
605 $sql = 'SELECT * FROM a JOIN b ON a.id = b.aid';
606 $tables = [
607 'a' => [
608 'rows' => [],
609 'columns' => ['id'],
610 'columnTypes' => [],
611 ],
612 'b' => [
613 'rows' => [],
614 'columns' => ['aid'],
615 'columnTypes' => [],
616 ],
617 ];
618
619 $result = $transformer->transform($sql, $tables);
620 self::assertStringContainsString('`a` AS', $result);
621 self::assertStringContainsString('`b` AS', $result);
622 $withCount = substr_count($result, 'WITH');
623 self::assertSame(1, $withCount);
624 }
625
626 public function testTransformSetOrderByMultipleItems(): void
627 {
628 $transformer = new SelectTransformer();
629 $sql = 'SELECT * FROM items ORDER BY `colors`, `id` ASC';
630 $tables = [
631 'items' => [
632 'rows' => [['id' => 1, 'colors' => 'red']],
633 'columns' => ['id', 'colors'],
634 'columnTypes' => [
635 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
636 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
637 ],
638 ],
639 ];
640
641 $result = $transformer->transform($sql, $tables);
642 self::assertStringContainsString('FIND_IN_SET', $result);
643 self::assertStringContainsString('`id` ASC', $result);
644 }
645
646 public function testTransformSetOrderByBitPowerCalculation(): void
647 {
648 $transformer = new SelectTransformer();
649 $sql = 'SELECT * FROM items ORDER BY `colors`';
650 $tables = [
651 'items' => [
652 'rows' => [['id' => 1, 'colors' => 'red']],
653 'columns' => ['id', 'colors'],
654 'columnTypes' => [
655 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
656 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue','green')"),
657 ],
658 ],
659 ];
660
661 $result = $transformer->transform($sql, $tables);
662 self::assertStringContainsString("IF(FIND_IN_SET('red'", $result);
663 self::assertStringContainsString(', 1, 0)', $result);
664 self::assertStringContainsString("IF(FIND_IN_SET('blue'", $result);
665 self::assertStringContainsString(', 2, 0)', $result);
666 self::assertStringContainsString("IF(FIND_IN_SET('green'", $result);
667 self::assertStringContainsString(', 4, 0)', $result);
668 }
669
670 public function testTransformNoOrderBySkipsRewrite(): void
671 {
672 $transformer = new SelectTransformer();
673 $sql = 'SELECT * FROM items WHERE id = 1';
674 $tables = [
675 'items' => [
676 'rows' => [['id' => 1, 'colors' => 'red']],
677 'columns' => ['id', 'colors'],
678 'columnTypes' => [
679 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
680 ],
681 ],
682 ];
683
684 $result = $transformer->transform($sql, $tables);
685 self::assertStringNotContainsString('FIND_IN_SET', $result);
686 }
687
688 public function testTransformSetNormalizationReordersMembers(): void
689 {
690 $transformer = new SelectTransformer();
691 $sql = 'SELECT * FROM t';
692 $tables = [
693 't' => [
694 'rows' => [['s' => 'blue,red']],
695 'columns' => ['s'],
696 'columnTypes' => [
697 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue','green')"),
698 ],
699 ],
700 ];
701
702 $result = $transformer->transform($sql, $tables);
703 self::assertStringContainsString("'red,blue'", $result);
704 self::assertStringNotContainsString("'blue,red'", $result);
705 }
706
707 public function testTransformSetNormalizationFiltersInvalidMembers(): void
708 {
709 $transformer = new SelectTransformer();
710 $sql = 'SELECT * FROM t';
711 $tables = [
712 't' => [
713 'rows' => [['s' => 'red,invalid']],
714 'columns' => ['s'],
715 'columnTypes' => [
716 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
717 ],
718 ],
719 ];
720
721 $result = $transformer->transform($sql, $tables);
722 self::assertStringContainsString("'red'", $result);
723 }
724
725 public function testTransformEmptyColumnsEmptyRowsThrowsForRowsWithoutColumns(): void
726 {
727 $transformer = new SelectTransformer();
728 $sql = 'SELECT * FROM t';
729 $tables = [
730 't' => [
731 'rows' => [['a' => 1]],
732 'columns' => [],
733 'columnTypes' => [],
734 ],
735 ];
736
737 $result = $transformer->transform($sql, $tables);
738 self::assertStringContainsString('AS `a`', $result);
739 }
740
741 public function testTransformStringWithQuotesEscapesCorrectly(): void
742 {
743 $transformer = new SelectTransformer();
744 $sql = 'SELECT * FROM t';
745 $tables = [
746 't' => [
747 'rows' => [['name' => "it's"]],
748 'columns' => ['name'],
749 'columnTypes' => ['name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)')],
750 ],
751 ];
752
753 $result = $transformer->transform($sql, $tables);
754 self::assertStringContainsString("it''s", $result);
755 }
756
757 public function testTransformSkipsUnreferencedFirstTableProcessesSecond(): void
758 {
759 $transformer = new SelectTransformer();
760 $sql = 'SELECT * FROM orders';
761 $tables = [
762 'users' => [
763 'rows' => [['id' => 1]],
764 'columns' => ['id'],
765 'columnTypes' => [],
766 ],
767 'orders' => [
768 'rows' => [['oid' => 10]],
769 'columns' => ['oid'],
770 'columnTypes' => [],
771 ],
772 ];
773
774 $result = $transformer->transform($sql, $tables);
775 self::assertStringContainsString('`orders` AS', $result);
776 self::assertStringNotContainsString('`users` AS', $result);
777 }
778
779 public function testTransformSkipsEmptyFirstTableProcessesSecond(): void
780 {
781 $transformer = new SelectTransformer();
782 $sql = 'SELECT * FROM users, orders';
783 $tables = [
784 'users' => [
785 'rows' => [],
786 'columns' => [],
787 'columnTypes' => [],
788 ],
789 'orders' => [
790 'rows' => [['oid' => 10]],
791 'columns' => ['oid'],
792 'columnTypes' => [],
793 ],
794 ];
795
796 $result = $transformer->transform($sql, $tables);
797 self::assertStringContainsString('`orders` AS', $result);
798 }
799
800 public function testTransformObjectWithToStringProducesStringValue(): void
801 {
802 $obj = new class () {
803 public function __toString(): string
804 {
805 return 'stringified';
806 }
807 };
808 $transformer = new SelectTransformer();
809 $sql = 'SELECT * FROM t';
810 $tables = [
811 't' => [
812 'rows' => [['val' => $obj]],
813 'columns' => ['val'],
814 'columnTypes' => [],
815 ],
816 ];
817
818 $result = $transformer->transform($sql, $tables);
819 self::assertStringContainsString('stringified', $result);
820 }
821
822 public function testTransformSetOrderByWithLimitClause(): void
823 {
824 $transformer = new SelectTransformer();
825 $sql = 'SELECT * FROM items ORDER BY `colors` LIMIT 10';
826 $tables = [
827 'items' => [
828 'rows' => [['id' => 1, 'colors' => 'red']],
829 'columns' => ['id', 'colors'],
830 'columnTypes' => [
831 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
832 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
833 ],
834 ],
835 ];
836
837 $result = $transformer->transform($sql, $tables);
838 self::assertStringContainsString('FIND_IN_SET', $result);
839 self::assertStringContainsString('LIMIT 10', $result);
840 }
841
842 public function testTransformSetNormalizationAllInvalid(): void
843 {
844 $transformer = new SelectTransformer();
845 $sql = 'SELECT * FROM t';
846 $tables = [
847 't' => [
848 'rows' => [['s' => 'invalid,also_invalid']],
849 'columns' => ['s'],
850 'columnTypes' => [
851 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
852 ],
853 ],
854 ];
855
856 $result = $transformer->transform($sql, $tables);
857 self::assertStringContainsString("'invalid,also_invalid'", $result);
858 }
859
860 public function testTransformSetNormalizationWithNonSetType(): void
861 {
862 $transformer = new SelectTransformer();
863 $sql = 'SELECT * FROM t';
864 $tables = [
865 't' => [
866 'rows' => [['s' => 'hello']],
867 'columns' => ['s'],
868 'columnTypes' => [
869 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'),
870 ],
871 ],
872 ];
873
874 $result = $transformer->transform($sql, $tables);
875 self::assertStringContainsString("'hello'", $result);
876 }
877
878 public function testTransformSetNormalizationDoubleQuotedMembers(): void
879 {
880 $transformer = new SelectTransformer();
881 $sql = 'SELECT * FROM t';
882 $tables = [
883 't' => [
884 'rows' => [['s' => 'b,a']],
885 'columns' => ['s'],
886 'columnTypes' => [
887 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET("a","b","c")'),
888 ],
889 ],
890 ];
891
892 $result = $transformer->transform($sql, $tables);
893 self::assertStringContainsString("'a,b'", $result);
894 }
895
896 public function testTransformSetNormalizationUnquotedFallback(): void
897 {
898 $transformer = new SelectTransformer();
899 $sql = 'SELECT * FROM t';
900 $tables = [
901 't' => [
902 'rows' => [['s' => 'b,a']],
903 'columns' => ['s'],
904 'columnTypes' => [
905 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET(a,b,c)'),
906 ],
907 ],
908 ];
909
910 $result = $transformer->transform($sql, $tables);
911 self::assertStringContainsString("'a,b'", $result);
912 }
913
914 public function testTransformSetOrderBySkipsUnreferencedTableInRewrite(): void
915 {
916 $transformer = new SelectTransformer();
917 $sql = 'SELECT * FROM items ORDER BY `colors`';
918 $tables = [
919 'other' => [
920 'rows' => [['id' => 1]],
921 'columns' => ['id'],
922 'columnTypes' => [
923 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
924 ],
925 ],
926 'items' => [
927 'rows' => [['id' => 1, 'colors' => 'red']],
928 'columns' => ['id', 'colors'],
929 'columnTypes' => [
930 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
931 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
932 ],
933 ],
934 ];
935
936 $result = $transformer->transform($sql, $tables);
937 self::assertStringContainsString('FIND_IN_SET', $result);
938 }
939
940 public function testTransformSetOrderByConflictingSetColumnsAcrossTables(): void
941 {
942 $transformer = new SelectTransformer();
943 $sql = 'SELECT * FROM a JOIN b ON a.id = b.id ORDER BY `colors`';
944 $tables = [
945 'a' => [
946 'rows' => [['id' => 1, 'colors' => 'red']],
947 'columns' => ['id', 'colors'],
948 'columnTypes' => [
949 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
950 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
951 ],
952 ],
953 'b' => [
954 'rows' => [['id' => 1, 'colors' => 'x']],
955 'columns' => ['id', 'colors'],
956 'columnTypes' => [
957 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
958 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('x','y')"),
959 ],
960 ],
961 ];
962
963 $result = $transformer->transform($sql, $tables);
964 self::assertStringNotContainsString('FIND_IN_SET', $result);
965 self::assertStringContainsString('ORDER BY `colors`', $result);
966 }
967
968 public function testTransformSetOrderByNonMatchingColumn(): void
969 {
970 $transformer = new SelectTransformer();
971 $sql = 'SELECT * FROM items ORDER BY `name`';
972 $tables = [
973 'items' => [
974 'rows' => [['id' => 1, 'colors' => 'red', 'name' => 'A']],
975 'columns' => ['id', 'colors', 'name'],
976 'columnTypes' => [
977 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
978 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
979 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'),
980 ],
981 ],
982 ];
983
984 $result = $transformer->transform($sql, $tables);
985 self::assertStringNotContainsString('FIND_IN_SET', $result);
986 }
987
988 public function testTransformSetNormalizationEmptyDeclaredList(): void
989 {
990 $transformer = new SelectTransformer();
991 $sql = 'SELECT * FROM t';
992 $tables = [
993 't' => [
994 'rows' => [['s' => 'val']],
995 'columns' => ['s'],
996 'columnTypes' => [
997 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET()'),
998 ],
999 ],
1000 ];
1001
1002 $result = $transformer->transform($sql, $tables);
1003 self::assertStringContainsString("'val'", $result);
1004 }
1005
1006 public function testTransformWithExistingWithAndLeadingComment(): void
1007 {
1008 $transformer = new SelectTransformer();
1009 $sql = '/* comment */WITH cte AS (SELECT 1) SELECT * FROM users, cte';
1010 $tables = [
1011 'users' => [
1012 'rows' => [['id' => 1]],
1013 'columns' => ['id'],
1014 'columnTypes' => [],
1015 ],
1016 ];
1017
1018 $result = $transformer->transform($sql, $tables);
1019 self::assertStringContainsString('`users` AS', $result);
1020 self::assertStringContainsString('cte AS (SELECT 1)', $result);
1021 }
1022
1023 public function testTransformSetOrderByExactRankTermFormat(): void
1024 {
1025 $transformer = new SelectTransformer();
1026 $sql = 'SELECT * FROM items ORDER BY `colors` DESC';
1027 $tables = [
1028 'items' => [
1029 'rows' => [['id' => 1, 'colors' => 'a']],
1030 'columns' => ['id', 'colors'],
1031 'columnTypes' => [
1032 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1033 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b')"),
1034 ],
1035 ],
1036 ];
1037
1038 $result = $transformer->transform($sql, $tables);
1039 self::assertMatchesRegularExpression('/ORDER BY\s+\(/', $result);
1040 self::assertStringContainsString(') DESC', $result);
1041 self::assertStringContainsString(' + ', $result);
1042 }
1043
1044 public function testTransformSetOrderByAscDirection(): void
1045 {
1046 $transformer = new SelectTransformer();
1047 $sql = 'SELECT * FROM items ORDER BY `colors` ASC';
1048 $tables = [
1049 'items' => [
1050 'rows' => [['id' => 1, 'colors' => 'x']],
1051 'columns' => ['id', 'colors'],
1052 'columnTypes' => [
1053 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1054 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('x','y')"),
1055 ],
1056 ],
1057 ];
1058
1059 $result = $transformer->transform($sql, $tables);
1060 self::assertMatchesRegularExpression('/ORDER BY\s+\(/', $result);
1061 self::assertStringContainsString(') ASC', $result);
1062 }
1063
1064 public function testTransformCteOutputContainsWithAndAs(): void
1065 {
1066 $transformer = new SelectTransformer();
1067 $sql = 'SELECT * FROM t';
1068 $tables = [
1069 't' => [
1070 'rows' => [['a' => 1, 'b' => 'hello']],
1071 'columns' => ['a', 'b'],
1072 'columnTypes' => [],
1073 ],
1074 ];
1075
1076 $result = $transformer->transform($sql, $tables);
1077 self::assertStringStartsWith('WITH', $result);
1078 self::assertStringContainsString('`t` AS', $result);
1079 self::assertStringContainsString('AS `a`', $result);
1080 self::assertStringContainsString('AS `b`', $result);
1081 self::assertStringContainsString('SELECT * FROM t', $result);
1082 }
1083
1084 public function testTransformMultiTableCteOutputContainsBothCtes(): void
1085 {
1086 $transformer = new SelectTransformer();
1087 $sql = 'SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id';
1088 $tables = [
1089 't1' => [
1090 'rows' => [['id' => 1]],
1091 'columns' => ['id'],
1092 'columnTypes' => [],
1093 ],
1094 't2' => [
1095 'rows' => [['t1_id' => 1, 'val' => 'x']],
1096 'columns' => ['t1_id', 'val'],
1097 'columnTypes' => [],
1098 ],
1099 ];
1100
1101 $result = $transformer->transform($sql, $tables);
1102 self::assertStringStartsWith('WITH', $result);
1103 self::assertStringContainsString('`t1` AS', $result);
1104 self::assertStringContainsString('`t2` AS', $result);
1105 self::assertStringContainsString('AS `id`', $result);
1106 self::assertStringContainsString('AS `val`', $result);
1107 }
1108
1109 public function testTransformRowWithNullValueProducesNullLiteral(): void
1110 {
1111 $transformer = new SelectTransformer();
1112 $sql = 'SELECT * FROM t';
1113 $tables = [
1114 't' => [
1115 'rows' => [['id' => 1, 'name' => null]],
1116 'columns' => ['id', 'name'],
1117 'columnTypes' => [],
1118 ],
1119 ];
1120
1121 $result = $transformer->transform($sql, $tables);
1122 self::assertStringContainsString('NULL AS `name`', $result);
1123 }
1124
1125 public function testTransformRowWithIntValueProducesCastSigned(): void
1126 {
1127 $transformer = new SelectTransformer();
1128 $sql = 'SELECT * FROM t';
1129 $tables = [
1130 't' => [
1131 'rows' => [['id' => 42, 'amt' => 99]],
1132 'columns' => ['id', 'amt'],
1133 'columnTypes' => [],
1134 ],
1135 ];
1136
1137 $result = $transformer->transform($sql, $tables);
1138 self::assertStringContainsString('CAST(42 AS SIGNED) AS `id`', $result);
1139 self::assertStringContainsString('CAST(99 AS SIGNED) AS `amt`', $result);
1140 }
1141
1142 public function testTransformRowWithFloatValueProducesCast(): void
1143 {
1144 $transformer = new SelectTransformer();
1145 $sql = 'SELECT * FROM t';
1146 $tables = [
1147 't' => [
1148 'rows' => [['id' => 1, 'price' => 9.99]],
1149 'columns' => ['id', 'price'],
1150 'columnTypes' => [],
1151 ],
1152 ];
1153
1154 $result = $transformer->transform($sql, $tables);
1155 self::assertStringContainsString('AS `price`', $result);
1156 self::assertStringContainsString('9.99', $result);
1157 }
1158
1159 public function testTransformRowWithBoolTrueValueProducesTrue(): void
1160 {
1161 $transformer = new SelectTransformer();
1162 $sql = 'SELECT * FROM t';
1163 $tables = [
1164 't' => [
1165 'rows' => [['id' => 1, 'active' => true]],
1166 'columns' => ['id', 'active'],
1167 'columnTypes' => [],
1168 ],
1169 ];
1170
1171 $result = $transformer->transform($sql, $tables);
1172 self::assertStringContainsString('TRUE AS `active`', $result);
1173 }
1174
1175 public function testTransformRowWithBoolFalseValueProducesFalse(): void
1176 {
1177 $transformer = new SelectTransformer();
1178 $sql = 'SELECT * FROM t';
1179 $tables = [
1180 't' => [
1181 'rows' => [['id' => 1, 'active' => false]],
1182 'columns' => ['id', 'active'],
1183 'columnTypes' => [],
1184 ],
1185 ];
1186
1187 $result = $transformer->transform($sql, $tables);
1188 self::assertStringContainsString('FALSE AS `active`', $result);
1189 }
1190
1191 public function testTransformRowWithStringValueProducesQuotedLiteral(): void
1192 {
1193 $transformer = new SelectTransformer();
1194 $sql = 'SELECT * FROM t';
1195 $tables = [
1196 't' => [
1197 'rows' => [['id' => 1, 'name' => 'Alice']],
1198 'columns' => ['id', 'name'],
1199 'columnTypes' => [],
1200 ],
1201 ];
1202
1203 $result = $transformer->transform($sql, $tables);
1204 self::assertStringContainsString("'Alice'", $result);
1205 self::assertStringContainsString('AS `name`', $result);
1206 }
1207
1208 public function testTransformMultipleRowsUnionAll(): void
1209 {
1210 $transformer = new SelectTransformer();
1211 $sql = 'SELECT * FROM t';
1212 $tables = [
1213 't' => [
1214 'rows' => [['id' => 1, 'v' => 'a'], ['id' => 2, 'v' => 'b']],
1215 'columns' => ['id', 'v'],
1216 'columnTypes' => [],
1217 ],
1218 ];
1219
1220 $result = $transformer->transform($sql, $tables);
1221 self::assertStringContainsString('UNION ALL', $result);
1222 self::assertStringContainsString('AS `id`', $result);
1223 self::assertStringContainsString("'a'", $result);
1224 self::assertStringContainsString("'b'", $result);
1225 self::assertStringContainsString('AS `v`', $result);
1226 }
1227
1228 public function testTransformEmptyRowsProducesWhereZero(): void
1229 {
1230 $transformer = new SelectTransformer();
1231 $sql = 'SELECT * FROM t';
1232 $tables = [
1233 't' => [
1234 'rows' => [],
1235 'columns' => ['id', 'name'],
1236 'columnTypes' => [],
1237 ],
1238 ];
1239
1240 $result = $transformer->transform($sql, $tables);
1241 self::assertStringContainsString('WHERE 0', $result);
1242 }
1243
1244 public function testTransformWithCastForIntegerColumn(): void
1245 {
1246 $transformer = new SelectTransformer();
1247 $sql = 'SELECT * FROM t';
1248 $tables = [
1249 't' => [
1250 'rows' => [['id' => 1]],
1251 'columns' => ['id'],
1252 'columnTypes' => [
1253 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1254 ],
1255 ],
1256 ];
1257
1258 $result = $transformer->transform($sql, $tables);
1259 self::assertStringContainsString('AS `id`', $result);
1260 self::assertStringContainsString('CAST(', $result);
1261 self::assertStringContainsString('SIGNED', $result);
1262 }
1263
1264 public function testTransformSetNormalizationWithEscapedSingleQuoteInMember(): void
1265 {
1266 $transformer = new SelectTransformer();
1267 $sql = 'SELECT * FROM t';
1268 $tables = [
1269 't' => [
1270 'rows' => [['s' => "it's"]],
1271 'columns' => ['s'],
1272 'columnTypes' => [
1273 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('it''s','me')"),
1274 ],
1275 ],
1276 ];
1277
1278 $result = $transformer->transform($sql, $tables);
1279 self::assertStringContainsString("it''s", $result);
1280 }
1281
1282 public function testTransformSetNormalizationWithEscapedDoubleQuoteInMember(): void
1283 {
1284 $transformer = new SelectTransformer();
1285 $sql = 'SELECT * FROM t';
1286 $tables = [
1287 't' => [
1288 'rows' => [['s' => 'say "hi"']],
1289 'columns' => ['s'],
1290 'columnTypes' => [
1291 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET("say ""hi""","other")'),
1292 ],
1293 ],
1294 ];
1295
1296 $result = $transformer->transform($sql, $tables);
1297 self::assertStringContainsString('say "hi"', $result);
1298 }
1299
1300 public function testTransformSetOrderByWithLowercaseSetType(): void
1301 {
1302 $transformer = new SelectTransformer();
1303 $sql = 'SELECT * FROM items ORDER BY `colors`';
1304 $tables = [
1305 'items' => [
1306 'rows' => [['id' => 1, 'colors' => 'red']],
1307 'columns' => ['id', 'colors'],
1308 'columnTypes' => [
1309 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1310 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "set('red','blue')"),
1311 ],
1312 ],
1313 ];
1314
1315 $result = $transformer->transform($sql, $tables);
1316 self::assertStringContainsString('FIND_IN_SET', $result);
1317 }
1318
1319 public function testTransformSetOrderByQualifiedColumnRefUsesTableAndColumn(): void
1320 {
1321 $transformer = new SelectTransformer();
1322 $sql = 'SELECT * FROM items ORDER BY `items`.`colors` ASC';
1323 $tables = [
1324 'items' => [
1325 'rows' => [['id' => 1, 'colors' => 'red']],
1326 'columns' => ['id', 'colors'],
1327 'columnTypes' => [
1328 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1329 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1330 ],
1331 ],
1332 ];
1333
1334 $result = $transformer->transform($sql, $tables);
1335 self::assertStringContainsString('FIND_IN_SET', $result);
1336 self::assertStringContainsString('`items`.`colors`', $result);
1337 self::assertStringContainsString(') ASC', $result);
1338 }
1339
1340 public function testTransformSetNormalizationLowercaseSetType(): void
1341 {
1342 $transformer = new SelectTransformer();
1343 $sql = 'SELECT * FROM t';
1344 $tables = [
1345 't' => [
1346 'rows' => [['s' => 'b,a']],
1347 'columns' => ['s'],
1348 'columnTypes' => [
1349 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "set('a','b','c')"),
1350 ],
1351 ],
1352 ];
1353
1354 $result = $transformer->transform($sql, $tables);
1355 self::assertStringContainsString("'a,b'", $result);
1356 }
1357
1358 public function testTransformSetOrderByExtractsCorrectMembersFromDefinition(): void
1359 {
1360 $transformer = new SelectTransformer();
1361 $sql = 'SELECT * FROM items ORDER BY `colors`';
1362 $tables = [
1363 'items' => [
1364 'rows' => [['id' => 1, 'colors' => 'red']],
1365 'columns' => ['id', 'colors'],
1366 'columnTypes' => [
1367 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1368 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue','green')"),
1369 ],
1370 ],
1371 ];
1372
1373 $result = $transformer->transform($sql, $tables);
1374 self::assertStringContainsString("FIND_IN_SET('red'", $result);
1375 self::assertStringContainsString("FIND_IN_SET('blue'", $result);
1376 self::assertStringContainsString("FIND_IN_SET('green'", $result);
1377 }
1378
1379 public function testTransformSetOrderByDoubleQuotedMembersExtractedCorrectly(): void
1380 {
1381 $transformer = new SelectTransformer();
1382 $sql = 'SELECT * FROM items ORDER BY `colors`';
1383 $tables = [
1384 'items' => [
1385 'rows' => [['id' => 1, 'colors' => 'alpha']],
1386 'columns' => ['id', 'colors'],
1387 'columnTypes' => [
1388 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1389 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET("alpha","beta")'),
1390 ],
1391 ],
1392 ];
1393
1394 $result = $transformer->transform($sql, $tables);
1395 self::assertStringContainsString("FIND_IN_SET('alpha'", $result);
1396 self::assertStringContainsString("FIND_IN_SET('beta'", $result);
1397 }
1398
1399 public function testTransformSetNormalizationPreservesOrderWhenAlreadySorted(): void
1400 {
1401 $transformer = new SelectTransformer();
1402 $sql = 'SELECT * FROM t';
1403 $tables = [
1404 't' => [
1405 'rows' => [['s' => 'a,b']],
1406 'columns' => ['s'],
1407 'columnTypes' => [
1408 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b','c')"),
1409 ],
1410 ],
1411 ];
1412
1413 $result = $transformer->transform($sql, $tables);
1414 self::assertStringContainsString("'a,b'", $result);
1415 }
1416
1417 public function testTransformConstructorDefaultsCastRenderer(): void
1418 {
1419 $transformer = new SelectTransformer(null, null);
1420 $sql = 'SELECT * FROM t';
1421 $tables = [
1422 't' => [
1423 'rows' => [['id' => 1]],
1424 'columns' => ['id'],
1425 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT')],
1426 ],
1427 ];
1428
1429 $result = $transformer->transform($sql, $tables);
1430 self::assertStringContainsString('CAST(', $result);
1431 }
1432
1433 public function testTransformConstructorExplicitCastRenderer(): void
1434 {
1435 $castRenderer = new MySqlCastRenderer();
1436 $quoter = new MySqlIdentifierQuoter();
1437 $transformer = new SelectTransformer($castRenderer, $quoter);
1438 $sql = 'SELECT * FROM t';
1439 $tables = [
1440 't' => [
1441 'rows' => [['id' => 1]],
1442 'columns' => ['id'],
1443 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT')],
1444 ],
1445 ];
1446
1447 $result = $transformer->transform($sql, $tables);
1448 self::assertStringContainsString('CAST(', $result);
1449 self::assertStringContainsString('AS `id`', $result);
1450 }
1451
1452 public function testTransformWithExistingWithClausePrependsCteBeforeExisting(): void
1453 {
1454 $transformer = new SelectTransformer();
1455 $sql = 'WITH existing AS (SELECT 1 AS x) SELECT * FROM users, existing';
1456 $tables = [
1457 'users' => [
1458 'rows' => [['id' => 1]],
1459 'columns' => ['id'],
1460 'columnTypes' => [],
1461 ],
1462 ];
1463
1464 $result = $transformer->transform($sql, $tables);
1465 $usersPos = strpos($result, '`users` AS');
1466 $existingPos = strpos($result, 'existing AS (SELECT 1');
1467 self::assertNotFalse($usersPos);
1468 self::assertNotFalse($existingPos);
1469 self::assertLessThan($existingPos, $usersPos);
1470 }
1471
1472 public function testTransformSetOrderByForClausePreservesForUpdateSuffix(): void
1473 {
1474 $transformer = new SelectTransformer();
1475 $sql = 'SELECT * FROM items ORDER BY `colors` FOR UPDATE';
1476 $tables = [
1477 'items' => [
1478 'rows' => [['id' => 1, 'colors' => 'red']],
1479 'columns' => ['id', 'colors'],
1480 'columnTypes' => [
1481 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1482 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1483 ],
1484 ],
1485 ];
1486
1487 $result = $transformer->transform($sql, $tables);
1488 self::assertStringContainsString('FIND_IN_SET', $result);
1489 self::assertStringContainsString('FOR UPDATE', $result);
1490 }
1491
1492 public function testTransformSetOrderByEmptyClauseNotRewritten(): void
1493 {
1494 $transformer = new SelectTransformer();
1495 $sql = 'SELECT * FROM items ORDER BY LIMIT 10';
1496 $tables = [
1497 'items' => [
1498 'rows' => [['id' => 1, 'colors' => 'red']],
1499 'columns' => ['id', 'colors'],
1500 'columnTypes' => [
1501 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1502 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1503 ],
1504 ],
1505 ];
1506
1507 $result = $transformer->transform($sql, $tables);
1508 self::assertStringContainsString('ORDER BY', $result);
1509 }
1510
1511 public function testTransformSetNormalizationTrimsCandidates(): void
1512 {
1513 $transformer = new SelectTransformer();
1514 $sql = 'SELECT * FROM t';
1515 $tables = [
1516 't' => [
1517 'rows' => [['s' => ' a , b ']],
1518 'columns' => ['s'],
1519 'columnTypes' => [
1520 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b','c')"),
1521 ],
1522 ],
1523 ];
1524
1525 $result = $transformer->transform($sql, $tables);
1526 self::assertStringContainsString("'a,b'", $result);
1527 }
1528
1529 public function testTransformSetNormalizationEmptyCandidateSkipped(): void
1530 {
1531 $transformer = new SelectTransformer();
1532 $sql = 'SELECT * FROM t';
1533 $tables = [
1534 't' => [
1535 'rows' => [['s' => 'a,,b']],
1536 'columns' => ['s'],
1537 'columnTypes' => [
1538 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b','c')"),
1539 ],
1540 ],
1541 ];
1542
1543 $result = $transformer->transform($sql, $tables);
1544 self::assertStringContainsString("'a,b'", $result);
1545 }
1546
1547 public function testTransformSetNormalizationWithNonSetStringType(): void
1548 {
1549 $transformer = new SelectTransformer();
1550 $sql = 'SELECT * FROM t';
1551 $tables = [
1552 't' => [
1553 'rows' => [['s' => 'hello']],
1554 'columns' => ['s'],
1555 'columnTypes' => [
1556 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'ENUM(\'a\',\'b\')'),
1557 ],
1558 ],
1559 ];
1560
1561 $result = $transformer->transform($sql, $tables);
1562 self::assertStringContainsString("'hello'", $result);
1563 }
1564
1565 public function testTransformEnumOrderByUsesDeclarationRank(): void
1566 {
1567 $transformer = new SelectTransformer();
1568 $sql = 'SELECT * FROM items ORDER BY `status`';
1569 $tables = [
1570 'items' => [
1571 'rows' => [['id' => 1, 'status' => 'active']],
1572 'columns' => ['id', 'status'],
1573 'columnTypes' => [
1574 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1575 'status' => new ColumnDeclaration(ColumnTypeFamily::STRING, "ENUM('active','inactive')"),
1576 ],
1577 ],
1578 ];
1579
1580 $result = $transformer->transform($sql, $tables);
1581 self::assertStringContainsString("ORDER BY FIELD(`status`, 'active', 'inactive')", $result);
1582 }
1583
1584 public function testTransformSetOrderByWithNonBacktickedColumnNotRewritten(): void
1585 {
1586 $transformer = new SelectTransformer();
1587 $sql = 'SELECT * FROM items ORDER BY colors';
1588 $tables = [
1589 'items' => [
1590 'rows' => [['id' => 1, 'colors' => 'red']],
1591 'columns' => ['id', 'colors'],
1592 'columnTypes' => [
1593 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1594 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1595 ],
1596 ],
1597 ];
1598
1599 $result = $transformer->transform($sql, $tables);
1600 self::assertStringNotContainsString('FIND_IN_SET', $result);
1601 }
1602
1603 public function testTransformSetNormalizationDoubleQuotedMemberWithEscapedQuote(): void
1604 {
1605 $transformer = new SelectTransformer();
1606 $sql = 'SELECT * FROM t';
1607 $tables = [
1608 't' => [
1609 'rows' => [['s' => 'say "hi"']],
1610 'columns' => ['s'],
1611 'columnTypes' => [
1612 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET("say ""hi""","other")'),
1613 ],
1614 ],
1615 ];
1616
1617 $result = $transformer->transform($sql, $tables);
1618 self::assertStringContainsString('say "hi"', $result);
1619 }
1620
1621 public function testTransformRowWithMissingColumnUsesNull(): void
1622 {
1623 $transformer = new SelectTransformer();
1624 $sql = 'SELECT * FROM t';
1625 $tables = [
1626 't' => [
1627 'rows' => [['a' => 1]],
1628 'columns' => ['a', 'b'],
1629 'columnTypes' => [],
1630 ],
1631 ];
1632
1633 $result = $transformer->transform($sql, $tables);
1634 self::assertStringContainsString('NULL AS `b`', $result);
1635 }
1636
1637 public function testTransformSetNormalizationWithUnquotedEmptyTokensSkipped(): void
1638 {
1639 $transformer = new SelectTransformer();
1640 $sql = 'SELECT * FROM t';
1641 $tables = [
1642 't' => [
1643 'rows' => [['s' => 'a']],
1644 'columns' => ['s'],
1645 'columnTypes' => [
1646 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET(a, ,b)'),
1647 ],
1648 ],
1649 ];
1650
1651 $result = $transformer->transform($sql, $tables);
1652 self::assertStringContainsString("'a'", $result);
1653 }
1654
1655 public function testTransformWithExistingWithClauseProducesCommaBetweenCtes(): void
1656 {
1657 $transformer = new SelectTransformer();
1658 $sql = 'WITH existing AS (SELECT 1 AS x) SELECT * FROM users, existing';
1659 $tables = [
1660 'users' => [
1661 'rows' => [['id' => 1]],
1662 'columns' => ['id'],
1663 'columnTypes' => [],
1664 ],
1665 ];
1666
1667 $result = $transformer->transform($sql, $tables);
1668 self::assertSame(1, substr_count($result, 'WITH'));
1669 }
1670
1671 public function testTransformSetOrderByMultipleItemsMixedColumnTypes(): void
1672 {
1673 $transformer = new SelectTransformer();
1674 $sql = 'SELECT * FROM items ORDER BY `colors`, `name`';
1675 $tables = [
1676 'items' => [
1677 'rows' => [['id' => 1, 'colors' => 'red', 'name' => 'A']],
1678 'columns' => ['id', 'colors', 'name'],
1679 'columnTypes' => [
1680 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1681 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1682 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'),
1683 ],
1684 ],
1685 ];
1686
1687 $result = $transformer->transform($sql, $tables);
1688 self::assertStringContainsString('FIND_IN_SET', $result);
1689 self::assertStringContainsString('`name`', $result);
1690 }
1691
1692 public function testTransformSetOrderByBitRankUsesCorrectPowerOfTwo(): void
1693 {
1694 $transformer = new SelectTransformer();
1695 $sql = 'SELECT * FROM items ORDER BY `colors`';
1696 $tables = [
1697 'items' => [
1698 'rows' => [['id' => 1, 'colors' => 'a']],
1699 'columns' => ['id', 'colors'],
1700 'columnTypes' => [
1701 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1702 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b','c')"),
1703 ],
1704 ],
1705 ];
1706
1707 $result = $transformer->transform($sql, $tables);
1708 self::assertStringContainsString(', 1, 0)', $result);
1709 self::assertStringContainsString(', 2, 0)', $result);
1710 self::assertStringContainsString(', 4, 0)', $result);
1711 }
1712
1713 public function testTransformSetNormalizationReordersUnsortedValue(): void
1714 {
1715 $transformer = new SelectTransformer();
1716 $sql = 'SELECT * FROM t';
1717 $tables = [
1718 't' => [
1719 'rows' => [['s' => 'c,a']],
1720 'columns' => ['s'],
1721 'columnTypes' => [
1722 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b','c')"),
1723 ],
1724 ],
1725 ];
1726
1727 $result = $transformer->transform($sql, $tables);
1728 self::assertStringContainsString("'a,c'", $result);
1729 }
1730
1731 public function testTransformSetNormalizationSkipsUnknownMember(): void
1732 {
1733 $transformer = new SelectTransformer();
1734 $sql = 'SELECT * FROM t';
1735 $tables = [
1736 't' => [
1737 'rows' => [['s' => 'a,unknown,b']],
1738 'columns' => ['s'],
1739 'columnTypes' => [
1740 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b','c')"),
1741 ],
1742 ],
1743 ];
1744
1745 $result = $transformer->transform($sql, $tables);
1746 self::assertStringContainsString("'a,b'", $result);
1747 }
1748
1749 public function testTransformSetNormalizationAllUnknownReturnsOriginal(): void
1750 {
1751 $transformer = new SelectTransformer();
1752 $sql = 'SELECT * FROM t';
1753 $tables = [
1754 't' => [
1755 'rows' => [['s' => 'x,y']],
1756 'columns' => ['s'],
1757 'columnTypes' => [
1758 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b')"),
1759 ],
1760 ],
1761 ];
1762
1763 $result = $transformer->transform($sql, $tables);
1764 self::assertStringContainsString("'x,y'", $result);
1765 }
1766
1767 public function testTransformSetNormalizationEmptyValueReturnsEmpty(): void
1768 {
1769 $transformer = new SelectTransformer();
1770 $sql = 'SELECT * FROM t';
1771 $tables = [
1772 't' => [
1773 'rows' => [['s' => '']],
1774 'columns' => ['s'],
1775 'columnTypes' => [
1776 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b')"),
1777 ],
1778 ],
1779 ];
1780
1781 $result = $transformer->transform($sql, $tables);
1782 self::assertStringContainsString("''", $result);
1783 }
1784
1785 public function testTransformSetOrderByNoOrderByKeywordSkipsRewrite(): void
1786 {
1787 $transformer = new SelectTransformer();
1788 $sql = 'SELECT * FROM items WHERE id = 1';
1789 $tables = [
1790 'items' => [
1791 'rows' => [['id' => 1, 'colors' => 'red']],
1792 'columns' => ['id', 'colors'],
1793 'columnTypes' => [
1794 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1795 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1796 ],
1797 ],
1798 ];
1799
1800 $result = $transformer->transform($sql, $tables);
1801 self::assertStringNotContainsString('FIND_IN_SET', $result);
1802 }
1803
1804 public function testTransformSetOrderByLimitSuffixPreserved(): void
1805 {
1806 $transformer = new SelectTransformer();
1807 $sql = 'SELECT * FROM items ORDER BY `colors` LIMIT 10';
1808 $tables = [
1809 'items' => [
1810 'rows' => [['id' => 1, 'colors' => 'red']],
1811 'columns' => ['id', 'colors'],
1812 'columnTypes' => [
1813 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1814 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1815 ],
1816 ],
1817 ];
1818
1819 $result = $transformer->transform($sql, $tables);
1820 self::assertStringContainsString('FIND_IN_SET', $result);
1821 self::assertStringContainsString('LIMIT 10', $result);
1822 }
1823
1824 public function testTransformSetOrderByLockSuffixPreserved(): void
1825 {
1826 $transformer = new SelectTransformer();
1827 $sql = 'SELECT * FROM items ORDER BY `colors` LOCK IN SHARE MODE';
1828 $tables = [
1829 'items' => [
1830 'rows' => [['id' => 1, 'colors' => 'red']],
1831 'columns' => ['id', 'colors'],
1832 'columnTypes' => [
1833 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1834 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1835 ],
1836 ],
1837 ];
1838
1839 $result = $transformer->transform($sql, $tables);
1840 self::assertStringContainsString('FIND_IN_SET', $result);
1841 self::assertStringContainsString('LOCK IN SHARE MODE', $result);
1842 }
1843
1844 public function testTransformSetOrderByWithQualifiedColumn(): void
1845 {
1846 $transformer = new SelectTransformer();
1847 $sql = 'SELECT * FROM items ORDER BY `items`.`colors`';
1848 $tables = [
1849 'items' => [
1850 'rows' => [['id' => 1, 'colors' => 'red']],
1851 'columns' => ['id', 'colors'],
1852 'columnTypes' => [
1853 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1854 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1855 ],
1856 ],
1857 ];
1858
1859 $result = $transformer->transform($sql, $tables);
1860 self::assertStringContainsString('FIND_IN_SET', $result);
1861 self::assertStringContainsString('`items`.`colors`', $result);
1862 }
1863
1864 public function testTransformSetOrderByWithDirectionPreserved(): void
1865 {
1866 $transformer = new SelectTransformer();
1867 $sql = 'SELECT * FROM items ORDER BY `colors` DESC';
1868 $tables = [
1869 'items' => [
1870 'rows' => [['id' => 1, 'colors' => 'red']],
1871 'columns' => ['id', 'colors'],
1872 'columnTypes' => [
1873 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1874 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1875 ],
1876 ],
1877 ];
1878
1879 $result = $transformer->transform($sql, $tables);
1880 self::assertStringContainsString('FIND_IN_SET', $result);
1881 self::assertStringContainsString('DESC', $result);
1882 }
1883
1884 public function testTransformSetOrderByConflictingColumnsInMultipleTablesNotRewritten(): void
1885 {
1886 $transformer = new SelectTransformer();
1887 $sql = 'SELECT * FROM items, products ORDER BY `colors`';
1888 $tables = [
1889 'items' => [
1890 'rows' => [['id' => 1, 'colors' => 'red']],
1891 'columns' => ['id', 'colors'],
1892 'columnTypes' => [
1893 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1894 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1895 ],
1896 ],
1897 'products' => [
1898 'rows' => [['id' => 1, 'colors' => 'green']],
1899 'columns' => ['id', 'colors'],
1900 'columnTypes' => [
1901 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1902 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('green','yellow')"),
1903 ],
1904 ],
1905 ];
1906
1907 $result = $transformer->transform($sql, $tables);
1908 self::assertStringNotContainsString('FIND_IN_SET', $result);
1909 }
1910
1911 public function testTransformSetNormalizationEmptyDefinitionReturnsOriginal(): void
1912 {
1913 $transformer = new SelectTransformer();
1914 $sql = 'SELECT * FROM t';
1915 $tables = [
1916 't' => [
1917 'rows' => [['s' => 'a,b']],
1918 'columns' => ['s'],
1919 'columnTypes' => [
1920 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET()'),
1921 ],
1922 ],
1923 ];
1924
1925 $result = $transformer->transform($sql, $tables);
1926 self::assertStringContainsString("'a,b'", $result);
1927 }
1928
1929 public function testTransformSetNormalizationSingleQuotedMemberWithEscapedQuote(): void
1930 {
1931 $transformer = new SelectTransformer();
1932 $sql = 'SELECT * FROM t';
1933 $tables = [
1934 't' => [
1935 'rows' => [['s' => "it's"]],
1936 'columns' => ['s'],
1937 'columnTypes' => [
1938 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('it''s','other')"),
1939 ],
1940 ],
1941 ];
1942
1943 $result = $transformer->transform($sql, $tables);
1944 self::assertStringContainsString("it''s", $result);
1945 }
1946
1947 public function testTransformSetNormalizationInvalidSetTypeFallsThrough(): void
1948 {
1949 $transformer = new SelectTransformer();
1950 $sql = 'SELECT * FROM t';
1951 $tables = [
1952 't' => [
1953 'rows' => [['s' => 'abc']],
1954 'columns' => ['s'],
1955 'columnTypes' => [
1956 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(100)'),
1957 ],
1958 ],
1959 ];
1960
1961 $result = $transformer->transform($sql, $tables);
1962 self::assertStringContainsString("'abc'", $result);
1963 }
1964
1965 public function testTransformSetOrderByWithTableNotInSqlSkipsMap(): void
1966 {
1967 $transformer = new SelectTransformer();
1968 $sql = 'SELECT * FROM items ORDER BY `colors`';
1969 $tables = [
1970 'items' => [
1971 'rows' => [['id' => 1, 'colors' => 'red']],
1972 'columns' => ['id', 'colors'],
1973 'columnTypes' => [
1974 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1975 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
1976 ],
1977 ],
1978 'other_table' => [
1979 'rows' => [['colors' => 'x']],
1980 'columns' => ['colors'],
1981 'columnTypes' => [
1982 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('x','y')"),
1983 ],
1984 ],
1985 ];
1986
1987 $result = $transformer->transform($sql, $tables);
1988 self::assertStringContainsString('FIND_IN_SET', $result);
1989 }
1990
1991 public function testTransformWithEmptyColumnsAndRowsDeriveColumnsFromRows(): void
1992 {
1993 $transformer = new SelectTransformer();
1994 $sql = 'SELECT * FROM t';
1995 $tables = [
1996 't' => [
1997 'rows' => [['a' => 1, 'b' => 2], ['a' => 3, 'c' => 4]],
1998 'columns' => [],
1999 'columnTypes' => [],
2000 ],
2001 ];
2002
2003 $result = $transformer->transform($sql, $tables);
2004 self::assertStringContainsString('AS `a`', $result);
2005 self::assertStringContainsString('AS `b`', $result);
2006 self::assertStringContainsString('AS `c`', $result);
2007 }
2008
2009 public function testTransformWithEmptyColumnsAndEmptyRowsSkipsCte(): void
2010 {
2011 $transformer = new SelectTransformer();
2012 $sql = 'SELECT * FROM t';
2013 $tables = [
2014 't' => [
2015 'rows' => [],
2016 'columns' => [],
2017 'columnTypes' => [],
2018 ],
2019 ];
2020
2021 $result = $transformer->transform($sql, $tables);
2022 self::assertSame('SELECT * FROM t', $result);
2023 }
2024
2025 public function testTransformMultipleRowsProducesUnionAll(): void
2026 {
2027 $transformer = new SelectTransformer();
2028 $sql = 'SELECT * FROM t';
2029 $tables = [
2030 't' => [
2031 'rows' => [['id' => 1], ['id' => 2]],
2032 'columns' => ['id'],
2033 'columnTypes' => [],
2034 ],
2035 ];
2036
2037 $result = $transformer->transform($sql, $tables);
2038 self::assertStringContainsString('UNION ALL', $result);
2039 }
2040
2041 public function testTransformFloatValueProducesFloat(): void
2042 {
2043 $transformer = new SelectTransformer();
2044 $sql = 'SELECT * FROM t';
2045 $tables = [
2046 't' => [
2047 'rows' => [['val' => 3.14]],
2048 'columns' => ['val'],
2049 'columnTypes' => [],
2050 ],
2051 ];
2052
2053 $result = $transformer->transform($sql, $tables);
2054 self::assertStringContainsString('3.14', $result);
2055 }
2056
2057 public function testTransformObjectWithToStringUsesToString(): void
2058 {
2059 $obj = new class () {
2060 public function __toString(): string
2061 {
2062 return 'stringified';
2063 }
2064 };
2065 $transformer = new SelectTransformer();
2066 $sql = 'SELECT * FROM t';
2067 $tables = [
2068 't' => [
2069 'rows' => [['val' => $obj]],
2070 'columns' => ['val'],
2071 'columnTypes' => [],
2072 ],
2073 ];
2074
2075 $result = $transformer->transform($sql, $tables);
2076 self::assertStringContainsString('stringified', $result);
2077 }
2078
2079 public function testTransformWithRowsButNoColumnsUsesRowKeys(): void
2080 {
2081 $transformer = new SelectTransformer();
2082 $sql = 'SELECT * FROM t';
2083 $tables = [
2084 't' => [
2085 'rows' => [['x' => 10, 'y' => 20]],
2086 'columns' => [],
2087 'columnTypes' => [],
2088 ],
2089 ];
2090
2091 $result = $transformer->transform($sql, $tables);
2092 self::assertStringContainsString('AS `x`', $result);
2093 self::assertStringContainsString('AS `y`', $result);
2094 }
2095
2096 public function testTransformSetOrderByExactBitRankExpression(): void
2097 {
2098 $transformer = new SelectTransformer();
2099 $sql = 'SELECT * FROM items ORDER BY `colors`';
2100 $tables = [
2101 'items' => [
2102 'rows' => [['id' => 1, 'colors' => 'a']],
2103 'columns' => ['id', 'colors'],
2104 'columnTypes' => [
2105 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
2106 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b')"),
2107 ],
2108 ],
2109 ];
2110
2111 $result = $transformer->transform($sql, $tables);
2112 self::assertStringContainsString("IF(FIND_IN_SET('a', `colors`) > 0, 1, 0)", $result);
2113 self::assertStringContainsString("IF(FIND_IN_SET('b', `colors`) > 0, 2, 0)", $result);
2114 }
2115
2116 public function testTransformSetOrderByQualifiedExactBitRank(): void
2117 {
2118 $transformer = new SelectTransformer();
2119 $sql = 'SELECT * FROM items ORDER BY `items`.`colors` ASC';
2120 $tables = [
2121 'items' => [
2122 'rows' => [['id' => 1, 'colors' => 'a']],
2123 'columns' => ['id', 'colors'],
2124 'columnTypes' => [
2125 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
2126 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b')"),
2127 ],
2128 ],
2129 ];
2130
2131 $result = $transformer->transform($sql, $tables);
2132 self::assertStringContainsString("FIND_IN_SET('a', `items`.`colors`)", $result);
2133 self::assertStringContainsString("FIND_IN_SET('b', `items`.`colors`)", $result);
2134 self::assertStringContainsString(' ASC', $result);
2135 }
2136
2137 public function testTransformSetNormalizationReordersEscapedSingleQuoteMember(): void
2138 {
2139 $transformer = new SelectTransformer();
2140 $sql = 'SELECT * FROM t';
2141 $tables = [
2142 't' => [
2143 'rows' => [['s' => "it's,me"]],
2144 'columns' => ['s'],
2145 'columnTypes' => [
2146 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('me','it''s')"),
2147 ],
2148 ],
2149 ];
2150
2151 $result = $transformer->transform($sql, $tables);
2152 self::assertStringContainsString("'me,it''s'", $result);
2153 }
2154
2155 public function testTransformSetNormalizationReordersEscapedDoubleQuoteMember(): void
2156 {
2157 $transformer = new SelectTransformer();
2158 $sql = 'SELECT * FROM t';
2159 $tables = [
2160 't' => [
2161 'rows' => [['s' => 'say "hi",other']],
2162 'columns' => ['s'],
2163 'columnTypes' => [
2164 's' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET("other","say ""hi""")'),
2165 ],
2166 ],
2167 ];
2168
2169 $result = $transformer->transform($sql, $tables);
2170 self::assertStringContainsString("'other,say \"hi\"'", $result);
2171 }
2172
2173 public function testTransformSetOrderByExactParenthesizedExpression(): void
2174 {
2175 $transformer = new SelectTransformer();
2176 $sql = 'SELECT * FROM items ORDER BY `colors`';
2177 $tables = [
2178 'items' => [
2179 'rows' => [['id' => 1, 'colors' => 'a']],
2180 'columns' => ['id', 'colors'],
2181 'columnTypes' => [
2182 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
2183 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b')"),
2184 ],
2185 ],
2186 ];
2187
2188 $result = $transformer->transform($sql, $tables);
2189 self::assertStringContainsString(
2190 "ORDER BY (IF(FIND_IN_SET('a', `colors`) > 0, 1, 0) + IF(FIND_IN_SET('b', `colors`) > 0, 2, 0))",
2191 $result,
2192 );
2193 }
2194
2195 public function testTransformSetOrderByEscapesSingleQuoteInFindInSet(): void
2196 {
2197 $transformer = new SelectTransformer();
2198 $sql = 'SELECT * FROM items ORDER BY `status`';
2199 $tables = [
2200 'items' => [
2201 'rows' => [['id' => 1, 'status' => "it's"]],
2202 'columns' => ['id', 'status'],
2203 'columnTypes' => [
2204 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
2205 'status' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('it''s','ok')"),
2206 ],
2207 ],
2208 ];
2209
2210 $result = $transformer->transform($sql, $tables);
2211 self::assertStringContainsString("FIND_IN_SET('it''s', `status`)", $result);
2212 }
2213
2214 public function testTransformUsesCustomCastRenderer(): void
2215 {
2216 $castRenderer = self::createStub(CastRenderer::class);
2217 $castRenderer->method('renderCast')->willReturn('CUSTOM_CAST');
2218 $transformer = new SelectTransformer($castRenderer);
2219 $sql = 'SELECT * FROM t';
2220 $tables = [
2221 't' => [
2222 'rows' => [['x' => 1]],
2223 'columns' => ['x'],
2224 'columnTypes' => [
2225 'x' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
2226 ],
2227 ],
2228 ];
2229
2230 $result = $transformer->transform($sql, $tables);
2231 self::assertStringContainsString('CUSTOM_CAST', $result);
2232 }
2233
2234 public function testTransformUsesCustomIdentifierQuoter(): void
2235 {
2236 $quoter = self::createStub(IdentifierQuoter::class);
2237 $quoter->method('quote')->willReturnCallback(static fn (string $id): string => "[$id]");
2238 $transformer = new SelectTransformer(null, $quoter);
2239 $sql = 'SELECT * FROM t';
2240 $tables = [
2241 't' => [
2242 'rows' => [['x' => 1]],
2243 'columns' => ['x'],
2244 'columnTypes' => [],
2245 ],
2246 ];
2247
2248 $result = $transformer->transform($sql, $tables);
2249 self::assertStringContainsString('[t]', $result);
2250 self::assertStringContainsString('[x]', $result);
2251 }
2252
2253 public function testTransformSetOrderByDirectionPlacedAfterClosingParen(): void
2254 {
2255 $transformer = new SelectTransformer();
2256 $sql = 'SELECT * FROM items ORDER BY `colors` DESC';
2257 $tables = [
2258 'items' => [
2259 'rows' => [['id' => 1, 'colors' => 'a']],
2260 'columns' => ['id', 'colors'],
2261 'columnTypes' => [
2262 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
2263 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('a','b')"),
2264 ],
2265 ],
2266 ];
2267
2268 $result = $transformer->transform($sql, $tables);
2269 self::assertStringContainsString(', 0)) DESC', $result);
2270 }
2271
2272 public function testTransformSetOrderByEscapesDoubleQuoteInFindInSet(): void
2273 {
2274 $transformer = new SelectTransformer();
2275 $sql = 'SELECT * FROM items ORDER BY `status`';
2276 $tables = [
2277 'items' => [
2278 'rows' => [['id' => 1, 'status' => 'say "hi"']],
2279 'columns' => ['id', 'status'],
2280 'columnTypes' => [
2281 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
2282 'status' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'SET("say ""hi""","ok")'),
2283 ],
2284 ],
2285 ];
2286
2287 $result = $transformer->transform($sql, $tables);
2288 self::assertStringContainsString("FIND_IN_SET('say \"hi\"', `status`)", $result);
2289 }
2290
2291 public function testTransformSetOrderByRewritesSecondSetColumnInSameTable(): void
2292 {
2293 $transformer = new SelectTransformer();
2294 $sql = 'SELECT * FROM items ORDER BY `priority`';
2295 $tables = [
2296 'items' => [
2297 'rows' => [['id' => 1, 'colors' => 'red', 'priority' => 'low']],
2298 'columns' => ['id', 'colors', 'priority'],
2299 'columnTypes' => [
2300 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
2301 'colors' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('red','blue')"),
2302 'priority' => new ColumnDeclaration(ColumnTypeFamily::STRING, "SET('low','medium','high')"),
2303 ],
2304 ],
2305 ];
2306
2307 $result = $transformer->transform($sql, $tables);
2308 self::assertStringContainsString("FIND_IN_SET('low', `priority`)", $result);
2309 self::assertStringContainsString("FIND_IN_SET('medium', `priority`)", $result);
2310 self::assertStringContainsString("FIND_IN_SET('high', `priority`)", $result);
2311 }
2312
2313 public function testTransformWithExistingWithClauseHasCommaBeforeExistingCte(): void
2314 {
2315 $transformer = new SelectTransformer();
2316 $sql = 'WITH existing AS (SELECT 1 AS x) SELECT * FROM users, existing';
2317 $tables = [
2318 'users' => [
2319 'rows' => [['id' => 1]],
2320 'columns' => ['id'],
2321 'columnTypes' => [],
2322 ],
2323 ];
2324
2325 $result = $transformer->transform($sql, $tables);
2326 self::assertStringContainsString('`users` AS', $result);
2327 $usersPos = strpos($result, '`users` AS');
2328 $existingPos = strpos($result, 'existing AS');
2329 self::assertNotFalse($usersPos);
2330 self::assertNotFalse($existingPos);
2331 $between = substr($result, $usersPos, $existingPos - $usersPos);
2332 self::assertStringContainsString(',', $between);
2333 }
2334
2335 public function testEmptyTableContextReturnsOriginalSql(): void
2336 {
2337 $transformer = new SelectTransformer();
2338 $sql = 'SELECT * FROM users WHERE id = 1';
2339 $result = $transformer->transform($sql, []);
2340 self::assertSame($sql, $result);
2341 }
2342
2343 public function testCteInjectedSqlStartsWithWith(): void
2344 {
2345 $transformer = new SelectTransformer();
2346 $sql = 'SELECT * FROM users WHERE id = 1';
2347 $tables = ['users' => ['rows' => [['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com']], 'columns' => ['id', 'name', 'email'], 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'), 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'), 'email' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)')]]];
2348 $result = $transformer->transform($sql, $tables);
2349 self::assertStringStartsWith('WITH', ltrim($result));
2350 }
2351
2352 public function testTableNameUsedAsCte(): void
2353 {
2354 $transformer = new SelectTransformer();
2355 $sql = 'SELECT * FROM users WHERE id = 1';
2356 $tables = ['users' => ['rows' => [['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com']], 'columns' => ['id', 'name', 'email'], 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'), 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'), 'email' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)')]]];
2357 $result = $transformer->transform($sql, $tables);
2358 self::assertStringContainsString('users', $result);
2359 self::assertStringContainsString('SELECT', strtoupper($result));
2360 }
2361
2362 public function testTransformIsDeterministic(): void
2363 {
2364 $transformer = new SelectTransformer();
2365 $sql = 'SELECT * FROM users WHERE id = 1';
2366 $tables = ['users' => ['rows' => [['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com']], 'columns' => ['id', 'name', 'email'], 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'), 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'), 'email' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)')]]];
2367 $result1 = $transformer->transform($sql, $tables);
2368 $result2 = $transformer->transform($sql, $tables);
2369 self::assertSame($result1, $result2);
2370 }
2371
2372 public function testTransformOutputIsNonEmpty(): void
2373 {
2374 $transformer = new SelectTransformer();
2375 $sql = 'SELECT * FROM users WHERE id = 1';
2376 $tables = ['users' => ['rows' => [['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com']], 'columns' => ['id', 'name', 'email'], 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'), 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'), 'email' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)')]]];
2377 $result = $transformer->transform($sql, $tables);
2378 self::assertNotEmpty($result);
2379 }
2380
2381 public function testCteContainsSelectUnionStructure(): void
2382 {
2383 $transformer = new SelectTransformer();
2384 $sql = 'SELECT * FROM users WHERE id = 1';
2385 $tables = ['users' => ['rows' => [['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com'], ['id' => 2, 'name' => 'Bob', 'email' => 'bob@example.com']], 'columns' => ['id', 'name', 'email'], 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'), 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'), 'email' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)')]]];
2386 $result = $transformer->transform($sql, $tables);
2387 $upper = strtoupper($result);
2388 self::assertStringContainsString('UNION ALL', $upper);
2389 self::assertStringContainsString('SELECT', $upper);
2390 self::assertStringContainsString(' AS ', $upper, 'CTE must contain AS keyword');
2391 }
2392
2393 public function testCteContainsCastExpressions(): void
2394 {
2395 $transformer = new SelectTransformer();
2396 $sql = 'SELECT * FROM users WHERE id = 1';
2397 $tables = ['users' => ['rows' => [['id' => 1, 'name' => 'Alice', 'email' => 'alice@example.com']], 'columns' => ['id', 'name', 'email'], 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'), 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'), 'email' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)')]]];
2398 $result = $transformer->transform($sql, $tables);
2399 $upper = strtoupper($result);
2400 self::assertStringContainsString('CAST(', $upper, 'CTE output must contain CAST expressions for typed columns');
2401 }
2402
2403 public function testEmptyRowsWithColumnsReturnsWithClause(): void
2404 {
2405 $transformer = new SelectTransformer();
2406 $sql = 'SELECT * FROM users WHERE id = 1';
2407 $tables = ['users' => ['rows' => [], 'columns' => ['id', 'name', 'email'], 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'), 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)'), 'email' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)')]]];
2408 $result = $transformer->transform($sql, $tables);
2409 self::assertStringStartsWith('WITH', ltrim($result));
2410 self::assertNotEmpty($result);
2411 }
2412}
2413