Re: Problem with a view: table from it contains wrong number of rows

On 17 Feb 2006 11:42:34 -0800, schonlinner_at_yahoo.com wrote:

>Hi,
>
>Our database: Oracle 10g Release 2, IBM AIX, 2 proc machine.
>
>I have the following problem and I've run out of ideas :-((
>
>Suppose I have a view:
>
>create or replace view v_200601 as (
>select a,b,c,sum(d) d from mytable group by a,b,c);
>
>Then I do:
>create table t_200601 as (select * from v_200601);
>
>And then the mystery:
>select count(*) from v_200601; ==> 274324
>select count(*) from t_200601; ==> 274278
>
>Some data is missing in the table t_200601. I can see what data is
>missing, I can find it when querying the view directly:
>select * from v_200601 where a='TXL' and b='MUC' and c='BER';
>==> 1 row returned.
>select * from t_200601 where a='TXL' and b='MUC' and c='BER';
>==> 0 rows returned.
>
>The data is *not* in table t_200601, although created from the view.
>
>Should I go to weekend, perhaps on Monday I'll see what is wrong?
>
>Can Oracle have such an apparent bug? Oracle does not crash, no alert
>in the alert.log, it just continues to work as usual...I'm quite
>anxious because this kind of statement is in our apps hundreds of times
>and potentially that bug destroyed all our data (apparently we can't
>verify data with a database which calculates wrong data...).
>
>Regards,
> Alex

Check for implicit type conversions, both in creating the view and in selecting from the view.

--
Sybrand Bakker, Senior Oracle DBA