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
[email protected] <mailto:[email protected]>
--
Website: http://www.chicoree.fr
