db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Leroux <s...@wanadoo.fr>
Subject Re: Inserts are out of order
Date Fri, 11 Sep 2009 09:45:26 GMT
Hi George,

This is not a problem with Derby. In fact you see the normal behavior of 
an RDBMS!

The relational model is based on "sets". And "sets" are *unordered*.
http://en.wikipedia.org/wiki/Relation_(database)
http://en.wikipedia.org/wiki/Relational_model

Even if some time you may observe that data are retrieved in the same 
order you have inserted them, this is merely a coincidence. You should 
never rely on that!

The golden rule is: if you want your data in a specific order, use an 
ORDER BY clause. If not, the result order is undefined (and may even 
vary from one request to an other).


Sylvain


George H a écrit :
> Hi, I have a strange problem and I am not sure if this is a problem with 
> Insert or select or maybe I am missing something here.
> 
> Apache Derby EmbeddedDriver
> 10.5.3.0 - (802917)
> 
> I have 3 tables and as I insert IDs into 1 table and do a select on it, 
> I see the values in the same order I inserted them in.
> I do this for 2 tables. My third table is my relationship table which 
> whose primary keys are the keys of both tables together.
> 
> When I insert rows, I do not see them in the same order I inserted them 
> in. Below I provide SQL statements that show this problem. I hope 
> someone can guide me as to what is the problem and how to solve it. Many 
> thanks in advance.
> 
> CREATE TABLE POS.TEST_SECTIONS
> (
>     SECTION_ID CHAR(36) NOT NULL,
>     SECTION_NAME VARCHAR(255) NOT NULL,
>     PRIMARY KEY(SECTION_ID)   
> );
> 
> CREATE TABLE POS.TEST_ITEMS
> (
>     ITEM_ID CHAR(36) NOT NULL,
>     ITEM_NAME VARCHAR(255) NOT NULL,
>     PRIMARY KEY(ITEM_ID)
> );
> 
> CREATE TABLE POS.SECTIONS_REL_ITEMS
> (
>     SECTION_ID CHAR(36) NOT NULL,
>     ITEM_ID CHAR(36) NOT NULL,   
>     FOREIGN KEY(SECTION_ID) REFERENCES POS.TEST_SECTIONS(SECTION_ID),   
>     FOREIGN KEY(ITEM_ID) REFERENCES POS.TEST_ITEMS(ITEM_ID),
>     PRIMARY KEY(SECTION_ID,ITEM_ID)
> );
> 
> INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) 
> VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','Section 1');
> INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) 
> VALUES('4e956141-77ef-48d3-adc8-ae617f4352fb','Section 2');
> INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) 
> VALUES('2100a63e-078c-41b0-9683-1e6ad88104c9','Section 3');
> INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) 
> VALUES('76dfcf3b-7705-4933-84b6-bfee0f0f98d2','Section 4');
> INSERT INTO POS.TEST_SECTIONS(SECTION_ID, SECTION_NAME) 
> VALUES('90b94bce-4223-4f91-a917-7363672b5efd','Section 5');
> 
> INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) 
> VALUES('0784c479-7cd6-4cef-a446-f7a3724321c4','Item 1');
> INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) 
> VALUES('04bc6246-02d3-43b8-81cd-399f52bc58d8','Item 2');
> INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) 
> VALUES('f481ba35-7f49-407a-aaed-fc6213ae1b90','Item 3');
> INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) 
> VALUES('9ee99832-c23a-41e6-bb85-e7f98035717c','Item 4');
> INSERT INTO POS.TEST_ITEMS(ITEM_ID, ITEM_NAME) 
> VALUES('4e233de6-4044-4f73-9450-29833549700f','Item 5');
> 
> INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) 
> VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','0784c479-7cd6-4cef-a446-f7a3724321c4');
> INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) 
> VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','04bc6246-02d3-43b8-81cd-399f52bc58d8');
> INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) 
> VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','f481ba35-7f49-407a-aaed-fc6213ae1b90');
> INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) 
> VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','9ee99832-c23a-41e6-bb85-e7f98035717c');
> INSERT INTO POS.SECTIONS_REL_ITEMS(SECTION_ID,ITEM_ID) 
> VALUES('b6c0bdf8-68c8-4882-9723-878574fefb52','4e233de6-4044-4f73-9450-29833549700f');
> 
> select * from POS.SECTIONS_REL_ITEMS;
> 
> Result
> SECTION_ID                                           ITEM_ID
> b6c0bdf8-68c8-4882-9723-878574fefb52    04bc6246-02d3-43b8-81cd-399f52bc58d8
> b6c0bdf8-68c8-4882-9723-878574fefb52    0784c479-7cd6-4cef-a446-f7a3724321c4
> b6c0bdf8-68c8-4882-9723-878574fefb52    4e233de6-4044-4f73-9450-29833549700f
> b6c0bdf8-68c8-4882-9723-878574fefb52    9ee99832-c23a-41e6-bb85-e7f98035717c
> b6c0bdf8-68c8-4882-9723-878574fefb52    f481ba35-7f49-407a-aaed-fc6213ae1b90
> 
> The Item IDs are out of order than how I inserted them in.
> --
> George
> george.dma@gmail.com <mailto:george.dma@gmail.com>


-- 
Website: http://www.chicoree.fr



Mime
View raw message