"I call it my billion-dollar mistake. It was the invention of the null reference in 1965.” — Tony Hoare
[http://www.cisco.com/web/europe/images/email/signature/est2014/logo_06.png?ct=1398192119726] Grant Overby Software Engineer Cisco.com<http://www.cisco.com/> grove...@cisco.com<mailto:grove...@cisco.com> Mobile: 865 724 4910 [http://www.cisco.com/assets/swa/img/thinkbeforeyouprint.gif] Think before you print. This email may contain confidential and privileged material for the sole use of the intended recipient. Any review, use, distribution or disclosure by others is strictly prohibited. If you are not the intended recipient (or authorized to receive for the recipient), please contact the sender by reply email and delete all copies of this message. Please click here<http://www.cisco.com/web/about/doing_business/legal/cri/index.html> for Company Registration Information. From: matshyeq <matsh...@gmail.com<mailto:matsh...@gmail.com>> Reply-To: "user@hive.apache.org<mailto:user@hive.apache.org>" <user@hive.apache.org<mailto:user@hive.apache.org>> Date: Tuesday, July 7, 2015 at 9:25 AM To: user <user@hive.apache.org<mailto:user@hive.apache.org>> Subject: Re: WHERE ... NOT IN (...) + NULL values = BUG >Obviously, the expected answer is always 2. That's incorrect. It's expected behaviour, SQL standard and I would expect every other DBs behave same way. The direct comparison to NULL returns FALSE. Always. Doesn't matter if used as <> ,=, IN, NOT IN. IS (NOT) NULL is the right way to handle such cases. COALESCE is some alternative too. Thank you, Kind Regards ~Maciek On Tue, Jul 7, 2015 at 11:46 AM, Furcy Pin <furcy....@flaminem.com<mailto:furcy....@flaminem.com>> wrote: Hi folks, just to let my fellow Hive users know that we found a bug with subquery in where clauses and created a JIRA for it. https://issues.apache.org/jira/browse/HIVE-11192 The latest version seems to be affected. Regards, Furcy Pin
