This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

<span class="iconify mr-1 svg-loading" data-icon="mdi:arrow-left"></span>{{tag>category:"OpenInsight 32-bit" author:"Donald Bakke" author:"bshumsky" author:"D Harmacek"}} [[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]] ==== Btree.Extract Works Reduce Fails (OpenInsight 32-bit) ==== === At 25 FEB 2020 01:21:08AM Donald Bakke wrote: === <QUOTE> 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: [list=1] [*] The dictionary is configured as a single part Key ID but some Key IDs have an asterisk. [*] 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. [/list] This code works every time: <code> SearchString = 'BATCH_NO' : @VM : '09' : @FM Table = 'PAC' DictVar = hDictPAC Keys = '' Option = '' Flag = '' Btree.Extract(SearchString, Table, DictVar, Keys, Option, Flag) </code> This code does not work (Flag=1, @List_Active=3, @RecCount=0): <code> ReduceScript = 'WITH {BATCH_NO} EQ "09"' SortList = '' Mode = 0 TableName = 'PAC' CursorVar = 0 Flag = '' Reduce(ReduceScript, SortList, Mode, TableName, CursorVar, Flag) </code> This also returns 0 rows: <code> SELECT PAC WITH BATCH_NO EQ '09' </code> 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. [url=mailto:dbakke@srpcs.com]Don Bakke[/url] [url=http://www.srpcs.com]SRP Computer Solutions, Inc.[/url] </QUOTE> ---- === At 25 FEB 2020 08:59AM bshumsky wrote: === <QUOTE>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 [url=http://www.revelation.com]Revelation Software, Inc.[/url] </QUOTE> ---- === At 25 FEB 2020 09:32AM D Harmacek wrote: === <QUOTE>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 </QUOTE> ---- === At 25 FEB 2020 10:17AM Donald Bakke wrote: === <QUOTE> <QUOTE>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.</QUOTE> 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? [url=mailto:dbakke@srpcs.com]Don Bakke[/url] [url=http://www.srpcs.com]SRP Computer Solutions, Inc.[/url] </QUOTE> ---- === At 25 FEB 2020 10:28AM bshumsky wrote: === <QUOTE> <QUOTE> <QUOTE>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.</QUOTE> 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? [url=mailto:dbakke@srpcs.com]Don Bakke[/url] [url=http://www.srpcs.com]SRP Computer Solutions, Inc.[/url]</QUOTE> 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 [url=http://www.revelation.com]Revelation Software, Inc.[/url] </QUOTE> [[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=9033EC40C795E44EBABD97E2ADBC895B|View this thread on the Works forum...]]