05-18-2017 5:40 PM
I have been working with SAP SQL Anywhere support group to pin down why a simple SQL statement was not using the primary key index and is doing a full table scan. The primary key of the table is numeric(20,0). The datawindow sql statement is of the form select * from table where key=:an_key - where :an_key is declared as a numeric argument. If I create the same datawindow but the value passed in is a string then the index is used. After much going back and forth we have determined that the issue is that PowerBuilder passes in a numeric argument as a double. A double is considered by SQL Anywhere to be an approximate datatype. If I have a primary key that is numeric(20,0) then it will not use the primary index and will do a table scan whereas if the primary key is an integer then it will use the primary key! This seems to affect all versions of Sybase SQL Anywhere. I have reproduced this in PB6.5 and PB12.5