Code Inspections in SQL
This topic lists all PhpStorm code inspections available in SQL.
You can toggle specific inspections or change their severity level on the Editor | Inspections page of the IDE settings Ctrl+Alt+S.
Inspection | Description | Default Severity |
|---|---|---|
Reports attempts to create NOT NULL columns without DEFAULT values. CREATE TABLE foo (a INT, b INT)
ALTER TABLE foo ADD c INT NOT NULL; By default, a column holds NULL values. In the example, we use the NOT NULL constraint that enforces a column not to accept NULL values. If we prohibit to use NULL values, we must set the DEFAULT value that SQL can use when we create a new record. ALTER TABLE foo ADD c INT NOT NULL DEFAULT 42; You can quickly add the DEFAULT value by using the Add DEFAULT value quick-fix. | | |
Reports invalid usages of SQL aggregate functions.
| | |
Reports columns that have identical names but belong to different tables. CREATE TABLE foo(id INT PRIMARY KEY);
CREATE TABLE bar(id INT PRIMARY KEY);
SELECT foo.id, bar.id FROM foo, bar WHERE id > 0; The id column appears in foo and bar tables. You need to qualify the column name to make the query correct. SELECT foo.id, bar.id FROM foo, bar WHERE foo.id > 0; | | |
Reports tables that contain two columns with an automatic increment. In MySQL, Microsoft SQL Server, and Db2 dialects, a table can have only one field with a auto-increment option, and this field must be a key. CREATE TABLE my_table
(
id INT AUTO_INCREMENT,
c2 INT AUTO_INCREMENT,
); The AUTO_INCREMENT constraint for c2 will be highlighted as c1 already has this constraint. To fix the warning, you can make id a primary key and delete AUTO_INCREMENT for c2. CREATE TABLE my_table
(
id INT AUTO_INCREMENT PRIMARY KEY,
c2 INT,
); | | |
Reports columns in the USING clause that does not exist in both tables. CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, l INT);
SELECT * FROM t1 JOIN t2 USING (j); In USING clauses, a column name must be present in both tables, and the SELECT query will automatically join those tables by using the given column name. As we do not have the j column in t2, we can rewrite the query using ON. The ON clause can join tables where the column names do not match in both tables. SELECT * FROM t1 JOIN t2 ON t1.j = t2.l; | | |
Reports SELECT aliases with names that match column names in the FROM clause. CREATE TABLE foo (a INT, b INT, c INT);
SELECT a b, c FROM foo; The a column uses the b alias but the b name is also used by the column from the foo table. | | |
Reports columns that are not in the GROUP BY clause or inside an aggregate function call. CREATE TABLE t1 (a INT, b INT);
SELECT a, b FROM t1 GROUP BY a; If you run the SELECT query, you will receive an error because Microsoft SQL Server expects the b column in GROUP BY or used inside an aggregate function. The following two examples will fix the error. SELECT a, b FROM t1 GROUP BY a, b;
SELECT a, max(b) max_b FROM t1 GROUP BY a; | | |
Reports conditions in WHERE or JOIN clauses that are always TRUE or always FALSE. CREATE TABLE t1 (a TEXT, b INT, c BOOLEAN);
SELECT a FROM t1 WHERE 'Cat' = 'Cat'; The 'Cat' = 'Cat' is always true and will be reported. | | |
Reports schemas and databases in the current session that are not introspected. | | |
Reports usages of DELETE or UPDATE statements without WHERE clauses. CREATE TABLE t1 (a TEXT, b INT, c BOOLEAN);
update t1 set a = 'Smith';
delete from t1; | | |
Reports usages of types that are deprecated and might disappear in future versions of DBMS.
Example (Oracle): CREATE TABLE ot.foo(
a NUMBER GENERATED BY DEFAULT AS IDENTITY,
b LONG NOT NULL
); | | |
Reports duplicated names of column aliases in SELECT lists. CREATE TABLE t1 (a TEXT, b INT, c INT);
SELECT a AS x, b AS x FROM t1; The x alias name is used for a and b columns. These assignments are highlighted as errors because you cannot use identical alias names for columns in Sybase ASE. | | |
Reports derived tables without aliases. CREATE TABLE table1 (id INT, name VARCHAR(20), cats FLOAT);
CREATE TABLE table2 (id INT, age INTEGER);
SELECT id AS ID, name, cats, age
FROM (SELECT table1.id, name, cats, age
FROM table1
JOIN table2 ON table1.id = table2.id); According to Derived Tables at dev.mysql.com, an alias is mandatory. You can add the alias by using the Introduce alias quick-fix. After the quick-fix is applied: SELECT id AS ID, name, cats, age
FROM (SELECT table1.id, name, cats, age
FROM table1
JOIN table2 ON table1.id = table2.id); | | |
Reports signature issues for built-in functions. CREATE TABLE foo (a INT, b INT, c INT)
SELECT IFNULL() FROM foo; -- error
SELECT IFNULL(a) FROM foo; -- error
SELECT IFNULL(a, b) FROM foo; -- OK
SELECT IFNULL(a, b, c) FROM foo; -- error In MySQL, the IFNULL() function accepts strictly two arguments. So, only the SELECT IFNULL(a, b) FROM foo; query is correct. | | |
Reports situations when you use SQL reserved keywords as identifier names in your query. CREATE TABLE select (identity INT IDENTITY NOT NULL, order INT NOT NULL); We use select, identity, and order as table and column names. But they are also reserved keywords in Microsoft SQL Server. Therefore, in order to use them as object names in the query, you must quote these identifiers. To quote them, you can use the Quote identifier quick-fix. After the quick-fix is applied: CREATE TABLE [select] ([identity] INT IDENTITY NOT NULL, [order] INT NOT NULL); | | |
Reports errors in date and time literals. This inspection is available in MySQL, Oracle, Db2, and H2. SELECT TIME '10 -12:13:14' FROM dual;
SELECT TIME ' 12 : 13 : 14 ' FROM dual;
SELECT TIME '12 13 14' FROM dual;
SELECT TIME '12-13-14' FROM dual;
SELECT TIME '12.13.14' FROM dual;
SELECT TIME '12:13:' FROM dual;
SELECT TIME '12:13' FROM dual;
SELECT TIME '12:' FROM dual; In this example, dates ignore the MySQL standard for date and time literals. Therefore, they will be highlighted. For more information about date and time literals in MySQL, see Date and Time Literals at dev.mysql.com. The following date and type literals are valid for MySQL. SELECT TIME '12:13:14' FROM dual;
SELECT TIME '12:13:14.555' FROM dual;
SELECT TIME '12:13:14.' FROM dual;
SELECT TIME '-12:13:14' FROM dual;
SELECT TIME '10 12:13:14' FROM dual;
SELECT TIME '-10 12:13:14' FROM dual; | | |
Reports illegal cursor states inside SQL routines.
Example (Microsoft SQL Server): CREATE TABLE t(col INT);
CREATE PROCEDURE foo() AS
BEGIN
DECLARE my_cursor CURSOR FOR SELECT * FROM t;
DECLARE a INT;
FETCH my_cursor INTO a;
CLOSE my_cursor;
END; According to CLOSE (Transact-SQL) at docs.microsoft.com, CLOSE must be issued on an open cursor, and CLOSE is not allowed on cursors that have only been declared or are already closed. So, we need to open the cursor to fix the warning. CREATE PROCEDURE foo() AS
BEGIN
DECLARE my_cursor CURSOR FOR SELECT * FROM t;
DECLARE a INT;
OPEN my_cursor;
FETCH my_cursor INTO a;
CLOSE my_cursor;
END; | | |
Reports variables that exceed the defined length in characters. CREATE PROCEDURE test() AS
BEGIN
DECLARE myVarOk VARCHAR(5) = 'abcde';
DECLARE myVarExceeded VARCHAR(5) = 'abcde12345';
SET myVarOk = 'xyz';
SET myVarExceeded = '123456789';
END; The myVarExceeded variable is defined as VARCHAR(5) but both assigned values ('abcde12345' and '123456789') exceed this limitation. You can truncate assigned values or increase the defined length. To increase the length, use the Increase type length quick-fix. After the quick-fix is applied: CREATE PROCEDURE test() AS
BEGIN
DECLARE myVarOk VARCHAR(5) = 'abcde';
DECLARE myVarExceeded VARCHAR(10) = 'abcde12345';
SET myVarOk = 'xyz';
SET myVarExceeded = '123456789';
END; | | |
Reports cases when you try to drop columns from indexed tables. This inspection is available in Microsoft SQL Server and Sybase ASE. CREATE TABLE test_index
(
col INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL UNIQUE,
col4 VARCHAR(200)
);
CREATE UNIQUE INDEX aaaa ON test_index (col, col2);
ALTER TABLE test_index
DROP COLUMN col; You cannot delete the col column because it is in the indexed table. To delete the column, you need to delete the aaaa index first (for example, DROP INDEX aaaa). | | |
Reports cases when you insert NULL values into columns that accept only NOT NULL values. CREATE TABLE br2 (
id INT NOT NULL,
col1 NVARCHAR (20) NOT NULL,
col2 NVARCHAR (20) NOT NULL,
);
--
INSERT INTO br2 (id, col1, col2)
VALUES (1, NULL, NULL); You cannot insert NULL values in col1 and col2 because they are defined as NOT NULL. If you run the script as is, you will receive an error. To fix this code, replace NULL in the VALUES part with some values (for example, 42 and 'bird'). INSERT INTO br2 (id, col1, col2)
VALUES (1, 42, 'bird'); | | |
Reports INSERT statements that assign values to generated columns. Generated columns can be read, but their values can not be directly written. CREATE TABLE foo
(
col1 INT,
col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
);
INSERT INTO foo(col1, col2) VALUES (1, 2); You cannot insert 2 into the col2 column because this column is generated. For this script to work, you can change 2 to DEFAULT. INSERT INTO foo(col1, col2) VALUES (1, DEFAULT); | | |
Reports ambiguous references in SQL code. CREATE TABLE foo
(
id INT,
name VARCHAR(5)
);
CREATE FUNCTION func(name VARCHAR(5)) RETURNS INT AS
$$
DECLARE
b INT;
BEGIN
-- `name` is ambiguous as it is used as a column name and a parameter
SELECT COUNT(*) INTO b FROM foo t WHERE t.name = name;
RETURN b;
END;
$$ LANGUAGE plpgsql; In PostgreSQL, you can use the #variable_conflict directives to explicitly specify a correct reference. For example, use #variable_conflict use_column to refer to a column name, or #variable_conflict use_variable to refer to a parameter. CREATE TABLE foo
(
id INT,
name VARCHAR(5)
);
CREATE FUNCTION func(name VARCHAR(5)) RETURNS INT AS
$$
#variable_conflict use_column
DECLARE
b INT;
BEGIN
SELECT COUNT(*) INTO b FROM foo t WHERE t.name = name;
RETURN b;
END;
$$ LANGUAGE plpgsql; | | |
Reports queries without explicit aliases in output expressions (for example, in the SELECT statement). CREATE TABLE foo(a INT, b INT);
SELECT 1, a + 1 AS A2, MAX(b) AS M
FROM foo; | ||
Reports functions that have no RETURN statements. CREATE FUNCTION foo RETURN int AS
BEGIN
END;
The foo function must return the integer value but the function body returns nothing. To fix the error, add a RETURN statement (for example, return 1;). CREATE FUNCTION foo RETURN int AS
BEGIN
RETURN 1;
END; | | |
Reports usages of multiple row limiting clauses in a single query. create table foo(a int);
select top 1 * from foo order by a offset 10 rows fetch next 20 rows only; The SELECT TOP clause is used to specify that only 1 record must be returned. The FETCH clause specifies the number of rows to return after the OFFSET clause has been processed. But as we already have the SELECT TOP limiting clause, the FETCH clause might be redundant. | | |
Reports arguments that are used without names in routine calls. By default, this inspection is disabled. CREATE FUNCTION foo(n INT, m INT) RETURNS INT AS
BEGIN
RETURN n + m;
END;
CREATE PROCEDURE test AS
BEGIN
foo n = 1, m = 2;
--- The following call misses parameter names and will be highlighted
foo 1, 2;
END; Parameters 1, 2 in the foo 1, 2; call are highlighted because they miss names. | ||
Reports the absence of data sources in the Database tool window (View | Tool Windows | Database). | | |
Reports comparisons with NULL that can be replaced with IS NULL or IS NOT NULL operators. CREATE TABLE foo ( id int );
SELECT * FROM foo WHERE NULL = NULL;
SELECT * FROM foo WHERE NULL != NULL; The NULL = NULL can be replaced with IS NULL, the NULL != NULL comparison with IS NOT NULL. To do this replacement, you can use Use IS NULL operator or Use IS NOT NULL operator quick-fixes. SELECT * FROM foo WHERE NULL IS NULL;
SELECT * FROM foo WHERE NULL IS NOT NULL; | | |
Reports redundant ELSE NULL clauses. SELECT CASE WHEN 2 > 1 THEN 'OK' ELSE NULL END AS alias FROM foo; The ELSE NULL part will never be executed and may be omitted. | | |
Reports alias expressions that duplicate names of columns in tables and might be redundant. CREATE TABLE foo(a INT, b INT);
SELECT * FROM foo foo(a, b);
SELECT * FROM foo foo(a);
SELECT * FROM foo foo(x);
SELECT * FROM foo foo(x, y); The first two aliases use the same column names as in the foo table. They are considered redundant because they column names are identical. | | |
Reports all the arguments except for the first expression that does not evaluate to NULL in COALESCE functions. SELECT COALESCE(NULL, NULL, NULL, 42, NULL, 'string') as a; The first NOT NULL argument is 42, all other arguments will be grayed out. | | |
Reports redundant ordering directions like ASC and DESC in ORDER BY clauses. CREATE TABLE foo(a INT, b INT, c INT);
SELECT * FROM foo ORDER BY a ASC, b DESC, c ASC; The ORDER BY keyword sorts the records in the ascending order by default. So, the ASC keyword for a and c columns is redundant. | | |
Reports redundant row limiting clauses like FETCH and LIMIT in queries. CREATE TABLE foo(a INT);
SELECT * FROM foo WHERE EXISTS(SELECT * FROM foo LIMIT 2);
SELECT * FROM foo WHERE EXISTS(SELECT * FROM foo FETCH FIRST 2 ROWS ONLY); To fix the warning, you can add OFFSET to limiting clauses. If OFFSET is missing, then LIMIT is redundant because the usage of LIMIT does not influence the operation result of EXISTS. In case with OFFSET, we skip first N rows and this will influence the output. SELECT * FROM foo WHERE EXISTS(SELECT * FROM foo OFFSET 1 ROW LIMIT 2);
SELECT * FROM foo WHERE EXISTS(SELECT * FROM foo OFFSET 1 ROW FETCH FIRST 2 ROWS ONLY); | | |
Reports situations when a dialect is not assigned to an SQL file. | | |
Reports situations when source code of a database object has been changed.
| | |
Reports statements that might lead to modification of a database during a read-only connection. CREATE TABLE foo(a INT);
INSERT INTO foo VALUES (1); As CREATE TABLE and INSERT INTO statements lead to a database modification, these statements will be highlighted in read-only connection mode. | | |
Reports incorrect usages of transition table variables in triggers. CREATE TABLE foo(a INT);
CREATE TRIGGER trg
AFTER DELETE ON foo
BEGIN
SELECT * FROM NEW;
END;
CREATE TRIGGER trig AFTER INSERT ON foo
REFERENCING OLD ROW AS newrow
FOR EACH ROW WHEN (a > 1)
INSERT INTO foo VALUES (1) In HSQLDB, DELETE triggers may be used only with the OLD state while INSERT triggers may have only the NEW state. So, in the previous example, NEW in SELECT * FROM NEW; will be highlighted as well as OLD in REFERENCING OLD ROW AS newrow. | | |
Reports type-related errors. | | |
Reports string literals that use national characters without the SELECT 'abcde' AS a;
SELECT N'abcde' AS b;
SELECT 'абвгд' AS c;
SELECT N'абвгд' AS d; The SELECT 'абвгд' AS c; does not have the N prefix, the 'абвгд' part will be highlighted. | | |
Reports unreachable statements inside SQL routines. CREATE FUNCTION foo() RETURNS INT AS
BEGIN
THROW;
RETURN 1;
END; In Microsoft SQL Server, the THROW statement raises an exception and transfers execution to the CATCH block of the TRY...CATCH construct. Therefore, the RETURN 1; part will never be executed. | | |
Reports unresolved SQL references. CREATE TABLE users(id INT, name VARCHAR(40));
CREATE TABLE admins(id INT, col1 INT);
SELECT users.id, admins.id FROM admins WHERE admins.id > 1; The users.id column is unresolved because the users table is missing in the FROM clause. | | |
Reports missing conditional checks for statements that might modify the whole database. CREATE TABLE foo (a INT,b INT,c INT);
CREATE TABLE bar (a INT,b INT,c INT);
DELETE table1 FROM foo table1 INNER JOIN bar table2; | | |
Reports unused common table expressions (CTE) inside the query. CREATE TABLE foo(a INT);
WITH a AS (SELECT 1 AS x FROM foo)
SELECT 1 + 2 FROM foo; By using WITH, we create a temporary named result set with the name a, also known as a common table expression (CTE). But we do not use this CTE later in the code. The unused CTE is greyed out. | | |
Reports columns, aliases, and other subquery items that are not referenced in the outer query expression. CREATE TABLE for_subquery(id INT);
SELECT a, q FROM (SELECT 1 AS a, 10 AS b, 2 + 3 AS q, id
FROM for_subquery) x; We reference a and q aliases from a subquery. But the b alias and the id column are not referenced in the outer SELECT statement. Therefore, b and id are grayed out. | | |
Reports unused arguments, variables, or parameters. CREATE FUNCTION foo(PARAMUSED INT, PARAMUNUSED INT) RETURNS INT AS
$$
BEGIN
RETURN PARAMUSED;
END
$$ LANGUAGE plpgsql; The PARAMUNUSED parameter is not used in the function and might be deleted. | | |
Reports usages of backward GOTO statements and GOTO statements used to exit a loop. CREATE PROCEDURE test(n INT) AS
DECLARE
x INT;
BEGIN
x := 0;
GOTO a;
<<a>> x := 1;
IF (n = 0) THEN
GOTO a;
END IF;
WHILE TRUE
LOOP
GOTO b;
END LOOP;
<<b>> x := 3;
END; | ||
Reports usages of transaction management statements like COMMIT or ROLLBACK in trigger bodies. CREATE TABLE employee_audit
(
id INT NOT NULL,
update_date DATE NOT NULL,
old_name VARCHAR2(100),
new_name VARCHAR2(100)
);
CREATE TABLE employees
(
id INT NOT NULL,
name VARCHAR2(100) NOT NULL
);
CREATE OR REPLACE TRIGGER trig_commit
AFTER UPDATE OF name
ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit VALUES (:old.id, SYSDATE, :old.name, :new.name);
COMMIT;
END;
CREATE OR REPLACE TRIGGER trig_rollback
AFTER UPDATE OF name
ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit VALUES (:old.id, SYSDATE, :old.name, :new.name);
ROLLBACK;
END; | | |
Reports situations when CASE and COALESCE calls are interchangeable. This inspection has the following intention actions: Replace with 'COALESCE' call and the opposite one Replace with CASE expression. SELECT
-- this CASE may be replaced by COALESCE
CASE
WHEN C1 IS NOT NULL THEN C1
ELSE 0
END
FROM dual; In the example, the CASE statement can be replaced with SELECT COALESCE(C1, 0) that produces the same output. If you prefer using CASE expressions, select the Prefer CASE expressions over COALESCE function option on the inspection page. | | |
Reports situations when CASE and IF are interchangeable. SELECT CASE
WHEN C1 IS NULL THEN 1
ELSE 0
END
FROM dual; To keep your code short, you can replace the CASE structure with IF. You can do that by applying the Replace with 'IF' call intention action. The example code will look as follows: SELECT IF(C1 IS NULL, 1, 0)
FROM dual; To revert IF to CASE, click IF and apply the Replace with CASE expression intention action. | | |
Reports calls in which positional arguments go after the named ones. Works in PostgreSQL, Oracle, and Db2. CREATE FUNCTION foo(a int, b int, c int) RETURNS int
LANGUAGE plpgsql AS
$$
BEGIN
RETURN a + b + c;
END
$$;
SELECT foo(a => 1, b => 2, c => 3);
-- `3` goes after the named argument
SELECT foo(1, b => 2, 3);
-- `1` and `3` go after the named argument
SELECT foo(b => 2, 1, 3); | | |
Reports situations when a number of parameters in VALUES does not match a number of columns in a target table. CREATE TABLE foo(a INT, b INT, c INT);
INSERT INTO foo VALUES (1,2,3,4) The foo table has three columns but in the INSERT INTO statement we pass four. | |
