Home » SQL & PL/SQL » SQL & PL/SQL » Inline View Bug ? (10.2.0.1.0)
Inline View Bug ? [message #388244] Tue, 24 February 2009 00:29 Go to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

We are Using Oracle 10g .

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options



It appears that Inline view gives wrong output while joining with normal table as below.


SQL> desc TEST_AR1 ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ACCOUNT_N                                          NUMBER(10)
 TYPE_V                                             VARCHAR2(1)
 NUM_V                                              VARCHAR2(19)
 AMT_N                                     NOT NULL NUMBER(14)

SQL> desc TEST_SAR1 ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ACCOUNT_N                                 NOT NULL NUMBER(10)
 NUM_V                                              VARCHAR2(20)
 AMT_N                                     NOT NULL NUMBER(14)


11:25:04 SQL> col comment1 format a10
11:25:18 SQL>  col comment2 format a50
11:25:18 SQL>  set pagesize 99
11:25:18 SQL>  Set linesize 99
11:25:20 SQL> --- test ar1 table count
11:25:26 SQL>  SELECT COUNT(*) FROM TEST_AR1 AB
11:25:27   2  ;

  COUNT(*)
----------
    645853

Elapsed: 00:00:00.00
11:25:28 SQL>
11:25:34 SQL> -- test sar1 group by count
11:25:36 SQL>  SELECT COUNT(*) FROM (
11:25:36   2   SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
11:25:36   3       FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)
11:25:36   4  ;

  COUNT(*)
----------
    645853

Elapsed: 00:00:01.00
11:25:38 SQL>
11:25:39 SQL>
11:25:39 SQL> -- select where amounts are not matching  ( with order by )
11:25:51 SQL>  SELECT TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
11:25:51   2           BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"

11:25:51   3   FROM TEST_AR1 AB ,
11:25:51   4       (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
11:25:51   5       FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
11:25:51   6   WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
11:25:51   7   AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
11:25:51   8   AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0)
11:25:51   9   ORDER BY 1, 2
11:25:53  10  ;

COMMENT1   COMMENT2
---------- --------------------------------------------------
6103804    1MIG/1185131/08 : 344212400 : 162212400
6107274    4MIG/1255076/07 : 171895600 : 159895600
6109701    F05/07/1925503 : 338949000 : 300105900
6110368    412/06/1000145920/D : 314543300 : 302543300
6112671    F01/08/2225492 : 130268600 : 43891400
6112671    F10/07/2115697 : 148826500 : 98290100
6112679    F05/08/2360334 : 1210522700 : 1154522700
6112679    F07/08/2430466 : 1798300800 : 1770300800
6113339    412/06/1000233388/D : 2533957000 : 2521957000
6113750    1MIG/1367495/08 : 147682500 : 147182500
6114008    412/06/1000252993/D : 1008067474900 : 8419536400
6115093    412/06/1000271833/D : 154203800 : 142203800
6115966    4MIG/1299352/07 : 114955800 : 89955800
6116020    F07/07/2002915 : 114125700 : 35336200
6117615    1MIG/1441960/08 : 141106400 : 97832100
6122926    412/06/1000276593/D : 267460300 : 255460300
6124414    1MIG/1475225/08 : 123901400 : 33901400

17 rows selected.

Elapsed: 00:00:04.07
11:25:59 SQL>
11:26:10 SQL>
11:26:10 SQL>
11:26:10 SQL>
11:26:10 SQL> -- select where amounts are not matching  ( without order by )
11:26:12 SQL>  select TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
11:26:12   2           BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"

11:26:12   3   from TEST_AR1 AB ,
11:26:12   4       (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
11:26:12   5       FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
11:26:12   6   WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
11:26:12   7   AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
11:26:12   8   AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0)
11:26:13   9  ;

COMMENT1   COMMENT2
---------- --------------------------------------------------
6115344    4MIG/1386168/07 : 675169000 : 605169000
6114036    1MIG/1385938/08 : 933827400 : 925827400
6128612    1MIG/1490700/08 : 388869000 : 388868900
6113923    4MIG/1376969/07 : 113101600 : 63101600
6115185    1MIG/1369621/07 : 1497945200 : 1425270500

Elapsed: 00:00:03.06
11:26:17 SQL>
11:26:35 SQL>
11:26:35 SQL> -- creating difference table for storing nums having difference in
 the amount
11:26:37 SQL>  CREATE TABLE TEST_AR_SAR_DIFF
11:26:37   2   (COMMENT1 VARCHAR2(200),
11:26:37   3    COMMENT2 VARCHAR2(200));

Table created.

Elapsed: 00:00:00.00
11:26:37 SQL>
11:26:40 SQL>
11:26:40 SQL>
11:26:45 SQL> --- inserting into test_ar_sar_diff (but 0 rows)
11:26:48 SQL>  INSERT INTO TEST_AR_SAR_DIFF
11:26:48   2   select TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
11:26:48   3           BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"

11:26:48   4   from TEST_AR1 AB ,
11:26:48   5       (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
11:26:48   6       FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
11:26:48   7   WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
11:26:48   8   AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
11:26:48   9   AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0)
11:26:48  10   ;

0 rows created.

Elapsed: 00:00:06.05
11:27:02 SQL> -- But still gives 5 records
11:27:11 SQL> select TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
11:27:13   2           BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"

11:27:13   3   from TEST_AR1 AB ,
11:27:13   4       (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
11:27:13   5       FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
11:27:13   6   WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
11:27:13   7   AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
11:27:13   8   AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0);

COMMENT1   COMMENT2
---------- --------------------------------------------------
6115344    4MIG/1386168/07 : 675169000 : 605169000
6114036    1MIG/1385938/08 : 933827400 : 925827400
6128612    1MIG/1490700/08 : 388869000 : 388868900
6113923    4MIG/1376969/07 : 113101600 : 63101600
6115185    1MIG/1369621/07 : 1497945200 : 1425270500

Elapsed: 00:00:03.03
11:27:17 SQL>
11:27:27 SQL>
11:27:27 SQL>
11:27:27 SQL> -- created table  test_sar_2 insted of inline view
11:27:29 SQL>  CREATE TABLE TEST_SAR_2 AS
11:27:29   2   SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
11:27:29   3   FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V;
 CREATE TABLE TEST_SAR_2 AS
              *
ERROR at line 1:
ORA-00955: name is already used by an existing object


Elapsed: 00:00:00.00
11:27:31 SQL> DROP TABLE TEST_SAR_2;

Table dropped.

Elapsed: 00:00:00.00
11:27:51 SQL> -- created table  test_sar_2 insted of inline view
11:27:58 SQL>  CREATE TABLE TEST_SAR_2 AS
11:27:58   2   SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
11:27:58   3   FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V;

Table created.

Elapsed: 00:00:01.09
11:28:01 SQL> select COUNT(*) from TEST_SAR_2;

  COUNT(*)
----------
    645853

Elapsed: 00:00:00.00
11:28:17 SQL>
11:28:31 SQL>
11:28:31 SQL>
11:28:31 SQL> -- replaced inser statemnt inline view with new table (inserting s
uccessfully)
11:28:33 SQL>  INSERT INTO TEST_AR_SAR_DIFF
11:28:33   2   SELECT TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
11:28:33   3           BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"

11:28:33   4   FROM TEST_AR1 AB ,
11:28:33   5        TEST_SAR_2 BB
11:28:33   6   WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
11:28:33   7   AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
11:28:33   8   AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0);

5 rows created.

Elapsed: 00:00:02.06
11:28:37 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
11:28:44 SQL>
11:28:59 SQL>
11:28:59 SQL> SELECT * FROM TEST_AR_SAR_DIFF ;

COMMENT1   COMMENT2
---------- --------------------------------------------------
6115344    4MIG/1386168/07 : 675169000 : 605169000
6114036    1MIG/1385938/08 : 933827400 : 925827400
6128612    1MIG/1490700/08 : 388869000 : 388868900
6113923    4MIG/1376969/07 : 113101600 : 63101600
6115185    1MIG/1369621/07 : 1497945200 : 1425270500

Elapsed: 00:00:00.00
11:29:09 SQL>



The Issues are like this .

1. Count of rows selected is different SELECT queries with and without ORDER BY clause.

with ORDER BY it gives 17 records
without ORDER BY it gives 05 records

2. Same SELECT query gives different Count of rows at different time. There is no back end process. Actually we recreated the table name with different fields to post here.(These queries are part of our application).

3. INSERT statement Does not insert anything with INLINE query while inserts 5 rows when we tried to insert with 'ALTERNATE new TABLE Instead of INLINE VIEW'

4. CREATE TABLE with SELECT (CTAS) works fine with inline view as below.


11:45:45 SQL> set pagesize 99
11:45:51 SQL> set linesize 99
11:45:57 SQL> col comment1 format a10
11:46:17 SQL> col comment2 format a50
11:46:25 SQL>
11:46:33 SQL> -- select where amounts are not matching  ( without order by )
11:46:36 SQL>  select TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
11:46:36   2           BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"

11:46:36   3   from TEST_AR1 AB ,
11:46:36   4       (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
11:46:36   5       FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
11:46:36   6   WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
11:46:36   7   AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
11:46:36   8   AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0)
11:46:37   9  ;

COMMENT1   COMMENT2
---------- --------------------------------------------------
6115344    4MIG/1386168/07 : 675169000 : 605169000
6114036    1MIG/1385938/08 : 933827400 : 925827400
6128612    1MIG/1490700/08 : 388869000 : 388868900
6113923    4MIG/1376969/07 : 113101600 : 63101600
6115185    1MIG/1369621/07 : 1497945200 : 1425270500

Elapsed: 00:00:03.05
11:46:41 SQL>
11:46:50 SQL>
11:47:55 SQL> --- CREATE TABLE WITH SELECT QUERY CTAS
11:48:02 SQL> CREATE TABLE TEST_AR_SAR_DIFF2 AS
11:48:10   2  select TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
11:48:12   3           BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"

11:48:12   4   from TEST_AR1 AB ,
11:48:12   5       (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
11:48:12   6       FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
11:48:12   7   WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
11:48:12   8   AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
11:48:12   9   AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0);

Table created.

Elapsed: 00:00:03.04
11:48:17 SQL> SELECT * FROM TEST_AR_SAR_DIFF2;

COMMENT1   COMMENT2
---------- --------------------------------------------------
6115344    4MIG/1386168/07 : 675169000 : 605169000
6114036    1MIG/1385938/08 : 933827400 : 925827400
6128612    1MIG/1490700/08 : 388869000 : 388868900
6113923    4MIG/1376969/07 : 113101600 : 63101600
6115185    1MIG/1369621/07 : 1497945200 : 1425270500

Elapsed: 00:00:00.00
11:48:34 SQL>



Why it happens like this? Is INLINE VIEW not recommended for bulk operation ? Or do we need to change any DB parameter for getting the exact result?

But anyway , there should not be any wrong result based on any query in Oracle. What do you think? Can we call it a Bug ?

Smile
Rajuvan.

Re: Inline View Bug ? [message #388452 is a reply to message #388244] Tue, 24 February 2009 22:04 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And see the Below SQLPLUS session. Same Select query returns different count count for same query.

Here , there is no process going on TEST_AR1 and TEST_SAR1 , as these tables are created just 10 Minutes back to post here. It is created from Actual Application tables.


09:26:37 SQL>
09:26:37 SQL> SELECT COUNT(*)
09:26:39   2  FROM TEST_AR1 AB ,
09:26:39   3      (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
09:26:39   4       FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
09:26:39   5  WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
09:26:39   6  AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
09:26:39   7  AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0);

  COUNT(*)
----------
         5

Elapsed: 00:00:03.07
09:26:44 SQL>
09:26:46 SQL>
09:26:46 SQL> SELECT COUNT(*)
09:26:49   2  FROM TEST_AR1 AB ,
09:26:49   3      (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
09:26:49   4       FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
09:26:49   5  WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
09:26:49   6  AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
09:26:49   7  AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0);

  COUNT(*)
----------
         6

Elapsed: 00:00:03.04
09:26:54 SQL>


Still I am suspicious on inline view.
Welcome any sort of response.

Smile
Rajuvan.
Re: Inline View Bug ? [message #388455 is a reply to message #388452] Tue, 24 February 2009 22:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Yes, it looks like you have probably hit a bug, perhaps 4604970 which was fixed in 10.2.0.3. Check metalink for patch and/or parameter settings for workaround.
Re: Inline View Bug ? [message #388468 is a reply to message #388244] Tue, 24 February 2009 23:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Thanks Barbara Once again,


./fa/2115/0/

I asked DBA to upgrade the Oracle version to 10.2.0.4 . I will let you know the feedback .

Smile
Rajuvan.
Re: Inline View Bug ? [message #388521 is a reply to message #388244] Wed, 25 February 2009 02:50 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

We upgraded to 10.2.0.4 Version.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Interestingly that old query was returning any query , and it is because of that bug, it returned the records.


14:01:26 SQL> SELECT TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
14:01:30   2             BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"
14:01:30   3    from TEST_AR1 AB ,
14:01:30   4         (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
14:01:30   5         FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
14:01:30   6    WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
14:01:30   7    AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
14:01:30   8    AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0);

no rows selected

Elapsed: 00:00:03.08
14:01:35 SQL>



Then we changed 5 records Manually . Then executed again . And it worked well .

14:06:01 SQL>
14:06:01 SQL>   -- AFter changing some amounts
14:06:03 SQL>   SELECT TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
14:06:03   2         BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"
14:06:03   3    from TEST_AR1 AB ,
14:06:03   4         (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
14:06:03   5         FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
14:06:03   6    WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
14:06:03   7    AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
14:06:03   8    AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0)
14:06:10   9  ;

COMMENT1   COMMENT2
---------- --------------------------------------------------
6105102    1MIG/1159976/07 : 48641400 : 48641300
6105102    1MIG/1159978/08 : 19373000 : 1934000
6105102    1MIG/1159979/08 : 27061900 : 27061850
6105102    1MIG/1159977/07 : 41407200 : 414067200
6105102    1MIG/1159980/07 : 55578000 : 45578000

Elapsed: 00:00:03.05
14:06:15 SQL>
14:06:24 SQL> -- Inserting Again
14:06:25 SQL>   INSERT INTO TEST_AR_SAR_DIFF
14:06:26   2    SELECT TO_CHAR(AB.ACCOUNT_N) "COMMENT1" ,
14:06:26   3           BB.NUM_V ||' : '||AB.AMT_N  ||' : '||BB.AMT_N "COMMENT2"
14:06:26   4    from TEST_AR1 AB ,
14:06:26   5         (SELECT B.ACCOUNT_N, B.NUM_V,SUM(B.AMT_N) AMT_N
14:06:26   6         FROM TEST_SAR1 B GROUP BY B.ACCOUNT_N, B.NUM_V)BB
14:06:26   7    WHERE AB.ACCOUNT_N = BB.ACCOUNT_N
14:06:26   8    AND  AB.TYPE_V||AB.NUM_V = BB.NUM_V
14:06:26   9    AND NVL(AB.AMT_N,0) <> NVL(BB.AMT_N,0);

5 rows created.

Elapsed: 00:00:06.75
14:06:34 SQL>
14:06:50 SQL> SELECT * FROM TEST_AR_SAR_DIFF;

COMMENT1   COMMENT2
---------- --------------------------------------------------
6105102    1MIG/1159976/07 : 48641400 : 48641300
6105102    1MIG/1159978/08 : 19373000 : 1934000
6105102    1MIG/1159979/08 : 27061900 : 27061850
6105102    1MIG/1159977/07 : 41407200 : 414067200
6105102    1MIG/1159980/07 : 55578000 : 45578000

Elapsed: 00:00:00.00
14:07:07 SQL>  



Thanks Orafaq Once again.

Smile
Rajuvan.




Re: Inline View Bug ? [message #388540 is a reply to message #388521] Wed, 25 February 2009 05:08 Go to previous message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
@Rajuvan,
Thanks a lot to all for sharing your thoughts.


Regards,
Hammer
Previous Topic: return xml in sys_refcursor
Next Topic: please help?
Goto Forum:
  


Current Time: Sun Dec 11 02:27:21 CST 2016

Total time taken to generate the page: 0.07418 seconds