Saturday, October 9, 2010

What's going on here?

Can something this simple really be so wrong? Here's a simple query in SQL Anywhere 11.0.1.2276 that returns 5 rows, starting at row number 10, from a table t where pkey is the PRIMARY KEY column t, and that column is DEFAULT AUTOINCREMENT with values 1, 2, 3...:


SELECT TOP 5 START AT 10 *
FROM t
ORDER BY pkey;

pkey,data
10,'B'
11,'B'
12,'B'
13,'B'
14,'B'

So far so good... now supposing we add WHERE data = 'B' to the query; shouldn't the result set be the same?

But it's not! Look here:

SELECT TOP 5 START AT 10 *
FROM t
WHERE data = 'B'
ORDER BY pkey;

pkey,data
15,'B'
16,'B'
17,'B'
18,'B'
19,'B'

What's going on here? Clearly rows 10 through 14 have data = 'B', why aren't those rows showing up in the second query?

This is a bug in SQL Anywhere 11.0.1.2276, right?

For the answer see Well, don't leave us hanging... is this a bug or not?

3 comments:

Anonymous said...

Well, don't leave us hanging...is this a bug or not?

Anonymous said...

I say it's a bug. The documentation pretty clearly states that the ORDER BY clause will be respected (and is in fact more or less required) for TOP and START AT.

Is there an index on the "data" column?

Breck Carter said...

Note: Earlier comments were temporarily deleted, and can now be found as part of this article: http://sqlanywhere.blogspot.com/2010/10/well-dont-leave-us-hanging-is-this-bug.html