Missing FLOAT(precision)?

[garretwilson]

I'm writing a lesson on SQL and laboriously creating a table of ANSI/ISO SQL support across products.

I noticed that the SQL standard allows for not only FLOAT but FLOAT(precision). Both PostgreSQL and Oracle support this, according to their official docs. But http://www.h2database.com/html/datatypes.html tells me that H2 does not support FLOAT(precision), although it does support DOUBLE(precision). Is this a typo in the syntax production, or is this a real limitation?

[grandinj]

Neither FLOAT nor DOUBLE supports a (precision) trailer in H2

[katzyn]

Actually DOUBLE(precision) is parsed, but value of precision parameter being ignored. FLOAT(precision) cannot be used at all.

[garretwilson]

Since SQL supports it (e.g. as per ISO 9075-2:2011(E) 6.1), I think it would be beneficial to at least allow FLOAT(precision), even if the precision is ignored.

[katzyn]

Please also notice that in H2 (and not only in H2) DOUBLE and FLOAT are the same type, for smaller type see REAL.

And PRECISION in documentation of DOUBLE means data type DOUBLE PRECISION, not precision parameter. Compare with documentation of DECIMAL where precision parameter is supported.

[katzyn]

@garretwilson Sorry, I was wrong. FLOAT(precision) is also parsed, it was a typo in my test. Of course, value is ignored too.

[garretwilson]

So that would appear to mean that the documentation at http://www.h2database.com/html/datatypes.html is incorrect. Should someone fix it?

[katzyn]

Features that available only for compatibility with other databases are not declared there to avoid usage of them by mistake.

[garretwilson]

Then why is VARCHAR2 listed? Surely it's only there for Oracle compatibility.

[garretwilson]

Besides, FLOAT(precision) is part of the ANSI/ISO standard, and is supported by PostgreSQL, Oracle, and I'm sure others.

I'm not trying to argue, I'm just confused about what you're saying. Don't we want the documentation to be correct?

[katzyn]

Documentation of H2 database is not very consistent. Additional aliases of data types are listed, but other things are usually not.

Oracle supports precision as is for FLOAT type, but this type is not recommended. BINARY_FLOAT and BINARY_DOUBLE are recommended instead.

PostgreSQL, MySQL, and SQL Server accept precision, but only use it to choose 32-bit or 64-bit storage format.

H2 ignores precision and uses 64-bit format, and there are other databases that do the same thing.

All these cases are valid.

This data type is not always documented in normal way. For example, in PostgreSQL there is no such type in table of numeric types:
https://www.postgresql.org/docs/9.6/static/datatype-numeric.html#DATATYPE-NUMERIC
But this type is mentioned later as additional:
https://www.postgresql.org/docs/9.6/static/datatype-numeric.html#DATATYPE-FLOAT

May be we should mention in documentation that precision may be specified, but will be ignored.