OI9.4.2 btree.xtract returning incorrect results.

Long standing routine with no recent changes.

Search criteria includes a date range on a date field that is getting things wrong.

In my testing example, the result should return just two keys.

Instead it returns a list of thirty that don't match the date range requested. The thirty returned are all the keys that match the first part of the criteria (employee), less the two I'm expecting. In other words, all the relevant keys prior (or maybe outside of) the requested date range.

If I change the ending date in the range, it can work correctly.

Examples:

required date range 19740 to 20107 gets it wrong

If I change the end date to 20099 or less, it gets it correct. 20100 or higher and it screws up.

Wrong

btreeWrong.png?dl=0

Correct

btreeCorrect.png?dl=0

This is the expected results

This is what I'm getting

Sounds like you have a value mark in one of your primary row ids which is throwing the index off. Write a program to scan the file for rogue characters in keys.

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Thanks. Will do.

The symptoms that led to me finding this anomaly were reported as occurring over multiple dbs.

I'll do the scan because it will be good to have handy, but it would seem quite coincidental if multiple dbs started developing rogue keys.

That said, these particular users do have a tendency to get somewhat creative with their keys.

Thanks. Will do.

The symptoms that led to me finding this anomaly were reported as occurring over multiple dbs.

I'll do the scan because it will be good to have handy, but it would seem quite coincidental if multiple dbs started developing rogue keys.

That said, these particular users do have a tendency to get somewhat creative with their keys.

Could these be new workstations that don't have the latest ClientSetup installed?

Don Bakke

SRP Computer Solutions, Inc.

I was also suspicious of this but I'm replicating on my local dev copy and just to be sure I reran the clientsetup.

This issue has raised it's ugly head again. Mainly because I provided a work around for the client, (shortened the date range) and then promptly forgot about it, but now the work around has also fallen over as we get closer to the troublesome date.

The symptoms are consistent across different dbs so it's not limited to specific data.

btree.extract with a search criteria searching a date field for a date range.

If the date range ends prior to the 11th January 2023, internally 20100, then everything works perfectly.

If the date range starts prior to the 11th Jan but ends after that day, the results returned are all keys with a date prior to the start date.

If the date range starts and ends after the 11th Jan, then null is returned regardless of whether there is data or not.

Search criteria examples

EMPLOYEE:@vm:89:@fm:ROSTER_DATE:@vm:19916~20099:@fm

Returns the list of keys expected.

EMPLOYEE:@vm:89:@fm:ROSTER_DATE:@vm:19916~20100:@fm

Returns all keys where ROSTER_DATE is prior to 19916

EMPLOYEE:@vm:89:@fm:ROSTER_DATE:@vm:20100~20150:@fm

Returns null

Note, it doesn't matter what value is in the employee index, I just left it in the example for transparency.

I've personally tested it with three separate dbs. Identical symptoms on all three.

My first inclination is to wonder whether you have system delimiters in any of your row ids? This can throw the index off.

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

I ran a routine to check all IDs for system delimiters.

Ran it on all three sets of data. None found.

I ran a routine to check all IDs for system delimiters.

Ran it on all three sets of data. None found.

Maybe it's time to get another pair of eyes to look at this for you. Sometimes these things have to be seen firsthand in order to figure out. Also, I think there is value in having your observations confirmed by another party.

Don Bakke

SRP Computer Solutions, Inc.

Search criteria examples

EMPLOYEE:@vm:89:@fm:ROSTER_DATE:@vm:19916~20099:@fm

Returns the list of keys expected.

EMPLOYEE:@vm:89:@fm:ROSTER_DATE:@vm:19916~20100:@fm

Returns all keys where ROSTER_DATE is prior to 19916

EMPLOYEE:@vm:89:@fm:ROSTER_DATE:@vm:20100~20150:@fm

Returns null

I assume you have a date conversion on the ROSTER_DATE field.

If so, try using the external format instead of the internal format.

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

I assume you have a date conversion on the ROSTER_DATE field.

If so, try using the external format instead of the internal format.

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Aaron - I think us old timers have internalized the idea that presenting our query values in external format is best pratice but I've never 100% understood why. Can you elaborate?

Don Bakke

SRP Computer Solutions, Inc.

This issue has raised it's ugly head again. Mainly because I provided a work around for the client, (shortened the date range) and then promptly forgot about it, but now the work around has also fallen over as we get closer to the troublesome date.

The symptoms are consistent across different dbs so it's not limited to specific data.

btree.extract with a search criteria searching a date field for a date range.

If the date range ends prior to the 11th January 2023, internally 20100, then everything works perfectly.

If the date range starts prior to the 11th Jan but ends after that day, the results returned are all keys with a date prior to the start date.

If the date range starts and ends after the 11th Jan, then null is returned regardless of whether there is data or not.

Search criteria examples

EMPLOYEE:@vm:89:@fm:ROSTER_DATE:@vm:19916~20099:@fm

Returns the list of keys expected.

EMPLOYEE:@vm:89:@fm:ROSTER_DATE:@vm:19916~20100:@fm

Returns all keys where ROSTER_DATE is prior to 19916

EMPLOYEE:@vm:89:@fm:ROSTER_DATE:@vm:20100~20150:@fm

Returns null

Note, it doesn't matter what value is in the employee index, I just left it in the example for transparency.

I've personally tested it with three separate dbs. Identical symptoms on all three.

@MarkBoorman probably just me being old but I usually use the actual date output format dd/mm/yyyy my suspicion is that maybe 20150 is being interpreted as 2/10/1950 also you could have 2/1/2000 read for the other.

Ding, ding, ding, ding, ding

First prize is awarded to Aaron.

Changing the search values to external format did fix the problem and all the correct results are now being returned.

It doesn't explain why the internal values just stop working as at 11th Jan, but whatever keeps the train moving will do me.

Contrary to Don's thoughts, sometime ago I internalised that the internal format is more reliable, which is probably why Oconving didn't occur to me this time around. If raw numbers don't work, how is adding an extra complication of conversion going to be more reliable?

Happy to eat humble pie to have a working solution.

Ding, ding, ding, ding, ding

First prize is awarded to Aaron.

Changing the search values to external format did fix the problem and all the correct results are now being returned.

It doesn't explain why the internal values just stop working as at 11th Jan, but whatever keeps the train moving will do me.

Contrary to Don's thoughts, sometime ago I internalised that the internal format is more reliable, which is probably why Oconving didn't occur to me this time around. If raw numbers don't work, how is adding an extra complication of conversion going to be more reliable?

Happy to eat humble pie to have a working solution.

I'll use as a springboard to also answer Don about internal/external formats during searching.

Way back when ARev 2.0 was the most recent version, and we all met up on CompuServe, it was considered common knowledge that using internal (IConv) values for selection was faster than using the external (OConv) values. For the purposes of this discussion, selection included btree.extract and select statements. This was mostly championed by a developer (who we shall just refer to as Lothar. While no one ever posted actual benchmarks or explained the reasoning in any detail, it was generally accepted as true by most developers. At least until it wasn't.

Because the problem was, that no one ever really used IConv values in selects. Only for dates. But it was faster, everyone knew that. But, if it really was faster, why only use it for dates? Still Lothar pushed his theory and it became lore, and all was good in the land. Then, one day, some developers started claiming that selection was broken. Results were wildly inacurate, and the inconsistencies and errors could not be reliably duplicated. Slowly, more and more complaints arose until, one day, the error reports grew rapidly and exponentially. It didn't help that this coincided with a new release of Advanced Revelation. Surely, Revelation released an incredibly buggy product. Accusations flew. The dark times had arrived.

This also matched the time-frame when the internal date started becoming an valid date. Once this was worked out, the community demanded RTI fix this problem, and they did. But not without revealing a horrible truth. Using the IConv values was not only actually slower, but only worked because of unintended consequences.

Let's look at this using an example that does not involve dates. Assume you have a file (INVOICES) where INVOICE_AMT is indexed. Now, you want to find all of you invoices that are over $1,234.56. Since Iconv values are always faster than Oconv values, the optimal way to generate this select statement would be

SELECT INVOICES WHERE INVOICE_AMT > 123456

123456 is the internal value, so that's what's passed in. But, as we all know, that won't return everything greater than $1,234.56, but it will use $123,456.00.

The system always (and always means without any exception) will apply the Oconv value from the dictionary against the search value. If the result of the conversion has an error, then the system will use the passed value. With dates, a number like 9874 will not convert, so the system will just use the passed value. However, if you pass in 20100, the system will treat that as 02-01-2000 and convert that to the internal value of 11720 and not use it's Oconv value 01-11-2023 as intended.

So, not only does the system return incorrect results, it's actually slower since the system has to process more code to handle the error in the conversion statement.

While ARev 3.12 has a flag to help mitigate the problem, I don't believe the flag was ever implemented in OpenInsight, nor should it have been.

For more information, see Date Fields in Selects (TB #112)

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Memory like an elephant.

Slow clap to you Sir.

Memory like an elephant.

Slow clap to you Sir.

Thanks. But, to be fair, I was the one who worked on the problem and wrote the technical bulletin, so it's a little more ingrained in my memory. It was also a very big deal at the time, having many heated discussions with "Lothar".

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

We are having the same problem for date 20100. It affects all tables. It happens if the column is indexed or not indexed. It happens in rlist. Haven't checked btree.extract. We are running OI 9.4.

So the solution is to use external format? OK. That's OK,but not good. What if the external format changes, isn't' that the whole point of external format? It's just a display thing and could change if the client wants it in a new format.

So i would have to read the oconv format from the dictionary before rlist? OK…

In retrospect, it would have made more sense for the index and the data to use the interlay format. But i can see that it's too late to change that now.

So i would have to read the oconv format from the dictionary before rlist? OK…

In retrospect, it would have made more sense for the index and the data to use the interlay format. But i can see that it's too late to change that now.

Josh,

As long as you know it is a date then you can oconv format it how you like as long as the dict iconv is DE

yeah, but let's say that the dictionary is "D", but you oconv it to "DE" in rlist. Will that work. I could this test myself, and i probably will when i have time.

Listing a table via TCL with date = 11/01/23 worked fine. Hence our confusion since the Rlist procedure said "No Records". We then created a symbolic dictionary item with data type = Integer (gets 20100) and used that in the Rlist procedure and that worked fine. Later, because the record ID is two part key we then selected the Employee table in Rlist, read each record, created a new ID (Employee*20100), if it wasn't there we skipped, and we got the same result. Everyone got paid.

yeah, but let's say that the dictionary is "D", but you oconv it to "DE" in rlist. Will that work. I could this test myself, and i probably will when i have time.

My guess is that it would 'americanise' the date if you rlist.

But, if you DE'd it as 11 JAN 2023 and passed it, then it should not interperate as 1 NOV 2023 as I guess it would if you passed 11/01/2023

Aaron,

Thank you for the background. Based on your explanation that:

The system always (and always means without any exception) will apply the Oconv value from the dictionary against the search value.

It appears that the issue of implied date formats could be mitigated in some systems with a lot of legacy internal date selections by modifying the column's date conversions. For instance, given this custom conversion routine:

SUBROUTINE CUSTDATE_CONV(Type, InVal, Branch, ReturnVal)



EQU DEFAULT_DATE_FORMAT To 'D'



// Resolve if we were supplied a format or should use the default

Branch = Trim(Branch)

If Branch = '' Then

	FormatToUse = DEFAULT_DATE_FORMAT

end else

	FormatToUse = Branch

end



if Type = 'ICONV' THEN

		

	// Is this a 5 digit number that starts with 2?

	If Num(InVal) And Len(InVal) EQ 5 and InVal[1,1] = '2' then

		// Yes, so throw an error because it's already inward converted

		Status() = 1

		ReturnVal = InVal

	end else

		// No, assume it's in the default format.

		ReturnVal = ICONV(InVal, FormatToUse)

	end



end else

	// We must be in OCONV

   ReturnVal = OCONV(InVal, FormatToUse)

end



return

By changing the dictionary conversion from D or D2/ to [CUSTDATE_CONV] or [CUSTDATE_D2/] the inward conversion can be a little more selective on what it decides is an internal or external converted date. This would allow internal dates like 20100 and 20101 to remain internal dates.

The only downside I can see is if users expect 20100 to be 2/1/00 or 20101 to be 2/1/01 they'll have to enter their dates with a leading zero or use separators.

This might help buy some time in systems that can't quickly be fixed.

Users shouldn't be entering data that goes into an rlist. Is that rlist injection (like sql injection)?

It isn't so much that users shouldn't be entering data that goes into an RLIST but more an issue of any user data should be sanitized. Yes, it could be possible for a user to inject additional selection criteria into an RLIST and either cause it to return more or less data and fundamentally alter the query. Data that goes into an RLIST parameter from an untrusted source should be checked for things like extra quote marks or delimiters.

View this thread on the Works forum...