Bug report - SAP HANA Date Type for NULL value in SQL statement

[Former Member]

Version: SAP HANA Version (fa/hana1sp12)

Bug:
There have one record with DATE datatype column, and the value is NULL in this column. Then system judge it is as valid value instead of invalid value when I command SQL statement with filter DATE IS NULL condition.

For example:
###[It should be correct, but system think it is incorrect.]

select RELEASE_DATE from table WHERE ORDER_NUMBER=number and release_date is null;

###[It should be incorrect, but system think it is correct.]

--
select RELEASE_DATE from table WHERE ORDER_NUMBER=number and release_date = '00000000';

--
select RELEASE_DATE from table WHERE ORDER_NUMBER=number' and release_date = '';

--
select RELEASE_DATE from table WHERE ORDER_NUMBER=number and release_date is not null;

[lbreddemann]

It looks like you're misinterpreting the results of the different statements here because HANA Studio renders these results all as a question mark. Usually, the question mark is used to indicate NULL values, however, it is also used for values that cannot be correctly mapped from HANA to JAVA/JDBC.

One of such "unmappable" values is the special date that can be created e.g. by date'000000'.

This value is neither a valid date that could be mapped to a JAVA calendar nor is it NULL. It's a special date that indicates "earlier than any other date" and is used as an initial value for date columns.

IS NULL will evaluate to FALSE for this value - but <,=,> comparisons work as expected.

One option to double-check what values are actually in the columns is to use a NON-JDBC client (e.g. hdbsql) to review the data.

[Former Member]

Hi Lars,

I still confused why I command the data IS NOT NULL but get the zero records to result. It should be one record, but return 0.

The normal situation is that you want to select one record which contains NULL value and you should use WHERE field IS NOT NULL, then you will get the record back.

This is the result from hdbsql.

----

select RELEASE_DATE from table WHERE ORDER_NUMBER='number' and release_date is null;
-----------------
RELEASE_DATE
0 rows selected (overall time 543.489 msec; server time 239 usec)
-----------------
select RELEASE_DATE from table WHERE ORDER_NUMBER='number' and release_date = '00000000';
select RELEASE_DATE from table WHERE ORDER_NUMBER='number' and release_date = '';
select RELEASE_DATE from table WHERE ORDER_NUMBER='number' and release_date is not null;
-----------------
RELEASE_DATE
?
1 row selected (overall time 583.590 msec; server time 3593 usec)
-----------------

abc.jpg

[Former Member]

Or more concise: Why the ‘’ and the ‘00000’ produce the same results? Is ‘’ the same as the null date?

[lbreddemann]

The conversions to_date('') and to_date('000000') yield the same special date value.

This value is NOT NULL.

That means, to_date('') IS NOT NULL and to_date('000000') IS NOT NULL.

And that is what you see in your results, too.

If you want to check the values in hdbsql you could use

select to_binary(to_date('')) from dummy
0x

whereas

select to_binary(to_date(NULL)) from dummy
?

Hope that clarifies it.

[Former Member]

I still thinking about your explanation. I put the NULL value into the DATE data type column, then the table will default two value for selection: blank or '0000'(the length of data type). But when you command the statement, the result will still be NULL, Is it correct?

[lbreddemann]

That's not correct understanding.

If you put a NULL value into the column, it's NULL and will stay NULL. When you look for IS NULL this row will match.

If you put in to_date(<empty string>) or to_date ('000000') then the special date is saved and that's NOT NULL but a value.

[Former Member]

Lars,

You are right, but I try to look for IS NULL this row, it is unmatched. Could you elaborate more about it?

select RELEASE_DATE from table WHERE ORDER_NUMBER='number' and release_date is null;
RELEASE_DATE
0 rows selected 

[lbreddemann]

By now it should be clear to you what this means: there are no records for which ORDER_NUMBER equals 'number' and for which RELEASE_DATE is in fact NULL.

I believe to have sufficiently explained how the special date value can be misinterpreted as NULL, how to create it and what to do with it.
To me, it does not appear to be a bug in HANA that leads to your selection of an empty result set, but that your data in fact does not have NULL values where you expect them.

[Former Member]

Hi Lars,

I need to thank you have the patient with me and share your thoughts, but I still don't understand. So I make the sheet. These is the process I get from our discussion.

But I still confuses with the display, can you fill answer in the column situation?

I reference that result you share with me, and you could see the following sheet. There are two difference NULL from Extract process to Display process. The green NULL and red NULL is the same UNKNOWN value? If yes, does it means stored and display will be different for DATE data type when received a UNKNOWN value initially?

[Former Member]

And...

Questions:

1. Why dose database engine treat blank and '00000000' as the same?

2. How do I view REALLY value in database? JDBC, ODBC, and .NET driver return a value of NULL for column. How the method do you recommend?

Thank you.

[YTHO]

Hi Jian Kuen,

I think you need to report this somewhere else. Go to https://launchpad.support.sap.com and create a ticket.

kind regards,