Thank you a lot, Evgenij and team. On Thursday, November 21, 2019 at 9:12:19 AM UTC+7, Evgenij Ryazanov wrote: > > 1. Unsupported type 17 is usually thrown when multiple versions of H2 were > used with the same database file (versions of TCP clients don't matter, > only versions of servers and embedded connections should match). For > example, IDEA users are affected, because IDEA uses 1.4.196 it its tools. > https://github.com/h2database/h2database/issues/2078 >
Beside not knowing that, I am not even sure if your explanation applies in that case. The error message was thrown, when 1) opening a database and then 2) CREATE TABLE AS ... and then 3) SELECT FROM on that new table. These steps 1-3 were all conducted on version 1.4.200 and the same sequence of actions works fine now with 1.4.201-pre. Although I will try to craft a simplified testcase. > > If you can reproduce that issue with 1.4.200 only, please try to build a > test case for it. > > > 2. Addition of DECIMAL(23, 5) to DECIMAL(23, 5) should produce a numeric > data type with scale 5 and any vendor-specific precision, according to the > SQL Standard. Therefore you should not assume that it will be DECIMAL(23, > 5), it can legally be different, for example, DECIMAL(2147483647, 5) or > something else. You need to add an explicit cast to DECIMAL(23, 5) such as > CAST(A + B AS DECIMAL(23, 5)) to be sure. Unfortunately, H2 does not > evaluate the scale properly, this issue is also known. > https://github.com/h2database/h2database/issues/1910 > The SQL standard determines the precision and scale in the following way: 1. If the declared type of both operands of a dyadic arithmetic operator is exact numeric, then the declared type of the result is an implementation-defined exact numeric type, with precision and scale determined as follows: a) Let S1 and S2 be the scale of the first and second operands respectively. b) The precision of the result of addition and subtraction is implementation-defined, and the scale is the maximum of S1 and S2. If I am not mistaken, that should give 5 (but not 100000) for the sample above. Definitely it should not give 2147483647 as 1.4.200 does. For our use case, 1.4.201-pre with DECIMAL(2147483647, 100000) is sufficient and does work, but I find it very confusing. I would have expected that an addition gives DECIMAL(23, 5) as standard, which could be cast to something else on demand -- because that is what you would have to do in Java or C when adding Shorts/Ints etc. Perhaps, we even could optimize it as (Max(Precision_S1, Precision_S2) + 1) and Max(Scale_S1, Scale_S2), just in order to safe space. > > 3. Released versions of H2 don't have any sane limits for scale, but such > limit exists in current sources. I think we need to add some code to allow > initialization of databases that already have abnormal data types such > as DECIMAL(2147483647, 2147483647) created by older versions of H2. > Yes, thank you very much for fixing it. 1.4.201-pre solved our problem in an acceptable way and we can leave that thread for documentation, when other users hit a similar problem with 1.4.200 (stable). -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/eedb9204-cc29-4cc3-b942-38bd6e107daa%40googlegroups.com.
