False positive SQL error

[hugorouty]

Hello,

It seems that the bug mentioned last month (https://github.com/spring-projects/sts4/issues/1274) returns with the new v1.56.0 upgrade. Since this morning I have hundreds of errors on my VSCode Spring Boot project, I just reverted to v1.55.1 and all the errors have disappeared...

This is my problems panel on VSCode, filtered on "errors" :

With v1.55.1 :

With 1.56.0 :

There are two different errors:

First is : PostgreSQL: no viable alternative at input ':user'vscode-spring-boot(SQL_SYNTAX)

Second error type is :

PostgreSQL: mismatched input '<EOF>' expecting {WHITESPACE, A_, ABORT, ABS, ABSOLUTE, ACCESS, ACTION, ADA, ADD, ADMIN, AFTER, AGGREGATE, ALLOCATE, ALSO, ALTER, ALWAYS, ANALYZE, ANY, ARE, ASENSITIVE, ASSERTION, ASSIGNMENT, AT, ATOMIC, ATTRIBUTE, ATTRIBUTES, AVG, BACKWARD, BEFORE, BEGIN, BERNOULLI, BETWEEN, BIGINT, BIT, BIT_LENGTH, BLOB, BOOLEAN, BREADTH, BY, C_, CACHE, CALL, CALLED, CARDINALITY, CASCADE, CASCADED, CATALOG, CATALOG_NAME, CEIL, CEILING, CHAIN, CHAR, CHAR_LENGTH, CHARACTER, CHARACTER_LENGTH, CHARACTER_SET_CATALOG, CHARACTER_SET_NAME, CHARACTER_SET_SCHEMA, CHARACTERISTICS, CHARACTERS, CHECKPOINT, CLASS, CLASS_ORIGIN, CLOB, CLOSE, CLUSTER, COALESCE, COBOL, COLLATION_CATALOG, COLLATION_NAME, COLLATION_SCHEMA, COLLECT, COLUMN_NAME, COMMAND_FUNCTION, COMMAND_FUNCTION_CODE, COMMENT, COMMIT, COMMITTED, CONDITION, CONDITION_NUMBER, CONNECT, CONNECTION, CONNECTION_NAME, CONSTRAINT_CATALOG, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, CONSTRAINTS, CONSTRUCTOR, CONTAINS, CONTINUE, CONVERSION, CONVERT, COPY, CORR, CORRESPONDING, COUNT, COVAR_POP, COVAR_SAMP, CREATE, CSV, CUBE, CUME_DIST, CURRENT, CURRENT_DEFAULT_TRANSFORM_GROUP, CURRENT_PATH, CURRENT_TRANSFORM_GROUP_FOR_TYPE, CURSOR, CURSOR_NAME, CYCLE, DATA, DATABASE, DATE, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, DAY, DEALLOCATE, DEC, DECIMAL, DECLARE, DEFAULTS, DEFERRED, DEFINED, DEFINER, DEGREE, DELETE, DELIMITER, DELIMITERS, DENSE_RANK, DEPTH, DEREF, DERIVED, DESCRIBE, DESCRIPTOR, DETERMINISTIC, DIAGNOSTICS, DICTIONARY, DISCARD, DISCONNECT, DISPATCH, DOMAIN, DOUBLE, DROP, DYNAMIC, DYNAMIC_FUNCTION, DYNAMIC_FUNCTION_CODE, EACH, ELEMENT, ENCODING, ENCRYPTED, END, EQUALS, ESCAPE, EVERY, EXCEPTION, EXCLUDE, EXCLUDING, EXCLUSIVE, EXEC, EXECUTE, EXISTS, EXP, EXPLAIN, EXTENSION, EXTERNAL, EXTRACT, FILTER, FINAL, FIRST, FLOAT, FLOOR, FOLLOWING, FORCE, FORMAT, FORTRAN, FORWARD, FOUND, FREE, FUNCTION, FUSION, G_, GENERAL, GENERATED, GET, GLOBAL, GO, GOTO, GRANTED, GREATEST, GROUPING, HANDLER, HIERARCHY, HOLD, HOST, HOUR, IDENTITY, IGNORE, IMMEDIATE, IMMUTABLE, IMPLEMENTATION, IMPLICIT, INCLUDING, INCREMENT, INDEX, INDICATOR, INHERITS, INOUT, INPUT, INSENSITIVE, INSERT, INSTANCE, INSTANTIABLE, INSTEAD, INT, INTEGER, INTERSECTION, INTERVAL, INVOKER, ISOLATION, K_, KEY, KEY_MEMBER, KEY_TYPE, LANGUAGE, LARGE, LAST, LEAST, LEFT, LENGTH, LEVEL, LISTEN, LN, LOAD, LOCAL, LOCATION, LOCATOR, LOCK, LOCKED, LOWER, M_, MAP, MATCH, MATCHED, MAX, MAXVALUE, MEMBER, MERGE, MESSAGE_LENGTH, MESSAGE_OCTET_LENGTH, MESSAGE_TEXT, METHOD, MIN, MINUTE, MINVALUE, MOD, MODE, MODIFIES, MODULE, MONTH, MORE_, MOVE, MULTISET, MUMPS, NAME, NAMES, NATIONAL, NCHAR, NCLOB, NESTING, NEW, NEXT, NO, NONE, NORMALIZE, NORMALIZED, NOTHING, NOTIFY, NOWAIT, NULLABLE, NULLIF, NULLS, NUMBER, NUMERIC, OBJECT, OCTET_LENGTH, OCTETS, OF, OFF, OIDS, OLD, OPEN, OPERATOR, OPTION, OPTIONS, ORDERING, ORDINALITY, OTHERS, OUT, OUTPUT, OVER, OVERLAY, OVERRIDING, OWNER, PAD, PARAMETER, PARAMETER_MODE, PARAMETER_NAME, PARAMETER_ORDINAL_POSITION, PARAMETER_SPECIFIC_CATALOG, PARAMETER_SPECIFIC_NAME, PARAMETER_SPECIFIC_SCHEMA, PARTIAL, PARTITION, PASCAL, PASSWORD, PATH, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, PLAIN, PLI, POSITION, POWER, PRECEDING, PRECISION, PREPARE, PRESERVE, PRIOR, PRIVILEGES, PROCEDURAL, PROCEDURE, PUBLIC, QUOTE, RANGE, RANK, READ, READS, REAL, REASSIGN, RECHECK, RECURSIVE, REF, REFERENCING, REFRESH, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY, REINDEX, RELATIVE, RELEASE, RENAME, REPEATABLE, REPLACE, RESET, RESTART, RESTRICT, RESULT, RETURN, RETURNED_CARDINALITY, RETURNED_LENGTH, RETURNED_OCTET_LENGTH, RETURNED_SQLSTATE, RETURNS, REVOKE, RIGHT, ROLE, ROLLBACK, ROLLUP, ROUTINE, ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_SCHEMA, ROW, ROW_COUNT, ROW_NUMBER, ROWS, RULE, SAVEPOINT, SCALE, SCHEMA, SCHEMA_NAME, SCOPE, SCOPE_CATALOG, SCOPE_NAME, SCOPE_SCHEMA, SCROLL, SEARCH, SECOND, SECTION, SECURITY, SELECT, SELF, SENSITIVE, SEQUENCE, SEQUENCES, SERIALIZABLE, SERVER_NAME, SESSION, SET, SETOF, SETS, SHARE, SHOW, SIMPLE, SIZE, SMALLINT, SOME, SOURCE, SPACE, SPECIFIC, SPECIFIC_NAME, SPECIFICTYPE, SQL, SQLCODE, SQLERROR, SQLEXCEPTION, SQLSTATE, SQLWARNING, SQRT, STABLE, START, STATE, STATEMENT, STATIC, STATISTICS, STDDEV_POP, STDDEV_SAMP, STDIN, STDOUT, STORAGE, STRICT, STRUCTURE, STYLE, SUBCLASS_ORIGIN, SUBMULTISET, SUBSTRING, SUM, SYSID, SYSTEM, SYSTEM_USER, TABLE, TABLE_NAME, TABLESPACE, TEMP, TEMPLATE, TEMPORARY, TIES, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TOP_LEVEL_COUNT, TRANSACTION, TRANSACTION_ACTIVE, TRANSACTIONS_COMMITTED, TRANSACTIONS_ROLLED_BACK, TRANSFORM, TRANSFORMS, TRANSLATE, TRANSLATION, TREAT, TRIGGER, TRIGGER_CATALOG, TRIGGER_NAME, TRIGGER_SCHEMA, TRIM, TRUE, TRUNCATE, TRUSTED, TYPE, UESCAPE, UNBOUNDED, UNCOMMITTED, UNDER, UNENCRYPTED, UNKNOWN, UNLISTEN, UNNAMED, UNNEST, UNTIL, UPDATE, UPPER, USAGE, USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_CODE, USER_DEFINED_TYPE_NAME, USER_DEFINED_TYPE_SCHEMA, VACUUM, VALID, VALIDATOR, VALUE, VALUES, VAR_POP, VAR_SAMP, VARCHAR, VARYING, VIEW, VOLATILE, WHENEVER, WIDTH_BUCKET, WITH, WITHIN, WITHOUT, WORK, WRITE, YEAR, ZONE, ABSTIME, BIGSERIAL, BIT_VARYING, BOOL, BOX, BYTEA, CHARACTER_VARYING, CIDR, CIRCLE, FLOAT4, FLOAT8, INET, INT2, INT4, INT8, JSON, JSONB, LINE, LSEG, MACADDR, MACADDR8, MONEY, PG_LSN, POINT, POLYGON, RELTIME, SERIAL, SERIAL2, SERIAL4, SERIAL8, SMALLSERIAL, TEXT, TIMESTAMPTZ, TIMETZ, TSQUERY, TSVECTOR, TXID_SNAPSHOT, UUID, VARBIT, XML, '(', DOUBLEQ_STRING_LITERAL, IDENTIFIER, IDENTIFIER_UNICODE}vscode-spring-boot(SQL_SYNTAX)

[BoykoAlex]

@hugorouty rather than revert to 1.55.1 turn off SQL queries validations here:

[BoykoAlex]

The only difference from 1.55.1 is that the default problem severity from IGNORE has changed to ERROR since we had more confidence in the parser as we now have a dedicated PostgreSQL ANTLR parser... but looks like we were too optimistic

[BoykoAlex]

The issue is the ; at the end... are you sure this is the right syntax?

[BoykoAlex]

@hugorouty Looks like ; is needed between statements. The parser seemed to be more strict in this case and doesn't allow for ; if the statement isn't followed by another statement. I can make the parser not as strict about it.

Do you mind pasting more queries with syntax errors? Looks like you have lots of them... I'm just curious to learn if there are more cases beyond ';' that require fixes. If you happen to have a link to a git repo with the project you're looking at it'd be even better :-) Thanks in advance!

[BoykoAlex]

I have fixed ; at the end of the first statement issue with 2b91b4cd538cf5083309a75391cca483ab24778e but likely there are others which I'd like to explore as well

[hugorouty]

@BoykoAlex

Thanks for the fix, I usually write and test my queries in a Datagrip console so I usually add; at the end of each. I will check during the day if I found others issues, but I can't share you the git repo it's a professional project (even table and column names are changed in shared queries).

[vchrisb]

I get an error when using a Param

  @Query(
    nativeQuery = true,
    value = "SELECT * FROM cards ORDER BY random() LIMIT :limit"
  )
  Set<Card> getRandomCards(@Param("limit") Integer number);

PostgreSQL: mismatched input 'LIMIT' expecting {<EOF>, ';'}

When replacing :limit with an integer, the error goes away.

[BoykoAlex]

@vchrisb Thanks for pasting the snippet. Seems like the issue is in the ANTLR parser grammar, namely clauses where parameter is expected. Will get this fixed

[BoykoAlex]

@vchrisb Fixed with 2b7ac1a3c22f39cdf0eb83858f9dd7efaf500bfd. Added support for more places to accept parameters. Interesting that parameter accepts reserved keyword limit... I ensured however that it is accepted becausew I trust this query works and worked for a long time ;-) However, there might be more reserved words that I'm missing so we'll add them gradually over time. Keeping opened for now for more PSQL snippets with parse errors. You can give it a try with a snapshot build from: https://cdn.spring.io/spring-tools/snapshot/STS4/nightly-distributions.html

[martinlippert]

Closing this for the upcoming 4.25.0 release now. Let's capture additional new findings in a separate new issue.

[rsercano]

Hello,

I just found out that "working queries" such as DELETE FROM X WHERE ID IN :ids still getting errors due to no parantesis around :ids parameter.

IMHO, such statements shouldn't be accepted as ERROR, practically this query is working just fine.

Using latest verison of extension.

[martinlippert]

@rsercano I created a new issue to collect further false positive errors around SQL validations for the next upcoming release: https://github.com/spring-projects/sts4/issues/1504. Please add a comment over there. Would be great if you could include a code snippet that contains the query that causes the false positive. Thanks!!!