Villeroy said
The zero is just a display problem
Actually, I think it's a philosophical issue. In the realm of SQL, NULL and 0 are distinct, but in the realm of mathematics they are equivalent, and NULL is represented by the number 0.
According the the HSQLDB user guide
IFNULL
ISNULL
IFNULL | ISNULL ( <value expr 1>, <value expr 2> )
Returns <value expr 1> if it is not null, otherwise returns <value expr 2>. The type of the return value is the type of <value expr 1>. Almost equivalent to SQL Standard COALESCE(<value expr 1>, <valueexpr 2>) function, but without type modification. (JDBC)
From “The type of the return value is the type of <value expr 1> “ it appears that a SQL function that returns a numeric result is Cast() into a numeric value.
I entered a query
Code: Select all
SELECT "num", CAST( "num" AS CHAR ) AS "char", CAST( "num" AS "TINYINT" ) AS "tinyint", CAST( "num" AS INTEGER ) AS "integer", CAST( "num" AS DOUBLE ) AS "double", CAST( "num" AS DECIMAL ) AS "decimal", CAST( "num" AS NUMERIC ) AS "numeric", CAST( "num" AS REAL ) AS "real" FROM "Items" WHERE "integer" IS NULL;
Which resulted in
As you can see, an empty table cell is displayed until it is cast as a numeric value. The WHERE clause demonstrates that the table cell is still NULL from the SQL viewpoint. So, it appears the developers of AOO must have made the philosophical decision that once NULL has been cast to a numeric value they will display 0, the mathematical representation.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11