Compatibility with MySQL's UNSIGNED integer types

[Lukas Eder]

Hello,

I understand that H2 syntactically supports the UNSIGNED keyword in DDL to stay compatible with MySQL:

    CREATE TABLE t_unsigned (

      u_byte tinyint unsigned,

      u_short smallint unsigned,

      u_int int unsigned,

      u_long bigint unsigned

    );

Obviously, this is just a syntax compatibility, as I cannot insert this:

    INSERT INTO t_unsigned (u_byte) VALUES (255);

Would it make sense to enhance the H2 database in order to go a bit further on UNSIGNED support? There are two alternative routes:

--------------------------------------------------------------------------

1. Simple fix: Just "upgrade" integer types in storage, and let the above table be equivalent to this one:

    CREATE TABLE t_unsigned (

      u_byte smallint,

      u_short integer,

      u_int bigint,

      u_long number(20)

    );

2. Sophisticated fix: Store unsigned numbers in signed number containers, but fix all relevant JDBC methods to produce the exact value:

    "255".equals(rs.getString("u_byte"));

    255 == rs.getInt("u_byte");

    (short) 255 == rs.getShort("u_byte");

    (byte) -1 == rs.getByte("u_byte"); // This would be "expected"

--------------------------------------------------------------------------

The simple fix would probably cause new issues in the long run. Besides, it would allow values that are out of range, e.g. 1000 for u_byte. The sophisticated fix would mean a bit of work, specifically because all the arithmetic operations would need to be adapted. Consider 

    cast((u_byte + u_byte) as smallint unsigned)

For u_byte == 255 (stored as -1), this would have to return 510, instead of -2

What do you think?

Cheers

Lukas

[Noel Grandin]

I can't see that adding full support is likely, since UNSIGNED datatypes
are hardly a SQL standard.
After all, the primary purpose of supporting them is to make running
unit tests for people who are MySQL easier, so it's not like it's a
production situation.

But I can see that making them work a little better would be a good idea.
Your best bet is to dig around the Parser class to add support.

> --
> 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?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Lukas Eder]

Hello,

2013/5/24 Noel Grandin <noelg...@gmail.com>

I can't see that adding full support is likely, since UNSIGNED datatypes are hardly a SQL standard.

You're right. The SQL:2008 Standard actually refers to numbers as such:

    4.4.1 Introduction to numbers

    "[...] For every numeric type, the least value is less than zero and the greatest value is greater than zero."

In fact, this means that the SQL standard "forbids" unsigned numbers :-) Nonetheless, many databases "enhance" the SQL standard. One of those databases is H2. The best example for standard enhancements and liberal interpretations are:

- H2's ARRAY support

- H2's MERGE statement

One good reason why unsigned integers are useful is the fact that people tend to use only positive numbers for keys.

 

After all, the primary purpose of supporting them is to make running unit tests for people who are MySQL easier, so it's not like it's a production situation.

Yes, that's the primary purpose right now. That's why I propose a new purpose, at least for the record / roadmap.

N.B: These databases also support unsigned integer numbers in some way:

- SQLite (I think)

- SQL Server's (TINYINT only)

- Sybase ASE

- Sybase SQL Anywhere 

But I can see that making them work a little better would be a good idea.
Your best bet is to dig around the Parser class to add support.

I'm just tossing around ideas, I think it would be too early in the discussion to implement anything on my side...

Cheers

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.