You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Kathey Marsden <km...@sbcglobal.net> on 2006/09/12 00:45:54 UTC
Did DERBY-883 fix this wrong results bug?
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.
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
Re: Did DERBY-883 fix this wrong results bug?
Posted by Myrna van Lunteren <m....@gmail.com>.
On 9/11/06, Manish Khettry <ma...@gmail.com> wrote:
> DERBY-883 did change how the SQL layer modifies the ResultSetNode tree for
> group by's so its possible that it has altered the runtime behavior in some
> cases, although this was not the intent!
>
> Its worth verifying first, that the new results are indeed correct and then
> which checkin fixed/regressed this query.
>
> Thanks,
> Manish
>
>
> On 9/11/06, Kristian Waagan <Kr...@sun.com> wrote:
> > 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.
> >
I did a quick check with some 10.2 beta builds I had lying about, and
the problem (trim trims off 12 to 1) occurs in 10.2.1.1 (436991) but
not in 10.2.1.3 (441574).
Of course, that doesn't narrow it down much. :-/
Myrna
Re: Did DERBY-883 fix this wrong results bug?
Posted by Manish Khettry <ma...@gmail.com>.
DERBY-883 did change how the SQL layer modifies the ResultSetNode tree for
group by's so its possible that it has altered the runtime behavior in some
cases, although this was not the intent!
Its worth verifying first, that the new results are indeed correct and then
which checkin fixed/regressed this query.
Thanks,
Manish
On 9/11/06, Kristian Waagan <Kr...@sun.com> wrote:
>
> 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
> >
> >
> >
> >
> >
>
>
Re: Did DERBY-883 fix this wrong results bug?
Posted by Kristian Waagan <Kr...@Sun.COM>.
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
>
>
>
>
>