BLOGS@DiGiTSS

This post represents thoughts based upon my experience and I have not reported this issue to Sybase as of yet, I firmly believes that such result should not return in given case and so I am representing my thoughts with example over my blog here.

Topic: "Sybase SQL Anywhere Database is buggy when we sort results by column which is having granularity close to Zero".

As I am developing PHP Web Applications, some time back I have faced an issue while I was using a control panel of the web application. It was using Sybase SQL Anywhere as the database engine. The problem was wired, I was not able to search a particular menu item which I have to make active/inactive through the list of about 20 or so odd rows. I was switching through pages and the results were sorted by "status" column, which was having value of either "active" or "inactive". I searched by keyword and I got the result immediately in my data-grid..! It was surprising and unexpected. At first what came into my mind was that oh..! there must be something wrong with my application framework or DAL (Data Access Layer).

Then I took the same query and I fired in the SQL Anywhere - Interactive SQL. The results were unexpected, I felt like this is a "bug" with Sybase itself. To avoid any doubt I created new table with some records and repeated the test and results were same. This incident has taken place at my workplace.

After some time I downloaded Sybase SQL Anywhere Web Edition 11.0 at my own desktop. I thought I was using bit older version at my office and here with the latest version the bug will be fixed, as this edition is using same engine which is used for their Enterprise products and there are some connection/licenses stuff which makes it Web Edition. I repeated the sequence and bingo the bug is still there.

Here is the details of the test as of last tested on Sybase SQL Anywhere Web Edition 11.0:

Table Definition:

CREATE TABLE "DBA"."contacts" (
"id" BIGINT NOT NULL DEFAULT autoincrement UNIQUE,
"first_name" VARCHAR(50) NULL,
"last_name" VARCHAR(50) NULL,
"email" VARCHAR(100) NULL,
"phone1" VARCHAR(25) NULL,
"phone2" VARCHAR(25) NULL,
"cell" VARCHAR(25) NULL,
"skype" VARCHAR(25) NULL,
"active" VARCHAR(1) NULL,
PRIMARY KEY ( "id" ASC )
);
COMMENT ON COLUMN "DBA"."contacts"."active" IS 'Y/N';

Test Data: this is a Table Unload result: You can use this test data by LOAD statement.

UNLOAD TABLE contacts TO 'C:/contacts_data.txt'
 
1,'Loren','Epsum','loren@gmail.com','510-575-5474',,'510-210-5472','lorene','Y'
2,'Steve','Gladen','steveg@gmail.com',,,,,'Y'
3,'Mark','Pane','mark@medicine.com',,,,,'Y'
4,'Jason','G','jasong@realword.com',,,,,'Y'
5,'Bill','G','billg@microsoft.com',,,,,'Y'
6,'Larry','Peterson','larryp@oracle.com',,,,,'Y'
7,'Oscar','Indian','oscara@sarah.com',,,,,'Y'
8,'Bishop','M','bishop@msn.com',,,,,'Y'
9,'Andrew','Smith','andrew@gmail.com',,,,,'Y'
10,'Danial','J','danialj@mashable.com',,,,,'Y'

Then after following SQL statements were executed from the Interactive SQL prompt.

select top 5 start at 1 id,first_name,last_name,active from contacts order by active;
select top 5 start at 6 id,first_name,last_name,active from contacts order by active;
select id,first_name,last_name,active from contacts order by active;

First SQL statement:

SELECT top 5 start at 1 id,first_name,last_name,active FROM contacts ORDER BY active;

Unlike MySQL, we have to use "1" instead of "0" to start with "1"st row of the result set, wherein MySQL we use "0", so in this case we should get first five rows of the table which as we can see in the screen shot we are getting.

Result (1):

First SQL statement - Result screen shot

Second SQL Statement:

SELECT top 5 start at 6 id,first_name,last_name,active FROM contacts ORDER BY active;

In this case we should get next 5 rows starting from 6th and till 10th, but we are not able to get that as we can see that in the following screen shot.
Result (2):

Second SQL statement - Result screenshot

The row with "id=6" is missing, instead result-set is containing a row with "id=1".

Just to make sure that table is having proper data in it, we can fire third SQL statement.

Third SQL Statement:

SELECT id,first_name,last_name,active FROM contacts ORDER BY active;

and here is the result (3):

Third SQL statement - Result screenshot

Solution/Work-around:

The purpose was just to make sure that if you are not aware of it and new to database of Sybase family then you may fall into trap. There is a simple solution available if you have a primary or unique key available then just include that just next to field you want to sort.

SELECT top 5 start at 1 id,first_name,last_name,active FROM contacts ORDER BY active,id;
SELECT top 5 start at 6 id,first_name,last_name,active FROM contacts ORDER BY active,id;

else any other field with high granularity, you can put in as additional sort column like following:

SELECT top 5 start at 1 id,first_name,last_name,active FROM contacts ORDER BY active,first_name;
SELECT top 5 start at 6 id,first_name,last_name,active FROM contacts ORDER BY active,first_name;

I believe this should not be like the way it is right now, anyway this is software world and anything is possible, I am going to submit a bug and let's see what they have to say on this.

Conclusion: As a matter of fact I am not sure why Sybase SQL Anywhere family of databases behaves in such a manner? I believe internally when they are doing sorting they are not using any other field similar to primary key (if available) or internal row-id and so when they return the result for next query (similar to 2nd SQL Statement), they are not able to maintain the sequence and consistency of the query results.

I am not happy about figuring out this bug out of this, but this should not happen at first place. If there is any hidden reason or some system parameter required to overcome such output then it should not be like that and such functionality should be taken care by default out of the box. Even open-source MySQL don't have any problem with such simple statements (I have tested that, looks like when there is a primary key they are either using it or they are outputting their results in the order in which they were entered when you perform such sort.)

I would like you to share your any such problems with any database, which affects the basic functionality, or your comments on this are highly invited. If you have faced similar problem with Sybase and there are some configuration settings which make it happen then also please reply back.