Re: problem or bug in oracle with Cube operator?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/10
Message-ID: <8hu5hi$71l$1@nnrp1.deja.com>#1/1

In article <17efd694.57cbdfa8_at_usw-ex0107-055.remarq.com>,   gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:
> Does anyone have much experience using the new olap
> functionality in oracle 8i?
>
> I'm trying to make use of the cube and grouping operators but am
> running into a problem...which I can't help but think is a bug
> in Oracle.
>
> The problem exists in 8.1.5 as well as in 8.1.6
>
> To sum up the problem:
>
> If a query is sent that constrains a column to a single value,
> the cube/grouping functions seem to produce somewhat erroneous
> results.
> Note: It only happens when constraining on a single value.
>
> For example:
>
> SELECT SUM ( A.SALES ),
> DECODE(GROUPING(B.PRODUCT),1,'**TOTAL**', B.PRODUCT) as COL1 ,
> DECODE(GROUPING(B.COLOR),1,'**TOTAL**', B.COLOR) as COL2
> FROM FACT_TABLE A, PRODUCT B WHERE (A.PROD_ID=B.PROD_ID)
> and B.COLOR IN ('RED') GROUP BY cube(B.PRODUCT,B.COLOR)
>
> NOTE: issuing B.COLOR ='RED' has the same problem so it is not
> related to the IN operator
>
> Yields:
>
> SUM(A.SALES) COL1 COL2
> ----------------- ---------- -------
> 307 WIDGETS RED
> 307 **TOTAL**
> 307 **TOTAL**
> 307 **TOTAL** **TOTAL**
>
> What makes this result set erroneous are the nulls that appear
> in the 2nd and 3rd rows of COL1 and COL2.
>
> Now, if I alter the statement to add a competely bogus value
> which I know doesn't exist in the column as follows, then it
> seems to work
> properly:
>
> SELECT SUM ( A.SALES ),
> DECODE(GROUPING(B.PRODUCT),1,'**TOTAL**', B.PRODUCT) as COL1 ,
> DECODE(GROUPING(B.COLOR),1,'**TOTAL**', B.COLOR) as COL2
> FROM FACT_TABLE A, PRODUCT B WHERE (A.PROD_ID=B.PROD_ID)
> and B.COLOR IN ('RED','BOGUS-COLOR-VALUE') GROUP BY cube
> (B.PRODUCT,B.COLOR)
>
> SUM(A.SALES) COL1 COL2
> ----------------- ---------- --------
> 307 WIDGETS RED
> 307 WIDGETS **TOTAL**
> 307 **TOTAL** RED
> 307 **TOTAL** **TOTAL**
>
> Has anyone run into this problem? Is it a bug in Oracle? If
> it's not a bug, I'd appreciate if someone could explain to me
> the logic for
> why it's not so that I can have a better understanding of how
> cube and grouping should work.
>
> Thanks,
> Gavin
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion
 Network *
> The fastest and easiest way to search and participate in Usenet -
 Free!
>
>

can you help me reproduce this? Here is my test:

ops$tkyte_at_8i> create table fact_table( sales int, prod_id int ); Table created.

ops$tkyte_at_8i> create table product( prod_id int, product varchar2(25), color varchar2(25) );
Table created.

ops$tkyte_at_8i> insert into fact_table values ( 10, 1 ); 1 row created.

ops$tkyte_at_8i> insert into fact_table values ( 10, 1 ); 1 row created.

ops$tkyte_at_8i> insert into product values ( 1, 'WIDGET', 'RED' ); 1 row created.

ops$tkyte_at_8i> SELECT SUM ( A.SALES ),
  2       DECODE(GROUPING(B.PRODUCT),1,'**TOTAL**', B.PRODUCT) as COL1 ,
  3       DECODE(GROUPING(B.COLOR),1,'**TOTAL**', B.COLOR) as COL2

  4 FROM FACT_TABLE A, PRODUCT B
  5 WHERE (A.PROD_ID=B.PROD_ID)
  6 and B.COLOR IN ('RED')
  7 GROUP BY cube(B.PRODUCT,B.COLOR)
  8 /

SUM(A.SALES) COL1                      COL2
------------ ------------------------- -------------------------
          20 WIDGET                    RED
          20 WIDGET                    **TOTAL**
          20 **TOTAL**                 RED
          20 **TOTAL**                 **TOTAL**

ops$tkyte_at_8i>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat Jun 10 2000 - 00:00:00 CDT