ModifyLimitQueryTest.php 7.39 KB
Newer Older
1 2 3
<?php

namespace Doctrine\Tests\DBAL\Functional;
Sergei Morozov's avatar
Sergei Morozov committed
4 5 6

use Doctrine\DBAL\Schema\Table;
use Doctrine\Tests\DbalFunctionalTestCase;
7 8 9
use const CASE_LOWER;
use function array_change_key_case;
use function count;
10

Sergei Morozov's avatar
Sergei Morozov committed
11
class ModifyLimitQueryTest extends DbalFunctionalTestCase
12
{
Sergei Morozov's avatar
Sergei Morozov committed
13
    /** @var bool */
14
    private static $tableCreated = false;
15

16
    protected function setUp() : void
17 18 19
    {
        parent::setUp();

Sergei Morozov's avatar
Sergei Morozov committed
20 21
        if (! self::$tableCreated) {
            $table = new Table('modify_limit_table');
22
            $table->addColumn('test_int', 'integer');
Sergei Morozov's avatar
Sergei Morozov committed
23
            $table->setPrimaryKey(['test_int']);
24

Sergei Morozov's avatar
Sergei Morozov committed
25 26
            $table2 = new Table('modify_limit_table2');
            $table2->addColumn('id', 'integer', ['autoincrement' => true]);
27
            $table2->addColumn('test_int', 'integer');
Sergei Morozov's avatar
Sergei Morozov committed
28
            $table2->setPrimaryKey(['id']);
29

Sergei Morozov's avatar
Sergei Morozov committed
30
            $sm = $this->connection->getSchemaManager();
31 32 33 34
            $sm->createTable($table);
            $sm->createTable($table2);
            self::$tableCreated = true;
        }
Sergei Morozov's avatar
Sergei Morozov committed
35 36
        $this->connection->exec($this->connection->getDatabasePlatform()->getTruncateTableSQL('modify_limit_table'));
        $this->connection->exec($this->connection->getDatabasePlatform()->getTruncateTableSQL('modify_limit_table2'));
37
    }
38

39
    public function testModifyLimitQuerySimpleQuery() : void
40
    {
Sergei Morozov's avatar
Sergei Morozov committed
41 42 43 44
        $this->connection->insert('modify_limit_table', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table', ['test_int' => 2]);
        $this->connection->insert('modify_limit_table', ['test_int' => 3]);
        $this->connection->insert('modify_limit_table', ['test_int' => 4]);
45

Sergei Morozov's avatar
Sergei Morozov committed
46
        $sql = 'SELECT * FROM modify_limit_table ORDER BY test_int ASC';
47

Sergei Morozov's avatar
Sergei Morozov committed
48 49 50 51
        $this->assertLimitResult([1, 2, 3, 4], $sql, 10, 0);
        $this->assertLimitResult([1, 2], $sql, 2, 0);
        $this->assertLimitResult([3, 4], $sql, 2, 2);
        $this->assertLimitResult([2, 3, 4], $sql, null, 1);
52
    }
53

54
    public function testModifyLimitQueryJoinQuery() : void
55
    {
Sergei Morozov's avatar
Sergei Morozov committed
56 57
        $this->connection->insert('modify_limit_table', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table', ['test_int' => 2]);
58

Sergei Morozov's avatar
Sergei Morozov committed
59 60 61 62 63
        $this->connection->insert('modify_limit_table2', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table2', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table2', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table2', ['test_int' => 2]);
        $this->connection->insert('modify_limit_table2', ['test_int' => 2]);
64

Sergei Morozov's avatar
Sergei Morozov committed
65
        $sql = 'SELECT modify_limit_table.test_int FROM modify_limit_table INNER JOIN modify_limit_table2 ON modify_limit_table.test_int = modify_limit_table2.test_int ORDER BY modify_limit_table.test_int DESC';
66

Sergei Morozov's avatar
Sergei Morozov committed
67 68 69
        $this->assertLimitResult([2, 2, 1, 1, 1], $sql, 10, 0);
        $this->assertLimitResult([1, 1, 1], $sql, 3, 2);
        $this->assertLimitResult([2, 2], $sql, 2, 0);
70
    }
71

72
    public function testModifyLimitQueryNonDeterministic() : void
73
    {
Sergei Morozov's avatar
Sergei Morozov committed
74 75 76 77
        $this->connection->insert('modify_limit_table', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table', ['test_int' => 2]);
        $this->connection->insert('modify_limit_table', ['test_int' => 3]);
        $this->connection->insert('modify_limit_table', ['test_int' => 4]);
78

Sergei Morozov's avatar
Sergei Morozov committed
79
        $sql = 'SELECT * FROM modify_limit_table';
80

Sergei Morozov's avatar
Sergei Morozov committed
81 82 83
        $this->assertLimitResult([4, 3, 2, 1], $sql, 10, 0, false);
        $this->assertLimitResult([4, 3], $sql, 2, 0, false);
        $this->assertLimitResult([2, 1], $sql, 2, 2, false);
84
    }
85

86
    public function testModifyLimitQueryGroupBy() : void
87
    {
Sergei Morozov's avatar
Sergei Morozov committed
88 89
        $this->connection->insert('modify_limit_table', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table', ['test_int' => 2]);
Sergei Morozov's avatar
Sergei Morozov committed
90

Sergei Morozov's avatar
Sergei Morozov committed
91 92 93 94 95
        $this->connection->insert('modify_limit_table2', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table2', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table2', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table2', ['test_int' => 2]);
        $this->connection->insert('modify_limit_table2', ['test_int' => 2]);
Sergei Morozov's avatar
Sergei Morozov committed
96 97 98 99 100 101 102 103

        $sql = 'SELECT modify_limit_table.test_int FROM modify_limit_table ' .
               'INNER JOIN modify_limit_table2 ON modify_limit_table.test_int = modify_limit_table2.test_int ' .
               'GROUP BY modify_limit_table.test_int ' .
               'ORDER BY modify_limit_table.test_int ASC';
        $this->assertLimitResult([1, 2], $sql, 10, 0);
        $this->assertLimitResult([1], $sql, 1, 0);
        $this->assertLimitResult([2], $sql, 1, 1);
104
    }
105

106
    public function testModifyLimitQuerySubSelect() : void
107
    {
Sergei Morozov's avatar
Sergei Morozov committed
108 109 110 111
        $this->connection->insert('modify_limit_table', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table', ['test_int' => 2]);
        $this->connection->insert('modify_limit_table', ['test_int' => 3]);
        $this->connection->insert('modify_limit_table', ['test_int' => 4]);
112

Sergei Morozov's avatar
Sergei Morozov committed
113
        $sql = 'SELECT modify_limit_table.*, (SELECT COUNT(*) FROM modify_limit_table) AS cnt FROM modify_limit_table ORDER BY test_int DESC';
114

Sergei Morozov's avatar
Sergei Morozov committed
115 116 117
        $this->assertLimitResult([4, 3, 2, 1], $sql, 10, 0);
        $this->assertLimitResult([4, 3], $sql, 2, 0);
        $this->assertLimitResult([2, 1], $sql, 2, 2);
118 119
    }

120
    public function testModifyLimitQueryFromSubSelect() : void
121
    {
Sergei Morozov's avatar
Sergei Morozov committed
122 123 124 125
        $this->connection->insert('modify_limit_table', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table', ['test_int' => 2]);
        $this->connection->insert('modify_limit_table', ['test_int' => 3]);
        $this->connection->insert('modify_limit_table', ['test_int' => 4]);
126

Sergei Morozov's avatar
Sergei Morozov committed
127
        $sql = 'SELECT * FROM (SELECT * FROM modify_limit_table) sub ORDER BY test_int DESC';
128

Sergei Morozov's avatar
Sergei Morozov committed
129 130 131
        $this->assertLimitResult([4, 3, 2, 1], $sql, 10, 0);
        $this->assertLimitResult([4, 3], $sql, 2, 0);
        $this->assertLimitResult([2, 1], $sql, 2, 2);
132 133
    }

134
    public function testModifyLimitQueryLineBreaks() : void
135
    {
Sergei Morozov's avatar
Sergei Morozov committed
136 137 138
        $this->connection->insert('modify_limit_table', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table', ['test_int' => 2]);
        $this->connection->insert('modify_limit_table', ['test_int' => 3]);
139 140 141 142 143 144 145 146 147 148 149 150

        $sql = <<<SQL
SELECT
*
FROM
modify_limit_table
ORDER
BY
test_int
ASC
SQL;

Sergei Morozov's avatar
Sergei Morozov committed
151
        $this->assertLimitResult([2], $sql, 1, 1);
152 153
    }

154
    public function testModifyLimitQueryZeroOffsetNoLimit() : void
155
    {
Sergei Morozov's avatar
Sergei Morozov committed
156 157
        $this->connection->insert('modify_limit_table', ['test_int' => 1]);
        $this->connection->insert('modify_limit_table', ['test_int' => 2]);
158

Sergei Morozov's avatar
Sergei Morozov committed
159
        $sql = 'SELECT test_int FROM modify_limit_table ORDER BY test_int ASC';
160

Sergei Morozov's avatar
Sergei Morozov committed
161
        $this->assertLimitResult([1, 2], $sql, null, 0);
162 163
    }

164 165 166 167
    /**
     * @param array<int, int> $expectedResults
     */
    private function assertLimitResult(array $expectedResults, string $sql, ?int $limit, int $offset, bool $deterministic = true) : void
168
    {
Sergei Morozov's avatar
Sergei Morozov committed
169
        $p    = $this->connection->getDatabasePlatform();
Sergei Morozov's avatar
Sergei Morozov committed
170
        $data = [];
Sergei Morozov's avatar
Sergei Morozov committed
171
        foreach ($this->connection->fetchAll($p->modifyLimitQuery($sql, $limit, $offset)) as $row) {
Sergei Morozov's avatar
Sergei Morozov committed
172
            $row    = array_change_key_case($row, CASE_LOWER);
173
            $data[] = $row['test_int'];
174
        }
175 176 177 178 179

        /**
         * Do not assert the order of results when results are non-deterministic
         */
        if ($deterministic) {
180
            self::assertEquals($expectedResults, $data);
181
        } else {
182
            self::assertCount(count($expectedResults), $data);
183
        }
184
    }
185
}