Monday, September 26, 2011

Let's play "Gotcha!" - Round One

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

Let's get started right away with a warm-up question:

What does SELECT 1 + 1 return?


The answer of course is 2!

Here's proof using SQL Anywhere 12.0.1:
SELECT 1 + 1;

1+1
2

OK, what are the rules?


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 SELECT 255 + 1 return?

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



Here's the answer, it's 256!
SELECT 255 + 1;

255+1
256


Question Two


What does SELECT 32766 + 1 return?



The answer is 32767!
SELECT 32766 + 1;

32766+1
32767

Congratulations to everyone who has two out of three right so far... only one more to go!


Question Three


What does SELECT 32767 + 1 return?



Did you say 32768? Are you sure? Want a hint?

Let's ask Microsoft SQL Server 2008:
1> SELECT 32767 + 1
2> GO

 -----------
       32768

But remember, this is a question about SQL Anywhere, not SQL Server...

...so don't rush, take your time.



Here's the answer...

Gotcha!


SELECT 32767 + 1;

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Value 32767 + 1 out of range for destination
SQLCODE=-158, ODBC 3 State="22003"

For you winners, see you all back here in two days for Round Two of "Gotcha!"

For the rest of you, check out the CAST() function in the docs:
SELECT CAST ( 32767 AS BIGINT ) + 1;

cast(32767 as bigint)+1
32768


2 comments:

Anonymous said...

You got me!

FWIW, "SELECT CAST(32767 AS INT) + 1" will do as well.

In constrast, multiplication does not seem to stick to TINYINTs, e.g. SELECT 32767 * 4 does work.

Volker Is Still Puzzled

Breck Carter said...

@Volker AKA Anonymous: That was a case of "BIGINT on the brain" :) Also, AFAIK TINYINT isn't an issue here, as the SELECT 255 + 1 showed, it's a SMALLINT (and INTEGER) thing. This behavior has been discussed before, but it always seems fresh when it bites anew.