Query STARTING WITH :Param returns inconsistent result depending upon ORDER BY
clause
-------------------------------------------------------------------------------------
Key: CORE-3452
URL: http://tracker.firebirdsql.org/browse/CORE-3452
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.1.4, 2.5.0, 1.5.6, 2.1.3, 2.1.2
Environment: Using IB Objects on a Win XP machine, thus 32 bit.
Reporter: Jason Wharton
I have a query that is shown in the SQL trace below that has an inconsistent
behavior depending upon what the sort order of the query is.
The problem seems to have to do with the STARTING WITH clause when used with an
input parameter. When the input is a blank string sometimes you get no records
and other times you get all records (that aren't null). This material can be
easily examined via IB Objects because this is one of the sample applications
that comes with the product. I'd be happy to furnish a copy of it for testing
purposes to anyone working on this issue.
Here are the tables I'm working with:
CREATE TABLE DESCRIPTION (
ID INTEGER NOT NULL
, DBID INTEGER DEFAULT 1 NOT NULL
, DESCRIPTION VARCHAR( 50 ) NOT NULL
, C_CODE VARCHAR( 8 ) DEFAULT '' NOT NULL
, C_DESC VARCHAR( 50 ) DEFAULT '' NOT NULL
, CONSTRAINT PK_DESCRIPTION
PRIMARY KEY ( ID )
)
CREATE TABLE ITEM (
ID INTEGER NOT NULL
, DBID INTEGER DEFAULT 1 NOT NULL
, ASSETNO VARCHAR( 20 ) DEFAULT NULL
, SCANCODE VARCHAR( 20 ) DEFAULT '+' NOT NULL
, SERIALNO VARCHAR( 20 ) DEFAULT NULL
, C_HOSTSCAN VARCHAR( 20 ) DEFAULT NULL
, ID_DESCRIPTION INTEGER DEFAULT 1 NOT NULL
, CONSTRAINT PK_ITEM
PRIMARY KEY ( DBID, ID )
)
ALTER TABLE ITEM
ADD CONSTRAINT RI_ITEM_DESCRIPTION
FOREIGN KEY ( ID_DESCRIPTION )
REFERENCES DESCRIPTION ( ID )
Here is the base statement that returned results:
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.C_HOSTSCAN >= ? /* OLNK_C_HOSTSCAN */ )
ORDER BY ITEM.C_HOSTSCAN ASC
PLAN SORT (JOIN (ITEM INDEX (IX_ITEM_C_HOSTSCAN), DESCRIPTION INDEX
(PK_DESCRIPTION)))
Here are examples of a query that didn't return results:
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.SCANCODE >= ? /* OLNK_SCANCODE */ )
ORDER BY ITEM.SCANCODE ASC
PLAN SORT (JOIN (ITEM INDEX (IX_ITEM_ASSETNO), DESCRIPTION INDEX
(PK_DESCRIPTION)))
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)))
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.C_CODE >= ? /* OLNK_C_CODE */ )
ORDER BY DESCRIPTION.C_DESC ASC
PLAN SORT (JOIN (ITEM INDEX (IX_ITEM_ASSETNO), DESCRIPTION INDEX
(PK_DESCRIPTION)))
Here is a little more detail showing the trace results.
----------------------------------------------------------
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
----*/
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
WhatsUp Gold - Download Free Network Management Software
The most intuitive, comprehensive, and cost-effective network
management toolset available today. Delivers lowest initial
acquisition cost and overall TCO of any competing solution.
http://p.sf.net/sfu/whatsupgold-sd
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
