CREATE TABLE t1 (
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
SELECT Col1 AS AfterInsert FROM t1;
UPDATE t1 SET Col1 = Col1 * 10;
SELECT Col1 AS AfterUpdate FROM t1;
SELECT Col1 AS AfterRollback FROM t1;
Running this query in Teradata SQL Assistant creates two results panes and highlights the ROLLBACK. The message at the bottom states, "ROLLBACK Failed. 3514: User-generated transaction ABORT."
Running this same query in Nexus creates three results panes.
Nexus adds a tab here with no results. It appears to correspond to the ROLLBACK.
Neither returns results for the "AfterRollback" query.
Running that query after running the whole script returns the expected (rolled back) results.
There is clearly more to understand here. I will update this post as I learn more.
Update 1 (2011-01-07)
Our system uses Teradata transaction semantics. Look here for a quick explanation of transaction semantics.
To see which mode you are running in a session, use HELP SESSION and look for the column "Transaction Semantics".
(Many thanks to my friend and colleague Joshua Skarphol for the details.)