Working with DOUBLE values sometimes results in inaccuracy.
Those inaccuracies are not unexpected when dealing with DOUBLEs. As DOUBLE is an approximative data type, some values can not be stored exactly. This is a general issue of floating-point arithmetic.
You can verify the data type of your column by creating a table using the query and viewing the data types for the newly-created column, for example:
CREATE TABLE TEST AS SELECT ROUND(((71222-65504)/65504*100) ,1); -- Creates a DOUBLE
You can use an exact numeric type, like DECIMAL to remove inaccuracies. Such as the example below:
SELECT ROUND( CAST ((71222-65504)/65504*100 AS DECIMAL (16,3)) ,1);
SELECT cast (1 as DECIMAL (17,16)) - cast (1E-16 as DECIMAL (17,16)) AS exact;
Is the ROWID always assigned in an ascending order? Is it possible to determine the sequence of inserted records with the ROWID?
The ROWIDs of a table are managed by the DBMS. They are assigned in an ascending order per node and stay distinct within a table. For different tables, they could be the same.
DML statements such as UPDATE, DELETE, TRUNCATE or MERGE might internally reorder data storage, invalidating and reassigning all the ROWIDs. Contrary to that, structural table changes such as adding a column leave the ROWIDs unchanged. Altering distribution keys of or reorganizing a table will certainly reassign ROWID values.
Therefore the ROWID can't be used to determine the exact sequence or age of a record, it is designed to be a short term identifier for rows to be used for duplicate elimination.
The ROWID pseudo column can only be used on table objects, not on views or subselects. If you try it on a view, you will receive an appropriate error message