ModifyLimitQueryTest.php 7.4 KB
Newer Older
1 2
<?php

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

use Doctrine\DBAL\Schema\Table;
6
use Doctrine\DBAL\Tests\FunctionalTestCase;
7 8
use function array_change_key_case;
use function count;
Grégoire Paris's avatar
Grégoire Paris committed
9
use const CASE_LOWER;
10

11
class ModifyLimitQueryTest extends FunctionalTestCase
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;
        }
Grégoire Paris's avatar
Grégoire Paris committed
35

Sergei Morozov's avatar
Sergei Morozov committed
36 37
        $this->connection->exec($this->connection->getDatabasePlatform()->getTruncateTableSQL('modify_limit_table'));
        $this->connection->exec($this->connection->getDatabasePlatform()->getTruncateTableSQL('modify_limit_table2'));
38
    }
39

40
    public function testModifyLimitQuerySimpleQuery() : void
41
    {
Sergei Morozov's avatar
Sergei Morozov committed
42 43 44 45
        $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]);
46

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

Sergei Morozov's avatar
Sergei Morozov committed
49 50 51 52
        $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);
53
    }
54

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

Sergei Morozov's avatar
Sergei Morozov committed
60 61 62 63 64
        $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]);
65

Sergei Morozov's avatar
Sergei Morozov committed
66
        $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';
67

Sergei Morozov's avatar
Sergei Morozov committed
68 69 70
        $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);
71
    }
72

73
    public function testModifyLimitQueryNonDeterministic() : void
74
    {
Sergei Morozov's avatar
Sergei Morozov committed
75 76 77 78
        $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]);
79

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

Sergei Morozov's avatar
Sergei Morozov committed
82 83 84
        $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);
85
    }
86

87
    public function testModifyLimitQueryGroupBy() : void
88
    {
Sergei Morozov's avatar
Sergei Morozov committed
89 90
        $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
91

Sergei Morozov's avatar
Sergei Morozov committed
92 93 94 95 96
        $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
97 98 99 100 101 102 103 104

        $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);
105
    }
106

107
    public function testModifyLimitQuerySubSelect() : void
108
    {
Sergei Morozov's avatar
Sergei Morozov committed
109 110 111 112
        $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]);
113

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

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

121
    public function testModifyLimitQueryFromSubSelect() : void
122
    {
Sergei Morozov's avatar
Sergei Morozov committed
123 124 125 126
        $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]);
127

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

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

135
    public function testModifyLimitQueryLineBreaks() : void
136
    {
Sergei Morozov's avatar
Sergei Morozov committed
137 138 139
        $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]);
140 141 142 143 144 145 146 147 148 149 150 151

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

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

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

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

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

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

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