Better database interoperability for BOOLEAN bind values inserted into non-BOOLEAN columns

[Lukas Eder]

Hello guys,

I was wondering if it would make sense for H2 to provide better database interoperability for BOOLEAN bind values when they're inserted into non-BOOLEAN columns. Consider this test scenario:

create table test(val varchar(1));

Now this program fails:

Connection c = getConnection();

PreparedStatement s = c.prepareStatement("insert into test values (?)");

s.setBoolean(1, true);

s.executeUpdate(); // H2 fails here

s = c.prepareStatement("select * from test");

ResultSet rs = s.executeQuery();

rs.next();

System.out.println(rs.getString(1));

The exception I get is this:

org.h2.jdbc.JdbcSQLException: Wert zu gross / lang für Feld "VAL VARCHAR(1)": "'TRUE' (4)"

Value too long for column "VAL VARCHAR(1)": "'TRUE' (4)"; SQL statement:

insert into test values (?) [22001-170]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)

at org.h2.message.DbException.get(DbException.java:169)

at org.h2.table.Column.validateConvertUpdateSequence(Column.java:315)

at org.h2.table.Table.validateConvertUpdateSequence(Table.java:689)

at org.h2.command.dml.Insert.insertRows(Insert.java:120)

at org.h2.command.dml.Insert.update(Insert.java:84)

at org.h2.command.CommandContainer.update(CommandContainer.java:75)

at org.h2.command.Command.executeUpdate(Command.java:230)

at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156)

at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142)

at org.jooq.test._.testcases.EnumTests.testCustomEnums(EnumTests.java:140)

The same statement works perfectly with Oracle, which returns 1.

This is just loud thinking, I'm not sure if it would be wise to change this behaviour in H2.

Cheers

Lukas

[Thomas Mueller]

Hi,

Hm, I'm not sure. Why would you use a varchar(1) for a boolean, and not bit? The following fails with PostgreSQL:

drop table test;

create table test(val varchar(1));

insert into test(val) values(true); -- value too long for type character varying(1) 22001/0

select * from test;

It does work with MySQL. It doesn't work with Oracle, as Oracle doesn't understand "true" ("column not allowed here"). It sounds like for both MySQL and Oracle, setBoolean(true) is kind of the same as setInt(1).

Now if PostgreSQL supports setBoolean(true) for varchar(1), then I think H2 should support it as well (I didn't test it yet).

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[Lukas Eder]

Hi Thomas,

Thanks for your response. Some comments inline

2014-02-12 20:05 GMT+01:00 Thomas Mueller <thomas.to...@gmail.com>:

Hi,

Hm, I'm not sure. Why would you use a varchar(1) for a boolean, and not bit?

In Oracle, people emulate booleans with any of number(1), char(1), varchar(1). While the database doesn't know anything about booleans, the JDBC driver does and treats them like 1/0.

 

The following fails with PostgreSQL:

drop table test;

create table test(val varchar(1));

insert into test(val) values(true); -- value too long for type character varying(1) 22001/0

select * from test;

It does work with MySQL. It doesn't work with Oracle, as Oracle doesn't understand "true" ("column not allowed here"). It sounds like for both MySQL and Oracle, 

setBoolean(true) is kind of the same as setInt(1).

Precisely.

 

Now if PostgreSQL supports setBoolean(true) for varchar(1), then I think H2 should support it as well (I didn't test it yet).

I had tried it, actually, and it doesn't work. You'll get the same error as when inlining the "true" literal. If you want, I can assemble a list of databases that behave like Oracle for all 16 jOOQ-supported databases by Friday. That'll put this idea in proper context.

Cheers

Lukas

[Rami Ojares]

I vote for strict type checking.
This means that setting a value with wrong type should produce an error.
Maybe someone has saved his booleans as "T" and "F" or "t" and "f" or
"true" and "false" or "yes" and "no" ...

Of course as a compatibility feature for different modes it is
defendable.

- Rami

[Lukas Eder]

Yes, strict type checking is certainly desirable. I've checked the latest draft documents of the the SQL:2011 standard. The PostgreSQL driver shows the expected behaviour when we use a CAST:

6.13 <cast specification>

[...]

10) If TD is fixed-length character string, then let LTD be the length in characters of TD.

[...]

e) If SD is boolean, then

Case:

i) If SV is True and LTD is not less than 4, then TV is 'TRUE' extended on the right by LTD–4

<space>s.

ii) If SV is False and LTD is not less than 5, then TV is 'FALSE' extended on the right by LTD–5

<space>s.

iii) Otherwise, an exception condition is raised: data exception — invalid character value for cast.

A similar specification is available for VARCHAR. Informally speaking, TV is a value of type TD and SV is a value of type SD such that TV = CAST(SV AS TD).

It's generally good to follow the standard, but from an interoperability perspective, a more relaxed interpretation might be valuable as well.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/me_teu3Shbc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

[Lukas Eder]

Hello again. So I've run my test against these 16 databases. These ones behave like Oracle (boolean true can be inserted into char(1))

- Firebird (inserts Y or N)

- HSQLDB

- IBM DB2

- MariaDB

- Microsoft Access

- MySQL

- Oracle

- SQL Server

- Sybase

These ones behave like PostgreSQL (boolean true cannot be inserted into char(1))

- CUBRID

- Derby

- H2

- Ingres

- PostgreSQL

- SQLite

I'll let you decide how to interpret this information :-)

Cheers

Lukas

[Noel Grandin]

On 2014-02-17 13:32, Lukas Eder wrote:
> Hello again. So I've run my test against these 16 databases. These ones behave like Oracle (boolean true can be inserted
> into char(1))
>
> - Firebird (inserts Y or N)
> - HSQLDB
> - IBM DB2
> - MariaDB
> - Microsoft Access
> - MySQL
> - Oracle
> - SQL Server
> - Sybase
>

What do the ones beside Firebird insert?

[Lukas Eder]

All 1/0 

[Thomas Mueller]

Hi,

Hm, whatever we do, this will not be compatible with some databases. I would keep the current behavior, until somebody provides a patch to support both (I wouldn't worry about Firebird compatibility).

Regards,

Thomas

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Lukas Eder]

Hello,

2014-02-19 7:55 GMT+01:00 Thomas Mueller <thomas.to...@gmail.com>:

Hi,

Hm, whatever we do, this will not be compatible with some databases. I would keep the current behavior, until somebody provides a patch to support both

A simple measure might be to add a sub section to the compatibility section here:

http://www.h2database.com/html/features.html#compatibility

If someone is using H2 as an integration test database for an Oracle productive database, they should use the H2 BOOLEAN type for columns that are "boolean" CHAR(1) or VARCHAR(1) in Oracle...

There are probably other things that would be worth mentioning, too.

 

(I wouldn't worry about Firebird compatibility).

No ;-)