Dynamically detecting and correcting errors in queries

Patent metadata

Field	Value
Publication number	US-11561979-B2
Application number	US-202017013956-A
Country	US
Kind code	B2
Filing date	Sep 8, 2020
Priority date	Sep 8, 2020
Publication date	Jan 24, 2023
Grant date	Jan 24, 2023

Abstract

A computer-implemented method dynamically detects and corrects an error in a query. The method includes identifying an error in a first query. The method further includes generating a set of alternate execution structures for the first query. The method includes running each of the alternate execution structures, including generating a set of results corresponding to each set of alternate execution structure, comparing each of the set of results against each other of the set of results, and storing each of the set of alternate execution structures to include a result of the set of results, for each alternate structure. The method further includes selecting, from the set of alternate execution structures, a first alternate execution structure based on a predetermined criteria, and implementing the first alternate structure in place of the first query.

First claim

What is claimed is:

1. A computer-implemented method comprising:
identifying an error in a first query, wherein the identifying is in response to a database receiving the first query;
generating a set of alternate execution structures for the first query, wherein the set of alternate execution structures includes at least two alternate execution structures;
running each of the alternate execution structures, wherein the running includes generating a set of results corresponding to each alternate execution structure in said set of alternate execution structures;
comparing, in response to the running, each of the set of results against each of the other sets of results, wherein the comparing is configured to identify an expected set of results;
storing each of the set of alternate execution structures to include a result of the set of results, for each alternate execution structure;
selecting, in response to the comparing, from the set of alternate execution structures, a first alternate execution structure based on the first alternate execution structure outputting the expected set of results; and
implementing the first alternate structure in place of the first query.

2. The method of claim 1 , wherein the first query is configured to return the expected set of results; and
the identifying the error in the first query is further in response to:
executing the first query where the first query returns an actual set of results; and
determining the expected set of results is different than the actual set of results.

3. The method of claim 2 further comprising:
grouping alternate execution structures in a group wherein each set of results within the group are equivalent; and
selecting the first alternate execution structure from the group that includes a greatest number of alternate execution structures, wherein the set of results that includes the greatest number of alternate execution structures include the expected set of results.

4. The method of claim 2 , wherein the identifying an error is in response to an update of the database.

5. The method of claim 4 , wherein a random set of queries are executed in response to the update to check for errors.

6. The method of claim 5 , wherein the expected set of results is based on the first query from the set of queries being executed prior to the update.

7. The method of claim 1 , wherein each set of results includes execution statistics.

8. The method of claim 7 , wherein selecting the first alternate structure is based on the execution statistics for the first alternate structure being most similar to a first query execution statistics.

9. The method of claim 1 , wherein selecting the first alternate structure is based on the first alternate structure being closest to an actual execution structure of the first query.

10. The method of claim 1 , wherein the set of alternate execution structures includes at least a best execution structure and a safest execution structure.

11. The method of claim 1 , wherein the first query includes an original execution structure, and the selecting the first alternate execution structure is based on the first execution structure being most similar to the original execution structure and the implementing the first alternate structure is in response to receiving an approval from a user.

12. The method of claim 1 , further comprising:
determining a scope of the error in the first query;
identifying a second query;
determining the second query contains a second error with the scope of the error; and
correcting the second error.

13. The method of claim 12 , wherein the identifying the second query is based on a characteristic of the first query matching a characteristic in the second query.

14. A system comprising:
a processor; and
a computer-readable storage medium communicatively coupled to the processor and storing program instructions which, when executed by the processor, are configured to cause the processor to:
identify, in response to a database receiving a first query, a first error in the first query;
determine a scope of the first error;
identify a second query, wherein the second query includes a characteristic in common with the first query;
determine the second query contains a second error within the scope of the first error;
generate, for the second query, a set of alternate execution structures, wherein the set of alternate execution structures includes at least two alternate execution structures;
run each alternate execution structure in the set of alternate execution structures;
compare, in response to the running, each of the set of results against each of the other sets of results, wherein the comparing is configured to identify an expected set of results;
select, in response to the running, a second alternate execution structure from the set of alternate execution structures, wherein the selection is based on the second alternate execution structure outputting the expected set of results; and
replace an execution structure of the first query with the second alternate execution structure.

15. The system of claim 14 , wherein the common characteristic includes a common source.

16. The system of claim 14 , wherein the common characteristic includes a common target and the common target can be selected from a group consisting of, the database, a table, and a column.

17. The system of claim 14 , wherein the program instructions are further configured to cause the processor to:
compare a set of results for each alternate execution structure against each other sets of results;
identify a most common set of results; and
wherein the determination the second error is contained in the second query is based on a first set of results from the first query being different than the most common set of results.

18. A computer program product, the computer program product comprising a computer readable storage medium having program instructions embodied therewith, the program instructions executable by a processing unit to cause the processing unit to:
identify an error in a first query, wherein the identifying is in response to a database receiving the first query, wherein the set of alternate execution structures includes at least two alternate execution structures;
generate a set of alternate execution structures for the first query;
run each of the alternate execution structures, wherein the running includes generating a set of results corresponding to each alternate execution structure in said set of alternate execution structures;
compare, in response to the running, each of the set of results against each of the other sets of results, wherein the comparing is configured to identify an expected set of results;
store each of the set of alternate execution structures to include a result of the set of results, for each alternate execution structure;
select, in response to the comparing, from the set of alternate execution structures, a first alternate execution structure based on the first alternate execution structure outputting the expected set of results; and
implement the first alternate structure in place of the first query.

19. The computer program product of claim of claim 18 , wherein the program instructions are further configured to cause the processing unit to:
group each alternate execution structures into a group wherein each set of results within the group are equivalent; and
select the first alternate execution structure from the group that includes a greatest number of alternate execution

Assignees

• IBM

Inventors

• Li Shuo
• Wang Xiaobo
• Sun sheng yan
• Zhang Hong Mei

Classifications

• G06F16/248

  Presentation of query results · CPC title

• G06F16/24552 Primary

  Database cache management · CPC title

• G06F16/24542

  Plan optimisation · CPC title

• G06F16/221

  Column-oriented storage; Management thereof · CPC title

• G06F16/24568

  Data stream processing; Continuous queries · CPC title