OI Indexes and nulls

Opto_Will · May 2023

I have an issue where a record is written where one of the indexed columns starts off as a null.
There is a process thats does a select on that column where the value = ""

However, the new record is not returned!

My testing indicates:
1. If I do an Index Update before the select it does not return the record
2. If I do an Index Rebuild before the select it DOES return the record
3. If I actually instead write a value to that column and modify my select to look for that value it DOES return the record ,as expected, without requiring a rebuild.

I intially thought maybe having a blank column didnt 'dirty' the column for the indexer but even following up (3) above by then blanking out the field with a WriteV and running the select looking again for a "" no longer returns the record (without a rebuild)

Is there something with OI Indexes and Nulls I am not aware of (I wouldnt be surprised, different systems handle nulls differently), specifically around why it would be a difference in a Index Update vs a Index Rebuild?

Opto_Will · May 2023

This is the little test harness I was using.
Clients is a real table but I was just playing with a non-existanct Client "AAAA".
My testing would decide on writing the whole record (with or without the CRec<1> value) or just doing a WriteV on that column. I would delete the row when I needed to clean it out and start again.

	$Insert RLIST_EQUATES
	declare subroutine	Update_Index, RList,Delete_Row
	declare function	RTI_CREATEGUID, SRP_Sort_Array, RTI_LIST
	
	Open "CLIENTS" to cFile Else
		debug
	end
	
	///////////////////////////////////
	! Test Data
	
		// Full Rec
		CRec	= ""
		CRec<1> = ""

	 	*Write cRec to cFile, "AAAA" else
		*	debug
	 	*end
		
	// OR
	
		// Single Field
		WriteV "" on CFile,"AAAA",1 else
			Debug
		end
	/////////////////////////////////// 	

	! Rebuild or Update? Toggle as required
	Update_Index("CLIENTS","CLIENT_NAME",0) 

	! Change the Search as required
	sentence		= 'SELECT CLIENTS WITH CLIENT_NAME = ""'
	
	MyRlist			= RTI_CREATEGUID("b")	; // Make a GUID in BASE64 format 
	RLIST(sentence, TARGET_SAVELIST$, MyRlist)
	FullList		= RTI_LIST("READ", MyRlist) 
	FullListSorted	= SRP_Sort_Array(FullList, "AL1", 1)
	
	! Cleanup test data (Toggle as required)
	*Delete_Row("CLIENTS","AAAA",0)

Edit: I did the same test for a BTree Extract. Unsurprisingly same result.

AusMarkB · May 2023

Sorry I don't have anything of an answer to offer other than to say this feels familiar.
I've had clients before where a standard select would not pick up the records as expected and only a rebuild of the index would fix the problem. Note, no changes to the data, just rebuild a specific index and whilst my memory is a little rusty on that, it's very possible the indexed field was looking for nulls.

I know for one client in particular, it got to a point that I was logging in remotely every payday to rebuild their index so that the pays would go through. It took several weeks to cotton onto the fact that the rebuild of the index was the only reliable way to rectify the situation and it became the goto response. The confusion was that it had worked for years without interference and it works for other clients so why them and why now?
We never fully resolved those questions.
We have however had other clients with the same experience but only on occasion and not regularly repeated.

That's why we could never identify why it didn't work, just that a rebuild of the index always rectified it in that moment.

So, sorry that that's just a story but it might help to know that you're probably on the right track on what is undoubtedly a confusing scenario.

DonBakke · May 2023

My very simple tests are showing that indexing empty strings (a term I prefer to use over "null" since the latter is generally considered a datatype) will index properly. By "simple" I mean I used the SRP Editor to alternatively change the value of an indexed column from actual data to an empty string and then repeat the process. I then used TCL to select for an empty string. In each case the index query worked as expected. I just let OI update the index in the background (I have my background indexing set to 2 seconds and I also have my Update Before Query box checked).

When you clear the field, do you go into the bang table and inspect the 0 record to see if the queue record was generated? Are your dictionary fields all properly set the correct length? Are you running the latest version of OI 9 with the latest patches? Are you using the UD service? All these things matter.

DonBakke · May 2023

The confusion was that it had worked for years without interference and it works for other clients so why them and why now?
We never fully resolved those questions.
We have however had other clients with the same experience but only on occasion and not regularly repeated.

IMO, OI indexes are unnecessarily vulnerable...but that is the reality of things. OI 10 has definitely hardened the reliability of indexes. That said, I am convinced that indexes can run reliably (even in OI 9) but they might require a little more TLC that we would like. The dictionary length issue is such an odd issue. The recent date issue is another gotcha. Odd delimiters and non-printable characters in keys is another. The list goes on.

Opto_Will · May 2023

@DonBakke.

I conceded that a null is often a Data Type in programming languages. You might recall I was a Oracle Developer for decades and that supports the concept of a null value. The Database, at least when I was knee deep in it, treats a character value with a length of zero as null. Yes, that is all very controversial but thats why I still reference a zero string as a null...because i always have (though even Oracle recommeneds you dont treat them the same!!)

I started with the UD. I tried the same test above on another system I have running the UD ...and it Worked. As did a simple TCL request.

So, I integrated the other system into the UD and it still did NOT work.

So I have one system working, one not. Both have the same Indexing Config:

Both OI 9.4.6 running a OI 9.4.6 licence.
All SRP are the same, updated, versions. (this should not mater though since we are talking strictly OI functions here)

We know all about the recent Date issue, as well as non-printable characters.

So the dictionary field length, why would that make a difference? I thought, not being a strongly-typed system, that OI didnt care about those sort of things unless you were integrating with external systems that DID care about types. I think I was making these assumptions based on : Dictionary Settings

Opto_Will · May 2023

@DonBakke
So I started looking at the 0 Record of the Index file and I think you may be onto something.
I did a rebuild on both systems to clear out the 0 Record and then created my Test Client from scratch.

Here is the record of the system that works
CLIENT_NAME is my Index Column, 'AAAA' is my example Key identifier.

this marries up to my Indexes on both systems:

Here is the 0 record from the non-working system:

Not only is CLIENT_NAME (and Allotrac_ID) missing but the other 3 indexes look to have duplicate entries!

Opto_Will · May 2023

I am getting a little stuck on figuring out what is 'behind the scences' of writing that 0 Record on the index that may be a pointer to my issue. Something is different between the two systems (obviously) though on the surface they are both the same....

Any nudges in a direction would be appreciated.

AusMarkB · May 2023

Opto_Will · May 2023

@AusMarkB, lets call ours the Schultz Club!!!!!
2 Members strong and growing...

MattCrozier · May 2023

Are there any duplicated rows in the !CLIENTS index definition record in the !CLIENTS file?

I'd try clearing all the pending transactions then deleting the ! record in the !CLIENTS file, forcing it to be recompiled.

DonBakke · May 2023

I conceded that a null is often a Data Type in programming languages. You might recall I was a Oracle Developer for decades and that supports the concept of a null value. The Database, at least when I was knee deep in it, treats a character value with a length of zero as null. Yes, that is all very controversial but thats why I still reference a zero string as a null...because i always have (though even Oracle recommeneds you dont treat them the same!!)

I guess a 0 length VARCHAR is a NULL but a 0 length CHAR is not NULL. At least this what I gleaned from this page. SQL is not like that. Empty strings are empty strings and NULLs are NULLs.

So I have one system working, one not. Both have the same Indexing Config:

Did you test the tables/indexes of one system in the other and vice-versa? I'm rather certain you'll experience the same results either way. That is, the index will fail with the same dataset regardless of which OI you connect with and the index will work with the same dataset regardless of which OI you connect with.

So the dictionary field length, why would that make a difference?

Because...reasons!? I have never fully understood the reason and you are justified to question the need given the so-called "flexibility" of the database. Veterans just know this is a long standing requirement...although I am hard pressed to find anything official about this.

DonBakke · May 2023

I am getting a little stuck on figuring out what is 'behind the scences' of writing that 0 Record on the index that may be a pointer to my issue. Something is different between the two systems (obviously) though on the surface they are both the same....

Any nudges in a direction would be appreciated.

I didn't mean to leave you hanging. I was without internet for 24 hours and basically was incommunicado this whole time.

As noted in my above response, I suspect your results will be consistent with each dataset. Assuming you have crossed all your t's and dotted all your i's, then I think your index might just be corrupt. Rebuilding indexes, or even removing all indexes and adding them back, will not always fix a corrupted index. You will want to manually remove the index and then add them back.

With regard to understanding the structure of index tables and records, I recommend these two blog articles from Sprezzatura. They are a bit dated, but that the essence of their content is still good:

Indexing in OpenInsight – Part 1 – What is indexing?
Indexing in Opensight Part 2 - How index transactions get created

OI Indexes and nulls

Comments