[Solved] Query returning zero instead of null

Creating tables and queries

[Solved] Query returning zero instead of null

Postby salvador » Tue Oct 31, 2017 4:41 pm

On OpenOffice Base I have this example table:
Code: Select all   Expand viewCollapse view
Items
┌─────┐
│ num │ integer
├─────┤
│     │← null value
│ 123 │
└─────┘

This simple query returns as expected:
Code: Select all   Expand viewCollapse view
SELECT "num" FROM "Items"
┌─────┐
│ num │
├─────┤
│     │← blank cell (ok!)
│ 123 │
└─────┘

But an addiction or a multiplication return a weird result:
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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 38 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
salvador
 
Posts: 7
Joined: Tue Oct 31, 2017 4:05 pm
Location: Italy

Re: Query returning zero instead of null

Postby UnklDonald418 » Tue Oct 31, 2017 6:13 pm

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   Expand viewCollapse view
SELECT CAST("num" AS CHAR) AS "Num"  FROM "Items";

 Edit: .
I just tried the basic query

Code: Select all   Expand viewCollapse view
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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 807
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query returning zero instead of null

Postby Villeroy » Tue Oct 31, 2017 6:28 pm

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   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25381
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query returning zero instead of null

Postby salvador » Fri Nov 03, 2017 7:19 pm

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

Re: Query returning zero instead of null

Postby Villeroy » Fri Nov 03, 2017 9:32 pm

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   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25381
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query returning zero instead of null

Postby UnklDonald418 » Fri Nov 03, 2017 10:48 pm

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   Expand viewCollapse view
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.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 807
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query returning zero instead of null

Postby salvador » Tue Nov 07, 2017 1:02 am

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   Expand viewCollapse view
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 29 times
Michele Salvador
OpenOffice 4.1.4, LibreOffice 5.4.2.2 on Windows 10
salvador
 
Posts: 7
Joined: Tue Oct 31, 2017 4:05 pm
Location: Italy

Re: Query returning zero instead of null

Postby chrisb » Tue Nov 07, 2017 2:38 am

salvador,
you could use a left join like so:-
Code: Select all   Expand viewCollapse view
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.5 & LibreOffice 5.4.3.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 161
Joined: Mon Jun 07, 2010 4:16 pm

Re: Query returning zero instead of null

Postby Sliderule » Tue Nov 07, 2017 9:50 pm

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

Code: Select all   Expand viewCollapse view
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.
User avatar
Sliderule
Volunteer
 
Posts: 1133
Joined: Thu Nov 29, 2007 9:46 am

Re: Query returning zero instead of null

Postby salvador » Thu Nov 09, 2017 3:49 am

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


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests