db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "dev@xx" <...@proxiflex.fr>
Subject Re: Inserts are out of order
Date Fri, 11 Sep 2009 09:49:29 GMT
Hi,
I don't think SQL define any rule about the order of tuples in a select clause when no order
by is defined.
jyl@xx

  ----- Original Message ----- 
  From: George H 
  To: Derby Discussion 
  Sent: Friday, September 11, 2009 11:33 AM
  Subject: Inserts are out of order


  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

Mime
View raw message