Adding not null column without default value | 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. |  Warning |
Aggregate-related problems | Reports invalid usages of SQL aggregate functions. The following situations are considered: Columns that are used in HAVING and ORDER BY clauses but are missed in GROUP BY clauses. CREATE TABLE foo(id INT PRIMARY KEY, a INT, b INT);
SELECT a, MAX(b) FROM foo GROUP BY a HAVING b > 0;
SELECT * FROM foo GROUP BY a ORDER BY b;
This rule does not apply when grouping is made by the primary key. SELECT * FROM foo GROUP BY id ORDER BY b;
Aggregate functions in a wrong context. Usually, you can use aggregate functions in the following contexts: a list of expressions in SELECT; in HAVING and ORDER BY sections; and other dialect-specific cases. The following queries will display an error. SELECT a FROM foo WHERE MAX(b) > 0;
SELECT a FROM foo GROUP BY MAX(a);
Nested calls of aggregate functions. SELECT MAX(SUM(a)) FROM foo GROUP BY a;
This rule does not apply to analytic functions. The following query is valid and correct. SELECT MAX(SUM(a) OVER ()) FROM foo;
Usages of HAVING without aggregate functions. In this case, consider rewriting your code using the WHERE section. SELECT a, MAX(b) FROM foo GROUP BY a HAVING a > 0;
| Warning |
Ambiguous reference | 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 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;
| Warning |
Auto-increment duplicate | 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 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,
);
| Warning |
Check using clause columns | 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 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;
| Warning |
Column is shadowed by alias | 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 a column uses the b alias but the b name is also used by the column from the foo table. | Warning |
Column should be in group by clause | 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 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;
| Warning |
Constant condition | 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 'Cat' = 'Cat' is always true and will be reported. | Warning |
Constant expression | 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 'Cat' = 'Cat' is always true and will be reported. The 'Cat' = null is always null and will be reported. | Warning |
Current console schema introspected | 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. | Warning |
Delete or update statement without where clauses | 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;
| Warning |
Deprecated type | 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
);
| Warning |
Duplicating column name in SELECT | 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 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. | Warning |
Each derived table should have alias | 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);
| Warning |
Function signature | 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 IFNULL() function accepts strictly two arguments. So, only the SELECT IFNULL(a, b) FROM foo; query is correct. | Warning |
Identifier should be quoted | 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 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);
| Warning |
Ill-formed date/time literals | 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;
| Warning |
Illegal cursor state | Reports illegal cursor states inside SQL routines. A routine has CLOSE or FETCH statements but a cursor might be closed. A routine has the OPEN statement but a cursor might be opened.
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;
| Warning |
Implicit string truncation | 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 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;
| Warning |
Index is dependent on column | 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 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). | Warning |
Insert NULL into NOT NULL column | 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 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');
| Warning |
Insertion into generated columns | 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 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); | Warning |
Misleading references | 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 #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;
| Warning |
Missing column aliases | 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;
| Disabled |
Missing return statement | Reports functions that have no RETURN statements. Example (Oracle): 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;
|  Error |
Multiple row limiting/offset clauses in queries | 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. | Warning |
Named arguments should be used | 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 1, 2 in the foo 1, 2; call are highlighted because they miss names. | Disabled |
No data sources configured | Reports the absence of data sources in the Database tool window (View | Tool Windows | Database). | Warning |
Null comparison | 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 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;
| Warning |
Redundant ELSE NULL clause | Reports redundant ELSE NULL clauses. Example (MySQL): 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. | Warning |
Redundant alias expressions | 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 foo table. They are considered redundant because they column names are identical. | Warning |
Redundant code in COALESCE call | 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 42, all other arguments will be grayed out. | Warning |
Redundant ordering direction | 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 ASC keyword for a and c columns is redundant. | Warning |
Redundant row limiting in queries | 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 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);
| Warning |
SQL dialect detection | 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. | Warning |
SQL source modification detection | 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: Object source code was changed in the database but code in the editor was not updated. Works in PostgreSQL, Microsoft SQL Server, Oracle, and Sybase ASE. You changed the object source code, introspected the database, but source code has been already changed by someone else. The database introspector was updated in the IDE and you need to download new object properties that were missing in the previous introspector version.
| Warning |
Statement with side effects | 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 CREATE TABLE and INSERT INTO statements lead to a database modification, these statements will be highlighted in read-only connection mode. | Warning |
Suspicious code in triggers | 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 SELECT * FROM NEW; will be highlighted as well as OLD in REFERENCING OLD ROW AS newrow. | Warning |
Types compatibility | Reports type-related errors. | Warning |
Unicode usage in SQL | Reports string literals that use national characters without the N prefix. 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 SELECT 'абвгд' AS c; does not have the N prefix, the 'абвгд' part will be highlighted. | Warning |
Unreachable code | 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 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. | Warning |
Unresolved reference | 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 users.id column is unresolved because the users table is missing in the FROM clause. | Error |
Unsafe 'join' clause in 'delete' statement | 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;
| Warning |
Unused common table expression | 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 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. | Warning |
Unused subquery item | 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 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. | Warning |
Unused variable | 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 PARAMUNUSED parameter is not used in the function and might be deleted. | Warning |
Usages of GOTO statements | 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;
| Disabled |
Use of transaction management statements in triggers | 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;
| Warning |
Using CASE instead of COALESCE function and vice versa | 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 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. | Warning |
Using CASE instead of conditional function and vice versa | 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. | Warning |
Using of named and positional arguments | 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);
| Error |
VALUES clause cardinality | 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 foo table has three columns but in the INSERT INTO statement we pass four. | Warning |