From derby-dev-return-99608-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Tue Dec 18 17:02:17 2012 Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 615AFE595 for ; Tue, 18 Dec 2012 17:02:17 +0000 (UTC) Received: (qmail 65360 invoked by uid 500); 18 Dec 2012 17:02:16 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 65065 invoked by uid 500); 18 Dec 2012 17:02:14 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 64899 invoked by uid 99); 18 Dec 2012 17:02:13 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 18 Dec 2012 17:02:13 +0000 Date: Tue, 18 Dec 2012 17:02:13 +0000 (UTC) From: "Bryan Pendleton (JIRA)" To: derby-dev@db.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (DERBY-6017) IN lists with constants may return wrong results MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DERBY-6017?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13535049#comment-13535049 ] Bryan Pendleton commented on DERBY-6017: ---------------------------------------- A very clear description, and a very clear analysis; thank you very much! The fact that floating point comparisons are approximate has always caught me up; I wish that SQL had made it illegal to perform an exact comparison ( "=", "IN", etc.) on a floating point type. Then we could have just declared this query illegal, and forced the user to think more clearly about what computation they were trying to express. Is there any hope for such a resolution in the SQL standard? > IN lists with constants may return wrong results > ------------------------------------------------ > > Key: DERBY-6017 > URL: https://issues.apache.org/jira/browse/DERBY-6017 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.9.1.0 > Reporter: Knut Anders Hatlen > Assignee: Knut Anders Hatlen > > Given this table: > ij> connect 'jdbc:derby:memory:db;create=true'; > ij> create table t(x bigint); > 0 rows inserted/updated/deleted > ij> insert into t values 9223372036854775805, 9223372036854775806, 9223372036854775807; > 3 rows inserted/updated/deleted > A query that uses an IN list that contains all the three values actually stored in the table, returns all three rows as expected: > ij> select * from t where x in (9223372036854775805, 9223372036854775806, 9223372036854775807); > X > -------------------- > 9223372036854775805 > 9223372036854775806 > 9223372036854775807 > 3 rows selected > However, if we add a value whose type precedence is higher, like a DOUBLE value, and that value happens to be equal to the approximation of the other values in the IN list when they are cast from BIGINT to DOUBLE, only one row is returned: > ij> select * from t where x in (9223372036854775805, 9223372036854775806, 9223372036854775807, 9.223372036854776E18); > X > -------------------- > 9223372036854775805 > 1 row selected > I believe this query should return all three rows too. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira