CODEWITHSUNDEEP
postgresqltimestamp
wutzebaer
wutzebaer

Reputation: 14865

Postgres - compare to a null-timestamp

hi this is not really a problem i just want to understand why:

in postgres 9

this_.lastModified<=NULL

evaluates to true, why? lastModified is a timestamp without timezone

i think it would be more logic to interpret it like "this_.lastModified<=0" which should evaluate to false if 0 is the lowest date and lastModified is a normal date

the complete query looks like this

select 
this_.*
from Entity this_ 
inner join DEntity d2_ on this_.device=d2_.id 
inner join u u1_ on this_.learner=u1_.userID 
inner join LMEntity m3_ on this_.method=m3_.id 
where u1_.userID='XXXX' and not (d2_.hardwareID='muh' and this_.timestamp='2013-08-02 00:00:00' and m3_.id=0 and this_.lastModified<=NULL)

Upvotes: 2

Views: 11704

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28551

this_.lastModified<=NULL always evaluates to null and in this case your where clause is effectively:

where u1_.userID='XXXX' and not (d2_.hardwareID='muh' and this_.timestamp='2013-08-02 00:00:00' and m3_.id=0 and null)

if all of the comparisons here: 

d2_.hardwareID='muh' and this_.timestamp='2013-08-02 00:00:00' and m3_.id=0

evaluates to 'true' the this whole clause evaluates to true:

where u1_.userID='XXXX' and not (true and null)

true and null evaluates to null

where u1_.userID='XXXX' and not null

not null evaluates to null

where u1_.userID='XXXX' and null

if u1_.userID='XXXX' equal true, u1_.userID='XXXX' and null evaluates to null

and where null is equal to where false.

In short, the whole 

where u1_.userID='XXXX' and not (d2_.hardwareID='muh' and this_.timestamp='2013-08-02 00:00:00' and m3_.id=0 and this_.lastModified<=NULL)

will evaluate to null if u1_.userID='XXXX' and all of d2_.hardwareID='muh' and this_.timestamp='2013-08-02 00:00:00' and m3_.id=0 gives true

Upvotes: 8

Related Questions