Alaska Software Inc. - FILTER & INDEX problem
Username: Password:
AuthorTopic: FILTER & INDEX problem
Damir HodakFILTER & INDEX problem
on Mon, 10 Mar 2014 18:06:00 +0100
Hello!

I have a problem when I use field which is in index expression, in filter
expression and there are no records which meets filter expression. 

Has anybody similar problem? 
Do you have some solution for my problem?


Example:

USE DATA
INDEX ON FIELD1 TO III

SET FILTER TO FIELD1="111111"   value "111111" doesn't exist in DATA
GO TOP
? Eof()                         return FALSE, but it should be TRUE



If value for FIELD1 in filter expressiion exist in DATA, everytihng works
ok.

I use Alaska Xbase++ 1.90.355 SL1 with HotFix RollUp 50 and FOXCDX DBE.


Regards 
Damir
Damir HodakRe: FILTER & INDEX problem
on Tue, 11 Mar 2014 14:44:54 +0100
On Mon, 10 Mar 2014 18:06:00 +0100, Damir Hodak wrote:

> Hello!
> 
> I have a problem when I use field which is in index expression, in filter
> expression and there are no records which meets filter expression. 
> 
> Has anybody similar problem? 
> Do you have some solution for my problem?
> 
> 
> Example:
> 
> USE DATA
> INDEX ON FIELD1 TO III
> 
> SET FILTER TO FIELD1="111111"   value "111111" doesn't exist in DATA
> GO TOP
> ? Eof()                         return FALSE, but it should be TRUE
> 
> 
> 
> If value for FIELD1 in filter expressiion exist in DATA, everytihng works
> ok.
> 
> I use Alaska Xbase++ 1.90.355 SL1 with HotFix RollUp 50 and FOXCDX DBE.
> 
> 
> Regards 
> Damir


Here is an example with test data.

FILTER_EXPAMPLE.zip
James LoughnerRe: FILTER & INDEX problem
on Tue, 11 Mar 2014 14:34:01 -0400
The filter you set in the example does have an existing record.

I do see the problem though if you set a filter on a indexed field for 
an nonexistent value. I'd never do that since scopes are the preferred 
method of filtering on an index and are considerably faster.


But if you do set a filter on an active indexed item you do see an EOF 
problem. If you have no index set on the field it works correctly.


Jim





On 03/11/2014 09:44 AM, Damir Hodak wrote:
> On Mon, 10 Mar 2014 18:06:00 +0100, Damir Hodak wrote:
>
>> Hello!
>>
>> I have a problem when I use field which is in index expression, in filter
>> expression and there are no records which meets filter expression.
>>
>> Has anybody similar problem?
>> Do you have some solution for my problem?
>>
>>
>> Example:
>>
>> USE DATA
>> INDEX ON FIELD1 TO III
>>
>> SET FILTER TO FIELD1="111111"   value "111111" doesn't exist in DATA
>> GO TOP
>> ? Eof()                         return FALSE, but it should be TRUE
>>
>>
>>
>> If value for FIELD1 in filter expressiion exist in DATA, everytihng works
>> ok.
>>
>> I use Alaska Xbase++ 1.90.355 SL1 with HotFix RollUp 50 and FOXCDX DBE.
>>
>>
>> Regards
>> Damir
>
>
> Here is an example with test data.
>
Damir HodakRe: FILTER & INDEX problem
on Wed, 12 Mar 2014 11:20:16 +0100
On Tue, 11 Mar 2014 14:34:01 -0400, James Loughner wrote:

> The filter you set in the example does have an existing record.
> 
> I do see the problem though if you set a filter on a indexed field for 
> an nonexistent value. I'd never do that since scopes are the preferred 
> method of filtering on an index and are considerably faster.
> 
> 
> But if you do set a filter on an active indexed item you do see an EOF 
> problem. If you have no index set on the field it works correctly.
> 
> 
> Jim
> 
> 
> 
> 
> 
> On 03/11/2014 09:44 AM, Damir Hodak wrote:
>> On Mon, 10 Mar 2014 18:06:00 +0100, Damir Hodak wrote:
>>
>>> Hello!
>>>
>>> I have a problem when I use field which is in index expression, in filter
>>> expression and there are no records which meets filter expression.
>>>
>>> Has anybody similar problem?
>>> Do you have some solution for my problem?
>>>
>>>
>>> Example:
>>>
>>> USE DATA
>>> INDEX ON FIELD1 TO III
>>>
>>> SET FILTER TO FIELD1="111111"   value "111111" doesn't exist in DATA
>>> GO TOP
>>> ? Eof()                         return FALSE, but it should be TRUE
>>>
>>>
>>>
>>> If value for FIELD1 in filter expressiion exist in DATA, everytihng works
>>> ok.
>>>
>>> I use Alaska Xbase++ 1.90.355 SL1 with HotFix RollUp 50 and FOXCDX DBE.
>>>
>>>
>>> Regards
>>> Damir
>>
>>
>> Here is an example with test data.
>>


Hi Jim!

The filter I set is SIFRA="10201" and there are no records which meet that
condition.

I also use scopes, but there are situations where I need to use scopes and
filter. In expample I commented out those lines.

Damir
James LoughnerRe: FILTER & INDEX problem
on Wed, 12 Mar 2014 19:36:03 -0400
I use filters with scopes also but I don't filter on the key but on 
non-indexed fields. Scopes are always faster so I scope first then set 
the index since the filter only has to act on a sub set of the records.

Jim



On 03/12/2014 06:20 AM, Damir Hodak wrote:
> On Tue, 11 Mar 2014 14:34:01 -0400, James Loughner wrote:
>
>> The filter you set in the example does have an existing record.
>>
>> I do see the problem though if you set a filter on a indexed field for
>> an nonexistent value. I'd never do that since scopes are the preferred
>> method of filtering on an index and are considerably faster.
>>
>>
>> But if you do set a filter on an active indexed item you do see an EOF
>> problem. If you have no index set on the field it works correctly.
>>
>>
>> Jim
>>
>>
>>
>>
>>
>> On 03/11/2014 09:44 AM, Damir Hodak wrote:
>>> On Mon, 10 Mar 2014 18:06:00 +0100, Damir Hodak wrote:
>>>
>>>> Hello!
>>>>
>>>> I have a problem when I use field which is in index expression, in filter
>>>> expression and there are no records which meets filter expression.
>>>>
>>>> Has anybody similar problem?
>>>> Do you have some solution for my problem?
>>>>
>>>>
>>>> Example:
>>>>
>>>> USE DATA
>>>> INDEX ON FIELD1 TO III
>>>>
>>>> SET FILTER TO FIELD1="111111"   value "111111" doesn't exist in DATA
>>>> GO TOP
>>>> ? Eof()                         return FALSE, but it should be TRUE
>>>>
>>>>
>>>>
>>>> If value for FIELD1 in filter expressiion exist in DATA, everytihng works
>>>> ok.
>>>>
>>>> I use Alaska Xbase++ 1.90.355 SL1 with HotFix RollUp 50 and FOXCDX DBE.
>>>>
>>>>
>>>> Regards
>>>> Damir
>>>
>>>
>>> Here is an example with test data.
>>>
>
>
> Hi Jim!
>
> The filter I set is SIFRA="10201" and there are no records which meet that
> condition.
>
> I also use scopes, but there are situations where I need to use scopes and
> filter. In expample I commented out those lines.
>
> Damir
>
Damir HodakRe: FILTER & INDEX problem
on Thu, 13 Mar 2014 10:03:37 +0100
On Wed, 12 Mar 2014 19:36:03 -0400, James Loughner wrote:

> I use filters with scopes also but I don't filter on the key but on 
> non-indexed fields. Scopes are always faster so I scope first then set 
> the index since the filter only has to act on a sub set of the records.
> 
> Jim
> 
> 
> 
> On 03/12/2014 06:20 AM, Damir Hodak wrote:
>> On Tue, 11 Mar 2014 14:34:01 -0400, James Loughner wrote:
>>
>>> The filter you set in the example does have an existing record.
>>>
>>> I do see the problem though if you set a filter on a indexed field for
>>> an nonexistent value. I'd never do that since scopes are the preferred
>>> method of filtering on an index and are considerably faster.
>>>
>>>
>>> But if you do set a filter on an active indexed item you do see an EOF
>>> problem. If you have no index set on the field it works correctly.
>>>
>>>
>>> Jim
>>>
>>>
>>>
>>>
>>>
>>> On 03/11/2014 09:44 AM, Damir Hodak wrote:
>>>> On Mon, 10 Mar 2014 18:06:00 +0100, Damir Hodak wrote:
>>>>
>>>>> Hello!
>>>>>
>>>>> I have a problem when I use field which is in index expression, in filter
>>>>> expression and there are no records which meets filter expression.
>>>>>
>>>>> Has anybody similar problem?
>>>>> Do you have some solution for my problem?
>>>>>
>>>>>
>>>>> Example:
>>>>>
>>>>> USE DATA
>>>>> INDEX ON FIELD1 TO III
>>>>>
>>>>> SET FILTER TO FIELD1="111111"   value "111111" doesn't exist in DATA
>>>>> GO TOP
>>>>> ? Eof()                         return FALSE, but it should be TRUE
>>>>>
>>>>>
>>>>>
>>>>> If value for FIELD1 in filter expressiion exist in DATA, everytihng works
>>>>> ok.
>>>>>
>>>>> I use Alaska Xbase++ 1.90.355 SL1 with HotFix RollUp 50 and FOXCDX DBE.
>>>>>
>>>>>
>>>>> Regards
>>>>> Damir
>>>>
>>>>
>>>> Here is an example with test data.
>>>>
>>
>>
>> Hi Jim!
>>
>> The filter I set is SIFRA="10201" and there are no records which meet that
>> condition.
>>
>> I also use scopes, but there are situations where I need to use scopes and
>> filter. In expample I commented out those lines.
>>
>> Damir
>>

Hi Jim
What are you doing if you have to "filter" records using two fields. Both
of those two fields are in index keys. I do this like I describe in the
following example:

USE DATA
INDEX ON FIELD1 TO INDEX1
INDEX ON FIELD2 TO INDEX2

SET INDEX TO INDEX1
SET SCOPE TO "111"
SET FILTER TO FIELD2="222"
GO TOP

...



Also, you mentioned that you set filter after setting scope.
Is this better than setting filter first and than scope? Is there a
difference? Have you tested this? 

Regards

Damir
James LoughnerRe: FILTER & INDEX problem
on Thu, 13 Mar 2014 14:34:27 -0400
On 03/13/2014 05:03 AM, Damir Hodak wrote:
> On Wed, 12 Mar 2014 19:36:03 -0400, James Loughner wrote:
>
>> I use filters with scopes also but I don't filter on the key but on
>> non-indexed fields. Scopes are always faster so I scope first then set
>> the index since the filter only has to act on a sub set of the records.
>>
>> Jim
>>
>>
>>
>> On 03/12/2014 06:20 AM, Damir Hodak wrote:
>>> On Tue, 11 Mar 2014 14:34:01 -0400, James Loughner wrote:
>>>
>>>> The filter you set in the example does have an existing record.
>>>>
>>>> I do see the problem though if you set a filter on a indexed field for
>>>> an nonexistent value. I'd never do that since scopes are the preferred
>>>> method of filtering on an index and are considerably faster.
>>>>
>>>>
>>>> But if you do set a filter on an active indexed item you do see an EOF
>>>> problem. If you have no index set on the field it works correctly.
>>>>
>>>>
>>>> Jim
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On 03/11/2014 09:44 AM, Damir Hodak wrote:
>>>>> On Mon, 10 Mar 2014 18:06:00 +0100, Damir Hodak wrote:
>>>>>
>>>>>> Hello!
>>>>>>
>>>>>> I have a problem when I use field which is in index expression, in filter
>>>>>> expression and there are no records which meets filter expression.
>>>>>>
>>>>>> Has anybody similar problem?
>>>>>> Do you have some solution for my problem?
>>>>>>
>>>>>>
>>>>>> Example:
>>>>>>
>>>>>> USE DATA
>>>>>> INDEX ON FIELD1 TO III
>>>>>>
>>>>>> SET FILTER TO FIELD1="111111"   value "111111" doesn't exist in DATA
>>>>>> GO TOP
>>>>>> ? Eof()                         return FALSE, but it should be TRUE
>>>>>>
>>>>>>
>>>>>>
>>>>>> If value for FIELD1 in filter expressiion exist in DATA, everytihng works
>>>>>> ok.
>>>>>>
>>>>>> I use Alaska Xbase++ 1.90.355 SL1 with HotFix RollUp 50 and FOXCDX DBE.
>>>>>>
>>>>>>
>>>>>> Regards
>>>>>> Damir
>>>>>
>>>>>
>>>>> Here is an example with test data.
>>>>>
>>>
>>>
>>> Hi Jim!
>>>
>>> The filter I set is SIFRA="10201" and there are no records which meet that
>>> condition.
>>>
>>> I also use scopes, but there are situations where I need to use scopes and
>>> filter. In expample I commented out those lines.
>>>
>>> Damir
>>>
>
> Hi Jim
> What are you doing if you have to "filter" records using two fields. Both
> of those two fields are in index keys. I do this like I describe in the
> following example:
>
> USE DATA
> INDEX ON FIELD1 TO INDEX1
> INDEX ON FIELD2 TO INDEX2
>
> SET INDEX TO INDEX1
> SET SCOPE TO "111"
> SET FILTER TO FIELD2="222"
> GO TOP
>
> ...
>
>
>
> Also, you mentioned that you set filter after setting scope.
> Is this better than setting filter first and than scope? Is there a
> difference? Have you tested this?
>
> Regards
>
> Damir
>

I usually have a compound index on both fields thus can use scope. But 
if you must turn off Rushmore

Jim
Andreas Gehrs-Pahl
Re: FILTER & INDEX problem
on Tue, 11 Mar 2014 16:32:17 -0400
Damir,

>I have a problem when I use field which is in index expression, in filter
>expression and there are no records which meets filter expression. 

This is a side-effect of the "Rushmore" technology that was introduced in 
Xbase++ Version 1.70.267 and can be disabled by one of two settings, either:

Set Rushmore Off

and/or:

Set Optimize Off

Those settings are Thread-Local and DBO-specific and can be changed during 
runtime. To keep Optimization enabled, I would recommend to just use the 
"Set Rushmore Off" setting in your case. As long as you do this before 
creating your Filter (with "DbSetFilter()"), the results for EoF() etc. 
will be the same as if you don't have an Index. You could even re-enable 
Rushmore right after you have created the Filter, if you like, because the 
DBO keeps track of the setting for the filter internally.

So, changing your code to this should work:

Set RushMore Off
DbSetFilter(&("{||" + cFilter + "}"), cFilter)
Set RushMore On

The reason for this is that with Rushmore and Optimize enabled -- which is 
the default setting -- all Filter (etc.) expressions that can benefit from 
an Index expression will be evaluated on the Index alone, rather than on the 
actual database fields. This works for both CDX and NTX Order Components.

I don't know if this inconsistent EoF() behavior qualifies as a "Defect" or 
as a "Feature", but I would suggest you ask Alaska for a statement (and/or 
a PDR, if it is considered a defect). At least the docs could be updated, 
to indicate the different behavior.

Hope that helps,

Andreas
Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas.GP@Charter.net
web:   http://www.Aerospace-History.net
Damir HodakRe: FILTER & INDEX problem
on Wed, 12 Mar 2014 11:41:13 +0100
On Tue, 11 Mar 2014 16:32:17 -0400, Andreas Gehrs-Pahl wrote:

> Damir,
> 
>>I have a problem when I use field which is in index expression, in filter
>>expression and there are no records which meets filter expression. 
> 
> This is a side-effect of the "Rushmore" technology that was introduced in 
> Xbase++ Version 1.70.267 and can be disabled by one of two settings, either:
> 
> Set Rushmore Off
> 
> and/or:
> 
> Set Optimize Off
> 
> Those settings are Thread-Local and DBO-specific and can be changed during 
> runtime. To keep Optimization enabled, I would recommend to just use the 
> "Set Rushmore Off" setting in your case. As long as you do this before 
> creating your Filter (with "DbSetFilter()"), the results for EoF() etc. 
> will be the same as if you don't have an Index. You could even re-enable 
> Rushmore right after you have created the Filter, if you like, because the 
> DBO keeps track of the setting for the filter internally.
> 
> So, changing your code to this should work:
> 
> Set RushMore Off
> DbSetFilter(&("{||" + cFilter + "}"), cFilter)
> Set RushMore On
> 
> The reason for this is that with Rushmore and Optimize enabled -- which is 
> the default setting -- all Filter (etc.) expressions that can benefit from 
> an Index expression will be evaluated on the Index alone, rather than on the 
> actual database fields. This works for both CDX and NTX Order Components.
> 
> I don't know if this inconsistent EoF() behavior qualifies as a "Defect" or 
> as a "Feature", but I would suggest you ask Alaska for a statement (and/or 
> a PDR, if it is considered a defect). At least the docs could be updated, 
> to indicate the different behavior.
> 
> Hope that helps,
> 
> Andreas

Hi Andreas!
Thank you for your sugestion. It works perfectly, except I don't know how
it would reflect on speed.

I sent email to Alaska and they replied "Thank you very much for this
notification" ?!

I investigated a little bit more my problem. It seems that program behaves
like filter is set to SIFRA > "11111" instead SIFRA = "11111" if there are
no records where SIFRA = "11111".
Also this happens only if there is index with key SIFRA. If I create index
with key SIFRA+GRUPA_PR it works ok.

Regards

Damir