Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 98421 invoked from network); 12 Sep 2006 00:03:01 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 12 Sep 2006 00:03:01 -0000 Received: (qmail 64409 invoked by uid 500); 12 Sep 2006 00:03:01 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 64195 invoked by uid 500); 12 Sep 2006 00:03:00 -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 64186 invoked by uid 99); 12 Sep 2006 00:03:00 -0000 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: local policy) Received: from [192.18.1.36] (HELO gmpea-pix-1.sun.com) (192.18.1.36) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 11 Sep 2006 17:02:58 -0700 Received: from d1-emea-10.sun.com ([192.18.2.120]) by gmpea-pix-1.sun.com (8.13.6+Sun/8.12.9) with ESMTP id k8C01CGU002608 for ; Tue, 12 Sep 2006 01:01:14 +0100 (BST) Received: from conversion-daemon.d1-emea-10.sun.com by d1-emea-10.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) id <0J5G00H01D72P000@d1-emea-10.sun.com> (original mail from Kristian.Waagan@Sun.COM) for derby-dev@db.apache.org; Tue, 12 Sep 2006 01:01:12 +0100 (BST) Received: from [192.168.1.100] ([62.16.224.92]) by d1-emea-10.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) with ESMTPSA id <0J5G009I7DDYI6W0@d1-emea-10.sun.com> for derby-dev@db.apache.org; Tue, 12 Sep 2006 01:01:12 +0100 (BST) Date: Tue, 12 Sep 2006 02:00:54 +0200 From: Kristian Waagan Subject: Re: Did DERBY-883 fix this wrong results bug? In-reply-to: <4505E722.8000703@sbcglobal.net> Sender: Kristian.Waagan@Sun.COM To: derby-dev@db.apache.org Message-id: <4505F8B6.9040103@Sun.COM> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7BIT References: <4505E722.8000703@sbcglobal.net> User-Agent: Thunderbird 1.5.0.5 (X11/20060810) X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Kathey Marsden wrote: > I am looking at a wrong results case and it would appear that it has > already been fixed. It appears to be wrong results when RTRIM is added > in to the query with 10.1.3, and 10.2. In the trunk I think it is ok. > > I am kind of guessing it was DERBY-883 that fixed it, but the query is > not group by expression actually. Hello Kathey, Not an answer to your question, but DERBY-883 also fixed a bug with a query using COALESCE. Maybe the patches fixed a more general bug somewhere in the sql/optimizer layer. At least one of the patches were merged into 10.2. Do you still see the bug there? If so, then there must be another patch affecting these queries as well. Regards, -- Kristian > Also the right results/wrong results assessment this is coming from the > original user. I am not sure I fully understand the queries and what is > happening. It would appear that in the expression char(r.h_t))) XVAL, > r.h_t is in question here so truncates the 12 to 1. > > Kathey > > > ij> ij> ij> drop table sample; > 0 rows inserted/updated/deleted > ij> create table SAMPLE ( > SAMPLEID integer not null, > END_TIME timestamp not null, > SAMPLE_RATE double not null, > STATUS integer default 0 > ); > 0 rows inserted/updated/deleted > ij> insert into sample values(2,'1999-02-01 12:00:00',32.22,1); > 1 row inserted/updated/deleted > ij> insert into sample values(3,'1999-03-01 12:00:00',42.22,1); > 1 row inserted/updated/deleted > ij> insert into sample values(4,'1999-04-01 12:00:00',52.22,1); > 1 row inserted/updated/deleted > ij> -- CORRECT -- > > select '' HOUR_INTERVAL, > sum(double(1) / case > when SAMPLE_RATE = 0 then > 1 > else > SAMPLE_RATE > end) / 3600 THROUGHPUT_PER_SEC, > ((char(r.y_t))) || '^' || '^' || > ((char(r.h_t))) XVAL, > '' NA, > '' SORTCOLUMN, > count(*) SAMPLE_COUNT > from (select year(s.END_TIME) y_t, > hour(s.END_TIME) h_t, > day(s.END_TIME) d_t, > month(s.END_TIME) mn_t, > minute(s.END_TIME) mi_t, > s.SAMPLE_RATE > from SAMPLE s) r > GROUP BY r.y_t, r.h_t > ORDER BY r.y_t, r.h_t; > HOUR_INTERVAL |THROUGHPUT_PER_SEC |XVAL > |NA |SORTCOLUMN |SAMPLE_COU& > ----------------------------------------------------------------------------------------------------------- > > |2.0519953166098077E-5 |*1999 ^^12 * > | | |3 > 1 row selected > ij> -- INCORRECT -- > > select '' HOUR_INTERVAL, > sum(double(1) / case > when SAMPLE_RATE = 0 then > 1 > else > SAMPLE_RATE > end) / 3600 THROUGHPUT_PER_SEC, > rtrim((char(r.y_t))) || '^' || '^' || > rtrim((char(r.h_t))) XVAL, > '' NA, > '' SORTCOLUMN, > count(*) SAMPLE_COUNT > from (select year(s.END_TIME) y_t, > hour(s.END_TIME) h_t, > day(s.END_TIME) d_t, > month(s.END_TIME) mn_t, > minute(s.END_TIME) mi_t, > s.SAMPLE_RATE > from SAMPLE s) r > GROUP BY r.y_t, r.h_t > ORDER BY r.y_t, r.h_t; > HOUR_INTERVAL |THROUGHPUT_PER_SEC |XVAL > |NA |SORTCOLUMN |SAMPLE_COU& > ----------------------------------------------------------------------------------------------------------- > > |2.0519953166098077E-5 |*1999^^1 * > | | |3 > 1 row selected > > ---------------------------------------------------------------- > > 2006-09-11 22:43:00.539 GMT: > Booting Derby version The Apache Software Foundation - Apache Derby - > 10.3.0.0 alpha - (1): instance c013800d-010d-9f0c-364b-00000011e070 > on database directory C:\marsden\repro\7978\wombat > > Database Class Loader started - derby.database.classpath='' > > 2006-09-11 22:43:10.804 GMT Thread[main,5,main] (XID = 138), (SESSIONID > = 0), drop table sample ******* null > > 2006-09-11 22:43:11.054 GMT Thread[main,5,main] (XID = 141), (SESSIONID > = 0), create table SAMPLE ( > > SAMPLEID integer not null, > > END_TIME timestamp not null, > > SAMPLE_RATE double not null, > > STATUS integer default 0 > > ) ******* null > > 2006-09-11 22:43:11.555 GMT Thread[main,5,main] (XID = 146), (SESSIONID > = 0), insert into sample values(2,'1999-02-01 12:00:00',32.22,1) ******* > Insert ResultSet using row locking: > deferred: false > insert mode: normal > Rows inserted = 1 > Indexes updated = 0 > Execute Time = 0 > Normalize ResultSet: > Number of opens = 1 > Rows seen = 1 > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 1.00 > optimizer estimated cost: 0.00 > > Source result set: > Row ResultSet: > Number of opens = 1 > Rows returned = 1 > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 1.00 > optimizer estimated cost: 0.00 > > > > > 2006-09-11 22:43:11.585 GMT Thread[main,5,main] (XID = 148), (SESSIONID > = 0), insert into sample values(3,'1999-03-01 12:00:00',42.22,1) ******* > Insert ResultSet using row locking: > deferred: false > insert mode: normal > Rows inserted = 1 > Indexes updated = 0 > Execute Time = 0 > Normalize ResultSet: > Number of opens = 1 > Rows seen = 1 > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 1.00 > optimizer estimated cost: 0.00 > > Source result set: > Row ResultSet: > Number of opens = 1 > Rows returned = 1 > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 1.00 > optimizer estimated cost: 0.00 > > > > > 2006-09-11 22:43:11.615 GMT Thread[main,5,main] (XID = 150), (SESSIONID > = 0), insert into sample values(4,'1999-04-01 12:00:00',52.22,1) ******* > Insert ResultSet using row locking: > deferred: false > insert mode: normal > Rows inserted = 1 > Indexes updated = 0 > Execute Time = 0 > Normalize ResultSet: > Number of opens = 1 > Rows seen = 1 > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 1.00 > optimizer estimated cost: 0.00 > > Source result set: > Row ResultSet: > Number of opens = 1 > Rows returned = 1 > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 1.00 > optimizer estimated cost: 0.00 > > > > > 2006-09-11 22:43:12.296 GMT Thread[main,5,main] (XID = 152), (SESSIONID > = 0), -- CORRECT -- > > > > select '' HOUR_INTERVAL, > > sum(double(1) / case > > when SAMPLE_RATE = 0 then > > 1 > > else > > SAMPLE_RATE > > end) / 3600 THROUGHPUT_PER_SEC, > > ((char(r.y_t))) || '^' || '^' || > > ((char(r.h_t))) XVAL, > > '' NA, > > '' SORTCOLUMN, > > count(*) SAMPLE_COUNT > > from (select year(s.END_TIME) y_t, > > hour(s.END_TIME) h_t, > > > day(s.END_TIME) d_t, > > month(s.END_TIME) mn_t, > > minute(s.END_TIME) mi_t, > > s.SAMPLE_RATE > > from SAMPLE s) r > > GROUP BY r.y_t, r.h_t > > ORDER BY r.y_t, r.h_t ******* Project-Restrict ResultSet (7): > Number of opens = 1 > Rows seen = 1 > Rows filtered = 0 > restriction = false > projection = true > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > restriction time (milliseconds) = 0 > projection time (milliseconds) = 0 > optimizer estimated row count: 1.00 > optimizer estimated cost: 46.02 > > Source result set: > Sort ResultSet: > Number of opens = 1 > Rows input = 1 > Rows returned = 1 > Eliminate duplicates = false > In sorted order = false > Sort information: > Number of rows input=1 > Number of rows output=1 > Sort type=internal > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Project-Restrict ResultSet (5): > Number of opens = 1 > Rows seen = 1 > Rows filtered = 0 > restriction = false > projection = true > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > restriction time (milliseconds) = 0 > projection time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Grouped Aggregate ResultSet: > Number of opens = 1 > Rows input = 3 > Has distinct aggregate = false > In sorted order = false > Sort information: > Number of rows input=3 > Number of rows output=1 > Sort type=internal > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Project-Restrict ResultSet (4): > Number of opens = 1 > Rows seen = 3 > Rows filtered = 0 > restriction = false > projection = true > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > restriction time (milliseconds) = 0 > projection time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Project-Restrict ResultSet (3): > Number of opens = 1 > Rows seen = 3 > Rows filtered = 0 > restriction = false > projection = true > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > restriction time (milliseconds) = 0 > projection time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Table Scan ResultSet for SAMPLE at read committed > isolation level using share row locking chosen by the optimizer > Number of opens = 1 > Rows seen = 3 > Rows filtered = 0 > Fetch Size = 1 > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > next time in milliseconds/row = 0 > > scan information: > Bit set of columns fetched={1, 2} > Number of columns fetched=2 > Number of pages visited=1 > Number of rows qualified=3 > Number of rows visited=3 > Scan type=heap > start position: > null stop position: > null qualifiers: > None > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > > > > 2006-09-11 22:43:12.426 GMT Thread[main,5,main] (XID = 154), (SESSIONID > = 0), -- INCORRECT -- > > > > select '' HOUR_INTERVAL, > > sum(double(1) / case > > when SAMPLE_RATE = 0 then > > 1 > > else > > SAMPLE_RATE > > end) / 3600 THROUGHPUT_PER_SEC, > > rtrim((char(r.y_t))) || '^' || '^' || > > rtrim((char(r.h_t))) XVAL, > > '' NA, > > '' SORTCOLUMN, > > count(*) SAMPLE_COUNT > > from (select year(s.END_TIME) y_t, > > hour(s.END_TIME) h_t, > > > day(s.END_TIME) d_t, > > month(s.END_TIME) mn_t, > > minute(s.END_TIME) mi_t, > > s.SAMPLE_RATE > > from SAMPLE s) r > > GROUP BY r.y_t, r.h_t > > ORDER BY r.y_t, r.h_t ******* Project-Restrict ResultSet (7): > Number of opens = 1 > Rows seen = 1 > Rows filtered = 0 > restriction = false > projection = true > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > restriction time (milliseconds) = 0 > projection time (milliseconds) = 0 > optimizer estimated row count: 1.00 > optimizer estimated cost: 46.02 > > Source result set: > Sort ResultSet: > Number of opens = 1 > Rows input = 1 > Rows returned = 1 > Eliminate duplicates = false > In sorted order = false > Sort information: > Number of rows input=1 > Number of rows output=1 > Sort type=internal > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Project-Restrict ResultSet (5): > Number of opens = 1 > Rows seen = 1 > Rows filtered = 0 > restriction = false > projection = true > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > restriction time (milliseconds) = 0 > projection time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Grouped Aggregate ResultSet: > Number of opens = 1 > Rows input = 3 > Has distinct aggregate = false > In sorted order = false > Sort information: > Number of rows input=3 > Number of rows output=1 > Sort type=internal > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Project-Restrict ResultSet (4): > Number of opens = 1 > Rows seen = 3 > Rows filtered = 0 > restriction = false > projection = true > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > restriction time (milliseconds) = 0 > projection time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Project-Restrict ResultSet (3): > Number of opens = 1 > Rows seen = 3 > Rows filtered = 0 > restriction = false > projection = true > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > restriction time (milliseconds) = 0 > projection time (milliseconds) = 0 > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > Source result set: > Table Scan ResultSet for SAMPLE at read committed > isolation level using share row locking chosen by the optimizer > Number of opens = 1 > Rows seen = 3 > Rows filtered = 0 > Fetch Size = 1 > constructor time (milliseconds) = 0 > open time (milliseconds) = 0 > next time (milliseconds) = 0 > close time (milliseconds) = 0 > next time in milliseconds/row = 0 > > scan information: > Bit set of columns fetched={1, 2} > Number of columns fetched=2 > Number of pages visited=1 > Number of rows qualified=3 > Number of rows visited=3 > Scan type=heap > start position: > null stop position: > null qualifiers: > None > optimizer estimated row count: 8.00 > optimizer estimated cost: 46.02 > > > > >