Strange behavior of subquery

From: Hans Mayr <mayr1972_at_gmx.de>
Date: Fri, 22 Feb 2008 07:45:16 -0800 (PST)
Message-ID: <cb6e946d-32af-41d0-9668-7fb7258d56a5@h25g2000hsf.googlegroups.com>

Hello,

I have a view V_BASE. Now I created a query with the following structure:

WITH w1 AS ( SELECT ..... V_BASE ....)
SELECT * FROM w1

Everything works fine.

Now I changed to

WITH w1 AS ( SELECT ..... V_BASE ....),
   w2 AS (SELECT ... w1 ...),
   w3 AS (SELECT ... w1...)

SELECT * FROM w1
UNION ALL
SELECT * FROM w2 WHERE 1=0
UNION ALL
SELECT * FROM w3 WHERE 1=0

And suddenly I get a different result, one date column in w1 changed its values by exactly one month. The "WHERE 1=0" is just to turn off w2 and w3, I will it on again once that I fixed the error. Has anybody any idea how or why w2 and w3 could influence the result of w1? I work on Oracle 9. I checked the structure carefully using http://www.sqlinform.com/ to make sure that the problem is not due to a wrong bracket or something like that. Or could w1, w2 and w3 even influence the results of V_BASE?

Thanks and Best,

Hans Received on Fri Feb 22 2008 - 09:45:16 CST