[Solved] Query returning zero instead of null

Creating tables and queries
Post Reply
salvador
Posts: 7
Joined: Tue Oct 31, 2017 4:05 pm
Location: Italy

[Solved] Query returning zero instead of null

Post by salvador »

On OpenOffice Base I have this example table:

Code: Select all

Items
┌─────┐
│ num │ integer
├─────┤
│     │← null value
│ 123 │
└─────┘
This simple query returns as expected:

Code: Select all

SELECT "num" FROM "Items"
┌─────┐
│ num │
├─────┤
│     │← blank cell (ok!)
│ 123 │
└─────┘
But an addiction or a multiplication return a weird result:

Code: Select all

SELECT "num" + 1 FROM "Items"
┌───────────┐
│ "num" + 1 │
├───────────┤
│ 0         │← zero instead of blank cell (wtf?!)
│ 124       │
└───────────┘

SELECT "num" * 10 FROM "Items"
┌────────────┐
│ "num" * 10 │
├────────────┤
│ 0          │← zero again?!
│ 1230       │
└────────────┘
The same happens using these functions, that all display a 0 (zero) where a blank cell (null) is expected:

Code: Select all

NULLIF( "num", 123 )
IFNULL( "num", null )
COALESCE( "num", "num" )
CASEWHEN( "num" = 123, "num", NULL )
CASE WHEN "num" = 123 THEN "num" ELSE NULL END
On the contrary LibreOffice does his job on blank cells where the value is null.

How can I get my blank cells and not zero on OpenOffice?

Thank you!
Attachments
null_zero.odb
Queries returning zero instead of null
(3.77 KiB) Downloaded 254 times
Last edited by salvador on Thu Nov 09, 2017 3:54 am, edited 4 times in total.
Michele Salvador
OpenOffice 4.1.4, LibreOffice 5.4.2.2 on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query returning zero instead of null

Post by UnklDonald418 »

Since "num" is defined as INTEGER type in table "items" by default the result will be displayed as an integer, thus 0 for NULL.
Try this query

Code: Select all

SELECT CAST("num" AS CHAR) AS "Num"  FROM "Items";
 Edit: .
I just tried the basic query

Code: Select all

SELECT "num" FROM "Items"
I'm not sure why but without any of the functions it seems to work despite the type declaration. 
Last edited by UnklDonald418 on Tue Oct 31, 2017 6:29 pm, edited 1 time in total.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query returning zero instead of null

Post by Villeroy »

UnklDonald418 wrote:Since "num" is defined as INTEGER type in table "items" by default the result will be displayed as an integer, thus 0 for NULL.
Try this query

Code: Select all

SELECT CAST("num" AS CHAR) AS "Num"  FROM "Items";
Sure? Has it always been like this? It smells like a regression. The table view shows the correct Null value.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
salvador
Posts: 7
Joined: Tue Oct 31, 2017 4:05 pm
Location: Italy

Re: Query returning zero instead of null

Post by salvador »

UnklDonald418 wrote:I'm not sure why but without any of the functions it seems to work despite the type declaration.
Yes, as I wrote in the first post, null values are returned as blank cells only by SELECT "num", by contrast for the same null values SELECT "num"+1 returns zero, and so make all the other functions (IFNULL, COALESCE, CASEWHEN...). Even NULLIF always returns zero instead of NULL! This behavior seems incoherent to me.

CAST("num" AS CHAR) apparently returns blank cells for null values. But this way all the values become text. This could be a perfect solution for a printed Report. But what if I need to import the query in a Calc spreadsheet and manage values as numbers? I lost all value types (INT, DATE, TIME...) and I find all the values turned into text, beginning with a leading single quotation mark. :?
Maybe there are ways to remove this single quotation mark in Calc, but it's a tricky workaround, isn't it?
Michele Salvador
OpenOffice 4.1.4, LibreOffice 5.4.2.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query returning zero instead of null

Post by Villeroy »

The zero is just a display problem within the Base component. When you export to Calc, null is what everybody expects.

This test query returns 1 instead of Null indicating that your original addition +1 actually returns a Null value.

Code: Select all

SELECT "T".*,Coalesce("T"."Addition", 1) AS "TEST" FROM (SELECT "num" + 1 AS"Addition" FROM "Items")AS "T"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query returning zero instead of null

Post by UnklDonald418 »

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
NullZeroTestAOO.png
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
salvador
Posts: 7
Joined: Tue Oct 31, 2017 4:05 pm
Location: Italy

Re: Query returning zero instead of null

Post by salvador »

Villeroy wrote:The zero is just a display problem within the Base component.
I totally agree with you. I looked in Bugzilla for this problem but didn't find anything about it.
UnklDonald418 wrote: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.
Somehow I can understand this philosophical decision, but for me at the moment is a practical problem.

Here is a simplified version of what my client wants: a monthly calendar derived from two tables: Month (Day) and Events (ID, Date).
The query is then imported in a Calc spreadsheet for further processing, and obviously values must remain numbers, not cast as char.

Code: Select all

SELECT "Day", ( SELECT COUNT(*) FROM "Events" WHERE DAY("Date") = "Day" GROUP BY "Date" ) AS "Count" FROM "Month"
┌─────┬───────┐
│ Day │ Count │
├─────┼───────┤
│ 1   │       │
│ 2   │ 3     │
│ 3   │       │
│ 4   │ 1     │
│ 5   │ 2     │
│ ... │ ...   │
│ 30  │       │
│ 31  │ 1     │
└─────┴───────┘
Blank cells in the second column are actually null values, but OpenOffice Base displays them as "zeroes". On the contrary LibreOffice keep them as blank cells, that is more correct in my opinion.
Maybe there is another way to obtain these blank cells also in OpenOffice?
Attachments
one_month.odb
Events in some days of a month
(4.34 KiB) Downloaded 213 times
Michele Salvador
OpenOffice 4.1.4, LibreOffice 5.4.2.2 on Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Query returning zero instead of null

Post by chrisb »

salvador,
you could use a left join like so:-

Code: Select all

select M."Day", E."Count"
from "Month" M
left join 
(select day("Date") "Day", count(*) "Count" from "Events" group by "Date") E on E."Day" = M."Day"
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Query returning zero instead of null

Post by Sliderule »

Another way to produce the results I think you desire, with OpenOffice Base, is:

Code: Select all

Select 
   A.* 
From 
     (
       SELECT 
         "Day", ( SELECT COUNT( * ) FROM "Events" WHERE DAY( "Date" ) = "Day" GROUP BY "Date" ) AS "Count" 
       FROM "Month"
     ) as A
Explanation: Using the SQL Query above, you will see a Select statement exactly as you wrote it, but, it is wrapped ( surrounded ) by a Select statement causing a result set as A between parentheses.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
salvador
Posts: 7
Joined: Tue Oct 31, 2017 4:05 pm
Location: Italy

Re: Query returning zero instead of null

Post by salvador »

chrisb, Sliderule,
I adopted both your solutions for my project.
Thank you so much! :bravo:
Michele Salvador
OpenOffice 4.1.4, LibreOffice 5.4.2.2 on Windows 10
Post Reply