Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (nike.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type;
  b=Y5AppeORlDZtUww+GSIabXp8p1DYgLXT9ikSFYta9/JWKh70GxlWEeD4cD0VRDsWUsKPNiViN0vHwbqy/5vJE8rBCfT9FwB7b6/8p93oWXxHwyZm0v3cFlSLIjoa2spQRnOaZS6GoOGb7jKFhbqrzdcMqHCNmbstmwYHLJ4TAFY=;
Message-ID: <10530.33911.qm@web121212.mail.ne1.yahoo.com>
Date: Fri, 4 Mar 2011 07:13:50 -0800 (PST)
From: Bejoy Ks <bejoy_ks@yahoo.com>
Subject: Date function unix_timestamp() with input values null doen't work as
 desired
To: hive user group <user@hive.apache.org>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1142464919-1299251630=:33911"

--0-1142464919-1299251630=:33911
Content-Type: text/plain; charset=us-ascii

Hi Everyone
    I'm facing an issue with hive on a relatively  larger query which involves 
joins on six hive tables. My query is running fine without any errors, all the 
map reduce jobs run to completion but unfortunately it is not showing up any 
results. I tried debugging the query and to investigate the root cause, When i 
removed one of the last conditions from the query it is showing up results, this 
is the condition I removed
and ((unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime 
))or  R8.REMOVAL_DATETIME IS NULL)
I have used a similar comparison operation within my working query on dates 
using the unix_timestamp method which is working as desired. But here it is not, 
I'm assuming this would be due to the fact that r8.removal_datetime has null 
values hence here the evaluation would happen against a valid unix stamp and 
null.
ie (unix_timestamp(r4.flight_datetime) <= unix_timestamp(r8.removal_datetime )) 
would inturn lead to  valid timestamp<= NULL

Has any one faced similar situations before? How can i get around this hurdle? 
Please advise. 


Regards
Bejoy.K.S


--0-1142464919-1299251630=:33911
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman,new york,times,serif;font-size:12pt"><div>Hi Everyone<br>&nbsp;&nbsp;&nbsp; I'm facing an issue with hive on a relatively&nbsp; larger query which involves joins on six hive tables. My query is running fine without any errors, all the map reduce jobs run to completion but unfortunately it is not showing up any results. I tried debugging the query and to investigate the root cause, When i removed one of the last conditions from the query it is showing up results, this is the condition I removed<br>and ((unix_timestamp(r4.flight_datetime) &lt;= unix_timestamp(r8.removal_datetime ))or&nbsp; R8.REMOVAL_DATETIME IS NULL)<br>I have used a similar comparison operation within my working query on dates using the unix_timestamp method which is working as desired. But here it is not, I'm assuming this would be due to the fact that
 r8.removal_datetime has null values hence here the evaluation would happen against a valid unix stamp and null.<br>ie (unix_timestamp(r4.flight_datetime) &lt;= unix_timestamp(r8.removal_datetime )) would inturn lead to&nbsp; valid timestamp&lt;= NULL<br><br>Has any one faced similar situations before? How can i get around this hurdle? Please advise. <br><br>Regards<br>Bejoy.K.S<br><br><br></div>
</div><br>

      </body></html>
--0-1142464919-1299251630=:33911--