FWD » Core Development » Database

The following change drove down the change in IndexSelectionWorker from trunk/ rev. 15486:

for each stat1:
   delete stat1.
end.

define buffer bStat2 for stat1.

create stat1.
stat1.f1 = "abc".
stat1.f2 = "bcd".
stat1.f3 = 10.                                                                            

define var hq as handle.
create query hq.
hq:set-buffers(buffer bStat2:handle).
hq:query-prepare("for each bStat2 where bStat2.f1 = 'abc' and bStat2.f2 = 'bcd' and bStat2.f3 = 10").
hq:query-open().
hq:get-first().
message hq:index-information(). // secondary
message avail(bStat2). // yes

stat table has the following structure:

ADD TABLE "stat1" 
  AREA "Schema Area" 
  DUMP-NAME "stat1" 

ADD FIELD "f1" OF "stat1" AS character 
  FORMAT "x(8)" 
  INITIAL "" 
  POSITION 2
  MAX-WIDTH 16
  ORDER 10

ADD FIELD "f2" OF "stat1" AS character 
  FORMAT "x(8)" 
  INITIAL "" 
  POSITION 3
  MAX-WIDTH 16
  ORDER 20

ADD FIELD "f3" OF "stat1" AS integer 
  FORMAT "->,>>>,>>9" 
  INITIAL "0" 
  POSITION 4
  MAX-WIDTH 4
  ORDER 30

ADD FIELD "f4" OF "stat1" AS integer 
  FORMAT "->,>>>,>>9" 
  INITIAL "0" 
  POSITION 5
  MAX-WIDTH 4
  ORDER 40

ADD FIELD "f5" OF "stat1" AS integer 
  FORMAT "->,>>>,>>9" 
  INITIAL "0" 
  POSITION 6
  MAX-WIDTH 4
  ORDER 50

ADD INDEX "main" ON "stat1" 
  AREA "Schema Area" 
  UNIQUE
  PRIMARY
  INDEX-FIELD "f1" ASCENDING 
  INDEX-FIELD "f2" ASCENDING 
  INDEX-FIELD "f3" ASCENDING 
  INDEX-FIELD "f4" ASCENDING 
  INDEX-FIELD "f5" ASCENDING 

ADD INDEX "secondary" ON "stat1" 
  AREA "Schema Area" 
  INDEX-FIELD "f1" ASCENDING 
  INDEX-FIELD "f2" ASCENDING 
  INDEX-FIELD "f3" ASCENDING 

As you can see the stat table has 2 indices with a common prefix (f1, f2 and f3), also secondary index is a non-unique index. And from the test case, index selected in OE is the secondary index.

Where clause wwhere bStat2.f1 = 'abc' and bStat2.f2 = 'bcd' and bStat2.f3 = 10 will not match any unique, since it lacks the f4 = ? and f5 =? suffix.
Failing to find an unique index the code will proceed with findLeadingIndexMatch method. This is where my changes are, so I don't think that with this change we are bypassing the logic in findUniqueIndexMatch in any way.

In findLeadingIndexMatch first we select indices that have the most equalityMatches with the where clause. If we still have multiple indices with the same number of equalityMatches matches, we proceed with beginsMatches and rangeMatches matches.

The essence of my changes is that in case we still have multiple indices with the same number of matches after all this verification, to additionally verify if any of the indices are matched completely by the where clause. In this way we break the tie between all possible indices.

Feel free to add any suggestions or test cases that you have.

Looking at calcIndexSelection it will always return a single index. Do you know whether that is intentional or selection of multiple indices is unimplemented yet?

• Supporting single-index selection is a tricky thing and inferring the rules for multiple indices is way more complex, especially as there is no strong documentation from 4GL side on the OE DB planner. The historic reasons AFAIK is that prior to OE x.y (i don't recall the x and y), only single-index selection existed. After x.y, multi-selection was implemented, but you could still toggle the legacy selection using a special flag (I think Andrei [ap] recalls what was that flag). Anyway, FWD is meant to run-time support the applications that used that flag to enable single-index selection from legacy environments.
• The value of supporting multi-index selection is quite low IMHO. This takes effect only on INDEX-INFORMATION and BY clause. INDEX-INFORMATION is quite irrelevant considering that FWD uses a dedicated SQL DB that does its own index selection, so this is just a legacy information that doesn't have a critical impact - except for unit tests that check the query performance (i.e. large customer unit tests). On the other hand, multi-selection index might change functionality when inferring the BY, but I didn't saw such problem in converted applications yet.

I comitted my change on 9267a, rev. 15522.

With this change, in findLeadingIndexMatch in case we have multiple indices those prefix was matched the where clause. We will break the tie between them by selecting those indices, that has been matched fully by the where clause.

I converted a large customer GUI application, while it indeed showed multiple difference in BY clause. I tested unittests and it showed no regression.
I looked at some differences between the result and a recent baseline, a bit of analysis on smaller examples in OE showed that indeed index selected should be the one from 9267a conversion.
I posted the conversion results on devsrv01:/tmp/src_9267.zip.

I also converted and run regression tests project, I do get around 5 tests that fails. But I am not sure whether those are from my conversion changes since even the baseline has some flows and a few tests randomly fails.
THe results of those I could also post on devsrv01:/tmp/src_9267.zip.

Another huge customer application application has been thoroughly tested with a slightly different version on 9267a, and no regression there was spotted. But this new modification should also be tested.

Eduard, can you augment the xfer testcases on index_selection with some of your findings and test that as well: make sure trunk fail with that and 9267a works.

Alexandru Lungu wrote:

Eduard, can you augment the xfer testcases on index_selection with some of your findings and test that as well: make sure trunk fail with that and 9267a works.

Sure.

If needed, there is also this wiki page Index Selection. In there, perhaps the most interesting part is the order of rules by which an index is selected (it's in the Single Index Selection Rules sub-chapter).

Radu, let me do you one better: https://proj.goldencode.com/artifacts/javadoc/latest/api/com/goldencode/p2j/schema/package-summary.html#Determining_Query_Sorting_ (Determining Query Sorting section) - for a real documentation passionate.

Added my testcases on tests/query/index/IndexSelectionExactMatching.cls.

Out of 13 tests:

- 6 test fails with trunk, revision 15524.

- 3 tests fails with 9267a, looking a bit into them it seems that there is a problem with findSortIndexMatch method in IndexSelectionWorker (but that is besides the scope of this task).

Also tried testcases in tests/query/index with 9267a and no regressions found there.

It seems that 9267a fixes my issue from #8357-140. Eduard, if you want, I can post here a small testcase with what I was facing.

Sure, go ahead.

Radu Apetrii wrote:

The point here is that idx2 and the WHERE clause share a prefix of fields, but they are not fully matched. However, with idx3 there is a perfect match of fields between the index and the where clause.

Yes, that is exactly the testcase for which this task has been created.

Eduard Soltan wrote:

Please review 9267a, rev. 15522.

I have tests with regression changes project, and a large customer GUI app. The result of GUI app conversion is pretty different, but the unit tests are fine.
But created some testcases in IndexSelectionExactMatching.cls, to prove that this is the behaviour in 4gl.

Ovidiu, could you please review?

If needed you could also compare GUI app sources converted with 9267a: devsrv01:/tmp/src_9267.zip with the baseline devsrv01:/tmp/src_baseline.zip.

Review of 9267a, r15522.

The changes do make sense. Please add the H entry.

I did analyse at the sources posted on devsrv01. Definitely, the ones generated by 9267a look better. However, at the end of Qry0212.java I saw something which was debatable, so I wrote the following isolated testcase:

define temp-table t1
   field f1 as int
   field f2 as int
   field f3 as int
   index i1 as primary unique f1 f2
   index i2 as unique f2 f1
   index i3 f3.

create t1. f1 = 1. f2 = 13. f3 = 99.
create t1. f1 = 1. f2 = 11. f3 = 98.
 create t1. f1 = 4. f2 = 10. f3 = 101.
 create t1. f1 = 2. f2 = 10. f3 = 101.
create t1. f1 = 3. f2 = 12. f3 = 100.

define variable k as logical.
k = can-find(find first t1 where f2 eq 10 and f3 eq 101).
message k.

find first t1 where f2 eq 10 and f3 eq 101.
message f1.

Ovidiu Maxiniuc wrote:

I did analyse at the sources posted on devsrv01. Definitely, the ones generated by 9267a look better. However, at the end of Qry0212.java I saw something which was debatable, so I wrote the following isolated testcase: In the can-find the 9267a chooses i3 index as opposed to baseline which picks i2. However, since we are in a can-find, we can extract the query and print the difference: The result is 2, meaning that f1 is part of the index used in find first. Switching f1 = 2 with f1 = 5 will result in 4 being printed! So i3 is not what we are looking for.

I see your point.

I played a bit with your example and noticed some interesting behaviour. Lets take following program as a base, and test 2 different 4gl constructs to test index selection.

   field f1 as int
   field f2 as int
   field f3 as int
   field f4 as int
   index i1 as primary unique f1 f2
   index i2 as unique f2 f1
   index i3 f3.

create t1. f1 = 1. f2 = 13. f3 = 99.
create t1. f1 = 1. f2 = 11. f3 = 98.
create t1. f1 = 4. f2 = 10. f3 = 101.
create t1. f1 = 2. f2 = 10. f3 = 101.
create t1. f1 = 3. f2 = 12. f3 = 100.

1) If we use a find, then your statement indeed hold and the i2 index is used.

find first t1 where f2 = 10 and f3 = 101.
message f1. // 2

2) but if a for first statement is used then index selected would be i3, since the output would be 4.

for first t1 where f2 = 10 and f3 = 101:
   message f1. // 4
end.

I made some changes to reflect behaviour from #9267-20 in 9267a, rev. 15523. Please review.

Eduard Soltan wrote:

I made some changes to reflect behaviour from #9267-20 in 9267a, rev. 15523. Please review.

I'm gonna assume that I need to re-run the conversion for that customer application. I've already done it last night (with 9267a rev. 15522), and I didn't encounter any errors, but if it's needed again, I'll queue it up.

Radu Apetrii wrote:

I'm gonna assume that I need to re-run the conversion for that customer application. I've already done it last night (with 9267a rev. 15522), and I didn't encounter any errors, but if it's needed again, I'll queue it up.

Yes, a re-conversion is required. I already converted regression tests project, in order to test if conversion does not break. And if the review goes well, I am going to run conversion of another customer GUI app.

Made the necessary changes on 9267a, revision 15524.

I will redo the conversion of a customer application this evening.

Conversion of a customer GUI application finished successfully. Also tested unit tests, with no regression.

Pushed the new sources on devsrv01:/tmp/src_9267a_15530.zip.

I finished testing another customer application. The result is positive, everything went fine. ✅

I finished testing another customer application after reconversion. Results of unit tests and smoke testing GUI are positive. ✅

Eduard Soltan wrote:

Conversion of a customer GUI application finished successfully. Also tested unit tests, with no regression.
Pushed the new sources on devsrv01:/tmp/src_9267a_15530.zip.

The new revision reverted some of the changes from 15529. I looked at some of them and they seem correct.

Eduard: I got informed by a customer of a query that uses the wrong index now (with the 9267a changes), and was using the right index before (so without the 9267a changes). I'll investigate that a bit, and I'll post here a testcase as soon as I have one.

OK, so, here's the testcase:

Table definition: ShowHide

ADD TABLE "test" 
  AREA "Schema Area" 
  DUMP-NAME "test" 

ADD FIELD "d1" OF "test" AS date 
  FORMAT "99/99/99" 
  INITIAL ?
  POSITION 6
  MAX-WIDTH 4
  ORDER 10

ADD FIELD "c1" OF "test" AS character 
  FORMAT "x(8)" 
  INITIAL "" 
  POSITION 7
  MAX-WIDTH 16
  ORDER 20

ADD FIELD "c2" OF "test" AS character 
  FORMAT "x(8)" 
  INITIAL "" 
  POSITION 8
  MAX-WIDTH 16
  ORDER 30

ADD FIELD "c3" OF "test" AS character 
  FORMAT "x(8)" 
  INITIAL "" 
  POSITION 9
  MAX-WIDTH 16
  ORDER 40

ADD INDEX "primary" ON "test" 
  AREA "Schema Area" 
  PRIMARY
  INDEX-FIELD "c1" ASCENDING 
  INDEX-FIELD "c2" ASCENDING 

ADD INDEX "idx1" ON "test" 
  AREA "Schema Area" 
  INDEX-FIELD "d1" ASCENDING 
  INDEX-FIELD "c1" ASCENDING 
  INDEX-FIELD "c3" ASCENDING 

ADD INDEX "idx2" ON "test" 
  AREA "Schema Area" 
  INDEX-FIELD "d1" ASCENDING 

The query:

for each test where d1 = today - 1:

Here, Progress and FWD trunk choose idx1. With the 9267a changes, idx2 is selected.

Radu Apetrii wrote:

Here, Progress and FWD trunk choose idx1. With the 9267a changes, idx2 is selected.

I saw something similar previously, but to be honest I didn't quite understand the internal logic of index selection in 4GL for this case.

But think I find a rule which make sense to me:
Spouse we have where clause with just one field check and checked field is the prefix of multiple indexies: for each tt1 where c2 = 'abc'

define temp-table tt1
       field c1 as char
       field c2 as char
       field c3 as char
       field c4 as char
       field c5 as char
       index main is primary unique c1 c2 c3
       index idx1 c2 c3 
       index idx2 c2.

define var hq as handle.
define var indexUsed as char.

create query hq.
hq:set-buffers(buffer tt1:handle).
hq:query-prepare("for each tt1 where c2 = 'abc'").
hq:query-open().
indexUsed = hq:index-information().

message indexUsed. // idx1

Please note the order of the indices definition. It is selected the first defined.

define temp-table tt1
       field c1 as char
       field c2 as char
       field c3 as char
       field c4 as char
       field c5 as char
       index main is primary unique c1 c2 c3
       index idx2 c2
       index idx1 c2 c3. 

define var hq as handle.
define var indexUsed as char.

create query hq.
hq:set-buffers(buffer tt1:handle).
hq:query-prepare("for each tt1 where c2 = 'abc'").
hq:query-open().
indexUsed = hq:index-information().

message indexUsed. // idx2

However if the where clause is just slightly changed, for example a another field check where c2 = 'abc' and c4 = 'tre' (such that a prefix of another index is not formed) or even something like this where c2 = 'abc' and yes. In that case idx2 is selected no matter of the definition order.

There are documented rules for index selection here: Chapter_27_Sorting_Query_Results

We should update this docs with any changes in index selection.

Constantin Asofiei wrote:

There are documented rules for index selection here: Chapter_27_Sorting_Query_Results

Based on this, let me try to explain what's happening in #9267-33.

• main doesn't have a common prefix with the where clause.
• idx1 and idx2 have exactly one leading component matching the where clause: c2. Because of this, the main index is taken off from the "selectable" index list, as it has less components matching than the other two indexes.

Further on, we continue analyzing Rule 4 for the two indexes in the "selectable" list (idx1 and idx2) because we are in the If more than one index has the same number of leading index components in equality matches, then this reduced list of selectable indexes further refined: case. The first bullet from Rule 4 doesn't apply since we don't have a begins clause. In the same manner, no index is chosen via the second bullet, as we don't have range matches. We then proceed to Rule 6, as stated in the second bullet from Rule 4.

• First idx1 and then idx2, then idx1 is chosen.
• First idx2 and then idx1, then idx2 is chosen.

Does this mean that the order in which we define the indexes is the tie-breaker here? If so, when do we take the alphabetical order, and when do we take the definition order?

Radu Apetrii wrote:

Does this mean that the order in which we define the indexes is the tie-breaker here? If so, when do we take the alphabetical order, and when do we take the definition order?

I would say that in this case order as declaration of the index takes precedence over the alphabetical order, at least from the tests that I performed. But I think that this happens in trunk also, not just in 9267a. So I think that this is beyond the scope of this task.

But that is weird, plase se the following example:

define temp-table tt1
       field c1 as char
       field c2 as char
       field c3 as char
       field c4 as char
       field c5 as char
       index main is primary unique c1 c2 c3
       index idx2 c2 c3 c4
       index idx1 c2 c3.

def var ch as char.
ch = 'abf'.

define var hq as handle.
define var indexUsed as char.

create query hq.
hq:set-buffers(buffer tt1:handle).

hq:query-prepare("for each tt1 where c2 = 'abc' and c3 = 'res'").
indexUsed = hq:index-information().

message indexUsed.

You could see that it is very similar to the cases in #9267-31 or #9267-33.

where clause of for each tt1 where c2 = 'abc' and c3 = 'res'" query has 2 indeces with the same prefix(idx1 and idx2). Just as we had in #9267-33, with the exection that now the prefix is a bit longer c1 c2.

But now, it does not matter the order in which the indices will be defined. This time idx1 (c2 c3) will always be selected.

Radu Apetrii wrote:

Does this mean that the order in which we define the indexes is the tie-breaker here? If so, when do we take the alphabetical order, and when do we take the definition order?

OK, an update to this: I believe the alphabetical order is for the "static" index definitions, or in other words, the ones that come from the .df files. For example, I used a .df file for table in which the indexes were defined as idx1 and then idx2, and another .df file in which the order was reversed, idx2 and then idx1. When doing the query, both used idx1 as this is alphabetically before idx2. So I can say for sure that Rule 8 is alright.

Now, on the other hand, when the indexes are defined in a more "dynamical" way, like they are in the examples from #9267-33 (i.e. directly into the procedure file), the alphabetical order does not seem to matter. Instead, the definition order is taken into consideration (i.e. which was written first, idx1 or idx2). I'm not exactly sure in this moment how we can achieve this, but in the meantime, please disagree with me on anything you see wrong.

I've also added Eric as a watcher to this discussion. I don't know why he wasn't here in the first place.

OK, an update to this: I believe the alphabetical order is for the "static" index definitions, or in other words, the ones that come from the .df files. For example, I used a .df file for table in which the indexes were defined as idx1 and then idx2, and another .df file in which the order was reversed, idx2 and then idx1. When doing the query, both used idx1 as this is alphabetically before idx2. So I can say for sure that Rule 8 is alright.

I think you are referring to persistent database vs temporary database (as opposed to statics vs dynamic).
Also, you are bringing up a good point: the persistent tables are only static (defined only once prior to the building of the project), but the temporary tables can be either static (defined in the procedure) or dynamic (with create temp-table) whose schema is built up at run-time. I wonder in this case what will be the index selection (mind the add-index function for temp-table handles).

Alexandru Lungu wrote:

OK, an update to this: I believe the alphabetical order is for the "static" index definitions, or in other words, the ones that come from the .df files. For example, I used a .df file for table in which the indexes were defined as idx1 and then idx2, and another .df file in which the order was reversed, idx2 and then idx1. When doing the query, both used idx1 as this is alphabetically before idx2. So I can say for sure that Rule 8 is alright.

I think you are referring to persistent database vs temporary database (as opposed to statics vs dynamic).
Also, you are bringing up a good point: the persistent tables are only static (defined only once prior to the building of the project), but the temporary tables can be either static (defined in the procedure) or dynamic (with create temp-table) whose schema is built up at run-time. I wonder in this case what will be the index selection (mind the add-index function for temp-table handles).

I think we're going to need an example for each of these cases. If Eduard is not available right now, I can do these relatively quickly.

Radu Apetrii wrote:

I think we're going to need an example for each of these cases. If Eduard is not available right now, I can do these relatively quickly.

I can do that also.

Eduard Soltan wrote:

Radu Apetrii wrote:

I think we're going to need an example for each of these cases. If Eduard is not available right now, I can do these relatively quickly.

I can do that also.

• Eduard: create the testcases for Alex's #9267-39 note. While I agree that this is a bit beyond the scope of this task, I don't want to break the current logic (a.k.a. index selection), as I know it will affect one of our customers.
• Radu: I'll look into #9267-36 and see what I can deduce.

Radu Apetrii wrote:

OK, then, if you agree, we'll split the work as following:

• Eduard: create the testcases for Alex's #9267-39 note. While I agree that this is a bit beyond the scope of this task, I don't want to break the current logic (a.k.a. index selection), as I know it will affect one of our customers.

Sound like a good plan.

• Radu: I'll look into #9267-36 and see what I can deduce.

If you need more assistance on it, just ask. I already sketched some TRPL code to fix this case.

Eduard Soltan wrote:

• Radu: I'll look into #9267-36 and see what I can deduce.

If you need more assistance on it, just ask. I already sketched some TRPL code to fix this case.

Oh, that's great then. As long as we have the cases talked in this task covered, I'm good.

Radu Apetrii wrote:

Oh, that's great then. As long as we have the cases talked in this task covered, I'm good.

I committed on 9267a, rev. 15531. I am doing now the conversion for regression test project, and it now passed Code Core Annotations phase. And didn't break, so I think changes are save.

Later toady I will start conversion of a customer GUI application.

Radu Apetrii wrote:

OK, then, if you agree, we'll split the work as following:

• Eduard: create the testcases for Alex's #9267-39 note. While I agree that this is a bit beyond the scope of this task, I don't want to break the current logic (a.k.a. index selection), as I know it will affect one of our customers.

Added testcases for index swlection order tests/query/index/IndexSelectionOrder.cls.

I didn't add test for persistence tables, as a I struggled a bit to add a database connection to UnitTest project in OE.
But it seems that #9267-44 image is right, for persistence tables index is chosen in alphabetically order and for temp-table definition order is the key (no matter static or dynamic defined).

Eduard Soltan wrote:

Radu Apetrii wrote:

Oh, that's great then. As long as we have the cases talked in this task covered, I'm good.

I committed on 9267a, rev. 15531. I am doing now the conversion for regression test project, and it now passed Code Core Annotations phase. And didn't break, so I think changes are save.

Later toady I will start conversion of a customer GUI application.

Ovidiu, could you do a review?

Eduard Soltan wrote:

I committed on 9267a, rev. 15531.

Eduard, are you sure the branch was bound when you committed the changes? My 9267a only goes to revision 15524.

I checked the devsrv01, the last revision of 9267a is indeed 15524, from 2024-10-30.

Sorry, my branch was unbind. I pushed on 9267a, rev. 15524.

I committed some of my changes regarding index selection on 9267a, rev. 15526.

The changes are quite minimal, and handle the issue when the WHOLE-INDEX flag was leaked to the selected index.
The changes have been reviewed and passed all the tests provided by a large customer application.

I looked at 9267a, rev. 15525/6, but I have some difficulties understanding the logic. I will go deeper tomorrow.

• remove had tab characters (index_selection.rules, lines 442-454) and fix indents to 3 chars
• add javadocs for the new isSimpleWhereClause field and chageSimpleWhereClause() method;
• getChildAt(0) can be replaced with the faster firstChild;
• can expression (of where clause) have multiple children?

Ovidiu Maxiniuc wrote:

I looked at 9267a, rev. 15525/6, but I have some difficulties understanding the logic. I will go deeper tomorrow.

• can expression (of where clause) have multiple children?

I don't know for sure. As the where clause is a logical expression, my intuition says that it will have just one child. And from all the testing that I have done, it always had one child.

The aim of the added logic is to detect the cases of simple where clauses (where buffer.t1 = ?). And if such case is detected, the logic added in findLeadingComponent is bypassed.

Committed on 9267a, rev. 15541.

Tested regression test projects and a large customer application unit test. Sources are on devsrv01:/tmp/src_15531.zip.

Added tests cases for general index selection rules on tests/query/index/MainIndexTests.cls.

Eduard Soltan wrote:

Committed on 9267a, rev. 15541.

Well, my 9267a only goes to revision 15527. Are we facing another "unbound" issue or did I do something wrong locally? Still, the testing on another customer application was successful with 9267a rev. 15527. I hope the only differences between rev. 15527 and 15541 are the commits that came from rebasing with trunk.

Eduard Soltan wrote:

- another GUI app, Radu did the conversion and query report. No regression.

It was reported from the customer that the index changes were good, at they now match Progress's index selection.

Alex/Eric/Greg: So, are we waiting for something else, or can this be merged into trunk?

Radu Apetrii wrote:

Eduard Soltan wrote:

- another GUI app, Radu did the conversion and query report. No regression.

It was reported from the customer that the index changes were good, at they now match Progress's index selection.

By this I meant that the queries affected by index changes now use the correct index (the same one as in Progress), instead of the old one from FWD.

Alex/Eric/Greg: So, are we waiting for something else, or can this be merged into trunk?

I understand that testing was done, but some Review will also be required. I will try to do this by EOD and if everything looks right, we can merge this.

Branch 9267a was merged into trunk revision 15561.

Updated Chapter_27_Sorting_Query_Results, with new index selection rule (4.1).

I found another case of incorrect index selection. As Eduard and Radu already engaged in this topic through #9267, I will add the test-case here:

def temp-table tt no-undo
    field f1 as int
    field f2 as int

    index idx2 f2 desc
    index idx1 f1 asc.

do transaction:
    create tt. tt.f1 = 1. tt.f2 = 4.
    create tt. tt.f1 = 3. tt.f2 = 2.
    create tt. tt.f1 = 4. tt.f2 = 4.
    create tt. tt.f1 = 6. tt.f2 = 3.
end.

do transaction:
    find next tt where f2 = 5 no-error.
    message avail(tt). // no
    find prev tt no-error. // 4GL idx2 is used; FWD: idx1 is used
    message avail(tt) . // 4GL: no; FWD: yes (this happens in 9030b and maybe trunk - the point is that find prev should be PREV on idx2, not idx1)
end.

From my understanding, FIND PREV should have used the first index defined (idx2), but it chooses to use idx1. Maybe this example can be rewritten with OPEN QUERY, to make a proper standalone.

I think it related to the way how index is selected in temp-tables, when the tie between 2 indices that satisfy all conditions can not be broken. In this case in 4gl the index is selected by the order of definition of the index. In fwd it still selected the index in lexicographical order.

I am just guessing at this point, but I think that find next and find prev are not related in any way. And for each statement index is calculated separately. But I will look more closely at it.

I think it makes sense: the first find next had a f2 = 5, therefore idx2(f2) was the logical choice. The simpler find prev picks the row 'stream' from where the previous navigation left, using the existing order, decided at the moment of the previous find with a predicate.

• with predicate. They will have an index associated reflected in the sort parameter and will create the cursor for subsequent queries;
• simple (predicated-less). These queries will not have an index/sort parameter imposed at conversion time. Instead, they pick the cursor from the previous find.
  If no query was executed before, the index is decided at runtime (usually the primary or effective primary).

The above requires that the current cursor is stored in the buffer instead of query. I do not expect temp-tables to behave different than permanent tables.

I think it makes sense: the first find next had a f2 = 5, therefore idx2(f2) was the logical choice. The simpler find prev picks the row 'stream' from where the previous navigation left, using the existing order, decided at the moment of the previous find with a predicate.

I don't think this is correct. If I am defining the indexes in a different order idx1 first and then idx2, then the result is different and a tt will be actually found. Thus, it is not about "inheriting" the index from the last FIND.

Alexandru Lungu wrote:

I don't think this is correct. If I am defining the indexes in a different order idx1 first and then idx2, then the result is different and a tt will be actually found. Thus, it is not about "inheriting" the index from the last FIND.

I think that this confirm that it is related to unimplemented logic for index selection by definition order.

Alexandru Lungu wrote:

I don't think this is correct. If I am defining the indexes in a different order idx1 first and then idx2, then the result is different and a tt will be actually found. Thus, it is not about "inheriting" the index from the last FIND.

Well, I did not put it to the test, just extrapolating and emitting a conjecture based on your reported results. This is new information. So the first defined index (effective primary) is used? I thought we already doing it this way.