db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kathey Marsden <kmarsdende...@sbcglobal.net>
Subject Did DERBY-883 fix this wrong results bug?
Date Mon, 11 Sep 2006 22:45:54 GMT
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






Mime
View raw message