Query generation based on merger of subqueries

Patent metadata

Field	Value
Publication number	US-11409744-B2
Application number	US-202016945735-A
Country	US
Kind code	B2
Filing date	Jul 31, 2020
Priority date	Aug 1, 2019
Publication date	Aug 9, 2022
Grant date	Aug 9, 2022

Abstract

Systems and methods for query generation based on merger of subqueries are described. For example, methods may include accessing a first join graph representing tables in a database, wherein the first join graph has vertices corresponding to respective tables in the database and directed edges corresponding to join relationships; receiving a first query specification that references data in two or more of the tables of the database to specify multiple subqueries in a set of subqueries; checking that two or more subqueries from the set of subqueries have the same join graph; checking that the two or more subqueries have the same set of grouping columns; responsive, at least in part, to the two or more subqueries having the same join graph and the same set of grouping columns, merging the two or more subqueries to obtain a consolidated query.

First claim

What is claimed is:

1. A method comprising:
accessing a first join graph representing tables in a database, wherein the first join graph has vertices corresponding to respective tables in the database and directed edges corresponding to join relationships;
receiving a first query specification that references data in two or more of the tables of the database to specify multiple subqueries in a set of subqueries;
determining that two or more subqueries from the set of subqueries have the same connected subgraph of the first join graph;
determining that the two or more subqueries have the same set of grouping columns;
responsive to determining that the two or more subqueries having have the same connected subgraph of the first join graph and the same set of grouping columns, merging the two or more subqueries to obtain a consolidated query;
invoking a transformed query on the database that is based on the consolidated query to obtain a transformed query result; and
presenting data based on the transformed query result.

2. The method of claim 1 , wherein the set of subqueries are related by a query graph, and comprising:
checking that none of the two or more subqueries are a root query in the query graph.

3. The method of claim 1 , wherein the set of subqueries are related by a query graph, and comprising:
checking that all of the two or more subqueries are a leaf query in the query graph.

4. The method of claim 1 , comprising:
checking that the two or more subqueries lack a windowing function.

5. The method of claim 1 , comprising:
checking that the two or more subqueries lack a having filter.

6. The method of claim 1 , comprising:
checking that the two or more subqueries lack an aggregate-distinct filter.

7. The method of claim 1 , wherein the set of subqueries are related by a query graph, and comprising:
checking that the two or more subqueries are respectively on an opposite side of a left-outer join or a right-outer join in the query graph.

8. The method of claim 1 , wherein the first query specification was generated based in part by a group function transformer decomposing a query to generate multiple subqueries in the set of subqueries.

9. A system, comprising:
a network interface,
a processor, and
a memory, wherein the memory stores instructions executable by the processor to:
access a first join graph representing tables in a database, wherein the first join graph has vertices corresponding to respective tables in the database and directed edges corresponding to join relationships;
receive a first query specification that references data in two or more of the tables of the database to specify multiple subqueries in a set of subqueries;
determine that two or more subqueries from the set of subqueries have the same connected subgraph of the first join graph;
determine that the two or more subqueries have the same set of grouping columns; and
responsive to a determination that the two or more subqueries have the same connected subgraph of the first join graph and the same set of grouping columns, merge the two or more subqueries to obtain a consolidated query.

10. The system of claim 9 , wherein the set of subqueries are related by a query graph, and the memory stores instructions executable by the processor to:
check that none of the two or more subqueries are a root query in the query graph.

11. The system of claim 9 , wherein the set of subqueries are related by a query graph, and the memory stores instructions executable by the processor to:
determining that the respective subqueries of the two or more subqueries are respective leaf queries in the query graph.

12. The system of claim 9 , wherein the memory stores instructions executable by the processor to:
check that the two or more subqueries lack a windowing function.

13. The system of claim 9 , wherein the memory stores instructions executable by the processor to:
check that the two or more subqueries lack a having filter.

14. The system of claim 9 , wherein the memory stores instructions executable by the processor to:
check that the two or more subqueries lack an aggregate-distinct filter.

15. The system of claim 9 , wherein the set of subqueries are related by a query graph, and the memory stores instructions executable by the processor to:
check that the two or more subqueries are respectively on an opposite side of a left-outer join or a right-outer join in the query graph.

16. The system of claim 9 , wherein the first query specification is generated based in part by a group function transformer decomposing a query to generate multiple subqueries in the set of subqueries.

17. A non-transitory computer-readable storage medium that includes instructions that, when executed by a processor, facilitate performance of operations comprising:
accessing a first join graph representing tables in a database, wherein the first join graph has vertices corresponding to respective tables in the database and directed edges corresponding to join relationships;
receiving a first query specification that references data in two or more of the tables of the database to specify multiple subqueries in a set of subqueries;
determining that two or more subqueries from the set of subqueries have the same connected subgraph of the first join graph;
determining that the two or more subqueries have the same set of grouping columns;
responsive to determining that the two or more subqueries have the same connected subgraph of the first join graph and the same set of grouping columns, merging the two or more subqueries to obtain a consolidated query;
invoking a transformed query on the database that is based on the consolidated query to obtain a transformed query result; and
presenting data based on the transformed query result.

18. The non-transitory computer-readable storage medium of claim 17 , wherein the set of subqueries are related by a query graph, and including instructions that, when executed by a processor, facilitate performance of operations comprising:
checking that none of the two or more subqueries are a root query in the query graph.

19. The non-transitory computer-readable storage medium of claim 17 , wherein the set of subqueries are related by a query graph, and including instructions that, when executed by a processor, facilitate performance of operations comprising:
checking that all of the two or more subqueries are a leaf query in the query graph.

20. The non-transitory computer-readable storage medium of claim 17 , including instructions that, when executed by a processor, facilitate performance of operations comprising:
checking that the two or more subqueries lack a windowing function.

Assignees

• Thoughtspot Inc

Inventors

• Shah Naman
• Kothari Rakesh
• Bansal Archit

Classifications

• G06F16/24535

  of sub-queries or views · CPC title

• G06F16/9024

  Graphs; Linked lists (G06F16/9027 takes precedence) · CPC title

• G06F16/24542 Primary

  Plan optimisation · CPC title

• G06F16/248

  Presentation of query results · CPC title

• G06F16/2456

  Join operations · CPC title