SelectStatementTest::testBuilderOrderByNull() - Code Metrics - Inspection of "Support IS NULL and IS NOT NULL with order by" - phpmyadmin/sql-parser - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#398)

unknown

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

phpmyadmin / sql-parser

Pull Request — master (#398)

SelectStatementTest::testBuilderOrderByNull() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like