Cannot insert minus sign into varchar column with check constraint (column <> '')

Question

I have an H2 table with a varchar column with a check constraint defined like this:

CONSTRAINT my_constraint CHECK (varchar_field <> '')

The following insert statement fails, but succeeds when I remove the constraint or insert any other value that is not just the minus sign ("-").

PreparedStatement ps= con.prepareStatement("INSERT INTO my_table (id, varchar_field) VALUES (?, ?);");
ps.setInt(1, id);
ps.setString(2, "-"); 
ps.executeUpdate();

Update: further tests reveal behavior that I do not understand. Is the minus sign some kind of string literal operator in SQL/H2 database? If yes, how do I escape it?

enter image description here

This may be an issue with the Beta version of H2 that you are using. I just tried the same thing with the latest stable release (1.3.176) and `SELECT '-' = '' AS result;` returned `FALSE`. — Gord Thompson, Mar 08 '15 at 16:16

score 2 · Accepted Answer · edited May 23 '17 at 11:50

2

Based on Gord's comment I tested with H2 1.3.176, but I still had the same problem. When I created a new test database, I did not observe the problem.

-> For the new test db i did not specify a collation, but the application db is created with "COLLATION=ENGLISH STRENGTH PRIMARY".

I really didn't expect primary strength collation to cause ('-' = '') to evaluate to true... I will have to revisit my own question from a few years ago: H2 database collation strength: what to choose?.

I am again very confused and unsure about what collation I should use.

edited May 23 '17 at 11:50

Community

1
1

answered Mar 08 '15 at 19:05

Reto Höhener

5,419
4
39
79

1

To recap: **specifying a collation ENGLISH STRENGTH PRIMARY causes the comparison of the constraint to consider a minus a non-sign.** Like "sublist" and "sub-list" being _equal_. – Joop Eggen Mar 08 '15 at 19:35
1

Try `CHECK LENGTH(varchar_field) > 0`. – Joop Eggen Mar 08 '15 at 19:48

score 1 · Answer 2 · answered Mar 10 '15 at 14:18

1

As documented, H2 uses java.text.Collator, so you get the same result as with that class. Example:

java.text.Collator c = Collator.getInstance();
c.setStrength(Collator.PRIMARY);
System.out.println(c.compare("-", "") == 0);
System.out.println(c.compare("-", "  ") == 0);
System.out.println(c.compare("-", "--") == 0);

Result:

true
true
true

answered Mar 10 '15 at 14:18

Thomas Mueller

48,905
14
116
132

Yes, thank you, Thomas. I just never thought that the primary strength collator would behave like that. For my new project I am now leaning towards using no collation at all, VARCHAR_IGNORECASE columns for some unique secondary keys, and rely on application code instead of SQL for all sorting requirements (still thinking about searching). – Reto Höhener Mar 10 '15 at 17:49
It's sometimes good if you have a method that converts your user data to a byte array or string that is easily sortable. And maybe you can convert this back to your user data. – Thomas Mueller Mar 12 '15 at 05:25

Cannot insert minus sign into varchar column with check constraint (column <> '')

2 Answers2