Postgres - compare to a null-timestamp

Question

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)

No it does not "evaluate to true". It evaluates to "unknown". You will have to show us the full query you are using so that we can understand why you think it evaluates to true. — user330315
– user330315, Commented Aug 2, 2013 at 9:27
Why do you think it evaluates to true? Can you give an example row where you think it evaluates to true? — Ihor Romanchenko
– Ihor Romanchenko, Commented Aug 2, 2013 at 9:42

Ihor Romanchenko · Accepted Answer · 2013-08-02 09:54:28Z

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

Collectives™ on Stack Overflow

Postgres - compare to a null-timestamp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related