bydindi wrote:(15 / 1.22) * 1.22 = 15.01 for my purposes is correct!
However if I try the file "wizard Create a new 'split' HSQL 2.x", the result is 15.00 instead 15.01
I realized that creating a new table, the settings values [FLOAT] and [REAL] not remain stored (if I close the table in edit mode and re-open, the settings are reset back to [DOUBLE])
In this case, you converted an 'embedded database' to a 'split HSQL
1.8 database.' And note that the particular Base template you used generates a new 'split HSQL
2.x database.' So you have two databases, each running a different version/generation of HSQLDB. Otherwise, the particular steps and/or configurations are not related to the issue of
data-types and SQL math.
As others have identified, this thread is actually about differences between the SQL engines in use: HSQLDB 1.8 and HSQLDB 2.x. There's apparently a difference in the 'default' settings that lead to different mathematical 'rounding' or 'truncation' (SQL math with INTEGERs -or- perhaps 'precision' and 'scale' handling) between HSQLDB 1.8 and HSQLDB 2.x.
Here's a few excerpts:
HSQLDB 1.8 documentaion:
In table definition statements, HSQLDB accepts size, precision and scale qualifiers only for certain types: CHAR(s), VARCHAR(s), DOUBLE(p), NUMERIC(p), DECIMAL(p,s) and TIMESTAMP(p).
By default specified precision and scale for the column is simply ignored by the engine. Instead, the values for the corresponding Java types are always used, which in the case of DECIMAL is an unlimited precision and scale. If a size is specified, it is stored in the database definition but is not enforeced by default. Once you have created the database (before adding data), you can add a database property value to enforce the sizes:
SET PROPERTY "sql.enforce_strict_size" true
This will enforce the specified size and pad CHAR fields with spaces to fill the size. This complies with SQL standards by throwing an exception if an attempt is made to insert a string longer than the maximum size. It also results in all DECIMAL values conforming to the specified precision and scale.
HSQLDB 2.x Guide > Chapter 2. SQL Language > Data Types and Operations > Type Length, Precision and Scale:
In older version of HyperSQL, all table column type definitions with a column length, precision or scale qualifier were accepted and ignored. HSQLDB 1.8 enforced correctness but included an option to enforce the length, precision or scale.
In HyperSQL 2, length, precision and scale qualifiers are always enforced.
SQL basics: Number data types...as suggested by
Arineckaig and outlined by
Villeroy above
Using INTEGERS in SQL calculations (while expecting a SCALE of 2 or more)...similar to
MTP's suggestion above
How to format SQL results...various post-processing formatting options
