[jira] Created: (DERBY-4759) incoherence on IDENTITY_VAL_LOCAL after INSERT via SELECT

[RobertoC (JIRA)]

incoherence on IDENTITY_VAL_LOCAL after INSERT via SELECT
---------------------------------------------------------

                 Key: DERBY-4759
                 URL: https://issues.apache.org/jira/browse/DERBY-4759
             Project: Derby
          Issue Type: Bug
          Components: JDBC
    Affects Versions: 10.6.1.0
         Environment: Win XP - JDK 1.6.0_20
            Reporter: RobertoC
            Priority: Blocker


wrong IDENTITY_VAL_LOCAL() after "SELECT ... INSERT" (see below example)

-- replicable on derby 10.6.1000000.938214

CREATE TABLE email2folder (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY NOT NULL,
    idemail bigint NOT NULL,
    idfolder integer NOT NULL
);


INSERT INTO email2folder(idemail,idfolder) VALUES(10,20);
INSERT INTO email2folder(idemail,idfolder) VALUES(30,40);

-- WARN: disconnect from DB and reconnect

insert into email2folder(idemail,idfolder) 
  select idemail,idfolder from email2folder where id<2;
-- expected: 1 row inserted
-- obtained: 1 row inserted (OK)
  
VALUES IDENTITY_VAL_LOCAL();
-- expected result: 3
-- obtained result: NULL  (ERROR!)
-- note:  if a previous "standard INSERT" is executed before INSERT via SELECT, 
the IDENTITY_VAL report the previous 
-- generated PK, possible producing data-incoherence in the application 
execution



-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.