All,

It might help if I give the results of the SQL trace to see what is going on
behind the scenes. There is one instance where no records are returned and
the instance where the records are returned. It seems that when the query is
sorted by the same column as the one in the STARTING WITH clause then it
works. If the query is sorted by a different column then it doesn't return
records.


--------------------------------------------------------------------------
This query has a blank string for the parameter and it returns no records.
--------------------------------------------------------------------------


/*---
PREPARE STATEMENT
TR_HANDLE = 4
STMT_HANDLE = 2

SELECT ITEM.ID, ITEM.DBID
FROM ITEM, DESCRIPTION
WHERE DESCRIPTION.ID=ITEM.ID_DESCRIPTION
AND ITEM.DBID = ? /* P_DBID */
AND ITEM.ASSETNO STARTING WITH ? /* P_SEARCH */
AND ( DESCRIPTION.DESCRIPTION >= ? /* OLNK_DESCRIPTION */ )
ORDER BY DESCRIPTION.C_CODE ASC

PLAN SORT (JOIN (ITEM INDEX (IX_ITEM_ASSETNO), DESCRIPTION INDEX
(PK_DESCRIPTION)))

FIELDS = [ Version 1 SQLd 2 SQLn 30
ITEM.ID = <NIL>
ITEM.DBID = <NIL> ]
----*/
/*---
DESCRIBE INPUT
STMT_HANDLE = 2
PARAMS = [ Version 1 SQLd 3 SQLn 3
< SQLType: 496 SQLLen: 4 > = <NIL>
< SQLType: 449 SQLLen: 20 > = <NIL>
< SQLType: 448 SQLLen: 50 > = <NIL> ]
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 4
STMT_HANDLE = 2
PARAMS = [ Version 1 SQLd 3 SQLn 3
[P_DBID] = 1
[P_SEARCH] = ''
[OLNK_DESCRIPTION] = '' ]
----*/
/*---
OPEN CURSOR
STMT_HANDLE = 2
NAME = C1218089689013596

----*/
/*---
FETCH
STMT_HANDLE = 2
FIELDS = [ Version 1 SQLd 2 SQLn 2
ITEM.ID = 0
ITEM.DBID = 0 ]

ERRCODE = 100
----*/
/*---
CLOSE CURSOR
STMT_HANDLE = 2
----*/


-------------------------------------------------------------------------
This query has a blank string for the parameter, but it returned records.
-------------------------------------------------------------------------


/*---
PREPARE STATEMENT
TR_HANDLE = 4
STMT_HANDLE = 2

SELECT ITEM.ID, ITEM.DBID
FROM ITEM, DESCRIPTION
WHERE DESCRIPTION.ID=ITEM.ID_DESCRIPTION
AND ITEM.DBID = ? /* P_DBID */
AND ITEM.ASSETNO STARTING WITH ? /* P_SEARCH */
AND ( ITEM.ASSETNO >= ? /* OLNK_ASSETNO */ )
ORDER BY ITEM.ASSETNO ASC

PLAN SORT (JOIN (ITEM INDEX (IX_ITEM_ASSETNO), DESCRIPTION INDEX
(PK_DESCRIPTION)))

FIELDS = [ Version 1 SQLd 2 SQLn 30
ITEM.ID = <NIL>
ITEM.DBID = <NIL> ]
----*/
/*---
DESCRIBE INPUT
STMT_HANDLE = 2
PARAMS = [ Version 1 SQLd 3 SQLn 3
< SQLType: 496 SQLLen: 4 > = <NIL>
< SQLType: 449 SQLLen: 20 > = <NIL>
< SQLType: 449 SQLLen: 20 > = <NIL> ]
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 4
STMT_HANDLE = 2
PARAMS = [ Version 1 SQLd 3 SQLn 3
[P_DBID] = 1
[P_SEARCH] = ''
[OLNK_ASSETNO] = '' ]

SECONDS = 0.016
----*/
/*---
OPEN CURSOR
STMT_HANDLE = 2
NAME = C1218089689013596

----*/
/*---
FETCH
STMT_HANDLE = 2
FIELDS = [ Version 1 SQLd 2 SQLn 2
ITEM.ID = 1001
ITEM.DBID = 1 ]
----*/

000000000000 Lots of fetches removed 00000000000000

/*---
FETCH
STMT_HANDLE = 2
FIELDS = [ Version 1 SQLd 2 SQLn 2
ITEM.ID = 1001
ITEM.DBID = 1 ]

ERRCODE = 100
----*/
/*---
CLOSE CURSOR
STMT_HANDLE = 2
----*/


PS. I'd first like to rule out that I'm not doing something stupid before I
move this over to firebird-devel. The last thing I took to them turned out
to be a support issue because I was overlooking something.

Thanks,
Jason Wharton
www.ibobjects.com