H2 `cast ('1' as unsigned integer)` will throw `Unknown data type: "UNSIGNED"`

[tszxxx]

H2: config: spring.datasource.url: jdbc:h2:mem:testdb;MODE=MYSQL sql: select cast ('1' as unsigned integer) exception JdbcSQLNonTransientException Unknown data type: "UNSIGNED"

Maven + POM: package: com.h2database - h2 version: 2.2.220

[katzyn]

Cast function in MySQL looks like standard cast specification, but it isn't the same, it uses some own names of data types, these names are different from actual names of data types in MySQL. unsigned integer here actually means bigint unsigned data type. On the same time, MySQL doesn't allow its own bigint unsigned data type to be used as a type name in cast. Some other data types can't be specified at all, even with different names. There is an almost 16-years-old bug: https://bugs.mysql.com/bug.php?id=34562

We can try to parse these { UNSIGNED | SIGNED } [ INTEGER | INT ] as BIGINT in MySQL and MariaDB compatibility modes of H2, it shouldn't be too problematic. But it will be much better to resolve this bug on MySQL side.

[Sintivrousai]

I would like to be assigned for the issue.

[tszxxx]

Cast function in MySQL looks like standard cast specification, but it isn't the same, it uses some own names of data types, these names are different from actual names of data types in MySQL. unsigned integer here actually means bigint unsigned data type. On the same time, MySQL doesn't allow its own bigint unsigned data type to be used as a type name in cast. Some other data types can't be specified at all, even with different names. There is an almost 16-years-old bug: https://bugs.mysql.com/bug.php?id=34562

We can try to parse these { UNSIGNED | SIGNED } [ INTEGER | INT ] as BIGINT in MySQL and MariaDB compatibility modes of H2, it shouldn't be too problematic. But it will be much better to resolve this bug on MySQL side.

Got that, I totally agree with you. But I may still need h2 to support this (even just ignore the unsigned identifier), otherwise I cannot make my SQL query compatible with MySql (v5.7).

[tszxxx]

I would like to be assigned for the issue.

I'm not sure about the standard process, should I assign anyone as the assignee, or the owner of the repo will do that?

[katzyn]

You can send a pull request with your changes.

1. This syntax should only be allowed within a cast specification and only if Mode.allNumericTypesHavePrecision is enabled (this flag controls MySQL-style deviations in data types).
2. Standard cast specification should work as it worked before even if this flag is set.

[katzyn]

Don't forget to add some tests.