Thanks matshyeq, you are right, I tested it on other sql engines and the result is the same. (but I still find this confusing...)
SELECT 1 IN (1,2,3,NULL) ; > true SELECT 1 IN (2,3) ; > false SELECT 1 IN (2,3,NULL) ; > NULL SELECT 1 NOT IN (1,2,3,NULL) ; > false SELECT 1 NOT IN (2,3,NULL) ; > NULL SELECT 1 NOT IN (2,3) ; > true On Tue, Jul 7, 2015 at 5:24 PM, Grant Overby (groverby) <grove...@cisco.com> wrote: > "I call it my billion-dollar mistake. It was the invention of the null > reference in 1965.” > — Tony Hoare > > > *Grant Overby* > Software Engineer > Cisco.com <http://www.cisco.com/> > grove...@cisco.com > Mobile: *865 724 4910 <865%20724%204910>* > > > > 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> > Reply-To: "user@hive.apache.org" <user@hive.apache.org> > Date: Tuesday, July 7, 2015 at 9:25 AM > To: user <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> 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 >> > >
