Data conversion error converting "'1TRUE'

[GoogleCodeExporter]

Please send a question to the H2 Google Group or StackOverflow first,
and only then, once you are completely sure it is an issue, submit it here.
The reason is that only very few people actively monitor the issue tracker.

Before submitting a bug, please also check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.I'm trying to run the following query:
Preparing: INSERT INTO manual_sentences (project_id, sentence_id, weight, 
is_from_external_source, original_sentence_id, `partition`) VALUES (?, ?, ?, ?, 
?, ?) ON DUPLICATE KEY UPDATE weight = weight + ?, is_from_external_source = 
is_from_external_source || ? 

Parameters: 42(Long), 1316(Long), 1(Integer), true(Boolean), 1315(Long), 
0(Integer), 1(Integer), true(Boolean)

2.If is_from_external_source = is_from_external_source || ?  kicks in the 
following error is returned on H2 DB running on MySQL mode:

org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: org.h2.jdbc.JdbcSQLException: Data 
conversion error converting "'1TRUE' (MANUAL_SENTENCES: IS_FROM_EXTERNAL_SOURCE 
TINYINT DEFAULT 0 NOT NULL)"; SQL statement:
INSERT INTO manual_sentences (project_id, sentence_id, weight, 
is_from_external_source, original_sentence_id, `partition`) 
        VALUES (?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE weight = weight + ?, is_from_external_source = is_from_external_source || ? [22018-175]
### The error may involve 
com.nuance.entrd.mc.nes.persistence.mappers.IManualSentenceMapper.addManualSente
nceObject-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO manual_sentences (project_id, sentence_id, weight, 
is_from_external_source, original_sentence_id, `partition`)         VALUES (?, 
?, ?, ?, ?, ?)        ON DUPLICATE KEY UPDATE weight = weight + ?, 
is_from_external_source = is_from_external_source || ?
### Cause: org.h2.jdbc.JdbcSQLException: Data conversion error converting 
"'1TRUE' (MANUAL_SENTENCES: IS_FROM_EXTERNAL_SOURCE TINYINT DEFAULT 0 NOT 
NULL)"; SQL statement:
INSERT INTO manual_sentences (project_id, sentence_id, weight, 
is_from_external_source, original_sentence_id, `partition`) 
        VALUES (?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE weight = weight + ?, is_from_external_source = is_from_external_source || ? [22018-175]
; SQL []; Data conversion error converting "'1TRUE' (MANUAL_SENTENCES: 
IS_FROM_EXTERNAL_SOURCE TINYINT DEFAULT 0 NOT NULL)"; SQL statement:
INSERT INTO manual_sentences (project_id, sentence_id, weight, 
is_from_external_source, original_sentence_id, `partition`) 
        VALUES (?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE weight = weight + ?, is_from_external_source = is_from_external_source || ? [22018-175]; nested exception is org.h2.jdbc.JdbcSQLException: Data conversion error converting "'1TRUE' (MANUAL_SENTENCES: IS_FROM_EXTERNAL_SOURCE TINYINT DEFAULT 0 NOT NULL)"; SQL statement:
INSERT INTO manual_sentences (project_id, sentence_id, weight, 
is_from_external_source, original_sentence_id, `partition`) 
        VALUES (?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE weight = weight + ?, is_from_external_source = is_from_external_source || ? [22018-175]
What is the expected output? What do you see instead?

What version of the product are you using? On what operating system, file
system, and virtual machine?

Do you know a workaround?
This query works on MySQL but fails on H2 DB so besides changing  my code to 
pass int instead of boolean I don't see other workaround

What is your use case, meaning why do you need this feature?

How important/urgent is the problem for you?

Please provide any additional information below.

Original issue reported on code.google.com by ghe...@gmail.com on 3 Mar 2014 at 8:46

[GoogleCodeExporter]

Closed because the reporter did not read the first line of the instructions.

Original comment by noelgrandin on 4 Mar 2014 at 12:30

• Changed state: Invalid

[GoogleCodeExporter]

H2 is not a MySQL clone.

I suggest to use "is_from_external_source = is_from_external_source or ?" 
instead. I think it is more logical to use "or" instead of "||", because "||" 
is the concatenation operation.

Original comment by thomas.t...@gmail.com on 4 Mar 2014 at 6:43