"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/> [email protected]<mailto:[email protected]> 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 <[email protected]<mailto:[email protected]>> Reply-To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Date: Tuesday, July 7, 2015 at 9:25 AM To: user <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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
