SelectStatementTest::testBuilderOrderByNull() - Code Metrics - Inspection of "Improving `Expression` to allow `is null` and `is..." - phpmyadmin/sql-parser - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#399)

unknown

created 2022-12-13 17:27 UTC

SelectStatementTest::testBuilderOrderByNull() A

↳ Parent: SelectStatementTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	13
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	8
nc	1
nop	0
dl	0
loc	13
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()
        );
    }

    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 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
27			public function testBuilderUnion(): void
28			{
29			$parser = new Parser('SELECT 1 UNION SELECT 2');
30			$stmt = $parser->statements[0];
31
32			$this->assertEquals(
33			'SELECT 1 UNION SELECT 2',
34			$stmt->build()
35			);
36			}
37
38			public function testBuilderWithIsNull(): void
39			{
40			$parser = new Parser('SELECT `test3`.`t1` is not null AS `is_not_null` FROM `test3` ;');
41			$stmt = $parser->statements[0];
42
43			$this->assertEquals('SELECT `test3`.`t1` is not null AS `is_not_null` FROM `test3`', $stmt->build());
44
45			$parser = new Parser('SELECT test3.t1 is null AS `col1` FROM test3');
46			$stmt = $parser->statements[0];
47
48			$this->assertEquals('SELECT test3.t1 is null AS `col1` FROM test3', $stmt->build());
49			}
50
51			public function testBuilderOrderByNull(): void
52			{
53			$query = 'SELECT * FROM some_table ORDER BY some_col IS NULL DESC;';
54			$parser = new Parser($query);
55			$stmt = $parser->statements[0];
56
57			$this->assertEquals('SELECT * FROM some_table ORDER BY some_col IS NULL DESC', $stmt->build());
58
59			$query = 'SELECT * FROM some_table ORDER BY some_col IS NOT NULL;';
60			$parser = new Parser($query);
61			$stmt = $parser->statements[0];
62
63			$this->assertEquals('SELECT * FROM some_table ORDER BY some_col IS NOT NULL ASC', $stmt->build());
64			}
65
66			public function testBuilderAlias(): void
67			{
68			$parser = new Parser(
69			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
70			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
71			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
72			. 'ORDER BY scb.id DESC LIMIT 0,300'
73			);
74			$stmt = $parser->statements[0];
75
76			$this->assertEquals(
77			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
78			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
79			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
80			. 'ORDER BY scb.id DESC LIMIT 0, 300',
81			$stmt->build()
82			);
83			}
84
85			public function testBuilderAliasOrder(): void
86			{
87			$parser = new Parser(
88			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
89			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
90			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
91			. 'ORDER BY scb.id LIMIT 0,300'
92			);
93			$stmt = $parser->statements[0];
94
95			$this->assertEquals(
96			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
97			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
98			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
99			. 'ORDER BY scb.id ASC LIMIT 0, 300',
100			$stmt->build()
101			);
102			}
103
104			public function testBuilderAliasOrderMultiple(): void
105			{
106			$parser = new Parser(
107			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
108			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
109			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
110			. 'ORDER BY scb.id DESC, scb.order LIMIT 0,300'
111			);
112			$stmt = $parser->statements[0];
113
114			$this->assertEquals(
115			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
116			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
117			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
118			. 'ORDER BY scb.id DESC, scb.order ASC LIMIT 0, 300',
119			$stmt->build()
120			);
121			}
122
123			public function testBuilderAliasOrderMultipleFunctions(): void
124			{
125			$parser = new Parser(
126			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
127			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
128			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
129			. 'ORDER BY scb.id DESC, YEAR(scb.dob) LIMIT 0,300'
130			);
131			$stmt = $parser->statements[0];
132
133			$this->assertEquals(
134			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
135			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
136			. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
137			. 'ORDER BY scb.id DESC, YEAR(scb.dob) ASC LIMIT 0, 300',
138			$stmt->build()
139			);
140			}
141
142			public function testBuilderAliasGroupByMultipleFunctions(): void
143			{
144			$parser = new Parser(
145			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
146			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
147			. 'WHERE `has_found_course` = \'1\' '
148			. 'GROUP BY scb.id, YEAR(scb.dob) LIMIT 0,300'
149			);
150			$stmt = $parser->statements[0];
151
152			$this->assertEquals(
153			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
154			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
155			. 'WHERE `has_found_course` = \'1\' '
156			. 'GROUP BY scb.id, YEAR(scb.dob) LIMIT 0, 300',
157			$stmt->build()
158			);
159			}
160
161			public function testBuilderAliasGroupByMultipleFunctionsOrderRemoved(): void
162			{
163			$parser = new Parser(
164			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
165			. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
166			. 'WHERE `has_found_course` = \'1\' '
167			. 'GROUP BY scb.id ASC, YEAR(scb.dob) DESC LIMIT 0,300'
168			);
169			$stmt = $parser->statements[0];
170
171			// The order is not kept, is this an expected behavior ?
172			// Ref: 4af06d24b041e499fb0e75ab3a98caf9a91700ef
173			// Issue: #154
174			$this->assertEquals(
175			'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
176			. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
177			. 'WHERE `has_found_course` = \'1\' '
178			. 'GROUP BY scb.id, YEAR(scb.dob) LIMIT 0, 300',
179			$stmt->build()
180			);
181			}
182
183			public function testBuilderAliasOrderCase(): void
184			{
185			$parser = new Parser(
186			'SELECT * FROM `world_borders` ORDER BY CASE '
187			. 'WHEN REGION = 2 THEN 99 '
188			. 'WHEN REGION > 3 THEN REGION+1 '
189			. 'ELSE 100 END LIMIT 0,300'
190			);
191			$stmt = $parser->statements[0];
192
193			$this->assertEquals(
194			'SELECT * FROM `world_borders` ORDER BY CASE '
195			. 'WHEN REGION = 2 THEN 99 '
196			. 'WHEN REGION > 3 THEN REGION+1 '
197			. 'ELSE 100 END ASC LIMIT 0, 300',
198			$stmt->build()
199			);
200			}
201
202			public function testBuilderAliasGroupByCase(): void
203			{
204			$parser = new Parser(
205			'SELECT * FROM `world_borders` GROUP BY CASE '
206			. 'WHEN REGION = 2 THEN 99 '
207			. 'WHEN REGION > 3 THEN REGION+1 '
208			. 'ELSE 100 END LIMIT 0,300'
209			);
210			$stmt = $parser->statements[0];
211
212			$this->assertEquals(
213			'SELECT * FROM `world_borders` GROUP BY CASE '
214			. 'WHEN REGION = 2 THEN 99 '
215			. 'WHEN REGION > 3 THEN REGION+1 '
216			. 'ELSE 100 END LIMIT 0, 300',
217			$stmt->build()
218			);
219			}
220
221			public function testBuilderEndOptions(): void
222			{
223			/* Assertion 1 */
224			$query = 'SELECT pid, name2 FROM tablename WHERE pid = 20 FOR UPDATE';
225			$parser = new Parser($query);
226			$stmt = $parser->statements[0];
227
228			$this->assertEquals(
229			$query,
230			$stmt->build()
231			);
232
233			/* Assertion 2 */
234			$query = 'SELECT pid, name2 FROM tablename WHERE pid = 20 LOCK IN SHARE MODE';
235			$parser = new Parser($query);
236			$stmt = $parser->statements[0];
237
238			$this->assertEquals(
239			$query,
240			$stmt->build()
241			);
242			}
243
244			public function testBuilderIntoOptions(): void
245			{
246			/* Assertion 1 */
247			$query = 'SELECT a, b, a+b INTO OUTFILE "/tmp/result.txt"'
248			. ' COLUMNS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\''
249			. ' LINES TERMINATED BY \'\n\''
250			. ' FROM test_table';
251			$parser = new Parser($query);
252			$stmt = $parser->statements[0];
253
254			$this->assertEquals(
255			$query,
256			$stmt->build()
257			);
258			}
259
260			public function testBuilderGroupBy(): void
261			{
262			$query = 'SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country';
263			$parser = new Parser($query);
264			$stmt = $parser->statements[0];
265
266			$this->assertEquals(
267			$query,
268			$stmt->build()
269			);
270			}
271
272			public function testBuilderIndexHint(): void
273			{
274			$query = 'SELECT * FROM address FORCE INDEX (idx_fk_city_id) IGNORE KEY FOR GROUP BY (a, b,c) WHERE city_id<0';
275			$parser = new Parser($query);
276			$stmt = $parser->statements[0];
277
278			$this->assertEquals(
279			$query,
280			$stmt->build()
281			);
282			}
283
284			public function testBuilderSurroundedByParanthesisWithLimit(): void
285			{
286			$query = '(SELECT first_name FROM `actor` LIMIT 1, 2)';
287			$parser = new Parser($query);
288			$stmt = $parser->statements[0];
289
290			$this->assertEquals(
291			'SELECT first_name FROM `actor` LIMIT 1, 2',
292			$stmt->build()
293			);
294			}
295			}
296

phpmyadmin / sql-parser

Pull Request — master (#399)

SelectStatementTest::testBuilderOrderByNull() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like