Home » SQL & PL/SQL » SQL & PL/SQL » Terrifying result of sql-query, need explanation.
Terrifying result of sql-query, need explanation. [message #608274] Tue, 18 February 2014 05:33 Go to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
Hello!
I have some table csp_psv with the list of the fields.
When i'm trying to get the data with the query
select t.*
   from csp_psv t
   where psv_raion_id is null or psv_raion_id = 1;

i get 1165 rows. When i'm trying to get the count of the rows with query
select count(1)
   from csp_psv t
   where psv_raion_id is null or psv_raion_id = 1;

the result is 399.
The query
select *
   from csp_psv t
   where psv_raion_id is null;

returns 399 rows, the query
select *
   from csp_psv t
   where psv_raion_id = 1;

returns 0 rows.
What's going on? What is the possible reason of this?
Thank you.
----
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608275 is a reply to message #608274] Tue, 18 February 2014 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It seems you reach a bug.
What does return
select * from csp_psv t where nvl(psv_raion_id,1) = 1;
select count(*) from csp_psv t where nvl(psv_raion_id,1) = 1;

Post execution plan for your queries.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608279 is a reply to message #608275] Tue, 18 February 2014 05:57 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
 
select * from csp_psv t where nvl(psv_raion_id,1) = 1;
----------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS			48088	6896	2751504
 TABLE ACCESS FULL	           CROSSV	CSP_PSV	48088	6896	2751504

returns 1165 rows.
select count(*) from csp_psv t where nvl(psv_raion_id,1) = 1;
----------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS			48088	1	4
 SORT AGGREGATE				                        1	4
  TABLE ACCESS FULL	          CROSSV	CSP_PSV	48088	6896	27584

returns 399 rows.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608280 is a reply to message #608279] Tue, 18 February 2014 06:00 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
The plans of the previous queries.
select t.*
   from csp_psv t
   where psv_raion_id is null or psv_raion_id = 1;
----------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS			48088	6896	2751504
 TABLE ACCESS FULL	          CROSSV	CSP_PSV	48088	6896	2751504



select t.*
   from csp_psv t
   where psv_raion_id is null;
----------------------------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS			48080	947	377853
 TABLE ACCESS FULL	          CROSSV	CSP_PSV	48080	947	377853

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608281 is a reply to message #608279] Tue, 18 February 2014 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How did you get the execution plans? Useful part of them are missing. Use dbms_xplan.
Anyway, it seems you reached a bug.
Is "csp_psv" a standard (heap) table?

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608282 is a reply to message #608281] Tue, 18 February 2014 06:16 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
I've created it with PL/SQL Explain plan.
the dbms_xplan result is
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  f60tdam2th14f, child number 0
-------------------------------------
select /*qqq1*/ * from csp_psv t where nvl(psv_raion_id,1) = 1
Plan hash value: 3015530264
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |       |       | 48088 (100)|          |
|*  1 |  TABLE ACCESS FULL| CSP_PSV |  6896 |  2687K| 48088   (1)| 00:42:17 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NVL("PSV_RAION_ID",1)=1)

and
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  gqkzytjph86v6, child number 0
-------------------------------------
select count(*) /*qqq2*/ from csp_psv t where nvl(psv_raion_id,1) = 1
Plan hash value: 1381376156
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       | 48088 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| CSP_PSV |  6896 | 27584 | 48088   (1)| 00:42:17 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(NVL("PSV_RAION_ID",1)=1)

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608283 is a reply to message #608282] Tue, 18 February 2014 06:20 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
And csp_psv is a heap table.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608284 is a reply to message #608282] Tue, 18 February 2014 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What are the indexes on the table? Specify if it is unique or not.

[Updated on: Tue, 18 February 2014 06:21]

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608285 is a reply to message #608284] Tue, 18 February 2014 06:29 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
There are some indexes on this table:
IDX_CSP_PSV_ERS_ID	        NORMAL	NONUNIQUE	VALID
IDX_CSP_PSV_ERS_ID_011013	NORMAL	NONUNIQUE	VALID
IDX_CSP_PSV_NUM_INC_ORG	        NORMAL	NONUNIQUE	VALID
IFK_CSP_PSV_CSD_OPFU	        NORMAL	NONUNIQUE	VALID
IFK_CSP_PSV_CSD_PSV_STAT	NORMAL	NONUNIQUE	VALID
IFK_CSP_PSV_CSP_LOG_LOAD	NORMAL	NONUNIQUE	VALID
IFK_CSP_PSV_CSP_LOG_PACKS	NORMAL	NONUNIQUE	VALID
IFK_CSP_PSV_OLD_PSV_ID	        NORMAL	NONUNIQUE	VALID
PK_CSP_PSV	                NORMAL	UNIQUE	        VALID

i've rebuilt all of them but result was the same.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608286 is a reply to message #608285] Tue, 18 February 2014 06:43 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If

select t.*
   from csp_psv t
   where psv_raion_id is null or psv_raion_id = 1;


gets 1165 rows, I would be curious what the result of this count is:

select count(*) from (
  select t.*
    from csp_psv t
    where psv_raion_id is null or psv_raion_id = 1
);


Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608287 is a reply to message #608286] Tue, 18 February 2014 06:48 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
I've got 399 rows. It's funny, isn't it?
I created a new table via 
create table csp_psv_2 as select * from csp_psv where 1=0;

Then i inserted all the rows using 
insert into csp_psv_2 select * from csp_psv;
and tried select all rows from csp_psv_2.
The result was 399 rows in list, not 1165 as select from csp_psv.

[Updated on: Tue, 18 February 2014 06:50]

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608288 is a reply to message #608287] Tue, 18 February 2014 06:55 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi,

Please can you tell me the result of queries : 


select   * 
from     csp_psv t 
where    nvl(trim(psv_raion_id),1) = 1;

select   count(1) 
from     csp_psv t 
where    nvl(trim(psv_raion_id),1) = 1;

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608291 is a reply to message #608288] Tue, 18 February 2014 07:21 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
It's 1165 for the first and 399 for the second one.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608292 is a reply to message #608291] Tue, 18 February 2014 07:36 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
If I understand this correctly, the number of rows returned is dependent on which columns you project. This can be an effect of row level security (or VPD). I cannot remember the detail of the 10.2 VPD capabiities, but certainly in 11.x it is possible for filters to be applied depending on this. Are there any VPD policies on that table?

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608293 is a reply to message #608291] Tue, 18 February 2014 07:48 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi Cott,

Please can you show the screenshot for the same data count in 2 different scenarios?

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608295 is a reply to message #608293] Tue, 18 February 2014 08:09 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
msol25
yes, sure. here it is.
/forum/fa/11708/0/

John Watson
thx going to read about this.
  • Attachment: oracle.png
    (Size: 172.52KB, Downloaded 1054 times)

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608300 is a reply to message #608295] Tue, 18 February 2014 08:21 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What I still wonder is whether the bug is in the server or the client.

- How many rows do you get when you don't select * but only select psv_id in the right scenario.
- Does SQL*Plus give the same results as the GUI client?

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608301 is a reply to message #608300] Tue, 18 February 2014 08:38 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
ThomasG
1. "select *" query returns 1065 rows. Even if i use a full list of columns instead of "*" the result is 1065. But when i select just "psv_id" the result is 399.
P.S. When the query is "select PSV_ID,ERS_ID",IAN_IC from ..." i've got 495 rows.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608305 is a reply to message #608301] Tue, 18 February 2014 08:44 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
ThomasG
2. Query 
select ian_name1 from csp_psv t where nvl(trim(psv_raion_id),1) = 1;

returns 540 rows via PL/SQL and via SQL*PLUS.

[Updated on: Tue, 18 February 2014 08:45]

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608306 is a reply to message #608301] Tue, 18 February 2014 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
do the results change after issuing the SELECT from brand new session?

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608308 is a reply to message #608306] Tue, 18 February 2014 08:54 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
BlackSwan
The results of the query are the same in the different sessions.
But it changes when i use in select different columns.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608311 is a reply to message #608308] Tue, 18 February 2014 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from SQL below

select owner, object_type from dba_objects where object_name = 'CSP_PSV';

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608312 is a reply to message #608311] Tue, 18 February 2014 09:14 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
CROSSV TABLE

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608390 is a reply to message #608312] Wed, 19 February 2014 05:44 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Stale tolerated MV query rewrite?

[Updated on: Wed, 19 February 2014 05:44]

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608405 is a reply to message #608295] Wed, 19 February 2014 07:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
What gui tool are you using. Just as a sanity check execute same queries in SQL*Plus.

SY.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608406 is a reply to message #608405] Wed, 19 February 2014 07:53 Go to previous messageGo to next message
Cott
Messages: 14
Registered: February 2014
Junior Member
i've written earlier that the results were the same via PL/SQL and SQL*PLUS.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608407 is a reply to message #608406] Wed, 19 February 2014 08:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
What datatype is psv_raion_id?

SY.

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608450 is a reply to message #608390] Wed, 19 February 2014 19:40 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Roachcoach wrote on Wed, 19 February 2014 22:44
Stale tolerated MV query rewrite?


Wouldn't a query rewrite show up in the Explain Plan?
Ross Leishman

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608451 is a reply to message #608450] Wed, 19 February 2014 19:45 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Can you get a ROWID added to one of the spurious queries?

select ian_name1, ROWID
from csp_psv t 
where nvl(trim(psv_raion_id),1) = 1;


Does it still return 540 rows?
Are all ROWIDs distinct?

This is probably all a bit academic and for our amusement. I reckon you have a corruption.
Ross Leishman

Report message to a moderator

Re: Terrifying result of sql-query, need explanation. [message #608466 is a reply to message #608450] Thu, 20 February 2014 03:03 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
rleishman wrote on Thu, 20 February 2014 01:40
Roachcoach wrote on Wed, 19 February 2014 22:44
Stale tolerated MV query rewrite?


Wouldn't a query rewrite show up in the Explain Plan?
Ross Leishman


I'd have thought so (though it's been a while since I looked) but the only explain plans for the actual problem queries were generated via a GUI and

a) I don't trust GUIs
b) I can't think of anything else remotely plausible because it's too simple a query on the surface to be a bug (imho)

Report message to a moderator

Previous Topic: Unique/check constraint on two columns
Next Topic: Procedure.,
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 09:46:50 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.