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?

Have you tried enclosing the column alias between double quotation marks `" "` instead of single ones (`' '`)? — Barranka, Apr 13 '15 at 16:41
also, try avoid using dashes `-`. Try **`Throughput_run_1_8_11`** instead of `Throughput-run_1_8_11` — Barranka, Apr 13 '15 at 16:44
`as 'Throughput-run_1_8_11' ` is invalid SQL. Single quotes are for string literals. Double quotes are for identifiers — , Apr 13 '15 at 17:38
thanks barranka abd a_horse_with_no_name, that was the issue! — SnoBro, Apr 16 '15 at 19:21

score 3 · Answer 1 · answered Apr 21 '16 at 10:26

3

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

answered Apr 21 '16 at 10:26

Simon Ludwig

1,754
1
20
27

In you case the "like" is reserved SQL word. Any other name will work. – Dmytro Maslenko Oct 22 '19 at 19:42
If you really have to use reserved keywords as column name (I don't advice it though), you can also [escape](https://stackoverflow.com/a/3463189/2049986) the column name. – Jacob van Lingen May 04 '21 at 13:16
Saved me!!! The field `value` is also reserved nowadays with `H2` database – Marcello DeSales Jul 30 '22 at 17:58

Tymur Kubai aka SirDiR · Answer 2 · 2022-05-20T11:14:42.873

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.

edited May 20 '22 at 11:14

answered May 20 '22 at 11:06

Tymur Kubai aka SirDiR

384
3
16

1

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] * * 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.` – Gonan Aug 17 '22 at 09:37

score 1 · Answer 3 · answered Oct 06 '21 at 17:49

1

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.

answered Oct 06 '21 at 17:49

Kwonkyu Park

23
4

score 1 · Answer 4 · edited Jul 26 '22 at 14:28

1

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

edited Jul 26 '22 at 14:28

Eric Aya

69,473
35
181
253

answered Jul 26 '22 at 13:51

Nils Teipel

11
1

score 0 · Answer 5 · answered May 29 '20 at 06:01

0

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.

answered May 29 '20 at 06:01

GrigoreG

21
3

Dmitry Illy · Answer 6 · 2023-07-14T15:01:43.817

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]

Syntax error in SQL statement - H2 error 42001

6 Answers6