JetBrains Rider 2023.1 Help

Code Inspections in SQL

This topic lists all JetBrains Rider code inspections available in SQL.

You can toggle specific inspections or change their severity level on the Editor | Inspection Settings | Inspection Severity | Other Languages page of the IDE settings Ctrl+Alt+S.

Inspection

Description

Default Severity

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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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, 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;

Warning 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 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 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 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 Warning

Insertion into generated columns

Reports INSERT statements that assign values to generated columns. Generated columns can be read, but their values can not 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 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 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 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 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, see 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 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 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 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 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 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 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 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 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 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 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 Warning

Types compatibility

Reports type-related errors.

Warning 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, see 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 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 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 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 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 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 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 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 details, see 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 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 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 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 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 Warning

Last modified: 21 March 2023