Query issue involving "date" range vs. specified

[barry.marcus]

We are experiencing an issue with our Texis database which we are at a loss to understand.

Here are two sets of queries that we issue to the Texis engine, depending on user input from our application:

Query Set 1:

SET indexwithin=7;
SET withinmode='word';
SELECT PATN_WKU FROM PATN WHERE (ABST_PAx\CLMS_PAx like 'image* test @0') and (ABST_PAx\CLMS_PAx like 'customer* @0') and (ABST_PAx\CLMS_PAx like '+image* +customer* @0 modif* w/2') and (PATN_ISD = '19991005') ORDER BY 1;

Query Set 2:

SET indexwithin=7;
SET withinmode='word';
SELECT PATN_WKU FROM PATN WHERE (ABST_PAx\CLMS_PAx like 'image* test @0') and (ABST_PAx\CLMS_PAx like 'customer* @0') and (ABST_PAx\CLMS_PAx like '+image* +customer* @0 modif* w/2') and (PATN_ISD > '19890101') and (PATN_ISD < '20061231') ORDER BY 1;

Notice that the only difference between the select statements is the date criteria. (Note: The "date" stored in the PATN_ISD column is actually a string formatted as "YYYYMMDD", so that date order is the same as "alphabetical" order.)

The result of the first query is:

PATN_WKU
------------+
5963214


The result of the second query is:

PATN_WKU
------------+
6354215
6267051
6263792

Since the specified date in the first query falls within the range of dates specified in the second query, logically we would assume that the result sets would intersect on the row(s) returned by the first query. This is not the case. The result sets are mutually exclusive and, as I mentioned above, we are completely at a loss as to why this is.

FYI, here are the commands we used to create the index that is ostensibly being used by the above queries:

SET delexp=0;
SET addexp='\alnum{1,99}';
CREATE METAMORPH INVERTED INDEX PATN_ABST_CLMS_MI ON PATN(ABST_PAx\CLMS_PAx,PATN_ISD);

[mark]

Being handled in ticket 467ad5e212 .