Project

General

Profile

Bug #8878

Jumble sort dictates a full preselect

Added by Artur Școlnic 19 days ago. Updated 1 day ago.

Status:
Internal Test
Priority:
Low
Assignee:
Artur Școlnic
Target version:
-
Start date:
Due date:
% Done:

100%

billable:
No
vendor_id:
GCD
case_num:
version_reported:
version_resolved:

History

#1 Updated by Eric Faulhaber 19 days ago

  • Project changed from FWD to Database
  • Description updated (diff)
  • Parent task deleted (#8797)
  • case_num deleted (8873)

Original report:

When jumble sort is detected, all queries in a joined query are converted as PreselectQuery. The rule that dictates this behavior is in rules/annotations/preselect_prep.rules line 191. This rule can lead to choosing the wrong query type. For example if there are queries that use first or last clause in a joined query and it is sorted by multiple fields, FWD will convert all individual queries as PreselectQuery instead of RandomAccesQuery for the ones that use first or last clause. The same is true for queries that use each and should be AdaptiveQuery.

#2 Updated by Artur Școlnic 16 days ago 

DEFINE QUERY q001 FOR
     p_table_1 FIELDS (id number),
     p_table_2 FIELDS (id number),
     p_table_3 FIELDS (id number).

OPEN QUERY q001 FOR 
     EACH p_table_1 WHERE p_table_1.id = 1,
     FIRST p_table_2 OUTER-JOIN WHERE p_table_2.id = p_table_1.id,
     FIRST p_table_3 OUTER-JOIN WHERE p_table_3.number = p_table_1.number
     by p_table_2.id by p_table_1.number.
get first q001.
DO WHILE AVAILABLE gl:
  DISPLAY p_table_1.number.
  GET NEXT q001.
END.

The query above is converted as a CompoundQuery with 3 components of PreselectQuery type.

#3 Updated by Alexandru Lungu 16 days ago

Artur, you found recently that using EACH for each component is also generating a PreselectQuery. I think that this is right, as BY clauses are triggering an in-memory sort on the 4GL side. Therefore, it is normal to have them preselected in the first place.

#4 Updated by Alexandru Lungu 16 days ago

  • Status changed from New to WIP
  • Assignee set to Artur Școlnic

Created 8878a. Please commit your changes to fix this on 8878a.

#5 Updated by Artur Școlnic 16 days ago

  • Priority changed from Normal to Low

I have done some more testing using various queries and I think you are right, however I found a case that I want to discuss, maybe I am wrong, bu it's worth mentioning.

DEFINE QUERY q001 FOR
     p_table_1 FIELDS (id number),
     p_table_2 FIELDS (id number),
     p_table_3 FIELDS (id number).

OPEN QUERY q001 FOR 
     EACH p_table_1 WHERE p_table_1.id = 1,
     EACH p_table_2 OUTER-JOIN WHERE p_table_2.id = p_table_1.id,
     EACH p_table_3 OUTER-JOIN WHERE p_table_3.number = p_table_1.number
     by p_table_1.number by p_table_2.number.
get first q001.
DO WHILE AVAILABLE p_table_1:
  DISPLAY p_table_1.number.
  GET NEXT q001.
END.

The query above is converted as a CompoundQuery with 3 components:
  • PreselectQuery
  • PreselectQuery
  • AdaptiveQuery

Shouldn't it be a PresortCompoundQuery with 3 PreselectQuery components?

#6 Updated by Alexandru Lungu 16 days ago

I suppose that the BY affects only the first two table sorting, whereas the third component does not have an imposed sorting order. That is why the third component is dynamically retrieving its records.

#7 Updated by Artur Școlnic 16 days ago

Then we can consider that only the first/last queries are converted incorrectly for now.

#8 Updated by Artur Școlnic 16 days ago

I committed the changes for jumble sort rule to 8878a/15285.

#9 Updated by Alexandru Lungu 15 days ago

  • Status changed from WIP to Review
  • % Done changed from 0 to 100

Eric/Ovidiu: please review.

#10 Updated by Ovidiu Maxiniuc 15 days ago

Review of 8878a/15285.

The only modified file is preselect_prep.rules:
  • lines 64-65: there are some hard-tabs (\t, char #09). They must be replaced with space (char #20). Also the ** padding at the left is missing;
  • lines 193-194: there are some dashes (-) at the beginning of the lines which will make the file unusable.

But I think that the idea is good: the FIRST / LAST sub-queries should not force the components to be PreselectQuery. These are 'single-row-result' queries (or possibly none, in case of OUTER-JOIN), so there is nothing to sort.

#11 Updated by Artur Școlnic 13 days ago

I addressed the formatting issues and committed 8878a/15286, awaiting further instructions for testing.

#12 Updated by Constantin Asofiei 12 days ago

ETF conversion shows no changes with 8878a

#13 Updated by Artur Școlnic 7 days ago

  • Status changed from Review to Internal Test

Should I do some more testing for 8878a?

#14 Updated by Alexandru Lungu 2 days ago

Artur, I think 8878a should be conversion tested:

  • Re-convert a large customer application (create a source baseline and check if there are static queries that get converted differently) and run fwdtests. I think the original issue that caused #8878 should be retested for regressions.

#15 Updated by Artur Școlnic 1 day ago

I think Constantin already did that and there were no issues.

#16 Updated by Alexandru Lungu 1 day ago

Artur Școlnic wrote:

I think Constantin already did that and there were no issues.

If you meant #8878-12 (about ETF), then my take on that is that ETF doesn't use the pattern discovered in #8878-1, so it was irrelevant. That is why I want to extend the testing with other application.
If you meant that Constantin actually converted something else in the backstage, please document here.

Also available in: Atom PDF