SQLServer2012PlatformTest.php 18.3 KB
Newer Older
1 2 3 4
<?php

namespace Doctrine\Tests\DBAL\Platforms;

Sergei Morozov's avatar
Sergei Morozov committed
5
use Doctrine\DBAL\DBALException;
6
use Doctrine\DBAL\Platforms\AbstractPlatform;
7 8 9
use Doctrine\DBAL\Platforms\SQLServer2012Platform;
use Doctrine\DBAL\Schema\Sequence;

10
class SQLServer2012PlatformTest extends AbstractSQLServerPlatformTestCase
11
{
12
    public function createPlatform() : AbstractPlatform
13
    {
Sergei Morozov's avatar
Sergei Morozov committed
14
        return new SQLServer2012Platform();
15 16
    }

17
    public function testSupportsSequences() : void
18
    {
Sergei Morozov's avatar
Sergei Morozov committed
19
        self::assertTrue($this->platform->supportsSequences());
20 21
    }

22
    public function testDoesNotPreferSequences() : void
23
    {
Sergei Morozov's avatar
Sergei Morozov committed
24
        self::assertFalse($this->platform->prefersSequences());
25 26
    }

27
    public function testGeneratesSequenceSqlCommands() : void
28 29
    {
        $sequence = new Sequence('myseq', 20, 1);
30
        self::assertEquals(
31
            'CREATE SEQUENCE myseq START WITH 1 INCREMENT BY 20 MINVALUE 1',
Sergei Morozov's avatar
Sergei Morozov committed
32
            $this->platform->getCreateSequenceSQL($sequence)
33
        );
34
        self::assertEquals(
35
            'ALTER SEQUENCE myseq INCREMENT BY 20',
Sergei Morozov's avatar
Sergei Morozov committed
36
            $this->platform->getAlterSequenceSQL($sequence)
37
        );
38
        self::assertEquals(
39
            'DROP SEQUENCE myseq',
Sergei Morozov's avatar
Sergei Morozov committed
40
            $this->platform->getDropSequenceSQL('myseq')
41
        );
42
        self::assertEquals(
Sergei Morozov's avatar
Sergei Morozov committed
43
            'SELECT NEXT VALUE FOR myseq',
Sergei Morozov's avatar
Sergei Morozov committed
44
            $this->platform->getSequenceNextValSQL('myseq')
45 46
        );
    }
47

48
    public function testModifyLimitQuery() : void
49
    {
Sergei Morozov's avatar
Sergei Morozov committed
50
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM user', 10, 0);
51
        self::assertEquals('SELECT * FROM user ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
52 53
    }

54
    public function testModifyLimitQueryWithEmptyOffset() : void
55
    {
Sergei Morozov's avatar
Sergei Morozov committed
56
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM user', 10);
57
        self::assertEquals('SELECT * FROM user ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
58 59
    }

60
    public function testModifyLimitQueryWithOffset() : void
61
    {
Sergei Morozov's avatar
Sergei Morozov committed
62
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC', 10, 5);
63
        self::assertEquals('SELECT * FROM user ORDER BY username DESC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
64 65
    }

66
    public function testModifyLimitQueryWithAscOrderBy() : void
67
    {
Sergei Morozov's avatar
Sergei Morozov committed
68
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM user ORDER BY username ASC', 10);
69
        self::assertEquals('SELECT * FROM user ORDER BY username ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
70 71
    }

72
    public function testModifyLimitQueryWithLowercaseOrderBy() : void
73
    {
Sergei Morozov's avatar
Sergei Morozov committed
74
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM user order by username', 10);
75
        self::assertEquals('SELECT * FROM user order by username OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
76 77
    }

78
    public function testModifyLimitQueryWithDescOrderBy() : void
79
    {
Sergei Morozov's avatar
Sergei Morozov committed
80
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC', 10);
81
        self::assertEquals('SELECT * FROM user ORDER BY username DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
82 83
    }

84
    public function testModifyLimitQueryWithMultipleOrderBy() : void
85
    {
Sergei Morozov's avatar
Sergei Morozov committed
86
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC, usereamil ASC', 10);
87
        self::assertEquals('SELECT * FROM user ORDER BY username DESC, usereamil ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
88 89
    }

90
    public function testModifyLimitQueryWithSubSelect() : void
91
    {
Sergei Morozov's avatar
Sergei Morozov committed
92
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result', 10);
93
        self::assertEquals('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
94 95
    }

96
    public function testModifyLimitQueryWithSubSelectAndOrder() : void
97
    {
Sergei Morozov's avatar
Sergei Morozov committed
98
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC', 10);
99
        self::assertEquals('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
100

Sergei Morozov's avatar
Sergei Morozov committed
101
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC', 10);
102
        self::assertEquals('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
103 104
    }

105
    public function testModifyLimitQueryWithSubSelectAndMultipleOrder() : void
106
    {
Sergei Morozov's avatar
Sergei Morozov committed
107
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC, uid ASC', 10, 5);
108
        self::assertEquals('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC, uid ASC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
109

Sergei Morozov's avatar
Sergei Morozov committed
110
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM (SELECT u.id uid, u.name uname) dctrn_result ORDER BY uname DESC, uid ASC', 10, 5);
111
        self::assertEquals('SELECT * FROM (SELECT u.id uid, u.name uname) dctrn_result ORDER BY uname DESC, uid ASC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
112

Sergei Morozov's avatar
Sergei Morozov committed
113
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC, id ASC', 10, 5);
114
        self::assertEquals('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC, id ASC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
115 116
    }

117
    public function testModifyLimitQueryWithFromColumnNames() : void
118
    {
Sergei Morozov's avatar
Sergei Morozov committed
119
        $sql = $this->platform->modifyLimitQuery('SELECT a.fromFoo, fromBar FROM foo', 10);
120
        self::assertEquals('SELECT a.fromFoo, fromBar FROM foo ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
121 122 123 124 125
    }

    /**
     * @group DBAL-927
     */
126
    public function testModifyLimitQueryWithExtraLongQuery() : void
127
    {
Sergei Morozov's avatar
Sergei Morozov committed
128 129 130 131
        $query  = 'SELECT table1.column1, table2.column2, table3.column3, table4.column4, table5.column5, table6.column6, table7.column7, table8.column8 FROM table1, table2, table3, table4, table5, table6, table7, table8 ';
        $query .= 'WHERE (table1.column1 = table2.column2) AND (table1.column1 = table3.column3) AND (table1.column1 = table4.column4) AND (table1.column1 = table5.column5) AND (table1.column1 = table6.column6) AND (table1.column1 = table7.column7) AND (table1.column1 = table8.column8) AND (table2.column2 = table3.column3) AND (table2.column2 = table4.column4) AND (table2.column2 = table5.column5) AND (table2.column2 = table6.column6) ';
        $query .= 'AND (table2.column2 = table7.column7) AND (table2.column2 = table8.column8) AND (table3.column3 = table4.column4) AND (table3.column3 = table5.column5) AND (table3.column3 = table6.column6) AND (table3.column3 = table7.column7) AND (table3.column3 = table8.column8) AND (table4.column4 = table5.column5) AND (table4.column4 = table6.column6) AND (table4.column4 = table7.column7) AND (table4.column4 = table8.column8) ';
        $query .= 'AND (table5.column5 = table6.column6) AND (table5.column5 = table7.column7) AND (table5.column5 = table8.column8) AND (table6.column6 = table7.column7) AND (table6.column6 = table8.column8) AND (table7.column7 = table8.column8)';
132

Sergei Morozov's avatar
Sergei Morozov committed
133
        $sql = $this->platform->modifyLimitQuery($query, 10);
134

Sergei Morozov's avatar
Sergei Morozov committed
135 136 137 138 139
        $expected  = 'SELECT table1.column1, table2.column2, table3.column3, table4.column4, table5.column5, table6.column6, table7.column7, table8.column8 FROM table1, table2, table3, table4, table5, table6, table7, table8 ';
        $expected .= 'WHERE (table1.column1 = table2.column2) AND (table1.column1 = table3.column3) AND (table1.column1 = table4.column4) AND (table1.column1 = table5.column5) AND (table1.column1 = table6.column6) AND (table1.column1 = table7.column7) AND (table1.column1 = table8.column8) AND (table2.column2 = table3.column3) AND (table2.column2 = table4.column4) AND (table2.column2 = table5.column5) AND (table2.column2 = table6.column6) ';
        $expected .= 'AND (table2.column2 = table7.column7) AND (table2.column2 = table8.column8) AND (table3.column3 = table4.column4) AND (table3.column3 = table5.column5) AND (table3.column3 = table6.column6) AND (table3.column3 = table7.column7) AND (table3.column3 = table8.column8) AND (table4.column4 = table5.column5) AND (table4.column4 = table6.column6) AND (table4.column4 = table7.column7) AND (table4.column4 = table8.column8) ';
        $expected .= 'AND (table5.column5 = table6.column6) AND (table5.column5 = table7.column7) AND (table5.column5 = table8.column8) AND (table6.column6 = table7.column7) AND (table6.column6 = table8.column8) AND (table7.column7 = table8.column8) ';
        $expected .= 'ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
140

141
        self::assertEquals($expected, $sql);
142 143 144 145 146
    }

    /**
     * @group DDC-2470
     */
147
    public function testModifyLimitQueryWithOrderByClause() : void
148 149 150
    {
        $sql      = 'SELECT m0_.NOMBRE AS NOMBRE0, m0_.FECHAINICIO AS FECHAINICIO1, m0_.FECHAFIN AS FECHAFIN2 FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0_.ESTUDIO_ID = e1_.ID INNER JOIN CLIENTE c2_ ON e1_.CLIENTE_ID = c2_.ID INNER JOIN USUARIO u3_ ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = ? ORDER BY m0_.FECHAINICIO DESC';
        $expected = 'SELECT m0_.NOMBRE AS NOMBRE0, m0_.FECHAINICIO AS FECHAINICIO1, m0_.FECHAFIN AS FECHAFIN2 FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0_.ESTUDIO_ID = e1_.ID INNER JOIN CLIENTE c2_ ON e1_.CLIENTE_ID = c2_.ID INNER JOIN USUARIO u3_ ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = ? ORDER BY m0_.FECHAINICIO DESC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY';
Sergei Morozov's avatar
Sergei Morozov committed
151
        $actual   = $this->platform->modifyLimitQuery($sql, 10, 5);
152

153
        self::assertEquals($expected, $actual);
154 155 156 157 158
    }

    /**
     * @group DBAL-713
     */
159
    public function testModifyLimitQueryWithSubSelectInSelectList() : void
160
    {
Sergei Morozov's avatar
Sergei Morozov committed
161
        $sql = $this->platform->modifyLimitQuery(
Sergei Morozov's avatar
Sergei Morozov committed
162 163 164 165 166 167
            'SELECT ' .
            'u.id, ' .
            '(u.foo/2) foodiv, ' .
            'CONCAT(u.bar, u.baz) barbaz, ' .
            '(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count ' .
            'FROM user u ' .
168 169 170 171
            "WHERE u.status = 'disabled'",
            10
        );

172
        self::assertEquals(
173

Sergei Morozov's avatar
Sergei Morozov committed
174 175 176 177 178 179
            'SELECT ' .
            'u.id, ' .
            '(u.foo/2) foodiv, ' .
            'CONCAT(u.bar, u.baz) barbaz, ' .
            '(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count ' .
            'FROM user u ' .
180
            "WHERE u.status = 'disabled' " .
Sergei Morozov's avatar
Sergei Morozov committed
181
            'ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY',
182 183 184 185 186 187 188
            $sql
        );
    }

    /**
     * @group DBAL-713
     */
189
    public function testModifyLimitQueryWithSubSelectInSelectListAndOrderByClause() : void
190
    {
Sergei Morozov's avatar
Sergei Morozov committed
191
        $sql = $this->platform->modifyLimitQuery(
Sergei Morozov's avatar
Sergei Morozov committed
192 193 194 195 196 197
            'SELECT ' .
            'u.id, ' .
            '(u.foo/2) foodiv, ' .
            'CONCAT(u.bar, u.baz) barbaz, ' .
            '(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count ' .
            'FROM user u ' .
198
            "WHERE u.status = 'disabled' " .
Sergei Morozov's avatar
Sergei Morozov committed
199
            'ORDER BY u.username DESC',
200 201 202 203
            10,
            5
        );

204
        self::assertEquals(
Sergei Morozov's avatar
Sergei Morozov committed
205 206 207 208 209 210
            'SELECT ' .
            'u.id, ' .
            '(u.foo/2) foodiv, ' .
            'CONCAT(u.bar, u.baz) barbaz, ' .
            '(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count ' .
            'FROM user u ' .
211
            "WHERE u.status = 'disabled' " .
Sergei Morozov's avatar
Sergei Morozov committed
212 213
            'ORDER BY u.username DESC ' .
            'OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY',
214 215 216 217 218 219 220
            $sql
        );
    }

    /**
     * @group DBAL-834
     */
221
    public function testModifyLimitQueryWithAggregateFunctionInOrderByClause() : void
222
    {
Sergei Morozov's avatar
Sergei Morozov committed
223
        $sql = $this->platform->modifyLimitQuery(
Sergei Morozov's avatar
Sergei Morozov committed
224 225 226 227 228 229
            'SELECT ' .
            'MAX(heading_id) aliased, ' .
            'code ' .
            'FROM operator_model_operator ' .
            'GROUP BY code ' .
            'ORDER BY MAX(heading_id) DESC',
230 231 232 233
            1,
            0
        );

234
        self::assertEquals(
Sergei Morozov's avatar
Sergei Morozov committed
235 236 237 238 239 240 241
            'SELECT ' .
            'MAX(heading_id) aliased, ' .
            'code ' .
            'FROM operator_model_operator ' .
            'GROUP BY code ' .
            'ORDER BY MAX(heading_id) DESC ' .
            'OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY',
242 243 244 245
            $sql
        );
    }

246
    public function testModifyLimitQueryWithFromSubquery() : void
247
    {
Sergei Morozov's avatar
Sergei Morozov committed
248
        $sql = $this->platform->modifyLimitQuery('SELECT DISTINCT id_0 FROM (SELECT k0_.id AS id_0 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result', 10);
249

Sergei Morozov's avatar
Sergei Morozov committed
250
        $expected = 'SELECT DISTINCT id_0 FROM (SELECT k0_.id AS id_0 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
251

252
        self::assertEquals($sql, $expected);
253
    }
254

255
    public function testModifyLimitQueryWithFromSubqueryAndOrder() : void
256
    {
Sergei Morozov's avatar
Sergei Morozov committed
257
        $sql = $this->platform->modifyLimitQuery('SELECT DISTINCT id_0, value_1 FROM (SELECT k0_.id AS id_0, k0_.value AS value_1 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result ORDER BY value_1 DESC', 10);
258

Sergei Morozov's avatar
Sergei Morozov committed
259
        $expected = 'SELECT DISTINCT id_0, value_1 FROM (SELECT k0_.id AS id_0, k0_.value AS value_1 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result ORDER BY value_1 DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
260

261
        self::assertEquals($sql, $expected);
262
    }
263

264
    public function testModifyLimitQueryWithComplexOrderByExpression() : void
265
    {
Sergei Morozov's avatar
Sergei Morozov committed
266
        $sql = $this->platform->modifyLimitQuery('SELECT * FROM table ORDER BY (table.x * table.y) DESC', 10);
267

Sergei Morozov's avatar
Sergei Morozov committed
268
        $expected = 'SELECT * FROM table ORDER BY (table.x * table.y) DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
269

270
        self::assertEquals($sql, $expected);
271
    }
272 273

    /**
Sergei Morozov's avatar
Sergei Morozov committed
274
     * @throws DBALException
275
     */
276
    public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromBaseTable() : void
277
    {
Sergei Morozov's avatar
Sergei Morozov committed
278 279 280 281 282 283 284 285 286 287 288 289 290 291
        $querySql   = 'SELECT DISTINCT id_0, name_1 '
            . 'FROM ('
            . 'SELECT t1.id AS id_0, t2.name AS name_1 '
            . 'FROM table_parent t1 '
            . 'LEFT JOIN join_table t2 ON t1.id = t2.table_id'
            . ') dctrn_result '
            . 'ORDER BY id_0 ASC';
        $alteredSql = 'SELECT DISTINCT id_0, name_1 '
            . 'FROM ('
            . 'SELECT t1.id AS id_0, t2.name AS name_1 '
            . 'FROM table_parent t1 '
            . 'LEFT JOIN join_table t2 ON t1.id = t2.table_id'
            . ') dctrn_result '
            . 'ORDER BY id_0 ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY';
Sergei Morozov's avatar
Sergei Morozov committed
292
        $sql        = $this->platform->modifyLimitQuery($querySql, 5);
293
        self::assertEquals($alteredSql, $sql);
294 295 296
    }

    /**
Sergei Morozov's avatar
Sergei Morozov committed
297
     * @throws DBALException
298
     */
299
    public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromJoinTable() : void
300
    {
Sergei Morozov's avatar
Sergei Morozov committed
301 302 303 304 305 306 307 308 309 310 311 312 313 314
        $querySql   = 'SELECT DISTINCT id_0, name_1 '
            . 'FROM ('
            . 'SELECT t1.id AS id_0, t2.name AS name_1 '
            . 'FROM table_parent t1 '
            . 'LEFT JOIN join_table t2 ON t1.id = t2.table_id'
            . ') dctrn_result '
            . 'ORDER BY name_1 ASC';
        $alteredSql = 'SELECT DISTINCT id_0, name_1 '
            . 'FROM ('
            . 'SELECT t1.id AS id_0, t2.name AS name_1 '
            . 'FROM table_parent t1 '
            . 'LEFT JOIN join_table t2 ON t1.id = t2.table_id'
            . ') dctrn_result '
            . 'ORDER BY name_1 ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY';
Sergei Morozov's avatar
Sergei Morozov committed
315
        $sql        = $this->platform->modifyLimitQuery($querySql, 5);
316
        self::assertEquals($alteredSql, $sql);
317 318 319
    }

    /**
Sergei Morozov's avatar
Sergei Morozov committed
320
     * @throws DBALException
321
     */
322
    public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnsFromBothTables() : void
323
    {
Sergei Morozov's avatar
Sergei Morozov committed
324 325 326 327 328 329 330 331 332 333 334 335 336 337
        $querySql   = 'SELECT DISTINCT id_0, name_1, foo_2 '
            . 'FROM ('
            . 'SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 '
            . 'FROM table_parent t1 '
            . 'LEFT JOIN join_table t2 ON t1.id = t2.table_id'
            . ') dctrn_result '
            . 'ORDER BY name_1 ASC, foo_2 DESC';
        $alteredSql = 'SELECT DISTINCT id_0, name_1, foo_2 '
            . 'FROM ('
            . 'SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 '
            . 'FROM table_parent t1 '
            . 'LEFT JOIN join_table t2 ON t1.id = t2.table_id'
            . ') dctrn_result '
            . 'ORDER BY name_1 ASC, foo_2 DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY';
Sergei Morozov's avatar
Sergei Morozov committed
338
        $sql        = $this->platform->modifyLimitQuery($querySql, 5);
339
        self::assertEquals($alteredSql, $sql);
340 341
    }

342
    public function testModifyLimitSubquerySimple() : void
343
    {
Sergei Morozov's avatar
Sergei Morozov committed
344 345 346 347 348
        $querySql   = 'SELECT DISTINCT id_0 FROM '
            . '(SELECT k0_.id AS id_0, k0_.field AS field_1 '
            . 'FROM key_table k0_ WHERE (k0_.where_field IN (1))) dctrn_result';
        $alteredSql = 'SELECT DISTINCT id_0 FROM (SELECT k0_.id AS id_0, k0_.field AS field_1 '
            . 'FROM key_table k0_ WHERE (k0_.where_field IN (1))) dctrn_result ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY';
Sergei Morozov's avatar
Sergei Morozov committed
349
        $sql        = $this->platform->modifyLimitQuery($querySql, 20);
350
        self::assertEquals($alteredSql, $sql);
351
    }
352

353
    public function testModifyLimitQueryWithTopNSubQueryWithOrderBy() : void
354
    {
Sergei Morozov's avatar
Sergei Morozov committed
355
        $querySql    = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC)';
356
        $expectedSql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC) ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
Sergei Morozov's avatar
Sergei Morozov committed
357
        $sql         = $this->platform->modifyLimitQuery($querySql, 10);
358
        self::assertEquals($expectedSql, $sql);
359

Sergei Morozov's avatar
Sergei Morozov committed
360
        $querySql    = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC) ORDER BY t.data2 DESC';
361
        $expectedSql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC) ORDER BY t.data2 DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
Sergei Morozov's avatar
Sergei Morozov committed
362
        $sql         = $this->platform->modifyLimitQuery($querySql, 10);
363
        self::assertEquals($expectedSql, $sql);
364
    }
365

366
    public function testModifyLimitQueryWithNewlineBeforeOrderBy() : void
367
    {
Sergei Morozov's avatar
Sergei Morozov committed
368
        $querySql    = "SELECT * FROM test\nORDER BY col DESC";
369
        $expectedSql = "SELECT * FROM test\nORDER BY col DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY";
Sergei Morozov's avatar
Sergei Morozov committed
370
        $sql         = $this->platform->modifyLimitQuery($querySql, 10);
371
        self::assertEquals($expectedSql, $sql);
372
    }
373
}