Home » Other » General » Non-deterministic query
Non-deterministic query [message #633267] Fri, 13 February 2015 04:25 Go to next message
John Watson
Messages: 8640
Registered: January 2010
Location: Global Village
Senior Member
I've constructed a case (never mind why) where identical SQLs produce different results, depending on how a table was implemented:
drop table t1;
drop sequence s1;

create table t1 (c1 number,c2 number) partition by range (c1) interval (5) (partition p1 values less than (0));
create sequence s1;

insert into t1(c1) select rownum from dual connect by level < 10;
update t1 set c2=s1.nextval;
select * from t1 order by c1;


drop table t1;
drop sequence s1;

create table t1 (c1 number,c2 number) partition by hash(c1) partitions 2;
create sequence s1;

insert into t1(c1) select rownum from dual connect by level < 10;
update t1 set c2=s1.nextval;
select * from t1 order by c1;
The reason for the difference is obvious, but does it count as breaking the rules of SQL? That the three identical INSERT/UPDATE/SELECT statements against a table that is (as far as SQL is concerned) the same give a different result?

This is not a real-world case. I just find it worrying when Uncle Oracle appears not to play by the rules of the relational paradigm.
Re: Non-deterministic query [message #633270 is a reply to message #633267] Fri, 13 February 2015 05:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I did a quick test to see if there would be any difference if we remove the dependency on the sequence and the update by creating an identity column. Now the statements reduce to INSERT/SELECT against the table, sequence is taken care by Oracle -

drop table t1 purge;
drop sequence s1;

CREATE TABLE t1 (c1 NUMBER,c2 NUMBER GENERATED ALWAYS AS IDENTITY) PARTITION BY RANGE (c1) INTERVAL (5) (PARTITION p1 VALUES LESS THAN (0));
--create sequence s1;

INSERT INTO t1(c1) SELECT ROWNUM FROM dual CONNECT BY LEVEL < 10;
--update t1 set c2=s1.nextval;
select * from t1 order by c1;


DROP TABLE t1 PURGE;
--drop sequence s1;

CREATE TABLE t1 (c1 NUMBER,c2 NUMBER generated always as identity) PARTITION BY HASH(c1) PARTITIONS 2;
--create sequence s1;

INSERT INTO t1(c1) SELECT ROWNUM FROM dual CONNECT BY LEVEL < 10;
--update t1 set c2=s1.nextval;
select * from t1 order by c1;


No difference in this case.
Re: Non-deterministic query [message #633273 is a reply to message #633270] Fri, 13 February 2015 06:08 Go to previous messageGo to next message
John Watson
Messages: 8640
Registered: January 2010
Location: Global Village
Senior Member
Your test gives a deterministic result, because you are using the identity sequence to populate C2 at row insertion time:
orclz>
orclz> select  COLUMN_NAME,IDENTITY_COLUMN,DATA_DEFAULT from user_tab_cols where table_name='T1';

COLUMN_NAME                    IDE
------------------------------ ---
DATA_DEFAULT
--------------------------------------------------------------------------------
C1                             NO


C2                             YES
"SCOTT"."ISEQ$$_93995".nextval


orclz>
In my example, the rows get updated with different values depending on their physical ordering in the table. I don't think SQL should permit that.
Re: Non-deterministic query [message #633275 is a reply to message #633273] Fri, 13 February 2015 06:24 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Could you post the actual results of the selects?
Re: Non-deterministic query [message #633276 is a reply to message #633275] Fri, 13 February 2015 06:27 Go to previous messageGo to next message
John Watson
Messages: 8640
Registered: January 2010
Location: Global Village
Senior Member
Sorry, I should have done so. First from the range partitioned table:
orclz> select * from t1 order by c1;

        C1         C2
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9

and now the hash partitioned table:
orclz> select * from t1 order by c1;

        C1         C2
---------- ----------
         1          5
         2          1
         3          6
         4          7
         5          2
         6          3
         7          8
         8          4
         9          9

9 rows selected.

orclz>

Re: Non-deterministic query [message #633282 is a reply to message #633276] Fri, 13 February 2015 08:56 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I see - I'm pretty certain that's 100% legal and not against any rules what so ever.
Not sure if there's a specific warning in the docs saying that'll happen but various other warnings make it unavoidable.

This goes hand in hand with the fact that a select (absent an order by) can return data in any order.
Really an update is a select with a data modification instruction tacked on.

In discussions on AskTom about order by I remember seeing examples (can't find it at the moment) where he did things to the contents of a table to force it return data in a different order to the order of insertion.

You could get one of those examples, add 2 columns, update one after first inserting data, update the second after whatever is done to force the order to change. The values will be different across the 2 columns.

In fact by oracles own rules it is theoretically possible for that update to give different results when run against 2 tables that were created the same way (this is highly unlikely of course, but completely legal)

You're effectively randomly assigning values from a discreet set to a set of rows.
Re: Non-deterministic query [message #633283 is a reply to message #633282] Fri, 13 February 2015 09:15 Go to previous messageGo to next message
John Watson
Messages: 8640
Registered: January 2010
Location: Global Village
Senior Member
Ah, right. When you put it like this
Quote:
Really an update is a select with a data modification instruction tacked on.

I can live with it. The UPDATE is non-deterministic because, as we all know, a SELECT without an ORDER BY is non-deterministic.

Thank you, people. These little things that worry me show what a sad person I am Sad
Re: Non-deterministic query [message #633287 is a reply to message #633283] Fri, 13 February 2015 21:23 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
John Watson wrote on Sat, 14 February 2015 02:15
The UPDATE is non-deterministic because, as we all know, a SELECT without an ORDER BY is non-deterministic.


I agree ... kind of. A SELECT without ORDER BY is sorted non-deterministically, but the tuples themselves are deterministic. The problem is the non-deterministic sequence.NEXTVAL combined with the non-deterministic sort. Any deterministic function would have been fine.

I don't see any rules broken here.

Ross Leishman
Re: Non-deterministic query [message #633289 is a reply to message #633287] Sat, 14 February 2015 01:27 Go to previous message
Michel Cadot
Messages: 68043
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Same thing for me.

Previous Topic: Controlling session timeouts for external facing iSupplier pages
Next Topic: oracle upgradation
Goto Forum:
  


Current Time: Thu Dec 02 16:10:53 CST 2021