Re: WHERE ... NOT IN (...) + NULL values = BUG
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
Re: WHERE ... NOT IN (...) + NULL values = BUG
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.comhttp://www.cisco.com/ grove...@cisco.commailto: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 herehttp://www.cisco.com/web/about/doing_business/legal/cri/index.html for Company Registration Information. From: matshyeq matsh...@gmail.commailto:matsh...@gmail.com Reply-To: user@hive.apache.orgmailto:user@hive.apache.org user@hive.apache.orgmailto:user@hive.apache.org Date: Tuesday, July 7, 2015 at 9:25 AM To: user user@hive.apache.orgmailto: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.commailto: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
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
