OpenInsight 9.4.4

IDX_SETS2

A client has reported problems getting valid results when selecting against an indexed column. We've performed all the regular sanity checks such as column lengths for indexed columns and Key ID columns, invalid characters in Key IDs, GFEs, etc. Everything checks out.

If we rebuild the index the results are correct. After a few weeks the results begin to fail, even to the point of returning 0 rows when we know there are several rows. Some values for this column seem to always return valid results while others do not.

There are two possible oddities that might be impacting the integrity of the index:

1. The dictionary is configured as a single part Key ID but some Key IDs have an asterisk.
2. The data being indexed is numeric, but single digit values always have one or two preceding zeroes (e.g., 01, 02, 03, 10, 001). This column is typed as VARCHAR with Left justification.

This code works every time:

SearchString        = 'BATCH_NO' : @VM : '09' : @FM

Table               = 'PAC'

DictVar             = hDictPAC

Keys                = ''

Option              = ''

Flag                = ''

Btree.Extract(SearchString, Table, DictVar, Keys, Option, Flag)

This code does not work (Flag=1, @List_Active=3, @RecCount=0):

ReduceScript        = 'WITH {BATCH_NO} EQ "09"'

SortList            = ''

Mode                = 0

TableName           = 'PAC'

CursorVar           = 0

Flag                = ''

Reduce(ReduceScript, SortList, Mode, TableName, CursorVar, Flag)

This also returns 0 rows:

SELECT PAC WITH BATCH_NO EQ '09'

Any ideas on where else to look? I figure there might be a way to analyze the index rows for possible broken pointers but I am missing my notes that document their structure.

Don Bakke

SRP Computer Solutions, Inc.

Hi, Don.

We've recently (within the past few weeks) received a report of something very similar, and we think we've tracked it down - it seems that in the most recent patch, we introduced a bug in SI_REDUCTION that affects left-justified numeric values (which appears to match what you're seeing here). We plan to issue a fix/patch soon, but in the interim if you can take a copy of SI_REDUCTION from an earlier version of 9.4 and put it on your system, we believe that should resolve the problem.

Sorry for the inconvenience, and again, we hope to have a fix available soon.

- Bryan Shumsky

Revelation Software, Inc.

I think I've seen this problem, too. Wish the clients would stop getting values with leading zeroes.

They look at the raw data through Excel and don't see those leading zeroes, too!

Dave Harmacek - Harmacek Database Systems - near Boston, MA USA

We plan to issue a fix/patch soon, but in the interim if you can take a copy of SI_REDUCTION from an earlier version of 9.4 and put it on your system, we believe that should resolve the problem.

Hi Bryan - Thanks for the quick response. I installed a 9.3.2 version of SI_REDUCTION and at first blush this appears to be working again. I take it that this confirms what you've already encountered and will address in an upcoming fix.

I am still curious about something. Why does rebuilding the index temporarily fix the problem but over time it starts to fall apart again?

Don Bakke

SRP Computer Solutions, Inc.

We plan to issue a fix/patch soon, but in the interim if you can take a copy of SI_REDUCTION from an earlier version of 9.4 and put it on your system, we believe that should resolve the problem.

Hi Bryan - Thanks for the quick response. I installed a 9.3.2 version of SI_REDUCTION and at first blush this appears to be working again. I take it that this confirms what you've already encountered and will address in an upcoming fix.

I am still curious about something. Why does rebuilding the index temporarily fix the problem but over time it starts to fall apart again?

Don Bakke

SRP Computer Solutions, Inc.

Hi, Don. I think there's an "optimization" in the update_index code that's causing the problem. When the index is rebuilt, that optimization isn't used, and thus everything reverts to its correct sorting…

- Bryan Shumsky

Revelation Software, Inc.

View this thread on the Works forum...