## 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...

```SELECT 255 + 1;

255+1
256
```

### Question Two

What does SELECT 32766 + 1 return?

```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.

### 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
```

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.