Conversion Query Reports¶

• Conversion Query Reports
  • Unindexed Queries
  • Indeterminate Sorting Queries
    • What is an indeterminate sorting query
    • Indeterminate sorting report

During conversion, FWD provides some reports about static queries. These reports are simple text files with output from the conversion rules. It is intended to migrate these to the Code Analytics toolset but at this time, there is not enough information to generate these reports in the analytics tools.

Unindexed Queries¶

// TODO

Indeterminate Sorting Queries¶

What is an indeterminate sorting query¶

When dealing with a query, there are two components that influence the order of the results: the chosen index and the BY clause. FWD merges the index fields and the field from the BY clause in order to create a single ORDER BY clause. Thus, in the following sections, the query's BY clause will actually refer the final/merged version of the two order components. Based on the query's BY clause, we can place queries in three categories (kudos to Eric for coming up with this):

Deterministic Sort

The only query that is 100% guaranteed to sort the same way is one where nothing has been left to chance. That is, the query's sort conditions overlap a unique index, for which all the indexed fields are mandatory. There is no ambiguity in the sorting; the sort criteria define the order, and the unique index enforces uniqueness, so that there are no subsets of results which have any ambiguity or order.

Nearly Deterministic Sort

The next least likely query to produce indeterminate sorting is one where the query's sort conditions overlap a unique index, but one or more of the fields in that index are not mandatory. In this case, there can be multiple occurrences of unknown value (NULL in SQL terms) for the non-mandatory field, without violating that unique index.

For any subset of results with unknown value in the non-mandatory field (all other fields in the unique index being equal within that subset), the records within that subset are not guaranteed to sort deterministically. That being said, there are mitigating factors which cannot be determined from a static analysis (which is what we are doing during conversion when we produce these reports):

• The business logic of the application might not allow unknown value to be stored in a uniquely indexed field, even if it is not explicitly defined as mandatory in the schema. This is not something the static analysis can determine. In fact, I would guess this would be a common idiom. So, a uniquely indexed field which is not explicitly mandatory may still be effectively mandatory.
• The data might not contain duplicate unknown values. This is essentially the flip-side of the previous bullet point, but the reason for mentioning it is that during conversion, we have no access to data export samples, so there is no way to determine whether there actually are duplicate unknown values in a uniquely indexed, non-mandatory field.

Indeterminate Sort

This case represents those queries whose sort criteria do not overlap a unique index. As such, there is no guarantee that any record in a result set does not have duplicate values among the fields involved in the sort criteria. Thus, there can be subsets of results which are not sorted deterministically between OE and FWD. In fact, there is no guaranteed contract that they will even sort deterministically from one execution to the next, or between dump and load operations, or between versions of OE. However, history shows us that the sort of even these queries does not tend to vary much over time. Progress is unlikely to change its internals drastically, so these are probably pretty stable sorts at this point.

Indeterminate sorting report¶

FWD, at conversion time, gathers data from queries that may result in an indeterminate sorting order of the results. For this, we have defined a Risk:

• Queries that are part of the Indeterminate Sort category from the previous section are reported as Moderate risk.
• Queries that are part of the Nearly Deterministic Sort category are reported as Low risk.
• Queries from the Deterministic Sort category are not reported, since they have a clear way of sorting records.

In the Code Conversion Annotations phase, FWD will generate an indeterminate_sorting_queries.csv file. An example of how this report looks when imported into a spreadsheet application:

What information can be extracted from the report:

• File - The path to the file in which the query can be found.
• Line number - The line number from the file where the query is written.
• Table - The name of the table that is involved in the query
• Chosen index - The chosen index for the query.
• Risk - The risk factor of the query. For more information on this, please check the previous bullet list and section.
• Query - The query string extracted from the file.