Cannot filter null Datetime values

Question

I have a problem that is driving me crazy. I have to query an oracle view that returns some DATETIME values. The incredible problem is that even if I set the "IS NOT NULL" on the WHERE clause and even if I set the NVL(FECHA_HASTA, FECHA_DESDE), I´m still getting null values!!. How is that possible???

This is the query:

SELECT CUIL as Cuil, 
    COD_TIPO_CAUSAL as CodTipoCausal, 
    COD_CONVENIO as CodConvenio, 
    FECHA_DESDE as FechaDesde, 
    NVL(FECHA_HASTA, FECHA_DESDE) as FechaHasta
FROM ORGANISMO.VCAUSAL_AUSENCIA
WHERE FECHA_HASTA IS NOT NULL
AND FECHA_HASTA > (SELECT SYSDATE - 180 FROM SYS.DUAL)
AND CUIL IN (SELECT CUIL FROM ORGANISMO.VEMPLEADO WHERE FECHA_EGRESO IS NULL OR FECHA_EGRESO > (SELECT SYSDATE FROM SYS.DUAL))

EDIT:

Here is dump(fecha_hasta, 1016) added:

@AlexPoole yes and it even fails from my application (where the problem started in the first place) — NicoRiff, Dec 19 '18 at 16:35
OK, then is the column data type in the view still actually 'date', or has it been converted to a string - with the follow-up of whether the '(null)' is being put there by SQL Developer, which seems likely on the face of it, or is actually in the view query result? i.e. whether it's possible that the value from the view isn't actually null at all? Clearly I'm clutching at straws here, but there isn't an obvious reason it would behave like that. It might be worth including your full Oracle version and patch level just in case this is a known bug. — Alex Poole, Dec 19 '18 at 16:45
Incidentally, you don't need the subqueries against `dual` - you can do `AND FECHA_HASTA > SYSDATE - 180` and `FECHA_EGRESO > SYSDATE`. And the `AND CUIL IN (...)` subquery could be reworked as a join, but you might have a reason for using that construct. It might be worth trying it as a join just in case that somehow avoids whatever bug you're hitting, if that is what's happening... — Alex Poole, Dec 19 '18 at 16:53
One other possibility is that the view is still returning a date, but it is corrupt; I'm not sure if that can present as null but I have a vague feeling I've seen it do that. Can you add the result of `select dump(fecha_hasta, 1016)` for that row from the view? — Alex Poole, Dec 19 '18 at 16:55
@AlexPoole the field datatype is DATE as that is the first thing I checked out with DESCRIBE. I will add the image of the dup on edit — NicoRiff, Dec 19 '18 at 17:03
Well, they aren't really null then, so some of the behaviour makes sense... but those dumped values should be 31-01-2157 23:59:59 and 31-12-2083 23:59:59, which look slightly odd maybe. SQL Developer is showing me those rather than null though (Oracle 11.2.0.4.5, SQL Developer 18.3). — Alex Poole, Dec 19 '18 at 17:14
So it is an issue with the view then. I will talk to this guys and see if they can filter the null values on the view then. It is the only thing that can be done I suppose. Thank you very much for your help... I appreciate that very much as I wouldn´t figure this out myself! — NicoRiff, Dec 19 '18 at 17:22

score 2 · Accepted Answer · answered Dec 19 '18 at 17:38

The dumped values show that the data is corrupt. The internal date format is well-known:

byte 1 - century (excess 100)  
byte 2 - year (excess 100)  
byte 3 - month  
byte 4 - day  
byte 5 - hour (excess 1)  
byte 6 - minute (excess 1)  
byte 7 - seconds (excess 1)

so the fourth byte in the two values that SQL Developer is reporting as null (even though they clearly are not actually null) should not be zero, as there is no day zero.

Based on those rules, 79,9d,2,0,18,3c,3c in hex, which is 121,157,2,0,24,60,60 in decimal, should convert as:

century: 121 - 100 = 21
year: 157 - 100 - 57
month: 2
day: 0
hour: 24 - 1 = 23
minute: 60 - 1 = 59
second: 60 - 1 = 59

or 2157-02-00 23:59:59. Similarly 78,b8,1,0,18,3c,3c converts to 2084-01-00 23:59:59.

Version 18.3 of SQL Developer displays those values, in both the script output and query results windows, as the previous day:

DT                  DUMPED                             
------------------- -----------------------------------
01-07-2020 23:59:59 Typ=12 Len=7: 78,78,7,1,18,3c,3c   
31-01-2157 23:59:59 Typ=12 Len=7: 79,9d,2,0,18,3c,3c   
31-12-2083 23:59:59 Typ=12 Len=7: 78,b8,1,0,18,3c,3c   
01-07-2018 00:00:00 Typ=12 Len=7: 78,76,7,1,1,1,1

whereas db<>fiddle shows the zero-day values.

So, since they are not actually null, it's reasonable that is not null and nvl() didn't affect them, and then it's up to the client or application as to how to present them.

The real issue is that you seem to have corrupted data in the tables underlying the view you're querying, so that needs to be investigated and fixed - assuming the invalid values can be safely identified, and you can find out what they should have been in the first place, which might be a struggle. Just filtering them out, either as part of the view or in your query, won't be simple though - unless you can filter out dates in the future. And assuming all the corruption is both that obvious and pushing dates into the future; on some level you have to question the validity of all of those dates... there could be much more subtle corruptions that look OK.

And then whatever process or tool caused the corruption needs to be tracked down and fixed so it doesn't happen again. Lots of things can cause corruption of course, but I believe imp used to have a bug that could corrupt dates and numbers, and OCI programs can too.

Thanks again for your help and investigation. I do not control the database. I even have a quite restricted user. I sent all this information to the database guys so they can check out what is underlying this issue. — NicoRiff, Dec 19 '18 at 17:44

Cannot filter null Datetime values

1 Answers1