OraFAQ Forum: SQL & PL/SQL » Mysterious behaviour of Oracle SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Mysterious behaviour of Oracle SQL (oracle 10g unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Mysterious behaviour of Oracle SQL [message #599014]

Mon, 21 October 2013 07:40

bowie1304
Messages: 6
Registered: October 2013
Location: Germany

Junior Member

Hallo All,
Perhaps you may have an Idea what is happening here.
The following sql statemenst are delivering different results:

select * from TABLE1
where nvl(value1,0) = 12485.83
and VALUE2 = 'CN'
-- and VALUE3 = '000000123456'
;

select * from TABLE1
where nvl(value1,0) = 12485.83
and VALUE2 = 'CN'
and VALUE3 = '000000123456'
;

we are expecting the same values in all fields defined for a record depening on the criteria, but what a surprise. In some fields, not all, there are values which are belong to values out of a record belonging to another Value3 defined select.
This problem is reproducable. We don't have any Idea what is happening here. The table is in the working area and compressed, maybe this is a problem but we don't know. Is there anybody else who has had a similar event.
Please help.

Kind regards
Willi

[Updated on: Mon, 21 October 2013 07:41]

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599021 is a reply to message #599014]

Mon, 21 October 2013 07:52

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Can you post a test case where we can reprduce the issue on our systems?

To the casual observer it looks like a data issue in your tables being as the two queries have different where clauses. That is to say the issue is your data doesnt meet your business logic requirement rather than an actual oracle issue.

However, stranger things have happened and a test case will let us help Smile

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599025 is a reply to message #599021]

Mon, 21 October 2013 08:18

bowie1304
Messages: 6
Registered: October 2013
Location: Germany

Junior Member

Testcase is quit difficult because the data are customer data.
I will describe the testcase.

We have one table TABLE1 with 157 fields with diffent field definitions, where as VALUE1 is a numeric field defined as Number(16,2), value2 is defined as NVARCHAR2(2) and value3 is defined as NVARCHAR2(12).

Both SQL's I mentioned down below are running against the same table with the same values as described below and we discovered a diffence in one field at nearly the end of the record, this field XXXXX is defined as Number(16,2).

Only one record is displaying the value on Value1. The result of the select is correct just till the field XXXX where the value of the field is just different. In the first SQL-result is displaying Null and the result of the second SQL is displaying a value 17387.89. This is not expected because it's the value of VALUE1 from another VALUE3 record.

The two queries down below are only quilty for only one record, but with the viewn diffs we could not trust the data anymore.

I used TOAD and SQL-plus on UNIX to verify that there is no problem with TOAD. But the direct way did show the same result.

I hope this helps.

Kind regards

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599028 is a reply to message #599025]

Mon, 21 October 2013 08:27

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I may be reading ti wrong, but it still sounds like a non-oracle issue, that you have a logical issue with the application layer.

Can you replicate the issue with anonymized test data?

It doesnt sound like there's any issue with the select statement logic above but the data in the table itself which is something of a different question.

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599032 is a reply to message #599028]

Mon, 21 October 2013 09:10

bowie1304
Messages: 6
Registered: October 2013
Location: Germany

Junior Member

Our Problem is that we don't know when this symptom occurs so it is quit difficult to reproduce the problem. I think we are not talking about different things.
The problem is which data to trust. One thing could be checked if there are any other records which show the same behaviour.

The Table1 is just a copy from another one which I checked by the moment. The other one did have the same definitions as the one showing the result, but did show the expected result from both SQL's the mentioned Field XXXXX is empty as expected.

Seeing this you may be right that something happens while copying the data from one to the other table.

But only one place in our sourcecode is filling up this field and this is a direct pic from the source table whereas other fields are recalculated.

Mystirious is also that toads schemabrowser did show the field XXXXX as Null in the source table with both criteria included.

The target table is compressed, the sourcetable not, may this be the problem, because after copying all data from source to target everything is fine but than we are compressing the target data table and using it in followup processing with using update statements for the target table and than the data are changing as mentioned.

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599034 is a reply to message #599032]

Mon, 21 October 2013 11:28

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Until you can supply us with a reliable test case we'll all be fumbling around in the dark, I'm afraid.

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599036 is a reply to message #599034]

Mon, 21 October 2013 11:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>This problem is reproducable.
>so it is quit difficult to reproduce the problem.

please make up our mind

a test case is required to properly debug this mystery.

please post full results of SQL below

SELECT * FROM V$VERSION;

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599062 is a reply to message #599036]

Tue, 22 October 2013 00:58

bowie1304
Messages: 6
Registered: October 2013
Location: Germany

Junior Member

@Blackswan
Hallo
I wrote reproducable a bit missleading. It is and is not, because the phenomen is not present in all cases.
It is reproduceable in the facts that we did not have only one record where this phenomen apears, but we can't say when it will happen.
My colleague and I do have one idea.
As I noted before and we just tested this.
The source table is uncompressed and did not show the result for both Select SQL's. In both results the field we are having this problem is showing NULL.
The target table will be compressed after the Data from Source table are successfully copied to the target table. At this state of process no differences in the result of the select SQL are visible. We first used the uncompressed target table to verify and than the compressed table to verify again. The result was the same. Both results showing NULL in the expected fields.

Now we do a processing on the table with inserts and updates on an unknown number of records, not all only a part of. Where there was an update or an insert of a record all looks fine, we do no compressing on updated and inserted records. After processing is finished we realized the phenomen in Several records where no update of the record took place.

I hope this clarifies a bit this phenomen.

What do you need as testcase, because this data are confidential. So my only chance is that you tell me what you need as testcase and I will try to present it in a reproducable way.

SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
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 HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

Is what you are asking for

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599073 is a reply to message #599062]

Tue, 22 October 2013 01:37

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

bowie1304 wrote on Tue, 22 October 2013 06:58

What do you need as testcase, because this data are confidential. So my only chance is that you tell me what you need as testcase and I will try to present it in a reproducable way.

DDL for tables which can have masked data inserted into and those insert statements.

We dont need the exact data you have - but a sample set of obfuscated or unrelated data which returns the same problems when you run code against it.

I assume the column names aren't confidential, but even if they are mask those too.

So if in the real world you have:

create table foo
(secret1 varchar2(50)
,secret2 number);

Just alter it to

create table nofoo
(notsecret1 varchar2(50)
,notsecret2 number);

Or something like that. The principles same with the data.

We dont need the real data, we need dummy data that manifests the issue in the same way - that way we can properly help.

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599075 is a reply to message #599073]

Tue, 22 October 2013 01:47

bowie1304
Messages: 6
Registered: October 2013
Location: Germany

Junior Member

Ok, that's a way I could do it, but this will take a while. I will present you than the source table sql, target table SQL a kind of dataset for source table. The processing is written down in my last post.

I also will present you data out of the target table which shows the phenomen.

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599081 is a reply to message #599075]

Tue, 22 October 2013 02:14

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Remember to include what you EXPECT as well as what you're actually getting.

Edit: It doesnt need to be many rows - just use a minimum required. Usually people by with about 10 or so.

[Updated on: Tue, 22 October 2013 02:15]

Report message to a moderator

Re: Mysterious behaviour of Oracle SQL [message #599242 is a reply to message #599036]

Wed, 23 October 2013 03:48

bowie1304
Messages: 6
Registered: October 2013
Location: Germany

Junior Member

Hi All,
Thanks for your assistance at all. The problem has been solved, and it could be reproduced.
The Data we are looking at, where placed in a compressed working table with an index on it.
We analyzed our processing and found that the data where "corruped" just in the moment where there are insert and/or update statements, mostly updates.
After this update process step the data did show the phenomen. Google and reading oracle documentation brings up that in 10G, compressed tables with index on it may not work properly.
We set up our testenvironement with a non compressed table and monitored the process from start to beginning.
With uncompressed table all data are now ok.
It was my first Idea and it was right.
Thanks all.

[Updated on: Wed, 23 October 2013 03:50]

Report message to a moderator

Previous Topic:	ORA-00913: too many values
Next Topic:	What is Stored Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 10:24:29 CDT 2024