Strange results of aggregate function against view; okay against table

Strange results of aggregate function against view; okay against table

Post by Thomas Gaine » Wed, 06 Nov 2002 07:57:50



Folks -

I suspect that I may be making a silly mistake, but this is confounding
my coworker and me.  I'm getting some wrong results when performing
an aggregate function on various columns in a view.  The same query
when executed on a table of the same form and data works as
expected.

My database version is 8.1.7.2.1.  My OS is Win2000.

Here is a simple test case that illustrates my problem.  It works
against
the scott.emp and scott.dept tables.

=====================================================

-- Create a simple view that combines the emp and dept tables to get
-- counts displayed in the columns.  Be sure to display zeroes when
-- appropriate.
create or replace view dummyview2 as
select
   distdepts.deptno,
   nvl(subquery1.CNT,0) count1,
   nvl(subquery2.CNT,0) count2
from
  (select distinct deptno from scott.dept)  distdepts,
  (select count(*) CNT, deptno from scott.emp
       where scott.emp.sal < 2000
       group by deptno) subquery1,
  (select count(*) CNT, deptno from scott.emp
       where scott.emp.sal < 4000
       group by deptno) subquery2
where distdepts.deptno = subquery1.deptno(+)
and   distdepts.deptno = subquery2.deptno(+);

-- Display the contents of this view.
select * from dummyview2;

-- Do a meaningless query that does a simple summation.  The
-- results of this query should look exactly like the stuff in the
-- view because all of the department ids in the view are unique.
select deptno, sum(count1), sum(count2) from
   dummyview2 group by deptno;

-- If you've performed the steps above, you'll see that the results
-- of the query immediately above DO NOT look exactly like the stuff
-- obtained from the view.  They should, though.

-- Do the same query, but this time, do it against
-- a table with the same form and data as the view.
drop table dummytab;
create table dummytab as select * from dummyview2;
select deptno, sum(count1), sum(count2) from
   dummytab group by deptno;

-- Pretty weird, huh??

=====================================================

Am I doing something silly here?  I'm wondering if I'm missing a hint or

perhaps an init.ora parameter that influences the aggregation functions
when executed against a view.

Thanks for your time.

Tom

 
 
 

Strange results of aggregate function against view; okay against table

Post by Thomas Gain » Thu, 07 Nov 2002 02:00:08


I'm sorry to be difficult, but could someone run the simple testcase
below for me, please?  It simply makes a view based on the scott.emp
and scott.dept tables and then does a query against that view.  I've
opened a TAR with Oracle Support and the person there says that
all is well.  But I've seen the following results on three of my databases
(these are Oracle8i and Oracle9i on Win2000 and Linux), and I'm
stumped!!

This is a snippet of what I'm seeing.  The result sets should be equal,
although your results may be slightly different from those below if the
contents of the emp and dept tables have been modified

++++++++++++++++++++++++++++++++++++++++++++++++
SQL>   select * from dummyview2;

    DEPTNO     COUNT1     COUNT2
---------- ---------- ----------
        10          1          2
        20          2          5
        30          5          6
        40          0          0
        44          0          0

SQL>   select deptno, sum(count1), sum(count2) from
  2       dummyview2 group by deptno;

    DEPTNO SUM(COUNT1) SUM(COUNT2)
---------- ----------- -----------
        10           2           2
        20           5           5
        30           6           6
        40           0           0
        44           0           0

++++++++++++++++++++++++++++++++++++++++++++++++

Thanks very much for your time.  I'll owe you!

Tom


> Folks -

> I suspect that I may be making a silly mistake, but this is confounding
> my coworker and me.  I'm getting some wrong results when performing
> an aggregate function on various columns in a view.  The same query
> when executed on a table of the same form and data works as
> expected.

> My database version is 8.1.7.2.1.  My OS is Win2000.

> Here is a simple test case that illustrates my problem.  It works
> against
> the scott.emp and scott.dept tables.

> =====================================================

> -- Create a simple view that combines the emp and dept tables to get
> -- counts displayed in the columns.  Be sure to display zeroes when
> -- appropriate.
> create or replace view dummyview2 as
> select
>    distdepts.deptno,
>    nvl(subquery1.CNT,0) count1,
>    nvl(subquery2.CNT,0) count2
> from
>   (select distinct deptno from scott.dept)  distdepts,
>   (select count(*) CNT, deptno from scott.emp
>        where scott.emp.sal < 2000
>        group by deptno) subquery1,
>   (select count(*) CNT, deptno from scott.emp
>        where scott.emp.sal < 4000
>        group by deptno) subquery2
> where distdepts.deptno = subquery1.deptno(+)
> and   distdepts.deptno = subquery2.deptno(+);

> -- Display the contents of this view.
> select * from dummyview2;

> -- Do a meaningless query that does a simple summation.  The
> -- results of this query should look exactly like the stuff in the
> -- view because all of the department ids in the view are unique.
> select deptno, sum(count1), sum(count2) from
>    dummyview2 group by deptno;

> -- If you've performed the steps above, you'll see that the results
> -- of the query immediately above DO NOT look exactly like the stuff
> -- obtained from the view.  They should, though.

> -- Do the same query, but this time, do it against
> -- a table with the same form and data as the view.
> drop table dummytab;
> create table dummytab as select * from dummyview2;
> select deptno, sum(count1), sum(count2) from
>    dummytab group by deptno;

> -- Pretty weird, huh??

> =====================================================

> Am I doing something silly here?  I'm wondering if I'm missing a hint or

> perhaps an init.ora parameter that influences the aggregation functions
> when executed against a view.

> Thanks for your time.

> Tom

> --


 
 
 

Strange results of aggregate function against view; okay against table

Post by Sybrand Bakke » Thu, 07 Nov 2002 03:04:32




Quote:>I'm sorry to be difficult, but could someone run the simple testcase
>below for me, please?  It simply makes a view based on the scott.emp
>and scott.dept tables and then does a query against that view.  I've
>opened a TAR with Oracle Support and the person there says that
>all is well.  But I've seen the following results on three of my databases
>(these are Oracle8i and Oracle9i on Win2000 and Linux), and I'm
>stumped!!

>This is a snippet of what I'm seeing.  The result sets should be equal,
>although your results may be slightly different from those below if the
>contents of the emp and dept tables have been modified

>++++++++++++++++++++++++++++++++++++++++++++++++
>SQL>   select * from dummyview2;

>    DEPTNO     COUNT1     COUNT2
>---------- ---------- ----------
>        10          1          2
>        20          2          5
>        30          5          6
>        40          0          0
>        44          0          0

>SQL>   select deptno, sum(count1), sum(count2) from
>  2       dummyview2 group by deptno;

>    DEPTNO SUM(COUNT1) SUM(COUNT2)
>---------- ----------- -----------
>        10           2           2
>        20           5           5
>        30           6           6
>        40           0           0
>        44           0           0

>++++++++++++++++++++++++++++++++++++++++++++++++

>Thanks very much for your time.  I'll owe you!

>Tom

Can not reproduce
Obviously also your dept table differs from the default

SQL*Plus: Release 9.0.1.0.1 - Production on Di Nov 5 18:59:02 2002

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Verbonden met:
Oracle9i Personal Edition Release 9.0.1.1.1 - Production
JServer Release 9.0.1.1.1 - Production

SQL> create or replace view dummyview2 as
  2  select
  3     distdepts.deptno,
  4     nvl(subquery1.CNT,0) count1,
  5     nvl(subquery2.CNT,0) count2
  6  from
  7    (select distinct deptno from scott.dept)  distdepts,
  8    (select count(*) CNT, deptno from scott.emp
  9         where scott.emp.sal < 2000
 10         group by deptno) subquery1,
 11    (select count(*) CNT, deptno from scott.emp
 12         where scott.emp.sal < 4000
 13         group by deptno) subquery2
 14  where distdepts.deptno = subquery1.deptno(+)
 15  and   distdepts.deptno = subquery2.deptno(+);

View is aangemaakt.

SQL> select * from dummyview2
  2  /

    DEPTNO     COUNT1     COUNT2
---------- ---------- ----------
        10          1          2
        20          2          5
        30          5          6
        40          0          0

SQL> select deptno, sum(count1), sum(count2) from
  2     dummyview2 group by deptno;

    DEPTNO SUM(COUNT1) SUM(COUNT2)
---------- ----------- -----------
        10           1           2
        20           2           5
        30           5           6
        40           0           0

SQL> create table dummytab as select * from dummyview2;

Tabel is aangemaakt.

SQL> select deptno, sum(count1), sum(count2) from
  2     dummytab group by deptno;

    DEPTNO SUM(COUNT1) SUM(COUNT2)
---------- ----------- -----------
        10           1           2
        20           2           5
        30           5           6
        40           0           0

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

 
 
 

Strange results of aggregate function against view; okay against table

Post by Thomas Gaine » Thu, 07 Nov 2002 04:40:31


Thanks very much for your assistance, Sybrand.  Everyone I've
checked with here at work, at Oracle, and elsewhere confirms what
you saw.  Something must be amiss with my specific installations,
although I can't even guess at what might be the problem.

The battle goes on.

Tom

 
 
 

1. Developing against English version of Oracle, running against French version

We're about to start a development project for a French customer, and
will be developing against an English version of Oracle.

Naturally, the customer will be running a French version.

Are we going to have problems with this scenario?  

I assume that schema definition etc. are language-independent (i.e.
always in English - being English-centric).  What about character sets
etc.

If necessary, we can install French and develop against it.

Thanks,

James Lavery
MicroSec Ltd

2. help - nt shutdown causes repl to fail with "row is bigger..."

3. Query performace against View or Table.

4. Error Message during Update command

5. wrong results from sql query against cube

6. website for Geographic Database Software

7. Set Precedence Against Multiple Result Sets?

8. Renme Tablespace in 8i

9. Problem using ADO stream object for result of XML template against SQL 2K

10. Checking query results against selectivity estimate

11. Compare Table against Full Text Indexed Table

12. Views to isolate against cross-database and cross-server

13. Perf tuning against view on SQL Server 7