packages/ztd-query-pdo-adapter/tests/Integration/MySql/PartitionSelectionTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\MySql;
6
7use Container\MySql80Container;
8use Container\MySql84Container;
9use PDO;
10use PHPUnit\Framework\Attributes\CoversNothing;
11use PHPUnit\Framework\Attributes\Large;
12use PHPUnit\Framework\TestCase;
13use ZtdQuery\Adapter\Pdo\ZtdPdo;
14
15/**
16 * @requires extension pdo_mysql
17 * @group integration
18 * @group mysql
19 */
20#[CoversNothing]
21#[Large]
22final class PartitionSelectionTest extends TestCase
23{
24    public function testRangePartitionSelectionMatchesShadowRows(): void
25    {
26        $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
27        /** @var PDO $pdo */
28        $pdo = new PDO(
29            sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
30            'root',
31            'root',
32            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
33        );
34
35        $databaseName = 'ztd_' . bin2hex(random_bytes(8));
36        $pdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
37        $pdo->exec(sprintf('USE `%s`', $databaseName));
38
39
40        try {
41            $pdo->exec('CREATE TABLE events (id INT NOT NULL, event_date DATE NOT NULL, '
42                . 'PRIMARY KEY (id, event_date)) PARTITION BY RANGE (YEAR(event_date)) ('
43                . 'PARTITION p2023 VALUES LESS THAN (2024), '
44                . 'PARTITION p2024 VALUES LESS THAN (2025), '
45                . 'PARTITION pmax VALUES LESS THAN MAXVALUE)');
46            $ztdPdo = ZtdPdo::fromPdo($pdo);
47            self::assertSame(4, $ztdPdo->exec('INSERT INTO events VALUES '
48                . "(1, '2023-06-01'), (2, '2024-01-15'), (3, '2024-11-20'), (4, '2025-02-01')"));
49
50            $selected = $ztdPdo->query('SELECT id FROM events PARTITION (p2024) ORDER BY id');
51            self::assertNotFalse($selected);
52            self::assertSame([2, 3], $selected->fetchAll(PDO::FETCH_COLUMN));
53
54            $combined = $ztdPdo->query('SELECT e.id FROM events PARTITION (p2023, pmax) AS e ORDER BY e.id');
55            self::assertNotFalse($combined);
56            self::assertSame([1, 4], $combined->fetchAll(PDO::FETCH_COLUMN));
57
58            $physical = $pdo->query('SELECT COUNT(*) FROM events');
59            self::assertNotFalse($physical);
60            self::assertSame(0, (int) $physical->fetchColumn());
61        } finally {
62            $pdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
63        }
64    }
65}
66