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
