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. Example (Microsoft SQL Server): 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. The following situations are considered:
| | |
Reports columns that have identical names but belong to different tables. Example (MySQL): 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 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. Example (MySQL): CREATE TABLE my_table
(
id INT AUTO_INCREMENT,
c2 INT AUTO_INCREMENT,
); The AUTO_INCREMENT constraint for 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. Example (MySQL): 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 SELECT * FROM t1 JOIN t2 ON t1.j = t2.l; | | |
Reports SELECT aliases with names that match column names in the FROM clause. Example (MySQL): CREATE TABLE foo (a INT, b INT, c INT);
SELECT a b, c FROM foo; The | | |
Reports columns that are not in the GROUP BY clause or inside an aggregate function call. Example (Microsoft SQL Server): 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 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. Example (MySQL): CREATE TABLE t1 (a TEXT, b INT, c BOOLEAN);
SELECT a FROM t1 WHERE 'Cat' = 'Cat'; The | | |
Reports conditions and expressions that are always true, false or null. Example (MySQL): CREATE TABLE t1 (a TEXT, b INT, c BOOLEAN);
SELECT a FROM t1 WHERE 'Cat' = 'Cat';
SELECT a FROM t1 WHERE 'Cat' = null; The The | | |
Reports schemas and databases in the current session that are not introspected. For example, this warning might occur when you try to create a table in the schema that is not introspected. Introspection is a method of inspecting a data source. When you perform introspection, structural information in the data source is inspected to detect tables, columns, functions, and other elements with their attributes. | | |
Reports usages of DELETE or UPDATE statements without WHERE clauses. Without WHERE clauses, DELETE drops all the data from the table, and UPDATE overwrites values for all the table rows. Example (MySQL): 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. Reported types:
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. Example (Sybase ASE): CREATE TABLE t1 (a TEXT, b INT, c INT);
SELECT a AS x, b AS x FROM t1; The | | |
Reports derived tables without aliases. Example (MySQL): 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. The inspection will report a wrong number of arguments, invalid keywords, wrong data types, and other issues. Example (MySQL): 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 | | |
Reports situations when you use SQL reserved keywords as identifier names in your query. Example (Microsoft SQL Server): CREATE TABLE select (identity INT IDENTITY NOT NULL, order INT NOT NULL); We use 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. Example (MySQL): 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, refer to 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. Example (Microsoft SQL Server): CREATE PROCEDURE test() AS
BEGIN
DECLARE myVarOk VARCHAR(5) = 'abcde';
DECLARE myVarExceeded VARCHAR(5) = 'abcde12345';
SET myVarOk = 'xyz';
SET myVarExceeded = '123456789';
END; The 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. Example (Microsoft SQL Server): 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 | | |
Reports cases when you insert NULL values into columns that accept only NOT NULL values. Example (Microsoft SQL Server): 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 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 cannot be directly written. Example (PostgreSQL): CREATE TABLE foo
(
col1 INT,
col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
);
INSERT INTO foo(col1, col2) VALUES (1, 2); You cannot insert | | |
Reports ambiguous references in SQL code. For example, when a name refer to both a table column and a routine parameter. The execution of such code might lead to errors or unexpected results due to counter-intuitive resolution logic. Usually, names with a more local scope have higher priority. Example (PostgreSQL): 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 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). Example (PostgreSQL): 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. Example (Oracle): CREATE FUNCTION foo RETURN int AS
BEGIN
END;
The CREATE FUNCTION foo RETURN int AS
BEGIN
RETURN 1;
END; | | |
Reports usages of multiple row limiting clauses in a single query. Example (Microsoft SQL Server): 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. For more information about the difference between named and unnamed parameters, refer to Binding Parameters by Name (Named Parameters) at docs.microsoft.com. Example (Microsoft SQL Server): 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 | ||
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. Example (Microsoft SQL Server): CREATE TABLE foo ( id int );
SELECT * FROM foo WHERE NULL = NULL;
SELECT * FROM foo WHERE NULL != NULL; The SELECT * FROM foo WHERE NULL IS NULL;
SELECT * FROM foo WHERE NULL IS NOT NULL; | | |
Reports redundant ELSE NULL clauses. Example (MySQL): SELECT CASE WHEN 2 > 1 THEN 'OK' ELSE NULL END AS alias FROM foo; The | | |
Reports alias expressions that duplicate names of columns in tables and might be redundant. Example (PostgreSQL): 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 | | |
Reports all the arguments except for the first expression that does not evaluate to NULL in COALESCE functions. Example (MySQL): SELECT COALESCE(NULL, NULL, NULL, 42, NULL, 'string') as a; The first NOT NULL argument is | | |
Reports redundant ordering directions like ASC and DESC in ORDER BY clauses. Example (MySQL): 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 | | |
Reports redundant row limiting clauses like FETCH and LIMIT in queries. Example (PostgreSQL): 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 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. For example, when you open a new SQL file without assigning a dialect to it, you see a notification where the best matching dialect is advised. Click the Use <dialect> link to use the advised dialect. Alternatively, click the Change dialect to link to select the other dialect. | | |
Reports situations when source code of a database object has been changed. The inspection is triggered when you perform database or object introspection. The introspection is run when you open source code of an object, run statements, and perform code refactoring. Also, you can run introspection by right-clicking an object and selecting Refresh. The inspection covers the following situations:
| | |
Reports statements that might lead to modification of a database during a read-only connection. To enable read-only mode for a connection, right-click a data source in the Database tool window (View | Tool Windows | Database) and select Properties. In the Data Sources and Drivers dialog, click the Options tab and select the Read-only checkbox. Example (MySQL): CREATE TABLE foo(a INT);
INSERT INTO foo VALUES (1); As | | |
Reports incorrect usages of transition table variables in triggers. Example (HSQLDB): 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 | | |
Reports type-related errors. | | |
Reports string literals that use national characters without the Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters. For more information, refer to nchar and nvarchar (Transact-SQL) at docs.microsoft.com. Example (Microsoft SQL Server): SELECT 'abcde' AS a;
SELECT N'abcde' AS b;
SELECT 'абвгд' AS c;
SELECT N'абвгд' AS d; The | | |
Reports unreachable statements inside SQL routines. Example (Microsoft SQL Server): CREATE FUNCTION foo() RETURNS INT AS
BEGIN
THROW;
RETURN 1;
END; In Microsoft SQL Server, the | | |
Reports unresolved SQL references. Example (MySQL): 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 | | |
Reports missing conditional checks for statements that might modify the whole database. For example, usages of JOIN clauses inside DELETE statements without ON or WHERE. Without conditional checks on JOIN, DELETE drops contents of the entire table. Example (MySQL): 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. Example (PostgreSQL): 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 | | |
Reports columns, aliases, and other subquery items that are not referenced in the outer query expression. Example (PostgreSQL): 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 | | |
Reports unused arguments, variables, or parameters. Example (PostgreSQL): CREATE FUNCTION foo(PARAMUSED INT, PARAMUNUSED INT) RETURNS INT AS
$$
BEGIN
RETURN PARAMUSED;
END
$$ LANGUAGE plpgsql; The | | |
Reports usages of backward GOTO statements and GOTO statements used to exit a loop. The extensive use of GOTO statements is generally not recommended. For more information, refer to GOTO statement in SQL procedures at ibm.com. Instead of jumping back to a previous statement using GOTO, consider using a loop. Instead of exiting the WHILE loop with GOTO, consider using other control-of-flow statements (for example, RETURN or BREAK). Example (Oracle): 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. With COMMIT or ROLLBACK statements in a trigger body, the trigger will not compile. The fail happens because triggers start during transactions. When the trigger starts the current transaction is still not complete. As COMMIT terminates a transaction, both statements (COMMIT and ROLLBACK) would lead to an exception. Changes that are executed in a trigger should be committed (or rolled back) by the owning transaction that started the trigger. Example (Oracle): 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. Example (MySQL): 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 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. Example (MySQL): 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. Example (In PostgreSQL): 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. Example (MySQL): CREATE TABLE foo(a INT, b INT, c INT);
INSERT INTO foo VALUES (1,2,3,4) The | |
