SelectStatementTest::testBuilderGroupByWithRollup() - Code Metrics - Inspection of "Update .out files" - phpmyadmin/sql-parser - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( b17b21...573f79 )

by William

created 2023-02-26 21:40 UTC

testBuilderGroupByWithRollup() A

↳ Parent: SelectStatementTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	9
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	6
nc	1
nop	0
dl	0
loc	9
rs	10
c	0
b	0
f	0

<?php

declare(strict_types=1);

namespace PhpMyAdmin\SqlParser\Tests\Builder;

use PhpMyAdmin\SqlParser\Parser;
use PhpMyAdmin\SqlParser\Tests\TestCase;

class SelectStatementTest extends TestCase
{
    public function testBuilder(): void
    {
        $query = 'SELECT * FROM t1 LEFT JOIN (t2, t3, t4) '
            . 'ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)';

        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT * FROM t1 LEFT JOIN (t2, t3, t4) '
            . 'ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)',
            $stmt->build()
        );

        $parser = new Parser('SELECT NULL IS NULL');
        $stmt = $parser->statements[0];

        $this->assertEquals('SELECT NULL IS NULL', $stmt->build());

        $parser = new Parser('SELECT NOT 1');
        $stmt = $parser->statements[0];

        $this->assertEquals('SELECT NOT 1', $stmt->build());

        $parser = new Parser('SELECT 1 BETWEEN 0 AND 2');
        $stmt = $parser->statements[0];

        $this->assertEquals('SELECT 1 BETWEEN 0 AND 2', $stmt->build());

        $parser = new Parser("SELECT 'a' NOT REGEXP '^[a-d]'");
        $stmt = $parser->statements[0];

        $this->assertEquals("SELECT 'a' NOT REGEXP '^[a-d]'", $stmt->build());

        $parser = new Parser("SELECT 'a' RLIKE 'a'");
        $stmt = $parser->statements[0];

        $this->assertEquals("SELECT 'a' RLIKE 'a'", $stmt->build());
    }

    public function testBuilderUnion(): void
    {
        $parser = new Parser('SELECT 1 UNION SELECT 2');
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT 1 UNION SELECT 2',
            $stmt->build()
        );
    }

    public function testBuilderWithIsNull(): void
    {
        $parser = new Parser('SELECT `test3`.`t1` is not null AS `is_not_null` FROM `test3` ;');
        $stmt = $parser->statements[0];

        $this->assertEquals('SELECT `test3`.`t1` is not null AS `is_not_null` FROM `test3`', $stmt->build());

        $parser = new Parser('SELECT test3.t1 is null AS `col1` FROM test3');
        $stmt = $parser->statements[0];

        $this->assertEquals('SELECT test3.t1 is null AS `col1` FROM test3', $stmt->build());
    }

    public function testBuilderOrderByNull(): void
    {
        $query = 'SELECT * FROM some_table ORDER BY some_col IS NULL DESC;';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals('SELECT * FROM some_table ORDER BY some_col IS NULL DESC', $stmt->build());

        $query = 'SELECT * FROM some_table ORDER BY some_col IS NOT NULL;';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals('SELECT * FROM some_table ORDER BY some_col IS NOT NULL ASC', $stmt->build());
    }

    public function testBuilderAlias(): void
    {
        $parser = new Parser(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
            . 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
            . 'ORDER BY scb.id DESC LIMIT 0,300'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
            . 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
            . 'ORDER BY scb.id DESC LIMIT 0, 300',
            $stmt->build()
        );
    }

    public function testBuilderAliasOrder(): void
    {
        $parser = new Parser(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
            . 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
            . 'ORDER BY scb.id LIMIT 0,300'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
            . 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
            . 'ORDER BY scb.id ASC LIMIT 0, 300',
            $stmt->build()
        );
    }

    public function testBuilderAliasOrderMultiple(): void
    {
        $parser = new Parser(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
            . 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
            . 'ORDER BY scb.id DESC, scb.order LIMIT 0,300'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
            . 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
            . 'ORDER BY scb.id DESC, scb.order ASC LIMIT 0, 300',
            $stmt->build()
        );
    }

    public function testBuilderAliasOrderMultipleFunctions(): void
    {
        $parser = new Parser(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
            . 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
            . 'ORDER BY scb.id DESC, YEAR(scb.dob) LIMIT 0,300'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
            . 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
            . 'ORDER BY scb.id DESC, YEAR(scb.dob) ASC LIMIT 0, 300',
            $stmt->build()
        );
    }

    public function testBuilderAliasGroupByMultipleFunctions(): void
    {
        $parser = new Parser(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
            . 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' '
            . 'GROUP BY scb.id, YEAR(scb.dob) LIMIT 0,300'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
            . 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' '
            . 'GROUP BY scb.id, YEAR(scb.dob) LIMIT 0, 300',
            $stmt->build()
        );
    }

    public function testBuilderAliasGroupByMultipleFunctionsOrderRemoved(): void
    {
        $parser = new Parser(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
            . 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' '
            . 'GROUP BY scb.id ASC, YEAR(scb.dob) DESC LIMIT 0,300'
        );
        $stmt = $parser->statements[0];

        // The order is not kept, is this an expected behavior ?
        // Ref: 4af06d24b041e499fb0e75ab3a98caf9a91700ef
        // Issue: #154
        $this->assertEquals(
            'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
            . 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
            . 'WHERE `has_found_course` = \'1\' '
            . 'GROUP BY scb.id, YEAR(scb.dob) LIMIT 0, 300',
            $stmt->build()
        );
    }

    public function testBuilderAliasOrderCase(): void
    {
        $parser = new Parser(
            'SELECT * FROM `world_borders` ORDER BY CASE '
            . 'WHEN REGION = 2 THEN 99 '
            . 'WHEN REGION > 3 THEN REGION+1 '
            . 'ELSE 100 END LIMIT 0,300'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT * FROM `world_borders` ORDER BY CASE '
            . 'WHEN REGION = 2 THEN 99 '
            . 'WHEN REGION > 3 THEN REGION+1 '
            . 'ELSE 100 END ASC LIMIT 0, 300',
            $stmt->build()
        );
    }

    public function testBuilderAliasGroupByCase(): void
    {
        $parser = new Parser(
            'SELECT * FROM `world_borders` GROUP BY CASE '
            . 'WHEN REGION = 2 THEN 99 '
            . 'WHEN REGION > 3 THEN REGION+1 '
            . 'ELSE 100 END LIMIT 0,300'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT * FROM `world_borders` GROUP BY CASE '
            . 'WHEN REGION = 2 THEN 99 '
            . 'WHEN REGION > 3 THEN REGION+1 '
            . 'ELSE 100 END LIMIT 0, 300',
            $stmt->build()
        );
    }

    public function testBuilderEndOptions(): void
    {
        /* Assertion 1 */
        $query = 'SELECT pid, name2 FROM tablename WHERE pid = 20 FOR UPDATE';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            $query,
            $stmt->build()
        );

        /* Assertion 2 */
        $query = 'SELECT pid, name2 FROM tablename WHERE pid = 20 LOCK IN SHARE MODE';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            $query,
            $stmt->build()
        );
    }

    public function testBuilderIntoOptions(): void
    {
        /* Assertion 1 */
        $query = 'SELECT a, b, a+b INTO OUTFILE "/tmp/result.txt"'
            . ' COLUMNS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\''
            . ' LINES TERMINATED BY \'\n\''
            . ' FROM test_table';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            $query,
            $stmt->build()
        );
    }

    public function testBuilderGroupBy(): void
    {
        $query = 'SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            $query,
            $stmt->build()
        );
    }

    public function testBuilderGroupByWithRollup(): void
    {
        $query = 'SELECT year FROM movies GROUP BY year WITH ROLLUP';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            $query,
            $stmt->build()
        );
    }

    public function testBuilderGroupByMultipleColumnsWithRollup(): void
    {
        $query = 'SELECT title, year FROM movies GROUP BY title, year WITH ROLLUP';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            $query,
            $stmt->build()
        );
    }

    public function testBuilderGroupByWithRollupWithOtherClauses(): void
    {
        $query = 'SELECT year FROM movies GROUP BY year WITH ROLLUP ORDER BY year ASC LIMIT 0, 5';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            $query,
            $stmt->build()
        );
    }

    public function testBuilderIndexHint(): void
    {
        $query = 'SELECT * FROM address FORCE INDEX (idx_fk_city_id) IGNORE KEY FOR GROUP BY (a, b,c) WHERE city_id<0';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            $query,
            $stmt->build()
        );
    }

    public function testBuilderSurroundedByParanthesisWithLimit(): void
    {
        $query = '(SELECT first_name FROM `actor` LIMIT 1, 2)';
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'SELECT first_name FROM `actor` LIMIT 1, 2',
            $stmt->build()
        );
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace PhpMyAdmin\SqlParser\Tests\Builder;
6
7			use PhpMyAdmin\SqlParser\Parser;
8			use PhpMyAdmin\SqlParser\Tests\TestCase;
9
10			class SelectStatementTest extends TestCase
11			{
12			public function testBuilder(): void
13			{
14			$query = 'SELECT * FROM t1 LEFT JOIN (t2, t3, t4) '
15			. 'ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)';
16
17			$parser = new Parser($query);
18			$stmt = $parser->statements[0];
19
20			$this->assertEquals(
21			'SELECT * FROM t1 LEFT JOIN (t2, t3, t4) '
22			. 'ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)',
23			$stmt->build()
24			);
25
26			$parser = new Parser('SELECT NULL IS NULL');
27			$stmt = $parser->statements[0];
28
29			$this->assertEquals('SELECT NULL IS NULL', $stmt->build());
30
31			$parser = new Parser('SELECT NOT 1');
32			$stmt = $parser->statements[0];
33
34			$this->assertEquals('SELECT NOT 1', $stmt->build());
35
36			$parser = new Parser('SELECT 1 BETWEEN 0 AND 2');
37			$stmt = $parser->statements[0];
38
39			$this->assertEquals('SELECT 1 BETWEEN 0 AND 2', $stmt->build());
40
41			$parser = new Parser("SELECT 'a' NOT REGEXP '^[a-d]'");
42			$stmt = $parser->statements[0];
43
44			$this->assertEquals("SELECT 'a' NOT REGEXP '^[a-d]'", $stmt->build());
45
46			$parser = new Parser("SELECT 'a' RLIKE 'a'");
47			$stmt = $parser->statements[0];
48
49			$this->assertEquals("SELECT 'a' RLIKE 'a'", $stmt->build());
50			}
51
52			public function testBuilderUnion(): void
53			{
54			$parser = new Parser('SELECT 1 UNION SELECT 2');
55			$stmt = $parser->statements[0];
56
57			$this->assertEquals(
58			'SELECT 1 UNION SELECT 2',
59			$stmt->build()
60			);
61			}
62
63			public function testBuilderWithIsNull(): void
64			{
65			$parser = new Parser('SELECT `test3`.`t1` is not null AS `is_not_null` FROM `test3` ;');
66			$stmt = $parser->statements[0];
67
68			$this->assertEquals('SELECT `test3`.`t1` is not null AS `is_not_null` FROM `test3`', $stmt->build());
69
70			$parser = new Parser('SELECT test3.t1 is null AS `col1` FROM test3');
71			$stmt = $parser->statements[0];
72
73			$this->assertEquals('SELECT test3.t1 is null AS `col1` FROM test3', $stmt->build());
74			}
75
76			public function testBuilderOrderByNull(): void
77			{
78			$query = 'SELECT * FROM some_table ORDER BY some_col IS NULL DESC;';
79			$parser = new Parser($query);
80			$stmt = $parser->statements[0];
81
82			$this->assertEquals('SELECT * FROM some_table ORDER BY some_col IS NULL DESC', $stmt->build());
83
84			$query = 'SELECT * FROM some_table ORDER BY some_col IS NOT NULL;';
85			$parser = new Parser($query);
86			$stmt = $parser->statements[0];
87
88			$this->assertEquals('SELECT * FROM some_table ORDER BY some_col IS NOT NULL ASC', $stmt->build());
89			}
90
91			public function testBuilderAlias(): void
92			{
93			$parser = new Parser(
94			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
95			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
96			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
97			. 'ORDER BY scb.id DESC LIMIT 0,300'
98			);
99			$stmt = $parser->statements[0];
100
101			$this->assertEquals(
102			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
103			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
104			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
105			. 'ORDER BY scb.id DESC LIMIT 0, 300',
106			$stmt->build()
107			);
108			}
109
110			public function testBuilderAliasOrder(): void
111			{
112			$parser = new Parser(
113			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
114			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
115			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
116			. 'ORDER BY scb.id LIMIT 0,300'
117			);
118			$stmt = $parser->statements[0];
119
120			$this->assertEquals(
121			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
122			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
123			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
124			. 'ORDER BY scb.id ASC LIMIT 0, 300',
125			$stmt->build()
126			);
127			}
128
129			public function testBuilderAliasOrderMultiple(): void
130			{
131			$parser = new Parser(
132			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
133			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
134			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
135			. 'ORDER BY scb.id DESC, scb.order LIMIT 0,300'
136			);
137			$stmt = $parser->statements[0];
138
139			$this->assertEquals(
140			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
141			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
142			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
143			. 'ORDER BY scb.id DESC, scb.order ASC LIMIT 0, 300',
144			$stmt->build()
145			);
146			}
147
148			public function testBuilderAliasOrderMultipleFunctions(): void
149			{
150			$parser = new Parser(
151			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
152			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
153			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
154			. 'ORDER BY scb.id DESC, YEAR(scb.dob) LIMIT 0,300'
155			);
156			$stmt = $parser->statements[0];
157
158			$this->assertEquals(
159			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
160			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
161			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
162			. 'ORDER BY scb.id DESC, YEAR(scb.dob) ASC LIMIT 0, 300',
163			$stmt->build()
164			);
165			}
166
167			public function testBuilderAliasGroupByMultipleFunctions(): void
168			{
169			$parser = new Parser(
170			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
171			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
172			. 'WHERE `has_found_course` = \'1\' '
173			. 'GROUP BY scb.id, YEAR(scb.dob) LIMIT 0,300'
174			);
175			$stmt = $parser->statements[0];
176
177			$this->assertEquals(
178			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
179			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
180			. 'WHERE `has_found_course` = \'1\' '
181			. 'GROUP BY scb.id, YEAR(scb.dob) LIMIT 0, 300',
182			$stmt->build()
183			);
184			}
185
186			public function testBuilderAliasGroupByMultipleFunctionsOrderRemoved(): void
187			{
188			$parser = new Parser(
189			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
190			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
191			. 'WHERE `has_found_course` = \'1\' '
192			. 'GROUP BY scb.id ASC, YEAR(scb.dob) DESC LIMIT 0,300'
193			);
194			$stmt = $parser->statements[0];
195
196			// The order is not kept, is this an expected behavior ?
197			// Ref: 4af06d24b041e499fb0e75ab3a98caf9a91700ef
198			// Issue: #154
199			$this->assertEquals(
200			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
201			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
202			. 'WHERE `has_found_course` = \'1\' '
203			. 'GROUP BY scb.id, YEAR(scb.dob) LIMIT 0, 300',
204			$stmt->build()
205			);
206			}
207
208			public function testBuilderAliasOrderCase(): void
209			{
210			$parser = new Parser(
211			'SELECT * FROM `world_borders` ORDER BY CASE '
212			. 'WHEN REGION = 2 THEN 99 '
213			. 'WHEN REGION > 3 THEN REGION+1 '
214			. 'ELSE 100 END LIMIT 0,300'
215			);
216			$stmt = $parser->statements[0];
217
218			$this->assertEquals(
219			'SELECT * FROM `world_borders` ORDER BY CASE '
220			. 'WHEN REGION = 2 THEN 99 '
221			. 'WHEN REGION > 3 THEN REGION+1 '
222			. 'ELSE 100 END ASC LIMIT 0, 300',
223			$stmt->build()
224			);
225			}
226
227			public function testBuilderAliasGroupByCase(): void
228			{
229			$parser = new Parser(
230			'SELECT * FROM `world_borders` GROUP BY CASE '
231			. 'WHEN REGION = 2 THEN 99 '
232			. 'WHEN REGION > 3 THEN REGION+1 '
233			. 'ELSE 100 END LIMIT 0,300'
234			);
235			$stmt = $parser->statements[0];
236
237			$this->assertEquals(
238			'SELECT * FROM `world_borders` GROUP BY CASE '
239			. 'WHEN REGION = 2 THEN 99 '
240			. 'WHEN REGION > 3 THEN REGION+1 '
241			. 'ELSE 100 END LIMIT 0, 300',
242			$stmt->build()
243			);
244			}
245
246			public function testBuilderEndOptions(): void
247			{
248			/* Assertion 1 */
249			$query = 'SELECT pid, name2 FROM tablename WHERE pid = 20 FOR UPDATE';
250			$parser = new Parser($query);
251			$stmt = $parser->statements[0];
252
253			$this->assertEquals(
254			$query,
255			$stmt->build()
256			);
257
258			/* Assertion 2 */
259			$query = 'SELECT pid, name2 FROM tablename WHERE pid = 20 LOCK IN SHARE MODE';
260			$parser = new Parser($query);
261			$stmt = $parser->statements[0];
262
263			$this->assertEquals(
264			$query,
265			$stmt->build()
266			);
267			}
268
269			public function testBuilderIntoOptions(): void
270			{
271			/* Assertion 1 */
272			$query = 'SELECT a, b, a+b INTO OUTFILE "/tmp/result.txt"'
273			. ' COLUMNS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\''
274			. ' LINES TERMINATED BY \'\n\''
275			. ' FROM test_table';
276			$parser = new Parser($query);
277			$stmt = $parser->statements[0];
278
279			$this->assertEquals(
280			$query,
281			$stmt->build()
282			);
283			}
284
285			public function testBuilderGroupBy(): void
286			{
287			$query = 'SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country';
288			$parser = new Parser($query);
289			$stmt = $parser->statements[0];
290
291			$this->assertEquals(
292			$query,
293			$stmt->build()
294			);
295			}
296
297			public function testBuilderGroupByWithRollup(): void
298			{
299			$query = 'SELECT year FROM movies GROUP BY year WITH ROLLUP';
300			$parser = new Parser($query);
301			$stmt = $parser->statements[0];
302
303			$this->assertEquals(
304			$query,
305			$stmt->build()
306			);
307			}
308
309			public function testBuilderGroupByMultipleColumnsWithRollup(): void
310			{
311			$query = 'SELECT title, year FROM movies GROUP BY title, year WITH ROLLUP';
312			$parser = new Parser($query);
313			$stmt = $parser->statements[0];
314
315			$this->assertEquals(
316			$query,
317			$stmt->build()
318			);
319			}
320
321			public function testBuilderGroupByWithRollupWithOtherClauses(): void
322			{
323			$query = 'SELECT year FROM movies GROUP BY year WITH ROLLUP ORDER BY year ASC LIMIT 0, 5';
324			$parser = new Parser($query);
325			$stmt = $parser->statements[0];
326
327			$this->assertEquals(
328			$query,
329			$stmt->build()
330			);
331			}
332
333			public function testBuilderIndexHint(): void
334			{
335			$query = 'SELECT * FROM address FORCE INDEX (idx_fk_city_id) IGNORE KEY FOR GROUP BY (a, b,c) WHERE city_id<0';
336			$parser = new Parser($query);
337			$stmt = $parser->statements[0];
338
339			$this->assertEquals(
340			$query,
341			$stmt->build()
342			);
343			}
344
345			public function testBuilderSurroundedByParanthesisWithLimit(): void
346			{
347			$query = '(SELECT first_name FROM `actor` LIMIT 1, 2)';
348			$parser = new Parser($query);
349			$stmt = $parser->statements[0];
350
351			$this->assertEquals(
352			'SELECT first_name FROM `actor` LIMIT 1, 2',
353			$stmt->build()
354			);
355			}
356			}
357

phpmyadmin / sql-parser

Push — master ( b17b21...573f79 )

testBuilderGroupByWithRollup() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like