Syntax error in SQL statement - H2 error 42001

Question

Upon running this SQL statement:

select TimeInterval, 
       ((((Timer*60)/1.0)*100)/((10.0*60)/60.0)) as 'Throughput-run_1_8_11' 
from StatExternalData, StatisticDefinition 
where StatisticDefinition.ID=StatExternalData.StatDefId 
      and StatisticName='PSI_CompTran_Successful_Cnt'  
order by TimeInterval asc

I get this error:

"select TimeInterval, ((((Timer*60)/1.0)*100)/((10.0*60)/60.0)) as 'Throughput-run_1_8_11'[*] from StatExternalData, StatisticDefinition where StatisticDefinition.ID=StatExternalData.StatDefId and StatisticName='PSI_CompTran_Successful_Cnt'  order by TimeInterval asc"; 
expected "identifier"; [42001-185]

I've figured out that the [*] is indicating what part of the statement is incorrect and that H2 error code 42001 signifies an invalid SQL statement, but I've been banging my head on the wall for weeks trying to figure out what the problem is, anyone have an idea?

Bruit

Have you tried enclosing the column alias between double quotation marks " " instead of single ones (' ')? – Substratum

also, try avoid using dashes -. Try Throughput_run_1_8_11 instead of Throughput-run_1_8_11 – Substratum

as 'Throughput-run_1_8_11' is invalid SQL. Single quotes are for string literals. Double quotes are for identifiers – Packton

thanks barranka abd a_horse_with_no_name, that was the issue! – Bruit

Answer 1

I had the same issue:

My Entity looked like this:

@Entity
public class ShopCommentRating {

@NotNull
private Boolean like;

}

The resulting Query contained a [*]

To remove the error i had to change the field name to sth. like this:

@Entity
public class ShopCommentRating {

@NotNull
private Boolean commentLike;

}

'lower case camel case' name

Olympiaolympiad

In you case the "like" is reserved SQL word. Any other name will work. – Artificiality

If you really have to use reserved keywords as column name (I don't advice it though), you can also escape the column name. – Laird

Saved me!!! The field value is also reserved nowadays with H2 database – Advocacy

Answer 2

No one seams to be curious what 185 means in [42001-185].

42001 is public static final int SYNTAX_ERROR_2 = 42001; source code

According to h2 sources, 185 is position of error in sql, but for me it looks really measleading because actually it is combination of row and symbol in row

185 -> means 1-row and 85-symbol.

Groark

This doesn't seem to be true for the '185' code you mention. It's just the build number. At the top of the linked file it says: Syntax error in SQL statement "SELECT * FORM[*] TEST "; * SQL statement: select * form test [42000-125] * </pre> * The [*] marks the position of the syntax error * (FORM instead of FROM in this case). * The error code is 42000, and the build number is 125, * meaning version 1.2.125. – Sycamore

Answer 3

check out the SQL Grammar page from H2. In my case, the problem was table name 'order' and dash('-'), not underscore('_'). Silly but deadly.

In your query the dash of alias 'Throughput-run_1_8_11' would be problem.

Heliotherapy

Answer 4

I solved that kind of ErrorCode by renaming my Entity. It appears, that some names are beeing interpreted as query-commands -.-

Combination

Answer 5

i had an issue with the 'year' column during initializing my h2(2.1.214) via hibernate(6.2.5.Final), surprisingly. switched its name to 'releaseYear', worked out.

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "\000a    create table book (\000a        id bigint not null,\000a        supply_date timestamp(6),\000a        author varchar(255) not null,\000a        title varchar(255) not null,\000a        [*]year varchar(255) not null,\000a        primary key (id)\000a    )"; expected "identifier"; SQL statement:

create table book (
    id bigint not null,
    supply_date timestamp(6),
    author varchar(255) not null,
    title varchar(255) not null,
    year varchar(255) not null,
    primary key (id)
) [42001-214]

Chorizo

Answer 6

In case of 42001-197 you can also check for Oracle analytical functions and H2 version, which needs to be from 1.4.198 onward, especially for ROW_NUMBER OVER PARTITION.

Chisholm