Let's play Gotcha! 2012 Edition, Round 1

Welcome to the first round of the 2012 Edition of "Gotcha!", the self-testing quiz game for SQL Anywhere developers.

Let's get started right away...

You know all about DEFAULT AUTOINCREMENT, right?

Here's a warm-up question:

---

What does t contain after the following INSERT?

CREATE TABLE t ( auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t VALUES ( DEFAULT ); 

---

Answer: It contains 1! (you knew that, right?)

SELECT * FROM t ORDER BY auto;

auto
1

Here's another warm-up question:

---

SQL Anywhere 12 stores the most recent DEFAULT AUTOINCREMENT value for each column in SYSTABCOL.max_identity.

What is that value for t.auto?

---

Answer: It's 1 again!

CHECKPOINT;
SELECT max_identity FROM SYSTABCOL WHERE column_name = 'auto';

max_identity
1

Note: The CHECKPOINT is sometimes required to force SQL Anywhere to update SYSTABCOL.max_identity.

The rules for Gotcha!

You will be asked three skill-testing questions involving SQL Anywhere, and you must answer all three correctly to move on to the next round.

Two out of three right answers don't count, you have to get them all.

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.

Here we go...

---

Question One

What does t1 contain after this code runs?

CREATE TABLE t1 ( t1_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t1 VALUES ( 9223372036854775806 );
INSERT t1 VALUES ( DEFAULT );

Listen to this while working on your answer, then scroll down to see if you got it right...



Here's the answer: While 9,223,372,036,854,775,806 is a pretty big number, the UNSIGNED BIGINT column in t1 can hold more than twice as much (18,446,744,073,709,551,614) so t1 looks like this:

SELECT * FROM t1 ORDER BY t1_auto;

t1_auto
9223372036854775806
9223372036854775807

Congratulations!

---

Question Two

What does this code do?

CREATE TABLE t2 ( t2_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t2 VALUES ( 9223372036854775806 );
INSERT t2 VALUES ( DEFAULT );
INSERT t2 VALUES ( DEFAULT );

Listen to this while working on your answer, then scroll down to see if you got it right...

Careful now!

The 2012 Edition of Gotcha! is more difficult than it was last year!

Here are some clues:

• Table t2 looks exactly the same as t1, only the names have changed.
  

• The t2_auto column is still UNSIGNED BIGINT, and it will still hold values up to 18,446,744,073,709,551,614, and
  

• that's wayyyyy bigger than 9,223,372,036,854,775,806.
  

Here's the answer: The third INSERT crashes!

Could not execute statement.
Column 't2_auto' in table 't2' cannot be NULL
SQLCODE=-195, ODBC 3 State="23000"
Line 34, column 1

Whoa!

We haven't even reached the Gotcha! question yet, what's going on? Inserting NULL is what DEFAULT AUTOINCREMENT tries to do when it runs out of numbers:

---

When the next value to be generated exceeds the maximum value that can be stored in the column to which the AUTOINCREMENT is assigned, NULL is returned. If the column has been declared to not allow NULLs, as is true for primary key columns, a SQL error is generated.

---

Here's the thing, though: UNSIGNED BIGINT works as expected, and so does the following code...

CREATE TABLE t2 ( t2_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t2 VALUES ( 9223372036854775806 );
INSERT t2 VALUES ( DEFAULT );
INSERT t2 VALUES ( 9223372036854775808 );

SELECT * FROM t2 ORDER BY t2_auto;

t2_auto
9223372036854775806
9223372036854775807
9223372036854775808

...so the problem must be with DEFAULT AUTOINCREMENT.

Yes, indeed...

...the underlying cause is that SYSTABCOL.max_identity is defined as BIGINT so it can't hold any value larger than 9,223,372,036,854,775,807. Never mind that DEFAULT AUTOINCREMENT can't be used to generate negative values, signed BIGINT was chosen as the data type.

Never mind that the Help says any old integer will do, which, presumably, includes UNSIGNED BIGINT: "When using AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type."

---

Question Three

What does this code do?

CREATE TABLE t3 ( t3_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( 9223372036854775808 );
INSERT t3 VALUES ( DEFAULT );

OK, maybe you're nervous now, maybe you didn't get Question 2 right, maybe you need a moment to relax...



Question 3 is really asking about what the last INSERT is going to do, the first three work just fine:

CREATE TABLE t3 ( t3_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( 9223372036854775808 );

SELECT * FROM t3 ORDER BY t3_auto;

t3_auto
1
2
9223372036854775808

So what is the next INSERT t3 VALUES ( DEFAULT ) going to do?



Is it going to insert a row with t3_auto = 3?

Wouldn't that make sense? That's what DEFAULT GLOBAL AUTOINCREMENT does, isn't it? If you explicitly insert a value outside the partition, the next DEFAULT AUTOINCREMENT value is calculated as if the explicit INSERT never happened: "Default column values are not affected by values in the column outside the current partition".



So... what's the answer?

It's going to insert 3, isn't it?

Gotcha!

No, it doesn't insert 3, it crashes just like before:

CREATE TABLE t3 ( t3_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( 9223372036854775808 );
INSERT t3 VALUES ( DEFAULT );

Could not execute statement.
Column 't3_auto' in table 't3' cannot be NULL
SQLCODE=-195, ODBC 3 State="23000"
Line 36, column 1

The reason is that the third INSERT, the one that explicitly inserts 9223372036854775808, sets SYSTABCOL.max_identity to this value:

SELECT max_identity FROM SYSTABCOL WHERE column_name = 't3_auto';

max_identity
9223372036854775807

That's NOT the last value that was inserted into t3, it can't store that because it wouldn't fit in a BIGINT. No, it stores the maximum possible positive BIGINT value, and that, folks, is the point of this whole game:

DEFAULT AUTOINCREMENT is limited to BIGINTs

---