Re: Strange behavior of subquery

From: <fitzjarrell_at_cox.net>
Date: Mon, 25 Feb 2008 05:40:04 -0800 (PST)
Message-ID: <777e1ec7-ed5e-4fe1-b742-a8edccd2b8b4@j28g2000hsj.googlegroups.com>

On Feb 25, 2:50 am, Hans Mayr <mayr1..._at_gmx.de> wrote:
> Hello,
>
> Thanks for your reply. Actually I was wrong, I do not work on Oracle
> 9. Here is the entry of v$version:
>
> Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
> Production
>
> I hope that you or someone else will be able to help me with this
> mystery.
>
> Best,
>
> Hans

And I'm not seeing any such behaviour on 10.2.0.3:

SQL> create or replace view v_base
  2 as select * from user_objects;

View created.

SQL>
SQL> with w1 as (

  2  	     select object_name, created, last_ddl_time, timestamp
  3  	     from v_base

  4 )
  5 select * from w1;

OBJECT_NAME CREATED LAST_DDL_ TIMESTAMP

---------------- --------- --------- -------------------
CTRTEST          15-FEB-08 15-FEB-08 2008-02-15:14:29:38
HIYABABE         15-FEB-08 15-FEB-08 2008-02-15:14:43:10
TABLE2           20-FEB-08 20-FEB-08 2008-02-20:14:43:14
ABC              13-FEB-08 13-FEB-08 2008-02-13:08:19:23
ABC_IND          13-FEB-08 13-FEB-08 2008-02-13:08:20:18
ARTICLE          24-FEB-08 24-FEB-08 2008-02-24:16:12:31
EMP              12-FEB-08 12-FEB-08 2008-02-12:08:10:12
DEPT             12-FEB-08 12-FEB-08 2008-02-12:08:10:12
BONUS            12-FEB-08 12-FEB-08 2008-02-12:08:10:12
SALGRADE         12-FEB-08 12-FEB-08 2008-02-12:08:10:12
DUMMY            12-FEB-08 12-FEB-08 2008-02-12:08:10:13

OBJECT_NAME      CREATED   LAST_DDL_ TIMESTAMP
---------------- --------- --------- -------------------
LIGNECOMMANDE    24-FEB-08 24-FEB-08 2008-02-24:16:12:31
V_BASE           25-FEB-08 25-FEB-08 2008-02-25:07:29:20
T                19-FEB-08 19-FEB-08 2008-02-19:15:59:21
EXCEP_TAB        21-FEB-08 21-FEB-08 2008-02-21:09:27:56
TAB1             21-FEB-08 21-FEB-08 2008-02-21:09:27:56
TAB2             21-FEB-08 21-FEB-08 2008-02-21:09:27:56

17 rows selected.

SQL>
SQL> with w1 as (

  2  	     select object_name, created, last_ddl_time, timestamp
  3  	     from v_base

  4 ),
  5 w2 as (

  6  	     select object_name, created, last_ddl_time, timestamp
  7  	     from w1

  8 ),
  9 w3 as (

 10  	     select object_name, created, last_ddl_time, timestamp
 11  	     from w1

 12 )
 13 select * from w1
 14 union all
 15 select * from w2 where 0=1
 16 union all
 17 select * from w3 where 0=1;

OBJECT_NAME CREATED LAST_DDL_ TIMESTAMP

---------------- --------- --------- -------------------
CTRTEST          15-FEB-08 15-FEB-08 2008-02-15:14:29:38
HIYABABE         15-FEB-08 15-FEB-08 2008-02-15:14:43:10
TABLE2           20-FEB-08 20-FEB-08 2008-02-20:14:43:14
ABC              13-FEB-08 13-FEB-08 2008-02-13:08:19:23
ABC_IND          13-FEB-08 13-FEB-08 2008-02-13:08:20:18
ARTICLE          24-FEB-08 24-FEB-08 2008-02-24:16:12:31
EMP              12-FEB-08 12-FEB-08 2008-02-12:08:10:12
DEPT             12-FEB-08 12-FEB-08 2008-02-12:08:10:12
BONUS            12-FEB-08 12-FEB-08 2008-02-12:08:10:12
SALGRADE         12-FEB-08 12-FEB-08 2008-02-12:08:10:12
DUMMY            12-FEB-08 12-FEB-08 2008-02-12:08:10:13

OBJECT_NAME      CREATED   LAST_DDL_ TIMESTAMP
---------------- --------- --------- -------------------
LIGNECOMMANDE    24-FEB-08 24-FEB-08 2008-02-24:16:12:31
V_BASE           25-FEB-08 25-FEB-08 2008-02-25:07:29:20
T                19-FEB-08 19-FEB-08 2008-02-19:15:59:21
EXCEP_TAB        21-FEB-08 21-FEB-08 2008-02-21:09:27:56
TAB1             21-FEB-08 21-FEB-08 2008-02-21:09:27:56
TAB2             21-FEB-08 21-FEB-08 2008-02-21:09:27:56

17 rows selected.

SQL> Please post the results you're seeing so we can understand this problem.

David Fitzjarrell Received on Mon Feb 25 2008 - 07:40:04 CST