RE: Query returning wrong results.

  • From: Prasada.Gunda@xxxxxxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 19 Mar 2004 16:57:03 -0500

No, I did not see anything in object_node column.

Here is the output.

SQL >select distinct statement_id, object_node from plan_table;

STATEMENT_ID                   OBJECT_NODE
------------------------------
---------------------------------------------------------------------
Wrong_results

Thanks.

Best Regards,
Prasad
860 843 8377


                                                                                
                                                        
                      "Khedr, Waleed"                                           
                                                        
                      <Waleed.Khedr@FMR.        To:       
"'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>                           
                      COM>                      cc:                             
                                                        
                      Sent by:                  Subject:  RE: Query returning 
wrong results.                                            
                      oracle-l-bounce@fr                                        
                                                        
                      eelists.org                                               
                                                        
                                                                                
                                                        
                                                                                
                                                        
                      03/19/2004 01:41                                          
                                                        
                      PM                                                        
                                                        
                      Please respond to                                         
                                                        
                      oracle-l                                                  
                                                        
                                                                                
                                                        
                                                                                
                                                        




When you do explain plan for the sql, do you see any ":Q" in the
object_node
column?

Waleed

-----Original Message-----
From: Prasada.Gunda@xxxxxxxxxxxxxxxx
[mailto:Prasada.Gunda@xxxxxxxxxxxxxxxx]
Sent: Friday, March 19, 2004 10:58 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Query returning wrong results.



Thanks Jared, Waleed and David for your replies.

It is not using PQO (but parallel_automatic_tuning is set to TRUE).  Table
has degree 1.  Also tested with noparallel hint and no_merge hints, it is
returning same wrong results.

DB is 8.1.7.4 and It is on Hp-Unix v11.

David : Inline view is returning 3 records and only one record satisfies
the outer filter predicate.

I will do more research on metalink and will update you all if I find
anything relevant.

Best Regards,
Prasad
860 843 8377




                      Jared.Still@radisy

                      s.com                     To:
oracle-l@xxxxxxxxxxxxx

                      Sent by:                  cc:

                      oracle-l-bounce@fr        Subject:  Re: Query
returning wrong results.
                      eelists.org





                      03/18/2004 04:31

                      PM

                      Please respond to

                      oracle-l










Looks like a bug.  If you provide some platform and version
information someone may have an idea as to which bug.

Better yet, check on MetaLink for related bugs.

If it is a parallel query it could be bug 1759227

Jared




   Prasada.Gunda@xxxxxxxxxxxxxxxx
                                          To:
   Sent by:                       oracle-l@xxxxxxxxxxxxx
   oracle-l-bounce@xxxxxxxxxxxxx          cc:
                                          Subject:        Query returning
                                  wrong results.
    03/18/2004 11:31 AM
    Please respond to oracle-l







Hi,

One of our developer sent me this query that it is returning wrong results.
Please see the following query.

select  v.col1, v.row_eff_date, v.row_term_date
from
(  -- Inline view begins
select a.col1
,    trunc(a.col2) row_eff_date
,    nvl(lead(trunc(a.col2)) over
       (partition by a.col1, a.tblnm, a.clmnnm order by a.col2),to_date
('12/31/2999','mm/dd/yyyy')) row_term_date
from   table1 a
where  a.tblnm  = 'INPUT_TABLE'
and    a.clmnnm = 'INPUT_COL'
and    a.col1   = '10000151'
union all
select a1.col1
,    to_date('01/01/1900','mm/dd/yyyy') row_eff_date
,    trunc(a1.col2) row_term_date
from   table1 a1
where  a1.tblnm  = 'INPUT_TABLE'
and    a1.clmnnm = 'INPUT_COL'
and    a1.col1   = '10000151'
and    (a1.col1, a1.col2) IN
               (select octl1.col1, min(octl1.col2)
                from table1 octl1
                where octl1.tblnm  ='INPUT_TABLE'
                and   octl1.clmnnm ='INPUT_COL'
                group by octl1.col1
                )
-- Inline view ends.
) v
where
(
    trunc(v.row_eff_date)   >  to_date('03/14/2004','MM/DD/YYYY')  OR
    trunc(v.row_term_date)  =  to_date('12/31/2999','MM/DD/YYYY')
)


If I run inline view on its own, it returns 3 records.

COL1         ROW_EFF_DAT ROW_TERM_DA
------------ ----------- -----------
10000151     01-jan-1900 13-dec-2002
10000151     13-dec-2002 23-jan-2003
10000151     23-jan-2003 31-dec-2999

If I run the entire sql, it suppose to return the following record.

COL1         ROW_EFF_DAT ROW_TERM_DA
------------ ----------- -----------
10000151     23-jan-2003 31-dec-2999

But, the query returns the following two records which is wrong.

COL1         ROW_EFF_DAT ROW_TERM_DA
------------ ----------- -----------
10000151     13-dec-2002 23-jan-2003
10000151     23-jan-2003 31-dec-2999

Did anybody experience this before. I kind of remember that it is something
to do with analytical function but I can not recall.
For the purpose of testing, when I created a table out of inner sql and
used that table in the inner query, it works fine. The DB is 8.1.7.4 on
Hp-unix v11.  I also opened a TAR and will update the status to the List.

Thanks in advance.

Best regards,
Prasad







*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is
for the exclusive use of addressee and may contain proprietary,
confidential and/or privileged information.  If you are not the intended
recipient, any use, copying, disclosure, dissemination or distribution is
strictly prohibited.  If you are not the intended recipient, please notify
the sender immediately by return e-mail, delete this communication and
destroy all copies.
*************************************************************************

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------








*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is
for the exclusive use of addressee and may contain proprietary,
confidential
and/or privileged information.  If you are not the intended recipient, any
use, copying, disclosure, dissemination or distribution is strictly
prohibited.  If you are not the intended recipient, please notify the
sender
immediately by return e-mail, delete this communication and destroy all
copies.
*************************************************************************

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------







*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for 
the exclusive use of addressee and may contain proprietary, confidential and/or 
privileged information.  If you are not the intended recipient, any use, 
copying, disclosure, dissemination or distribution is strictly prohibited.  If 
you are not the intended recipient, please notify the sender immediately by 
return e-mail, delete this communication and destroy all copies.
*************************************************************************

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: