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
– Barranka, Commented Apr 13, 2015 at 16:41
also, try avoid using dashes -. Try Throughput_run_1_8_11 instead of Throughput-run_1_8_11 — Barranka
– Barranka, Commented Apr 13, 2015 at 16:44
as 'Throughput-run_1_8_11' is invalid SQL. Single quotes are for string literals. Double quotes are for identifiers — user330315
– user330315, Commented Apr 13, 2015 at 17:38
thanks barranka abd a_horse_with_no_name, that was the issue! — SnoBro
– SnoBro, Commented Apr 16, 2015 at 19:21

Simon Ludwig · Accepted Answer · 2016-04-21 10:26:45Z

6

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, 2016 at 10:26

Simon Ludwig

1,8341 gold badge23 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dmytro Maslenko Over a year ago

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

Jacob van Lingen Over a year ago

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

Marcello DeSales Over a year ago

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

Tymur Kubai aka SirDiR · Accepted Answer · 2022-05-20 11:14:42Z

4

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, 2022 at 11:14

answered May 20, 2022 at 11:06

Tymur Kubai aka SirDiR

4244 silver badges18 bronze badges

1 Comment

Gonan Over a year ago

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.

Dmitry Illy · Accepted Answer · 2023-07-14 15:01:43Z

2

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]

edited Jul 14, 2023 at 15:01

answered Jul 14, 2023 at 15:01

Dmitry Illy

212 bronze badges

Comments

Kwonkyu Park · Accepted Answer · 2021-10-06 17:49:15Z

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 6, 2021 at 17:49

Kwonkyu Park

337 bronze badges

Comments

Eric Aya · Accepted Answer · 2022-07-26 14:28:57Z

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, 2022 at 14:28

Eric Aya

70.2k36 gold badges190 silver badges266 bronze badges

answered Jul 26, 2022 at 13:51

Nils Teipel

234 bronze badges

Comments

GrigoreG · Accepted Answer · 2020-05-29 06:01:16Z

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, 2020 at 6:01

GrigoreG

213 bronze badges

Collectives™ on Stack Overflow

Syntax error in SQL statement - H2 error 42001

6 Answers 6

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related