SQL: Expect NULL but get zero

[NeoOOUEU]

I'm new to OO (Base). I have some experience with MS Access and SQL. OO Base seems to be a useable alternative solution for me but I have to struggle with some issues.

One is: For a table with 3 decimal columns, NULL-able, I'd like to calculate the average value. In case at least one of the values is NULL also the average value should be NULL.

My query:

Code: Select all

SELECT "ID", "ScreeningDate", "Story", "Video", "Audio", ( "Story" + "Video" + "Audio" ) / 3 AS "AVG" FROM "Screenings"

I'd expect the numeric expression to be NULL if at least one argument is NULL but I get zero:

I did also some other attempts but it is always the same: Zero and not NULL

Code: Select all

SELECT
    "ID"
  , "ScreeningDate"
  , "Story"
  , "Video"
  , "Audio"

  , ( "Story" + "Video" + "Audio" ) / 3 AS "AVG"

  , NULLIF ( ( "Story" + "Video" + "Audio" ) / 3, 0 ) AS "AVG-NULLIF"
  , NULLIF ( ( "Story" + "Video" + "Audio" ) / 3, 6.8 ) AS "AVG-NULLIF6.8"

  , NULLIF ( ( "Story" + "Video" + "Audio" ) / 3, NULL ) AS "AVG-NULLIFNULL"

  , NULLIF ( ( "Story" + "Video" + "Audio" ) / 3, 0.0 ) AS "AVG-NULLIF0.0"

  , CASE
      WHEN "Story" IS NULL THEN NULL
      ELSE( "Story" + "Video" + "Audio" ) / 3
    END AS "AVG-CASE"

  , CASE
      WHEN "Story" IS NULL THEN 99.9
      ELSE( "Story" + "Video" + "Audio" ) / 3
    END AS "AVG-CASE99.9"

  , NULL AS "RC-NULL"

FROM "Screenings"

As you can see for column "AVG-NULLIF6.8" (at the second row): The NULLIF is basically working but I don't get NULL.
Column "RC-NULL" was used to see if I can return NULL in principle (which is working fine).

What is wrong with my view?
I'm a wrong to expect NULL for the arithmetic expression if an argument is NULL?

I'm using the built-in HSQLDB with the latest JRE8 (from Oracle).


P.S.: Please note the different style of the date in the two queries. For the second query I have to use the direct SQL mode and so the date format of the table will be ignored and the fu..ing wrong 2-digit-year format, used for the short date format for my locale (which is basically not okay and not following any standard) will be used.
It would be fine to have the possibility to set the default date format in OO (or in Base) or at least ISO standard format should be used (but not the stupid Java FormatStyle.SHORT).

[NeoOOUEU]

It's working fine in LibreOffice.

Since I found several bugs in OO (this, on creating PK, etc.) it makes no sense for me to use OO and I'll try LO (even it looks also very buggy).

[Villeroy]

The error is in the office GUI. The HSQL database engine does return a Null value.

Code: Select all

SELECT IFNULL("Calculated", -1) AS "Test" FROM(SELECT <some calculation> AS "Calculated" FROM "Somewhere")

returns -1 where Null is expected indicating that the calculated value is Null actually.
In a form or report, a user-defined number format like 0;-0;"" gives the right look.
-----------------
OO died around 2015. Unfortunately, "OpenOffice" is the better known brand.
https://www.libreoffice.org/discover/li ... penoffice/
Base is the "drunken cousin" among the other components. Nevertheless, LO Base gets some bug fix or slight improvements every now and then. Base is just a small addition to the whole suite. Under Linux the Base packages are 2 or 3 dozends of megabytes in size including the drivers for various databases. This is not comparable to a database development suite like MS Access. Nevertheless, it is far more useful than Calc for what most people try to do with tabular data.
--------------------------
P.S. Personally, I would implement the table design as a many-to-many relation where Null values would not exist.
P.P.S. At least I would make "Story", "Video", "Audio" mandatory (not nullable).

[NeoOOUEU]

The error is in the office GUI. The HSQL database engine does return a Null value.
...
In a form or report, a user-defined number format like 0;-0;"" gives the right look.

If a Software is not able to handle a basic concepts like NULL values (and others) correctly, it is unusable (to me).

It's also not an issue on forms or reports but it's an issue in queries ("Abfragen")!

It is less-than-fashionable to use zero in case of NULL in most cases! Also in my case it is just wrong.

-----------------
OO died around 2015. Unfortunately, "OpenOffice" is the better known brand.
https://www.libreoffice.org/discover/li ... penoffice/
Base is the "drunken cousin" among the other components. Nevertheless, LO Base gets some bug fix or slight improvements every now and then. Base is just a small addition to the whole suite. Under Linux the Base packages are 2 or 3 dozends of megabytes in size including the drivers for various databases. This is not comparable to a database development suite like MS Access. Nevertheless, it is far more useful than Calc for what most people try to do with tabular data.
--------------------------

Regardless of whether "OO" or "OO.org" dies around 2015 to me it looks also "[Apache] OpenOffice" is in a coma.
I was not pleased by the fork of OO.org, now one decade ago, but I have to accept it.
I knew this before but I wanted to give Apache OpenOffice a try (since I like Apache more in general).
Personally I think there aren't enough resources for both office suites and IMHO "OpenOffice" has no chance anymore and it makes less sense to keep it alive. Sorry.

I don't know what you mean with "drunken cousin" exactly. If it's just on the number of features is maybe sad. It it's on bugs it's not bearable. If it is not maintained and not fixed it might be better to kick it out.

Compare to MS Access: First, you can compare everything with everything. You can also compare apples with pears. You should not confound compare-able with similar. It's not about the process of comparing but more about the result of the comparison and the aspects of the comparison.
Second, your argument for the size is wrong at all to me. You have to include also Writer and Calc (and maybe basic components of OpenOffice and maybe also the JRE) for a full-featured solution (incl. forms and reports), since they are used and an integral part of a solution like MS Access (as I know Access 2002).
If you compare "Base" (incl. needed Writer and Calc) with Access (2002) I guess there will be no big difference in size. However, this is not a feasible comparison because of a different architecture design.
If you compare the features of "Base" (incl. Writer and Calc) to Access (2002) it is very similar to me. Of course, there are several differences, but for me and my use it's absolute comparable. By now I'm not missing features in "Base" (with Writer and Calc). Several aspects of "Base" are nearly exactly the same like in Access! For me it is undeniably that "Base" was built with Access (and maybe comparable products at this time, like FoxPro) as role model in many aspects.

Personally I see "Base" as the real UVP of OpenOffice/LibreOffice! For me it's the only reasonable alternative to a desktop DBMS, in particular MS Access. For word processor, spreadsheet and drawing applications there are also other alternatives – or partly there are no real alternatives to MS (for e.g. complex XLS-spreadsheets, which are not useable with other spreadsheet applications).
Beside "Base" I see no useable alternatives to MS Access, if you have a slightly higher complexity than in most very basic examples.
If you look e.g. to Kexi: It's now decades behind MS Access as I used it last time, years ago (version 2002). It struggles on very basic concepts like lookup-tables and so it might be better to use Spreadsheets in many cases. I like the basic concept and design of Kexi, but it's still a toy tool and I think it will never come near to Access (like it is the aim of them).

If very basic concepts ore not working in OpenOffice but are fine in LibreOffice I have no choice. Sorry.

P.S. Personally, I would implement the table design as a many-to-many relation where Null values would not exist.
P.P.S. At least I would make "Story", "Video", "Audio" mandatory (not nullable).

For me (for my case) it's not a good way to do it because all three values are an integral unit of the entity, i.e. they should only appear in triple (or not). The case of one value missing should not exist in real life (but there are some cases of missing values by accident at old data and the design should be able to handle it too). To use a many-to-many relationship would just increase the complexity (e.g. need for a sub-form to enter/edit/view the data) and it makes it much more complicated to get comparable average-values, i.e. only avg-values if all three values exists.
To make it mandatory is not possible, at least for old data with missing values.