SelectStatementTest::testBuilderWithIsNull() - 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 12:51 UTC

SelectStatementTest::testBuilderWithIsNull() A

↳ Parent: SelectStatementTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	11
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

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

phpmyadmin / sql-parser

Pull Request — master (#399)

SelectStatementTest::testBuilderWithIsNull() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like