Let's play "Gotcha!" - New Season

Welcome to a new season of "Gotcha!", the self-testing quiz game for developers who work with SQL Anywhere.

No warmup question this time, and the rules have changed...

• You will be shown one example and asked two skill-testing questions about it:
  
  1. What did the author want this code to do?
     
  2. What was the "Gotcha!" moment? In other words, what did the code actually do?
     

• For bonus points,
  
  3. Why?
     

One other rule is still the same, though...

No computers!

You have to answer all the questions without looking anything up, and without running any actual tests. This is a test of your SQL Anywhere knowledge, not your prowess at Googling the docs.

Memo to the Alphas: Question 1 is intended to bell curve you folks out of competition. Not the Deltas or the Gammas... even you Gammas will get Question 1 right if you work at it. But you Alphas and Betas don't see how Questions 1 and 2 can possibly have different answers, do you? "That's what the code does, how could the author expect anything else?"... it's why you guys never score 100% on exams, you litigate the questions :)

OK, here we go!

Question 1: What did the author want this code to do?

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   COMMIT;
   SELECT 'Hello, world' AS c;
END;

BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;

Take your time, think about it...



OK, here's the answer, here's what the author wanted to see from the SELECT * FROM t:

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   COMMIT;
   SELECT 'Hello, world' AS c;
END;

BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;

c
'Hello, world'

Question 2: What was the "Gotcha!" moment?

In other words, what did the code actually do?

Let's have another look at the code...

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   COMMIT;
   SELECT 'Hello, world' AS c;
END;

BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;

Think about it...

Gotcha!

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   COMMIT;
   SELECT 'Hello, world' AS c;
END;

BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;

Could not execute statement.
COMMIT/ROLLBACK not allowed within atomic operation
SQLCODE=-267, ODBC 3 State="42000"

For bonus points: Why?

Hint: What you see is what you get... there are no triggers involved. Heck, there are no real tables involved at all... the code you see here was the only thing run on a brand-new SQL Anywhere 12.0.1 database.

And just in case you're jonesing for the docs, here's another hint: By default BEGIN blocks are not atomic, which means neither are procedure calls.

Plus, it doesn't help if table t is declared NOT TRANSACTIONAL. To drive those points home, this code gives the same Gotcha! result:

CREATE PROCEDURE p()
BEGIN NOT ATOMIC
   COMMIT;
   SELECT 'Hello, world' AS c;
END;

BEGIN NOT ATOMIC
   DECLARE LOCAL TEMPORARY TABLE t ( c  LONG VARCHAR ) NOT TRANSACTIONAL;
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;

Could not execute statement.
COMMIT/ROLLBACK not allowed within atomic operation
SQLCODE=-267, ODBC 3 State="42000"

Stumped? Take your time...



You want another hint?

OK: If you take out the COMMIT, it does what the author wanted it to do...

CREATE PROCEDURE p() RESULT ( c VARCHAR ( 100 ) )
BEGIN
   SELECT 'Hello, world' AS c;
END;

BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c VARCHAR ( 100 ) );
   INSERT t SELECT p.c FROM p();
   SELECT * FROM t;
END;

c
'Hello, world'

And no, it's not a bug, it's a feature! ...it's supposed to work this way!

Time's Up!

Thank you for playing "Gotcha!"

See you next time, good night everyone!

---