db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Did DERBY-883 fix this wrong results bug?
Date Tue, 12 Sep 2006 00:00:54 GMT
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
> 
> 
> 
> 
> 


Mime
View raw message