Relational Indexes and "0" keys (OpenInsight 32-Bit) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>category:"OpenInsight 32-Bit" author:"Karen Oland" author:"Dave Harmacek" author:"James Mowbray" author:"Richard Hunt" author:"Warren Auyong"}}

[[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]]


==== Relational Indexes and "0" keys (OpenInsight 32-Bit) ====
=== At 18 APR 2006 03:04:08PM Karen Oland wrote: ===
 
 
<QUOTE>
Is it possible that RTI could fix the code for relational indexes in OI to properly maintain these in the case of records with a zero key ("0") - perhaps fixing the logic that skips the write from "if key" to "if len(key)" (or something similar)?



Annoyingly, we have to keep loading AREV to rebuid the indexes in a set of three files which have few records (which are updated infrequently), but all have numerically keyed records and all have a record with the number zero as the key.  After an update to nearly any record, the relational indexes drop the zero key in the related fields of any updated record (and if you rebuild all indexes in OI, it drops all the zero keyed records from the lists).
</QUOTE>
----

=== At 18 APR 2006 03:17PM Dave Harmacek wrote:  ===

<QUOTE>Gee Karen,  I depend upon this "bug".  



This is quite useful if you only want to index to a list of a few rows of a large table.  For example, I want only a list of keys where the STATUS is "NEW".



Since you can create a relational index on a symbolic, maybe a symbolic could evaluate those "0" row keys to something non-zero?



Sincerely, Dave

</QUOTE>

----

=== At 19 APR 2006 10:59AM Karen Oland wrote:  ===

<QUOTE>Better to depend on good code, than buggy side effects.



Your code would still work if nulls are ignored (esp as it seems yours may be a symbolic) - but long existing code doesn't since zeroes are not (which are valid keys, while nulls technically are not, despite AREV and OI occasionally creating such records).

</QUOTE>

----

=== At 19 APR 2006 11:06AM Karen Oland wrote:  ===

<QUOTE>And I suppose I didn't answer your question as to why not just use some other substitute value for the valid value of zero - because those are the keys.



So, my code would have to have logic around every read/xlate of those three files that substituted the dummy value for a zero, so that if I was told that record "zero" was the related one, it read another (or, write out the records with "zero" as the key, then feed any program other than indexing the key of "0" (which may rule using an MFS) so that all sorts, reads, etc would work.  And since these three files are part of the login process and contain info on where someone is logged in, which files to attach, what default parameters to use when in that database, etc (with the "0" area being a training area shipped with the product for over ten years), the files are all accessed a lot.



Or, RTI could fix a bug that should not be there. Which should not break any existing code -- as it will put the behavior back to what was before and would still ignore null values and not create null keyed records (as at least one buggy old version used to do, long ago). 

</QUOTE>

----

=== At 19 APR 2006 04:00PM James Mowbray wrote:  ===

<QUOTE>I assume the problem occurs when you WRITE one of the three '0' records?  This has been an issue all along in OI 32.  You should put it in the 'Issues' list as a 'Critical' issue.  Then RevSoft might fix it sooner than later.  After all, it is just a minor change to SI.MFS.

</QUOTE>

----

=== At 21 APR 2006 10:08AM Dave Harmacek wrote:  ===

<QUOTE>I'm afraid a fix would break every one of my systems, since I would get relationals rows that exceed 64k bytes.  I still have hybrid systems (Arev and OI) and cannot move up to the unlimited row length features.



This "bug" has existed since Revelation F, I recall.



Maybe you could look at the indexing code and see if that is where the "0" row in relationals is excluded.  

</QUOTE>

----

=== At 21 APR 2006 11:31AM Richard Hunt wrote:  ===

<QUOTE>Both sides of this argument can use a "symbolic" to handle the "0" keys. So given that... wouldn't it be proper to fix the flaw?

</QUOTE>

----

=== At 22 APR 2006 02:10PM Karen Oland wrote:  ===

<QUOTE>The flaw should be corrected - those relying on the bug should have been using nulls all along (either in their symbolic or data) - nulls don't cause the ]64K problem in AREV or OI as they are not indexed. And are handled just as well if one attempts to add them (and I'll assume that the person using them is not, since "NEW" is his other chosen value).  But having the indexes work differently between AREV and OI does cause a tremendous problem in existing code, especially when the reason is a bug in OI. And with shared data and people on both systems, we cannot just change our keys since OI decided to ignore a perfectly valid key value (hopefully, of course, this is the only place where the zero key is ignored and treated as a null). Using a symbolic to get around it would mean using a different key value (still numeric, maybe -1? to get sorts and such to still work correctly) and modifying who knows how much code (hopefully none, but then again, I've never tried using -1 as a key and know of at least one area it would cause a problem with and that is naming directories to store data in -- easily gotten around, but just that much more that would have to be modified to make the two versions co-exist (over and above what has been required to get around OI's lack of support for some features, none of which were critical, but which saved time when writing reports and such - such as less support for complex and/or sorting inside a LIST statement).



If RTI wants to release the code for the indexes, I'm sure I could just fix it here -- but it would be a task that many would have to do and one that would have to be repeated with each upgrade, here and everywhere else that proper functioning was required. Not to mention that it violates a number of DB principles and I doubt the other database competitors choose to ignore keys with a ZERO when building any of their indexes.

</QUOTE>

----

=== At 23 APR 2006 01:12AM Warren Auyong wrote:  ===

<QUOTE>]The flaw should be corrected - those relying on the bug should have ]been using nulls all along (either in their symbolic or data) - nulls 

]don't cause the ]64K problem in AREV or OI as they are not indexed



Not exactly true. In ARev v3.12 try rebuilding relational indexes on a large file, where the related field has many nulls. You'll get a "variable exceeds maximum length" or out of string space error. Even though a related to record with a key of "" (null) is not written out a bug in the rebuild routines still creates the record in memory which causes the error(s).



Mike Ruane mentioned at the SoCalRUG meeting earlier this year that a "SI.MFS2" is in the works, which eliminates background indexing (except for relational indexes). I would not doubt that many indexing issues are being addressed even as we speak.

</QUOTE>

----

=== At 24 APR 2006 02:18PM Karen Oland wrote:  ===

<QUOTE>]



That is an unfortunate flaw in the change made to rebuilding indexes in 3.12 (an otherwise welcome update). However, I haven't seen the problem using the older methods of rebuilding relational indexes. So, we rebuild most indexes using the Update/Rebuild index menu and the relational ones by typing a long command at TCL (building on the old index.update routine released years ago).



What would be a major problem in 3.12 would be using zero ("0") in a symbolic to mean "ignore me in relational indexes" - as that would (properly) build a key to a record keyed with zero, which would then break after exceeding 64K in size. And there would be no way to get around that limit there, while allowing the index to continue ignoring nulls results in no problems at all (other than during a rebuild, having to remember to not use the menu to rebuild relationals).

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=28FD6B7B5A14E6B3852571540068BFBB|View this thread on the Works forum...]]