Update: This question is still OPEN because @@IDENTITY is returning the WRONG VALUE... if that is expected behavior, it is NOT EXPECTED by Deltas or Gammas, probably not even by Betas; i.e., it is NOT EXPECTED by ordinary folk... so, at the very least it should be explained and documented.

Tests show that the previous @@IDENTITY value is returned to SELECT @@IDENTITY when there is an INSERT in the FROM clause that causes a DEFAULT AUTOINCREMENT column to be incremented.

This behavior feels like either (a) expected but undocumented and therefore surprising behavior, or (b) a feature-not-yet-considered (surely not a bug!)

(I am HOPING for Door Number Three: I'm missing something obvious)

Here's a demonstration that was run twice in a row on both 12.0.1.3298 and 16.0.0.1512, so the "previous @@IDENTITY value" starts out as 3 for the second test:

BEGIN
   DROP TABLE t;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t (
   pkey  INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data  VARCHAR ( 10 ) );

SELECT @@IDENTITY AS "identity1a WRONG",
       old.*
  FROM ( INSERT t ( data ) VALUES ( 'Hello' ) ) REFERENCING ( FINAL AS old );

SELECT @@IDENTITY AS "identity1b CORRECT",
       t.*
  FROM t
 ORDER BY pkey;

SELECT @@IDENTITY AS "identity2a WRONG",
       old.*
  FROM ( INSERT t ( data ) VALUES ( 'World' ) ) REFERENCING ( FINAL AS old );

SELECT @@IDENTITY AS "identity2b CORRECT",
       t.*
  FROM t
 ORDER BY pkey;

SELECT @@IDENTITY AS "identity3a WRONG",
       old.*
  FROM ( INSERT t ( data ) VALUES ( 'World' ) ) REFERENCING ( FINAL AS old );

SELECT @@IDENTITY AS "identity3b CORRECT",
       t.*
  FROM t
 ORDER BY pkey;

identity1a WRONG        pkey data       
-------------------- ----------- ---------- 
                   3           1 Hello      
(1 rows)

identity1b CORRECT        pkey data       
-------------------- ----------- ---------- 
                   1           1 Hello      
(1 rows)

identity2a WRONG        pkey data       
-------------------- ----------- ---------- 
                   1           2 World      
(1 rows)

identity2b CORRECT        pkey data       
-------------------- ----------- ---------- 
                   2           1 Hello      
                   2           2 World      
(2 rows)

identity3a WRONG        pkey data       
-------------------- ----------- ---------- 
                   2           3 World      
(1 rows)
Execution time: 0.525 seconds
Execution time: 0.005 seconds
Execution time: 0.036 seconds
Execution time: 0.03 seconds
Execution time: 0.008 seconds
Execution time: 0.015 seconds

identity3b CORRECT        pkey data       
-------------------- ----------- ---------- 
                   3           1 Hello      
                   3           2 World      
                   3           3 World      
(3 rows)
Execution time: 0.004 seconds
Execution time: 0.014 seconds