Query language representation usable for constraints causing query failure

Abstract

Techniques and solutions are provided for implementing query constraints. Keywords in a query language are provided that indicate the presence of a constraint. During query execution, if the constraints are not met, the query may be terminated / failed. In some cases, keywords are introduced into a query by a query optimizer. In a particular example, a keyword is introduced as part of an optimized query, where at least some query operations are performed using a federated database system. Keywords indicating the constraints may be included in query language statements and sent to the federated database system for execution. If the constraints are not met, the federated database system may send a failure notification to the primary database system.

Description

TECHNICAL FIELD

[0001] The present disclosure generally relates to query processing. Particular embodiments relate to a constrained query language representation, where query execution fails if the constraint is not satisfied. BACKGROUND

[0002] Enterprises increasingly commonly store data in various systems, including in one or more local (or “on-premise”) systems (which can or can not be physically proximate), in one or more cloud-based systems, or in a combination of local and cloud-based systems. Systems can be of different types—such as storing data in different formats (e.g., a relational database versus a database storing JSON documents) or using different database management systems (e.g., using software and / or hardware provided by different vendors). Even where data is stored in the same format and using the same vendor’s software, there can be differences in what data is stored in a particular location and the schema used to store it.

[0003] To help address these issues, database federation techniques have been used. In a federated database environment, requests for database operations, such as queries, can specify sources at local database systems or at “remote” databases accessed using data federation. Unlike traditional distributed databases, where data is physically replicated across multiple nodes, federated databases allow for virtual integration of data from different sources without the need for data replication.

[0004] Communication between different databases in a federated environment is typically through specialized adapters or APIs that facilitate data access and query execution. These adapters or APIs act as intermediaries between the “source” database systems and the federated system, translating requests and responses between the federated system’s query language (e.g., SQL) and the local query language or protocol supported by the data source. A “source” database system, also referred to as a master database system, is the system that receives queries from clients and is primarily responsible for query execution, including communication with any federated systems referenced by the query.

[0005] When a query is executed in a source database system, the query optimizer of the source database system determines the optimal execution plan. While the optimizer primarily generates SQL statements for traditional database systems, in a federated environment, it can generate federated query plans or optimization instructions. These plans or instructions provide guidance for accessing and processing data from individual data sources, ensuring efficient query execution across the federated environment.

[0006] Data transmitted between the source database system and the federated system typically includes query requests, intermediate results, and final result sets. Query requests contain the necessary information for retrieving the desired data, such as selection conditions, join criteria, and aggregation functions. Intermediate results can be transmitted between the federated system and the source database system during distributed query processing to optimize performance and reduce data transfer overhead. Finally, the final result sets containing the merged and aggregated data from all relevant federated sources are returned to the source database system for presentation to the user or application.

[0007] In summary, federated database systems employ specialized adapters or APIs to facilitate communication between different data sources, enabling seamless integration of data from disparate sources without the need for physical data replication. Query requests and results are transmitted between the source systems and the federated system using standardized protocols, where data access commands are typically SQL statements or their equivalents. This approach ensures the autonomy and sovereignty of data by allowing each data source to maintain control over its data assets while supporting collaboration and data integration across the federated environment.

[0008] In some cases, an operation that utilizes remote data can be performed on the remote system, while in other cases, the operation is performed on the source system, and a query optimizer can select between different plans in which the operation is performed on different systems. The location in which the operation is performed can impact query execution time and compute resource usage. For example, if a filter operation or join can be performed on the remote (federated) system, then less data can need to be transferred from the remote system to the source system than if a larger dataset is transferred to the source system and the filter / join is performed on the local system. However, in some cases, a query can have an operation that prevents sending a large subset of the query operations to the remote system. Thus, there is room for improvement. SUMMARY

[0009] This Summary is provided to introduce some concepts in a simplified form that are further described below in the DETAILED DESCRIPTION. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.

[0010] Techniques and solutions are provided for implementing query constraints. A keyword in a query language is provided that indicates the presence of a constraint. During query execution, the query can be terminated / fail if the constraint is not satisfied. In some cases, the keyword is introduced into a query by a query optimizer. In particular examples, the keyword is introduced as part of optimizing a query, where at least some query operations are performed using a federated database system. The keyword indicating the constraint can be included in a query language statement and sent to the federated database system for execution. If the constraint is not satisfied, the federated database system can send a failure notification to the primary database system.

[0011] In one aspect, the disclosure provides a process for rewriting a query to include a constraint. A first query is received at a first database system. The first query includes a first plurality of query operations. The first query is rewritten to provide a second query. The second query includes one or more query operations that are different from the first plurality of query operations of the first query. A first query operation of the one or more query operations is a keyword in a query language and expresses a constraint. During query execution, the query fails if the constraint is not satisfied.

[0012] In another aspect, the disclosure provides a process for executing a query at a federated database. At a federated database system, a first plurality of query operations is received from a source database system. The first plurality of query operations includes a first query operation that includes a keyword of a query language and expresses a constraint. During execution of the first plurality of query operations at the federated database system, the query including the first query operation fails if the constraint is not satisfied. The constraint is evaluated. Execution results from executing at least a portion of the first plurality of query operations are returned to the source database system.

[0013] In another aspect, the disclosure provides a process for executing a query including a constraint. A first query is received or generated by a first database system. The first query includes one or more query operations, where a first query operation is a keyword in a query language that expresses a constraint. The query fails if the constraint is not satisfied during query execution. The first query is caused to be executed. During execution of the first query, it is determined that the constraint is not satisfied. Based on the determination, the first query is caused to fail.

[0014] The disclosure also includes computing systems configured to perform the methods described above or comprising instructions to perform the methods described above, and tangible, non-transitory computer-readable storage media comprising the instructions to perform the methods described above. Various other features and advantages can be incorporated into the technology as described herein as can be desired. BRIEF DESCRIPTION OF DRAWINGS

[0015] Figure 1 is a diagram depicting an example database system that can be used when implementing aspects of the disclosed technology.

[0016] Figure 2 is a diagram depicting a computing environment in which a database system can use a data federation to access data in a remote computing system, including through a virtual table that maps to a remote table of the remote computing system.

[0017] Figure 3A is a diagram showing how constraints in a query plan can prevent larger sub-plans from being sent to a federated database system for execution.

[0018] Figure 3B is a diagram showing Figure 3A how a larger sub-plan of a query plan of may be sent to a federated database system if expressed in a format that can be sent to the federated database system, such as a query language.

[0019] Figure 4 is a diagram providing a data definition language statement defining two tables and a SQL statement performing a scalar sub-query on the tables, and operations to be performed in executing the SQL statement.

[0020] Figure 5 is a diagram showing Figure 4 how a SQL statement of Figure 4 may be rewritten as a group by over a join operation to replace a scalar sub-query in the SQL statement, and a graphical depiction of operations in executing the rewritten SQL statement.

[0021] Figure 6 is a diagram showing a query plan for a rewritten SQL statement of Figure 5 , where the query plan includes constraints that cause the SQL statement to be executed more similarly to the original scalar sub-query, such as causing the query to fail if a uniqueness constraint is not satisfied.

[0022] Figure 7 is a diagram showing how a query can be associated with multiple scalar sub-queries that can be rewritten as a group by over a join, where performance of the rewritten scalar sub-queries can be used to improve query performance using the disclosed techniques.

[0023] Figure 8 is a diagram showing techniques in accordance with the present disclosure in which a query is rewritten and includes constraints that can be expressed in a query language representation of the rewritten query.

[0024] Figure 9 provides example code that can be used to implement constraints of Figure 8 .

[0025] Figure 10 is a table providing an explanation of operations in executing a query of Figure 8 .

[0026] Figure 11 is a graph showing steps involved in executing a query Figure 5 , where constraints are expressed in a query plan rather than in a form that can be sent to federated systems.

[0027] Figure 12 is a graph showing steps involved in executing a query Figure 8 , where constraints are in a form that can be sent to federated systems.

[0028] Figure 13A is a flow diagram of example operations of a process to rewrite a query to include constraints.

[0029] Figure 13B is a flow diagram of example operations of a process to execute a query at a federated database.

[0030] Figure 13C is a flow diagram of example operations of a process to execute a query including constraints.

[0031] Figure 14 is a diagram of an example computing system in which some described embodiments can be implemented.

[0032] Figure 15 is an example cloud computing environment that can be used in connection with the techniques described herein. DETAILED DESCRIPTION

[0033] Example 1 - Overview

[0034] Enterprises increasingly commonly store data in various systems, including in one or more local (or “on-premises”) systems (which can or can not be physically proximate), in one or more cloud-based systems, or in a combination of local and cloud-based systems. Systems can be different types—such as storing data in different formats (e.g., a relational database versus a database storing JSON documents) or using different database management systems (e.g., using software and / or hardware provided by different vendors). Even where data is stored in the same format and using the same vendor’s software, there can be differences in what data is stored in a particular location and the schema used to store it.

[0035] To help address these issues, database federation techniques have been used. In a federated database environment, requests for database operations, such as queries, can specify sources at local database systems or at “remote” databases accessed using data federation. Unlike traditional distributed databases, where data is physically replicated across multiple nodes, federated databases allow virtual integration of data from different sources without requiring data replication.

[0036] Communication between different databases in a federated environment is typically facilitated through specialized adapters or APIs that enable data access and query execution. These adapters or APIs act as intermediaries between the "source" database system and the federated system, translating requests and responses between the federated system's query language (e.g., SQL) and the data source's supported native query language or protocol. The "source" database system, also referred to as the primary database system, is the system that receives queries from clients and is primarily responsible for query execution, including communication with any federated systems referenced by the query.

[0037] When a query is executed in the source database system, the source database system's query optimizer determines the optimal execution plan. While the optimizer primarily generates SQL statements for traditional database systems, in a federated environment, it can generate federated query plans or optimization instructions. These plans or instructions provide guidance for accessing and processing data from various data sources, ensuring efficient query execution across the federated environment.

[0038] The data transmitted between the source database system and the federated systems typically includes query requests, intermediate results, and final result sets. Query requests contain the necessary information for retrieving the desired data, such as selection criteria, join conditions, and aggregation functions. Intermediate results can be transmitted between the federated systems and the source database system during distributed query processing to optimize performance and reduce data transmission overhead. Finally, the final result set, containing the merged and aggregated data from all relevant federated sources, is returned to the source database system for presentation to users or applications.

[0039] In summary, federated database systems employ specialized adapters or APIs to facilitate communication between different data sources, enabling seamless integration of data from diverse sources without the need for physical data replication. Query requests and results are transmitted between source systems and the federated system using standardized protocols, with data access commands typically being SQL statements or their equivalents. This approach ensures data autonomy and sovereignty by allowing each data source to maintain control over its data assets while supporting collaboration and data integration across the federated environment.

[0040] In some cases, an operation that utilizes remote data can be performed on the remote system, while in other cases the operation is performed on the source system, and the query optimizer can select between different plans in which the operation is performed on different systems. The location in which the operation is performed can impact query execution time and compute resource usage. For example, if a filter operation or join can be performed on the remote (federated) system, then less data can need to be transferred from the remote system to the source system than if the larger dataset is transferred to the source system and the filter / join is performed on the local system. However, in some cases, a query can have an operation that prevents a larger subset of the query operations from being sent to the remote system. Thus, there is room for improvement.

[0041] As an example of how the nature of some query operations limits what can be performed at the federated system, in some cases, a constraint can not be explicitly expressed in the query, but can be inserted into the query plan by the query optimizer. For example, the query optimizer can determine that an explicit query operation involves a constraint, such as a scalar subquery, where a scalar subquery is a subquery that returns exactly one value. The value can be a value of a single attribute or a computed value derived from multiple rows or attributes, but ultimately, it is a single scalar result. That is, a scalar subquery involves evaluating an equality condition, and if multiple values are returned from the subquery and compared to some other value, then the query will fail. A scalar subquery can be written as a combination of a group by operation and a join operation, but in doing so, the equality condition is removed, and if multiple rows satisfy the selection condition, then the query will not fail.

[0042] To help ensure that the rewritten query produces the same results as the original query, including query failure, a constraint can be added to the query plan that ensures that at most one record is returned for each selection condition. If the constraint is not satisfied during query execution, then the query can terminate with a failed operation.

[0043] However, generally, data federation techniques only allow transmission of SQL operations, not portions of the query plan that can reflect constraints. Since constraints cannot be sent to the federated system for enforcement, operations are generally not performed at the federated system. Instead, they are performed at the source database system, even if this can require more data to be transferred from the federated system. For example, a query that includes a scalar subquery can ultimately be a single value, or the query can return a complete record if the value of the scalar subquery is used under a larger condition. If an equivalent join could be sent to the federated data source, in the case where the scalar subquery is rewritten as a join, only the value of a single record needs to be transferred if the constraint is evaluated at the federated system. By contrast, in the case where there is no remote evaluation of the constraint, thousands or millions of rows can need to be transferred to the source database system for processing. However, even in cases where the reduction in data to be transferred is not significant or even nonexistent, all of the data is generally transferred to the source database system so that the constraint can be evaluated. If the constraint is proven to be violated, the query can fail, but the resources to transfer the data are "wasted" compared to a scenario where compliance can be determined on the federated data source before the data is transferred.

[0044] For at least some types of operations, portions of the query can be rewritten in a manner that helps maintain the intent of the original query. For example, a LIMIT statement can be introduced to ensure that a single value is returned in the case where a scalar subquery is rewritten as a group by over a join operation. However, in this case, the query will return a result even if the query would fail under the original query due to an explicit operation in the original query or due to a constraint added to the query plan of the written query as part of query optimization. Allowing the query to fail can be beneficial because it prevents execution of a potentially flawed or unintended query.

[0045] The present disclosure addresses these techniques by introducing keywords that act as constraints on query operations. In some cases, these constraints are introduced during query rewriting, for example by a query optimizer. In other cases, the constraints can be specified in the original query. Constraints can be implemented in a variety of ways. For example, a constraint can be specified for a particular query operation, such as a keyword that specifies a constraint to be used with a scalar subquery. In another example, a constraint can be specified based on the nature of the constraint, and then the constraint can be used in different operations related to the constraint, such as evaluating uniqueness in various query contexts.

[0046] Note that as used in describing the disclosed innovations, constraints refer to constraints where the query execution fails if the constraint is violated, as opposed to other types of constraints where the query result can be forced to be a particular result or result type (such as providing a single value or null value) but does not fail. Thus, in another implementation, the keyword can generally specify a constraint where the query should fail if a particular condition is not met. The query optimizer or executor can include logic to determine the exact condition of the constraint based on the query operation to which the constraint is applied (such as by wrapping around the query option, such as a grouping operation, a join operation, other types of aggregation, or a check to see if a particular value exists in the dataset). As an example, if the query includes the generic keyword indicating a constraint, the query optimizer or executor can determine that the constraint is associated with a scalar subquery, and can then determine that the implementation of the constraint of the scalar subquery should be used, and that the query execution should fail if more than one value is returned and used in evaluating the equality condition.

[0047] In general, the database that optimizes the query and another database (such as a federated system) that executes part of the query supports a particular keyword that identifies the constraint. In this way, the federated system can perform more types of query operations, which can reduce data transfer by either recognizing that the query would fail before sending data from the federated system to the source database system, or by limiting the amount of data transferred to the source database system (e.g., by providing the results of the execution from the federated system, rather than the data used by the source database system in generating the execution results).

[0048] The disclosed technology can provide advantages over other possible approaches to implementing constraints in a way that can be sent to other database systems, such as using detailed SQL constructs like CASE statements. For example, the keyword can encapsulate complex logic behind a simple and intuitive term, making the SQL query more readable and easier to understand. In contrast, alternative techniques can result in more complex and harder-to-read SQL code.

[0049] The use of a particular keyword can give the SQL optimizer more flexibility and result in a more efficient execution plan compared to using a particular construct. The SQL optimizer has a clear understanding of the functionality represented by the constraint keyword, allowing it to handle that functionality differently depending on the details of the query and data. This can include using case statements, introducing new query plan operators, or other techniques.

[0050] On the other hand, when particular constructs such as case statements are used, they represent very specific logic. This can limit the flexibility of the optimizer to rewrite or optimize those constructs, as it must preserve the exact semantics of those constructs. As a result, the optimizer can not be able to generate the most efficient execution plan possible when using the more flexible keyword.

[0051] When a key constraint is violated (i.e., more than one value is returned), query execution fails immediately. This can make it easier to catch and handle errors. By contrast, with alternative techniques, errors can not be caught until later stages of query execution, which can lead to more complex error handling scenarios.

[0052] Using a constraint keyword can lead to more consistent query code. This is because the same keyword is always used to express the same logic. By contrast, with alternative techniques, different SQL constructs can be used in different parts of the code to express the same logic, depending on the details of the query plan.

[0053] Furthermore, having a constraint keyword can increase the portability of query language code, as the same keyword is across different SQL engines. By contrast, alternative techniques can rely on features specific to a particular query, which can limit code portability.

[0054] Example 2 describes an example database system that can be used to implement the disclosed technology. The database system can be an example of a source database system accessed by a local system or a federated system. Example 3 provides an example of a virtual table that includes a logical pointer that can be updated to point to different locations, including locations in a federated system or locations in a local database system (including local tables, or tables maintained in a cache). It should be understood that virtual tables can be implemented in different ways, including in a way that is “statically” mapped to a particular federated data source of a particular federated system. Examples 4-8 more specifically describe the disclosed technology for expressing query execution constraints in a form that can be sent to a federated system.

[0055] Example 2 - Example Database Architecture

[0056] Database systems often operate using either online transaction processing (OLTP) workloads, which are typically transaction-oriented, or online analytical processing (OLAP) workloads, which typically involve data analysis. OLTP transactions are often used for core business functions, such as entering, manipulating, or retrieving operational data, and users typically expect transactions or queries to complete quickly. For example, OLTP transactions can include operations such as insert (INSERT), update (UPDATE), and delete (DELETE), as well as relatively simple queries. OLAP workloads typically involve queries for enterprise resource planning and other types of business intelligence. OLAP workloads typically perform few, if any, updates to database records, instead they typically read and analyze past transactions, often large numbers of transactions.

[0057] Figure 1An example database environment 100 is shown. The database environment 100 can include a client 104. Although a single client 104 is shown, the client 104 can represent multiple clients. The one or more clients 104 can be OLAP clients, OLTP clients, or a combination thereof.

[0058] The client 104 communicates with a database server 106. Through various subcomponents, the database server 106 can process requests for database operations, such as requests to store, read, or manipulate data (i.e., CRUD operations). A session manager component 108 can be responsible for managing connections between the client 104 and the database server 106, such as clients that communicate with the database server using a database programming interface, such as Java Database Connectivity (JDBC), Open Database Connectivity (ODBC), or Database Shared Library (DBSL). Typically, the session manager 108 can concurrently manage connections with multiple clients 104. The session manager 108 can perform functions such as creating new sessions for client requests, assigning client requests to existing sessions, and authenticating access to the database server 106. For each session, the session manager 108 can maintain a context that stores a set of parameters related to the session, such as settings or transaction isolation levels related to committing database transactions (e.g., statement-level isolation or transaction-level isolation).

[0059] For other types of clients 104, such as web-based clients (such as clients that use the HTTP protocol or similar transport protocols), the client can interface with an application manager 110. Although shown as a component of the database server 106, in other implementations, the application manager 110 can be external to the database server 106, but in communication with the database server 106. The application manager 110 can initiate new database sessions with the database server 106, and perform other functions in a similar manner as the session manager 108.

[0060] The application manager 110 can determine the type of application making the request for a database operation, and mediate the execution of the request at the database server 106, such as by invoking or performing a procedure call, generating a query language statement, or converting data between formats usable by the client 104 and the database server 106. In a particular example, the application manager 110 receives a request for a database operation from the client 104, but does not store information related to the request, such as state information.

[0061] Once a connection is established between the client 104 and the database server 106, including when established through the application manager 110, execution of client requests is typically performed using a query language, such as the Structured Query Language (SQL). In executing requests, the session manager 108 and the application manager 110 can communicate with the query interface 112. The query interface 112 can be responsible for creating connections with appropriate execution components of the database server 106. The query interface 112 can also be responsible for determining whether a request is associated with a previously cached statement or stored procedure, and invoking the stored procedure or associating the previously cached statement with the request.

[0062] At least some types of requests for database operations, such as statements in a query language for writing or manipulating data, can be associated with a transaction context. In at least some embodiments, each new session can be assigned to a transaction. Transactions can be managed by the transaction manager 114. The transaction manager 114 can be responsible for operations such as coordinating transactions, managing transaction isolation, tracking running and closed transactions, and managing the commit or rollback of transactions. In performing these operations, the transaction manager 114 can communicate with other components of the database server 106.

[0063] The query interface 112 can communicate with a query language processor 116, such as a Structured Query Language processor. For example, the query interface 112 can forward query language statements or other database operation requests from the client 104 to the query language processor 116. The query language processor 116 can include a query language executor 120, such as an SQL executor, which can include a thread pool 124. Some requests for database operations or components thereof can be executed directly by the query language processor 116. Other requests or components thereof can be forwarded by the query language processor 116 to another component of the database server 106. For example, transaction control statements, such as commit or rollback operations, can be forwarded by the query language processor 116 to the transaction manager 114. In at least some cases, the query language processor 116 is responsible for executing operations that retrieve or manipulate data (e.g., select, update, delete). Other types of operations, such as queries, can be sent by the query language processor 116 to other components of the database server 106. The query interface 112 and the session manager 108 can maintain and manage context information associated with requests for database operations. In particular implementations, the query interface 112 can maintain and manage context information for requests received through the application manager 110.

[0064] When the session manager 108 or the application manager 110 establishes a connection between the client 104 and the database server 106, a client request such as a query can be assigned to a thread of the thread pool 124, for example, using the query interface 112. In at least one implementation, a thread is associated with a context for performing processing activities. The thread can be managed by an operating system of the database server 106, or by another component of the database server, or in combination with another component of the database server. Generally, at any point, the thread pool 124 contains a plurality of threads. In at least some cases, the number of threads in the thread pool 124 can be adjusted dynamically, such as in response to a level of activity at the database server 106. In particular aspects, each thread of the thread pool 124 can be assigned to a plurality of different sessions.

[0065] When a query is received, the session manager 108 or the application manager 110 can determine whether an execution plan for the query already exists, for example, in the plan cache 136. If an execution plan for the query exists, the cached execution plan can be retrieved and forwarded to the query language executor 120, for example, using the query interface 112. The query can be sent to an execution thread of the thread pool 124 determined by the session manager 108 or the application manager 110, for example. In particular examples, the query plan is implemented as an abstract data type.

[0066] If the query is not associated with an existing execution plan, the query can be parsed using the query language parser 128. The query language parser 128 can examine the query language statements of the query, for example, to ensure that they have proper syntax and that the statements are otherwise valid. For example, the query language parser 128 can check to see whether tables and records recited in the query language statements are defined in the database server 106.

[0067] The query can also be optimized using the query language optimizer 132. The query language optimizer 132 can manipulate elements of the query language statements to allow the query to be processed more efficiently. For example, the query language optimizer 132 can perform operations such as unnesting a query or determining an optimized order of execution for various operations in the query, such as operations within a statement. After optimization, an execution plan can be generated or compiled for the query. In at least some cases, the execution plan can be cached, such as in the plan cache 136, and if the query is received again, the execution plan can be retrieved, such as by the session manager 108 or the application manager 110.

[0068] In the disclosed technology, the query language optimizer 132 can determine portions of a query plan that access federated data sources and can determine portions of the query plan to send to corresponding federated systems for execution. The query language optimizer 132 can rewrite portions of the query for more efficient execution, including generating sub-plans that improve efficiency by performing operations at the federated systems rather than the source database systems. In rewriting portions of the query, the query language optimizer 132 can rewrite query operations that can implicitly be associated with a constraint in a first version of the query to operations that explicitly set forth the constraint in a rewritten query language representation of the operation.

[0069] Once a query execution plan has been generated or received, the query language executor 120 can oversee execution of the query’s execution plan. For example, the query language executor 120 can invoke appropriate subcomponents of the database server 106.

[0070] In executing a query, the query language executor 120 can invoke a query processor 140, which can include one or more query processing engines. Query processing engines can include, for example, an OLAP engine 142, a join engine 144, an attribute engine 146, or a compute engine 148. The OLAP engine 142 can, for example, apply rules to create an optimized execution plan for OLAP queries. The join engine 144 can be used to implement relational operators, typically for non-OLAP queries, such as join and aggregation operations. In particular implementations, the attribute engine 146 can implement columnar data structures and access operations. For example, the attribute engine 146 can implement merge functions and query processing functions, such as scanning a column.

[0071] In certain cases, such as if the query involves complex or internally parallelized operations or sub-operations, the query executor 120 can send the operations or sub-operations of the query to a job executor component 154, which can include a thread pool 156. The execution plan for a query can include multiple plan operators. In particular implementations, each job execution thread of the job execution thread pool 156 can be assigned to a separate plan operator. The job executor tenant 154 can be used to execute at least a portion of the operators of the query in parallel. In some cases, plan operators can be further divided and parallelized, for example, to have operations simultaneously access different portions of the same table. Using the job executor component 154 can increase the load on one or more processing units of the database server 106, but can improve the execution time of the query.

[0072] The query processing engine of the query processor 140 can access data stored in the database server 106. The data can be stored in row storage 162 in row-by-row format or in column storage 164 in column-by-column format. In at least some cases, data can be converted between row-by-row format and column-by-column format. The particular operations performed by the query processor 140 can access or manipulate data in the row storage 162, the column storage 164, or at least for certain types of operations (such as joins, merges, and subqueries), data in both the row storage 162 and the column storage 164. In at least some aspects, the row storage 162 and the column storage 164 can be maintained in main memory.

[0073] The persistence layer 168 can communicate with the row storage 162 and the column storage 164. The persistence layer 168 can be responsible for actions such as committing write transactions, storing redo log entries, rolling back transactions, and periodically writing data to storage to provide persistent data 172.

[0074] In performing requests for database operations, such as queries or transactions, the database server 106 can need to access information stored at another location, such as another database server. The database server 106 can include a communication manager 180 to manage such communications. The communication manager 180 can also mediate communications between the database server 106 and the client 104 or the application manager 110 when the application manager is located outside of the database server.

[0075] In some cases, the database server 106 can be part of a distributed database system that includes multiple database servers. At least a portion of the database servers can include some or all of the components of the database server 106. In some cases, the database servers of the database system can store multiple copies of data. For example, a table can be replicated at more than one database server. Additionally or alternatively, information in the database system can be distributed among multiple servers. For example, a first database server can hold a copy of a first table and a second database server can hold a copy of a second table. In still further implementations, information can be divided among the database servers. For example, a first database server can hold a first portion of a first table and a second database server can hold a second portion of the first table.

[0076] In performing requests for database operations, database server 106 can need to access other database servers or other sources of information at systems within the database system or external systems, such as the external system at which the parameterized data object resides. Communication manager 180 can be used to mediate such communications. For example, communication manager 180 can receive and route requests for information from components of database server 106 (or from another database server), and receive and route replies.

[0077] Database server 106 can include components for coordinating data processing operations involving remote data sources. In particular, database server 106 includes a data federation component 190 that at least partially processes requests to access data maintained at remote systems. In performing its functions, data federation component 190 can include one or more adapters 192, where an adapter can include logic, settings, or connection information that can be used to communicate with a remote system, such as to obtain information to help generate a virtual parameterized data object or to perform a request for data using a virtual parameterized data object (such as issuing a request to a remote system for data accessed using a corresponding parameterized data object of the remote system). Examples of adapters include “connectors” implemented in technology available from SAP SE of Walldorf, Germany. Further, the disclosed technology can use technology that underlies data federation technology, such as SAP SE’s Smart Data Access (SDA) and Smart Data Integration (SDI).

[0078] Example 3 - Example Virtual Table, Including Updateable Logical Pointers

[0079] Figure 2 A computing environment 200 in which the disclosed embodiments can be implemented is shown. Figure 2 The basic computing environment 200 includes a number of features that can be common to different embodiments of the disclosed technology, including one or more applications 208 that can access a central computing system 210, which can be a cloud computing system. Central computing system 210 is shown as a monolithic / single system, but it should be understood that, particularly in a cloud environment, the central computing system can include multiple computing systems that work together as a single system. For example, central computing system 210 can be implemented as multiple “nodes,” including an anchor node and zero or more non-anchor nodes. Central computing system 210 can also be a more typical “distributed” database system that includes a master node and one or more worker nodes.

[0080] The central computing system 210 can do so by providing access to data stored in one or more remote database systems 212, which can be federated systems with federated data sources. In turn, the remote database systems 212 can be accessed by one or more applications 214. In some cases, the applications 214 can also be the applications 208. That is, some applications can only (directly) access data in the central computing system 210, some applications can only access data in the remote database systems 212, and other applications can access data in both the central computing system and the remote database systems.

[0081] The central computing system 210 can include a query processor 220. The query processor 220 can include a number of components, including a query optimizer 222 and a query executor 224. The query optimizer 222 can be responsible for determining a query execution plan 226 for a query to be executed using the central computing system 210. The query plan 226 generated by the query optimizer 222 can include a logical plan indicating, for example, an order of operations (e.g., joins, projections) to be performed in the query and a physical plan for implementing such operations. Once developed by the query optimizer 222, the query plan 226 can be executed by the query executor 224. The query plan 226 can be stored in a query plan cache 228 as a cached query plan 230. When a query is resubmitted for execution, the query processor 220 can determine whether a cached query plan 230 exists for the query. If so, the cached query plan 230 can be executed by the query executor 224. If not, a query plan 226 is generated by the query optimizer 222. In some cases, a cached query plan 230 can be invalidated, such as if a change is made to a database schema or at least to a component of the database schema used by the query (e.g., a table or view).

[0082] A data dictionary 234 can maintain one or more database schemas for the central computing system 210. In some cases, the central computing system 210 can implement a multi-tenant environment, and different tenants can have different database schemas. In at least some cases, at least some database schema elements can be shared by multiple database schemas.

[0083] The data dictionary 234 can include definitions (or schemas) for different types of database objects, such as schemas for tables or views. Although the following discussion references tables for ease of explanation, it should be understood that the discussion can apply to other types of database objects, particularly database objects associated with retrievable data, such as materialized views. A table schema can include information such as a name of the table, a number of attributes (or columns or fields) in the table, names of the attributes, data types of the attributes, an order in which the attributes should be displayed, primary key values, foreign keys, associations with other database objects, partitioning information, or replication information.

[0084] The table schemas maintained by the data dictionary 234 can include local table schemas 236, which can represent tables that are primarily maintained on the central computing system 210. The data dictionary 234 can include replicated table schemas 238, which can represent tables for which at least a portion of the table data is stored in the central computing system 210 (or which is primarily managed by the database management system of the central computing system, even if stored outside of the central computing system, such as in a data lake or another cloud service). Tables having data associated with the replicated table schemas 238 will typically periodically have their data updated from a source table, such as a remote table 244 of the data store 242 of the remote database system 212 (a federated data source).

[0085] The replication can be accomplished using one or both of a replication service 246 of the remote database system 212 or a replication service 248 of the central computing system 210. In particular examples, the replication service can be a SAP Intelligent Data Hub (SDI) service, a SAP Landscape Transformation Replication Server, a SAP Data Services, a SAP Replication Server, a SAP Event Stream Processor, or a SAP HANA Direct Extractor Connector, all of which are SAP SE of Walldorf, Germany.

[0086] In some cases, data in the remote database system 212 can be accessed by the central computing system 210 without replicating data from the remote database system, such as using federation techniques. The data dictionary 234 can store virtual table schemas 252 for virtual tables that map to remote tables, such as the remote tables 244 of the remote database system 212. Data in the remote tables 244 can be accessed using a federation service 256, for example using the SAP Intelligent Data Access protocol of SAP SE of Walldorf, Germany. The federation service 256 can be responsible for converting query operations into a format that can be processed by the appropriate remote database system 212, sending the query operations to the remote database system, receiving query results, and providing the query results to the query executor 224.

[0087] The data dictionary 234 can include updatable virtual table schema 260 with updatable logical pointers 262. The updated virtual table schema 260 can optionally be associated with state information 264. The table pointers 262 can be logical pointers that identify what table should be accessed for data corresponding to the virtual table schema 260. For example, depending on the state of the table pointer 262, the table pointer can point to a remote table 244 of the remote database system 212 or a replicated table 266 (which can be generated from the remote table 244) located in a data store 268 of the central computing system 210. The data store 268 can also store data for local tables 270, which can be defined by the local table schema 236.

[0088] The table pointers 262 can change between the remote table 244 and the replicated table 266. In some cases, a user can manually change the table pointed to by the table pointer 262. In other cases, the table pointer 262 can change automatically, for example, in response to detecting a defined condition.

[0089] The state information 264 can include an indicator that identifies the virtual table schema 260 as being associated with a remote table 244 or a replicated table 266. The state information 264 can also include information about the replication status of the replicated table 266. For example, once a request is made to change the table pointer 262 to point to a replicated table 266, it can take time before the replicated table is ready for use. The state information 264 can include whether the replication process has started, has completed, or the status of the progress of generating the replicated table 266.

[0090] Changes to the updatable virtual table schema 260 and the management of the replicated table 266 associated with the virtual table schema can be managed by a virtual table service 272. Although shown as a separate component of the central computing system 210, the virtual table service 272 can be incorporated into other components of the central computing system 210, such as the query processor 220 or the data dictionary 234.

[0091] When a query is executed, the query is processed by the query processor 220, including executing the query using the query executor 224 to obtain data from one or both of the data store 242 of the remote database system 212 or the data store 268 of the central computing system 210. The query results can be returned to the application 208. The query results can also be cached, such as in a cache 278 of the central computing system 210. The cached results can be represented as a cached view 280 (e.g., materialized query results).

[0092] The application 214 can access data in the remote database system 212, such as through the session manager 286. The application 214 can modify the remote table 244. When the table pointer 262 of the updatable virtual table schema 260 references the remote table 244, changes made by the application 214 are reflected in the remote table. When the table pointer 262 references the replicated table 266, changes made by the application 214 can be reflected in the replicated table using the replication service 246 or the replication service 248.

[0093] Example 4 - Example constraint to prevent sending larger query sub-plans to federated system

[0094] Figure 3A A high-level diagram is provided showing typical operations for a query involving federated data sources at a federated system. In particular, Figure 3A A source system 310 and a federated system 314 are shown. The source system 310 can be the system that initially receives a query and performs query optimization, and can also perform certain query operations. For example, the source system 310 can perform query operations with respect to data sources directly associated with the source system. The source system 310 can also perform operations on data received from the federated system 314.

[0095] The federated system 314 can send query results (e.g., a sub-query sent to the federated system 314 by the source system 310) to the source system for further processing, which can include combining the results with results of query operations performed at the source system or on another federated system, or returning results from the federated system 314 in response to the query. As described in Example 1, in some cases certain operations involving data at the federated system 314 cannot be performed at the federated system, even if they involve data of the federated system, such as in the case of a scalar sub-query, where a single value constraint in the query plan can not be passed to the federated system. In these cases, the federated system 314 sends the relevant data to the source system 310, which can then perform operations such as joins or filters, even if these operations only involve federated data.

[0096] Consider a query plan 320 processed by the source system 310. The query plan 320 represents operations in the query as nodes 324 (shown as nodes 324a-324f). Assume that sub-plan A 330 represents an operation that uses data of the federated system 314. Node 324c can represent a join operation of data retrieved using sub-queries of tables 328a, 328b performed by nodes 324d, 324f (which can include table scans). Node 324e represents a constraint. For example, assume that node 324 is a join produced by rewriting a scalar sub-query in the original query provided to the source system 310. Node 324e represents a constraint that the result of each value evaluated is a single value, where violation of the constraint causes the query to fail.

[0097] The question then is which parts of the query plan 320 can be sent to the federated system 314 for execution. As discussed, the operations sent to the federated system are typically expressed in a language such as SQL, rather than sending parts of the query plan. Since sub-plan A 330 includes a constraint, sub-plan A is not sent to the federated system 314. Instead, the optimization or execution process can determine whether a portion of sub-plan A 330 can be sent to the federated system 314, since all of the operations can be expressed in SQL. Sub-plan B 334 is identified, which includes nodes 324d, 324f— table scans of federated data. These operations can be expressed in SQL, so sub-plan B 334 can be sent to the federated system 314. Similarly, sub-plan C 336 can be identified and sent to the federated system 314. Although both sub-plans 334, 336 can be sent to the federated system 314, the constraint of node 324e and the join operation 324c are still executed at the main system 310.

[0098] As noted above, there can be disadvantages to not being able to send larger sub-plans to the federated system 314, for example, if the constraint of node 324e can be expressed in a way that the federated system can handle, then a smaller amount of data can have been sent. Or, if the constraint can be sent to the federated system 314, even if it does not reduce the amount of data, it can provide efficiencies if the query failure can be identified before the data is transferred from the federated system 314 to the source system 310.

[0099] Figure 3B It is shown that if the constraint of node 324e can be sent to the federated system, then the entire sub-plan A 330 can be sent to the federated system 314.

[0100] While the disclosed techniques can be beneficial when using federated database systems, they can also be useful in other scenarios. For example, constraints can be useful even for queries that are executed at a single database system. While the constraints can be introduced as part of query rewriting, in some cases it can be beneficial for a user or process to write a query that includes the constraint if the condition of the constraint that causes the query to fail is not met.

[0101] Example 5 - Example blocking constraint introduced during rewriting of a scalar subquery

[0102] Figure 4 Example SQL statements 410, 412 are shown that create two tables, and a SQL statement 414 that defines a SELECT operation that includes a scalar subquery. Figure 4 A graphical representation 420 of the query is also provided.

[0103] In statement 414, SELECT TABLE1.COL1 FROM… can be referred to as the main query or outer query that selects column COL1 from TABLE1. Statement 414 includes a scalar subquery of SELECT COL2 FROM TABLE2 WHERE TABLE1.COL3 = TABLE2.COL3. For purposes of using the disclosed technology, a scalar subquery is a subquery that returns a single value, even though that single value can be used to select multiple values as part of another query operation, such as in an outer select (SELECT). In SQL statement 414, the scalar subquery dynamically determines the value of COL2 from TABLE2 based on the condition related to TABLE1 (TABLE1) and TABLE2 (TABLE2).

[0104] The scalar subquery in SQL statement 414 is used to filter records in TABLE1 based on the condition that for corresponding rows between TABLE1 and TABLE2 that match on COL3, COL2 in TABLE1 must match COL2 in TABLE2. The functionality and correctness of this query implicitly relies on the assumption that this subquery (SELECT COL2 FROM TABLE2 WHERE TABLE1.COL3 = TABLE2.COL3) will return a single value. This is where the uniqueness constraint comes into play.

[0105] If TABLE2.COL3 is not unique, or the relationship between TABLE1.COL3 and TABLE2.COL3 does not guarantee a single corresponding TABLE2.COL2 for each TABLE1.COL3, then the subquery can return multiple rows, causing the query to fail with an error such as “subquery returns more than 1 row”

[0106] More specifically, in SQL, under an equality condition in the WHERE clause, such as TABLE1.COL2 = (subquery), the expectation is that the subquery on the right side of the equality operator will return exactly one value. This is because the equality operator (=) is designed to compare a single value from the left-hand side to another single value from the right-hand side. When a subquery in a SQL statement returns multiple values, the equality comparison TABLE1.COL2 = (subquery) becomes invalid because the right-hand side does not resolve to a single value. SQL cannot use the equality operator to compare one value to multiple values, resulting in a runtime error.

[0107] Figure 5An SQL statement 510 is introduced that rewrites the original query from SQL statement 414 by eliminating the scalar subquery and replacing it with a direct join operation. The diagram also includes a visual representation 520 of the modified query. Previously, SQL statement 414 used a nested WHERE clause that would result in a potential failure if the scalar subquery returned multiple values. Unlike SQL statement 414, SQL statement 510 removes the nested WHERE clause, allowing both the join operation and the WHERE clause to handle multiple results without causing the query to fail. Specifically, the use of a HAVING (such that) clause with TABLE1.COL2 = TABLE2.COL2 allows multiple matching rows. This is because the HAVING clause evaluates each row pair individually, as opposed to the approach of the scalar subquery, which would attempt a single value comparison and potentially fail when multiple matches are encountered.

[0108] As discussed, the query optimizer can enhance the rewritten query, for example, through constraints in the query plan, to mirror the operational characteristics of the original query, including the enforcement of the uniqueness constraint inherent to the scalar subquery. In the example query execution plan 600 shown (in the form of an explain plan), one of the included key optimizer constraints is the management of data retrieval through table scans in step 1. The table scan operations on table 1 and table 2 not only ensure that the extracted data satisfies the join condition t1.COL3 = t2.COL3, but also enforce uniqueness on these columns similar to a unique index. If the table scan or subsequent operations detect multiple matches for what should be a uniquely joined condition, the optimizer can be configured to flag this as an error, causing the query to fail, thus preventing data integrity issues and ensuring robust error handling. Figure 6

[0109] In the example query execution plan, step 2 involves a filtering operation that further refines the data set. This operation, while not explicitly mentioned in the rewritten SQL statement, is conducted under the constraints imposed by the optimizer. It verifies the matches of t1.COL3 with t2.COL3 and ensures that these matches are unique. If multiple rows from table 2 correspond to a single row in table 1 in a way that violates the assumed uniqueness, this filtering operation can trigger a failure, replicating the behavior of the scalar subquery where multiple returns would invalidate the query. This implementation maintains the integrity of the query by ensuring that the join condition results in a uniquely defined data set, thus preventing the possibility of ambiguous or erroneous data processing.

[0110] The nested loop join operation of step 3 combines the filtered rows of TABLE1 and TABLE2 based on the join condition TABLE1.COL2 = TABLE2.COL2. It iterates through each row of one table and matches it with the corresponding row from the other table, resulting in a joined result. ​

[0111] The hash aggregation operation of Step 4 performs a grouping operation on the filtered dataset from Table 1, grouping rows based on the value of COL1. This constraint ensures that each distinct value of COL1 forms a separate group, and an aggregate function such as COUNT or SUM is computed within each group. By applying the grouping constraint, the operation ensures that the aggregation is performed correctly and each group represents a unique value of COL1. This helps replicate the behavior of the original scalar subquery, where aggregation is performed on different values of COL1, resulting in a final result that is highly consistent with the output of the original query.

[0112] Having both filter and group by constraints in the query plan is beneficial for query optimization and accuracy. The filter constraint reduces the dataset to only relevant rows before performing the grouping operation, eliminating unnecessary data processing. The group by constraint ensures that aggregation is accurately performed on different values of COL1, providing a final result that closely matches the behavior of the original scalar subquery. These constraints collectively optimize query execution, improve performance, and ensure the correctness of query results, making the query plan more robust and efficient.

[0113] As explained, while the SQL statement 510 can be executed by the federated system, the optimizer constraints cannot typically be passed to the federated system. Therefore, the operations for the rewritten version of the scalar subquery (SQL statement 510) are typically performed by the source system after receiving the necessary data from the federated system. Alternatively, the original scalar subquery itself can be sent to the federated system for execution.

[0114] Switching from a scalar subquery to a simple join in a SQL query can provide significant performance benefits due to several underlying efficiency and resource utilization factors, particularly when dealing with large datasets. Executing a scalar subquery once per row in an outer query means that if the outer query processes a large number of rows, the scalar subquery also needs to be executed that many times. This repeated execution can be very inefficient, especially if the subquery involves complex calculations or accesses large tables. Each execution of the scalar subquery involves its own set of data extraction and processing, including parsing SQL, planning the query, and potentially reading from disk if the data is not cached, which heavily consumes CPU and I / O resources when repeated.

[0115] In contrast, simple joins are typically handled in a single pass of the data using optimization algorithms such as hash joins or merge joins. Modern database systems are highly optimized for join operations, utilizing indexes, partitioning, and in-memory data structures to efficiently find matching rows, reducing disk I / O and speeding up data retrieval. Furthermore, joins generally make better use of database resources. Unlike scalar subqueries, which can cause high load on database resources by repeating disk accesses, joins optimize memory usage and support parallel processing, which can significantly reduce query execution time.

[0116] Furthermore, joins improve data locality and caching. The repeated execution of scalar subqueries can prevent efficient use of a database’s cache, as each execution can require loading data into memory, potentially evicting other valuable data from the cache. However, with joins, especially if tables are properly indexed or if the database engine can preload necessary data into memory, much of the operation can be confined to RAM, which is much faster than disk operations. Efficient use of cache and memory results in faster data processing and less stress on a database’s physical I / O system.

[0117] Finally, the scalability of joins tends to be better than that of scalar subqueries. As data volume grows, poorly performing scalar subqueries can deteriorate sharply, with total time and resource cost scaling linearly with data volume. In contrast, joins follow a more predictable performance curve and can handle increases in data volume more smoothly, benefiting from batch processing, parallel execution, and optimized memory management. This makes joins particularly suitable for high-volume or complex query environments, where joins can reduce execution time, lower resource consumption, improve cache utilization, and provide overall better scalability. These factors collectively make the use of simple joins the preferred method in large-scale database management systems, resulting in significantly improved performance and efficiency compared to scalar subqueries.

[0118] The aforementioned advantages can be compounded, as queries such as those in enterprise database systems can often be quite complex. A single query can include many scalar subqueries for joining data sources, where performance benefits can be realized by rewriting the scalar subqueries as joins, as shown in Figure 7 where, in some examples, the objects on which joins are performed can be database tables or views.

[0119] Example 6 - Example constraint representation usable by federated systems

[0120] Figure 8The particular technique of this example is shown where a SQL (or other query processing language) keyword (key) is introduced into a version 810 of the query 510 representing the constraint where the query stops execution / fails if the constraint is not satisfied. In this particular example, the keyword "SINGLE" is used to indicate that only one value should be provided for a provided argument (such as a particular table column).

[0121] Figure 8 A graphical depiction 820 of the query 810 is also provided. The graphical depiction 820 includes a grouping constraint 830 and a filter constraint 834, which correspond to the constraints discussed in association with the query plan 600 of Figure 6 .

[0122] The "SINGLE" keyword can be associated with one or more functions that determine whether a given constraint is satisfied. Figure 9 Example python functions 910 for implementing filter constraints and example python functions 920 for implementing grouping constraints are provided.

[0123] Figure 10 A table 1000 is provided that provides an explanation of the query 810, where the table includes a column 1014 that provides the operator name and a column 1018 that provides additional details for a given operator. The operations above the dashed line are performed at the source system, while the operations below the dashed line are performed at the remote system. As can be seen, the disclosed technique allows the operations associated with the join over group by representation of the subquery to be performed at the remote system, thereby providing the efficiencies described earlier. Figure 8

[0124] In the case of the "SINGLE" keyword, the keyword can be specific to a join operation corresponding to a rewritten scalar subquery, or can be used for other cases where a check of a single value is desired when associated with a particular argument, depending on the implementation.

[0125] In other scenarios, the keyword can specify that the query should fail if the constraint is not satisfied, but the keyword can be used with a variety of constraints. For example, the keyword "CONSTRAINT" can be used instead of the "SINGLE" keyword. The logic of the query processor can then determine the appropriate constraint to use, such as depending on the provided argument or the context around the keyword. In the case of the SQL statement 810, the context can include the argument being a table column, and the keyword is used in relation to a grouping operation on the results of a join.

[0126] ​The following discussion provides additional examples of how the CONSTAINT keyword can be used. The following query retrieves data from the employee table, but fails if any of the retrieved salaries are below a certain threshold (e.g., $30,000).

[0127] SELECT *

[0128] FROM employees

[0129] CONSTRAINT salary >= 30000;

[0130] The following query computes the average salary from the employee table, but fails if the average salary is less than $50,000.

[0131] SELECT AVG(salary) AS average_salary

[0132] FROM employees

[0133] CONSTRAINT average_salary >= 50000;

[0134] The following query retrieves the total salary for each department from the employee table, but fails if the total salary for any department (department) is less than $100,000.

[0135] SELECT department_id, SUM(salary) AS total_salary

[0136] FROM employees

[0137] GROUP BY department_id

[0138] CONSTRAINT total_salary >= 100000;

[0139] The following query joins the employee table and the department table to list the employees in the "Engineering" department, but fails if the total number of such employees is less than 10.

[0140] SELECT e.name, d.department_name

[0141] FROM employees e

[0142] JOIN departments d ON e.department_id = d.department_id

[0143] WHERE d.department_name = ‘”Engineering”

[0144] CONSTRAINT COUNT(e.employee_id) >= 10;

[0145] The following query updates the salary in the employees table, but fails if there are actually no rows to update (for example, if there is no employee with the given id). Typically, if a constraint check fails for an update, insert, or delete operation, a transaction rollback can be implemented.

[0146] UPDATE employees

[0147] SET salary = salary + 5000

[0148] WHERE employee_id = 12345

[0149] CONSTRAINT ROW_COUNT() > 0;

[0150] The following query deletes from the employees table where the employee has not logged in for more than a year, but fails if fewer than 5 rows are to be deleted.

[0151] DELETE FROM employees

[0152] WHERE last_login < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

[0153] CONSTRAINT ROW_COUNT() >= 5;

[0154] The following query inserts a new employee into the employees table, but fails if an employee with the same email already exists.

[0155] INSERT INTO employees (name, email, salary)

[0156] VALUES (“John Doe”, “john.doe@example.com”, 55000)

[0157] CONSTRAINT NOT EXISTS (SELECT 1 FROM employees WHERE email =“john.doe@example.com”) ;

[0158] Example 7 - Embodiment Performance Improvement

[0159] Figure 11 A diagram 1100 of operations performed when executing a SQL statement 1110 is provided, where the SQL statement corresponds to a rewritten version of a query that originally contained a scalar subquery with a join. In this case, the original characteristic of the scalar subquery, which fails if multiple values are compared as part of that subquery, is preserved by placing a constraint 1120, 1124 in the query plan. However, the constraint prevents the larger subplan from being sent to the federated system. As can be seen, the operation 1144 performed at the federated system results in 1,000,000 being sent to the source system.

[0160] Figure 12 A diagram 1200 of operations performed when executing a SQL statement 1210 is provided, where the SQL statement 1210 corresponds to the SQL statement 1110, except that the constraint is expressed in the SQL statement 1210, rather than being specified in the query plan, but not specified in the SQL statement 1210 itself.

[0161] Because the constraint is expressed in the SQL statement 1210, the join operation and the constraint check can be performed at the federated system. Rather than transmitting 1,000,000 rows to the source system, the result of the operations 1240, 1242 causes a single row to be returned to the source system, saving a significant amount of computational resources.

[0162] Example 8 - Example Operations Involving Virtual Parameterized Data Objects

[0163] Figure 13A A process 1300 for rewriting a query to include a constraint is shown. At 1304, a first query is received at a first database system. The first query includes a first plurality of query operations. The first query is rewritten at 1308 to provide a second query. The second query includes one or more query operations that are different from the first plurality of query operations of the first query. A first query operation of the one or more query operations is a keyword in a query language and expresses a constraint. During query execution, the query fails if the constraint is not satisfied.

[0164] Figure 13Bis a flowchart of a process 1330 for executing a query at a federated database. At a federated database system, at 1334, a first plurality of query operations is received from a source database system. The first plurality of query operations includes a first query operation that includes a keyword in a query language and expresses a constraint. During execution of the first plurality of query operations at the federated database system, the query including the first query operation fails if the constraint is not satisfied. The constraint is evaluated at 1338. At 1342, an execution result from execution of at least a portion of the first plurality of query operations is returned to the source database system.

[0165] Figure 13C is a flowchart of a process 1350 for executing a query that includes a constraint. At 1354, a first query is received or generated by a first database system. The first query includes one or more query operations, where a first query operation is a keyword in a query language that expresses a constraint. The query fails if the constraint is not satisfied during query execution. At 1358, the first query is caused to be executed. At 1362, during execution of the first query, it is determined that the constraint is not satisfied. Based on the determination, at 1366, the first query is caused to fail.

[0166] Example 9 - Additional Embodiments

[0167] In Example 1, a computing system is provided that includes at least one memory, one or more hardware processor units coupled to the memory, and one or more computer-readable storage media. The storage media store computer-executable instructions that, when executed, cause the computing system to perform operations. The operations include receiving, at a first database system, a first query, where the first query includes a first plurality of query operations. The first query is rewritten to provide a second query. The second query includes one or more query operations that are different from the first plurality of query operations of the first query. A first query operation of the one or more query operations is a keyword in a query language and expresses a constraint. During query execution, the query fails if the constraint is not satisfied.

[0168] In Example 2, the operations of the computing system from Example 1 are extended to include sending the plurality of query operations of the second query to a federated database system. A plurality of the second plurality of query operations includes the first query operation.

[0169] In Example 3, the operations of the computing system from Example 1 or Example 2 are further extended to include receiving, from the federated database system, an indicator that the constraint is not satisfied, and subsequently terminating the query.

[0170] In Example 4, the computing system from any of Examples 1-3 is specified such that the first query includes a query operation that is a scalar subquery that is rewritten into a query operation in the second query that includes a join operation and a group operation.

[0171] In Example 5, the computing system from Example 4 checks whether there is only one value for each different group defined by the group operation.

[0172] In Example 6, the computing system from any of Examples 1-5, the constraint checks for a particular condition and is usable with multiple types of query operations.

[0173] In Example 7, the computing system from any of Examples 1-6, during query execution, determines an implementation of the constraint to use based on a context in the second query for the constraint.

[0174] In Example 8, a method implemented in a computing system is provided, the computing system including at least one hardware processor and at least one memory coupled to the hardware processor. The method includes receiving, at a federated database system from a source database system, a first plurality of query operations. The first plurality of query operations includes a first query operation that includes a keyword of a query language and expresses a constraint. During execution of the first plurality of query operations at the federated database system, a query including the first query operation fails if the constraint is not satisfied. The constraint is evaluated. An execution result from execution of at least a portion of the first plurality of query operations is returned to the source database system.

[0175] In Example 9, the method as described in Example 8 includes a case where the constraint is not satisfied and the execution result includes an indicator of the query failure.

[0176] In Example 10, the method from Example 8 includes a case where the constraint is satisfied and the execution result includes data that satisfies a condition of the first plurality of query operations.

[0177] In Example 11, the method from any of Examples 8-10 includes a case where the first plurality of query operations includes a query operation that corresponds to a scalar subquery that is rewritten into a query operation that includes a join operation and a group operation.

[0178] In Example 12, the method from Example 11 includes a case where evaluating the constraint includes determining whether there is only one value for each different group defined by the group operation.

[0179] In Example 13, the method from any of Examples 8-12 includes a case where the constraint checks for a particular condition and is usable with multiple types of query operations.

[0180] In Example 14, the method from any of Examples 8-13 includes the following case: during the execution of a query by the query executor of the federated database system, the implementation of the constraint to be used is determined based on the context in the first plurality of query operations against the constraint.

[0181] In Example 15, a method implemented in a computing system is provided. The method includes receiving or generating a first query at a first database system. The first query includes one or more query operations, wherein the first query operation is a keyword in a query language expressing constraints. If a constraint is not satisfied during query execution, the query fails. The method also includes causing the first query to be executed, determining during the execution of the first query that a constraint is not satisfied, and causing the first query to fail based on that determination.

[0182] In Example 16, the method from Example 15 is extended to include rewriting the second query to provide the first query operation to the first query.

[0183] In Example 17, the method from Example 15 or Example 16 is further extended to include sending at least a portion of one or more query operations of a first query that includes a first query operation to a second database system for execution.

[0184] In Example 18, the method from Example 17 is further extended to include receiving an indicator from a second database system that a constraint is not satisfied, and then terminating the query.

[0185] In Example 19, the method from Example 17 includes the following case: the second query includes a query operation against a scalar subquery, and the scalar subquery is rewritten as a query operation in the first query that includes a join operation and a grouping operation.

[0186] In Example 20, the method from Example 19 includes the following case: evaluating constraints includes determining whether there is only one value for each distinct group defined by the grouping operation.

[0187] Example 9 - Computing System

[0188] Figure 14 A generalized example of a suitable computing system 1400 in which the described innovation can be implemented is depicted. The computing system 1400 is not intended to impose any limitation on the scope or functionality of this disclosure, as the innovation can be implemented in various general-purpose or special-purpose computing systems.

[0189] refer to Figure 14 The computing system 1400 includes one or more processing units 1410, 1415 and memories 1420, 1425. Figure 14In particular embodiments, the basic configuration 1430 is included within the dashed line. The processing units 1410, 1415 execute computer-executable instructions, such as those used to implement the database environment and associated methods described in examples 1-8. A processing unit can be a general- purpose central processing unit (CPU), processor in an application-specific integrated circuit (ASIC) or any other type of processor. In a multi-processing system, multiple processing units execute computer-executable instructions to increase processing power. For example, Figure 14 A central processing unit 1410 and a graphics processing unit or co-processing unit 1415 are shown. The tangible memory 1420, 1425 can be volatile (such as registers, cache, RAM), non-volatile (such as ROM, EEPROM, flash memory, etc.) memory, or some combination of the two, accessible by the processing units 1410, 1415. The memory 1420, 1425 stores software 1480 implementing one or more innovations described herein, in the form of computer-executable instructions suitable for execution by the processing units 1410, 1415.

[0190] The computing system 1400 can have additional features. For example, the computing system 1400 includes storage 1440, one or more input devices 1450, one or more output devices 1460, and one or more communication connections 1470. An interconnection mechanism (not shown) such as a bus, controller, or network interconnects the components of the computing system 1400. Typically, operating system software (not shown) provides an operating environment for other software executing in the computing system 1400, and coordinates activities of the components of the computing system 1400.

[0191] The tangible storage 1440 can be removable or non-removable, and includes magnetic disks, magnetic tapes or cassettes, CD-ROMs, DVDs, or any other medium that can be used to store information in a non-transitory way and that can be accessed within the computing system 1400. The storage 1440 stores instructions for the software 1480 implementing one or more innovations described herein.

[0192] The input devices 1450 can be a touch input device such as a keyboard, mouse, pen, or trackball, a voice input device, a scanning device, or another device that provides input to the computing system 1400. The output devices 1460 can be a display, printer, speaker, CD-writer, or another device that provides output from the computing system 1400.

[0193] The communication connection(s) 1470 enable communication over a communication medium to another computing entity such as another database server. The communication medium conveys information such as computer-executable instructions, audio or video input or output, or other data in a modulated data signal. A modulated data signal is a signal that has one or more of its characteristics set or changed in such a manner as to encode information in the signal. By way of example, and not limitation, communication media can use an electrical, optical, RF, or other carrier.

[0194] The innovations can be described in the general context of computer- executable instructions, such as those included in program modules, being executed on a computing system on a target real or virtual processor. Generally, program modules or components include routines, programs, libraries, objects, classes, components, data structures, etc. that perform particular tasks or implement particular abstract data types. In various embodiments, the functionality of the program modules can be combined or split between program modules as desired in various embodiments. Computer-executable instructions for program modules can be executed within a local or distributed computing system.

[0195] The terms "system" and "device" are used interchangeably herein. Unless the context clearly indicates otherwise, neither term implies any limitation on the types of computing systems or computing devices. In general, computing systems or computing devices can be local or distributed, and can include special-purpose hardware and / or general-purpose hardware in combination with software implementing the functionality described herein.

[0196] For presentation purposes, the detailed description uses terms such as "determine" and "use" to describe computer operations in a computing system. These terms are high-level abstractions of the operations performed by a computer and should not be confused with acts performed in actions by a human. The actual computer operations corresponding to these terms vary depending on implementation.

[0197] Example 10 - Cloud Computing Environment

[0198] Figure 15 An example cloud computing environment 1500 in which the described technologies can be implemented is depicted. The cloud computing environment 1500 includes a cloud computing service 1510. The cloud computing service 1510 can include various types of cloud computing resources, such as computer servers, data repositories, networking resources, and the like. The cloud computing service 1510 can be located centrally (e.g., provided by a data center of an enterprise or organization) or distributed (e.g., provided by various computing resources located at different locations, such as different data centers, and / or located at different cities or countries).

[0199] Cloud computing services 1510 are utilized by various types of computing devices (e.g., client computing devices), such as computing devices 1520, 1522, and 1524. For example, computing devices (e.g., 1520, 1522, and 1524) can be computers (e.g., desktop or laptop computers), mobile devices (e.g., tablet computers or smartphones), or other types of computing devices. For example, computing devices (e.g., 1520, 1522, and 1524) can utilize cloud computing services 1510 to perform computing operations (e.g., data processing, data storage, etc.).

[0200] Example 11 - Implementation

[0201] Although the operations of some of the disclosed methods are described in particular orders, unless the particular language in this document specifies otherwise, the described ordering is not the only way in which the disclosed methods can be performed. For example, unless the particular language in this document specifies otherwise, the operations can be performed in any order or in parallel, and the ordering described herein is not limiting.

[0202] Any of the disclosed methods can be implemented as computer-executable instructions or a computer program product stored on one or more computer-readable storage media (e.g., tangible, non-transitory computer-readable storage media) and executed on a computing device (e.g., any available computing device, including a smartphone or other mobile device that includes computing hardware). Tangible computer-readable storage media are computer-readable storage media having a physical form that is readable by a computing device. Tangible computer-readable storage media include, for example, one or more optical media discs (such as DVD or CD), a volatile memory component (such as DRAM or SRAM), or a non-volatile memory component (such as flash memory or hard drives). As examples and as referenced herein, computer-readable storage media include memory 1420 and 1425 and storage 1440. The term computer-readable storage media does not include signals and carrier waves. Further, the term computer-readable storage media does not include communication connections (e.g., 1470). Figure 14

[0203] ​Any computer-executed instructions for implementing the disclosed technology, as well as any data created and used during implementation of the disclosed embodiments, can be stored on one or more computer-readable storage media. The computer-executable instructions can be part of, for example, a dedicated software application or a software application that is accessed or downloaded via a web browser or other software application (such as a remote computing application). Such software can be executed, for example, on a single local computer (e.g., any suitable commercially available computer) or in a network environment using one or more network computers (e.g., via the Internet, a wide-area network, a local-area network, a client-server network (such as a cloud-computing network), or other such network).

[0204] For clarity, only certain selected aspects of the software-based implementations are described. Other details that are well known in the art are omitted. For example, it should be understood that the disclosed technology is not limited to any particular computer language or program. For instance, the disclosed technology can be implemented by software written in C++, Java, Perl, JavaScript, Python, Ruby, ABAP, Structured Query Language, Adobe Flash, or any other suitable programming language, or in some examples, by a markup language, such as html or XML, or a combination of suitable programming languages and markup languages. Likewise, the disclosed technology is not limited to any particular computer or type of hardware. Certain details of suitable computers and hardware are well known and need not be set forth in detail in this disclosure.

[0205] Furthermore, any software-based embodiments (including, for example, computer-executable instructions for causing a computer to perform any of the disclosed methods) can be uploaded, downloaded, or remotely accessed through a suitable communication means. Such suitable communication means include, for example, the Internet, the World Wide Web, an intranet, software applications, cable (including fiber optic cable), magnetic communications, electromagnetic communications (including RF, microwave, and infrared communications), electronic communications, or other such communication means.

[0206] The disclosed methods, apparatus, and systems should not be construed as limiting in any way. Instead, the present disclosure is directed toward all novel and nonobvious features and aspects of the various disclosed embodiments, alone and in various combinations and subcombinations with each other. The disclosed methods, apparatus, and systems are not limited to any specific aspect or feature or combination of features, nor do the disclosed embodiments require the presence of any particular described advantages or solve any particular disclosed problem.

[0207] The techniques from any example can be combined with the techniques described in any one or more of the other examples. In view of the many possible embodiments to which the principles of the disclosed technology can be applied, it should be recognized that the examples are shown and described by way of illustration and not as limitations. The scope of the disclosed technology should be determined with reference to the claims that are appended hereto and their equivalents.

Claims

1. A computing system comprising: at least one memory; one or more hardware processor units coupled to the at least one memory; and one or more computer-readable storage media storing computer-executable instructions that, when executed, cause the computing system to perform operations comprising: receiving, at a first database system, a first query comprising a first plurality of query operations; and rewriting the first query to provide a second query comprising a second plurality of query operations, the second plurality of query operations comprising one or more query operations different from the first plurality of query operations of the first query, a first query operation of the one or more query operations being a keyword in a query language expressing a constraint, wherein, during query execution, the first query fails when the constraint is not satisfied.

2. The computing system of claim 1, the operations further comprising: sending, to a federated database system, a plurality of query operations of the second plurality of query operations, the plurality of query operations of the second plurality of query operations including the first query operation.

3. The computing system of claim 2, the operations further comprising: receiving, from the federated database system, an indicator that the constraint is not satisfied; and terminating the query.

4. The computing system of claim 1, wherein, the first query comprising a query operation for a scalar subquery, and the scalar subquery being rewritten in the second query to include a join operation and a grouping operation.

5. The computing system of claim 4, wherein, the constraint checking for whether there is only one value for each distinct group defined by the grouping operation.

6. The computing system of claim 1, wherein, the constraint checking a particular condition and being usable with multiple types of query operations.

7. The computing system of claim 1, wherein, during query execution, a query executor determining an implementation of the constraint to use based on a context in the second query for the constraint.

8. A method implemented in a computing system comprising at least one hardware processor and at least one memory coupled to the at least one hardware processor, the method comprising: receiving, at a federated database system, a first plurality of query operations from a source database system, the first plurality of query operations including a first query operation expressing a constraint, the first query operation being a keyword of a query language, wherein, during query execution of the first plurality of query operations at the federated database system, a query including the first plurality of query operations fails when the constraint is not satisfied; evaluating the constraint; and providing, to the source database system, an execution result from executing at least a portion of the first plurality of query operations.

9. The method of claim 8, wherein, the constraint not being satisfied, and the execution result including an indicator that the query failed.

10. The method of claim 8, wherein, the constraint being satisfied, and the execution result including data satisfying a condition of the first plurality of query operations.

11. The method of claim 8, wherein, the first plurality of query operations including a query operation corresponding to a scalar subquery, the scalar subquery being rewritten to include a join operation and a grouping operation.

12. The method of claim 11, wherein, evaluating the constraint including determining whether there is only one value for each distinct group defined by the grouping operation.

13. The method of claim 8, wherein, The constraint checks for a particular condition and can be used with multiple types of query operations.

14. The method of claim 8, further comprising: determining, during execution of a query by a query executor of the federated database system, an implementation of the constraint to use based on a context in the first plurality of query operations for the constraint.

15. One or more computer-readable storage media comprising: computer-executable instructions that, when executed by a computing system comprising at least one hardware processor and at least one memory coupled to the at least one hardware processor, cause the computing system to receive or generate, at a first database system, a first query comprising one or more query operations, a first query operation of the one or more query operations being a keyword in a query language that expresses a constraint, wherein the query fails during query execution if the constraint is not satisfied; computer-executable instructions that, when executed by the computing system, cause the computing system to cause the first query to be executed; computer-executable instructions that, when executed by the computing system, cause the computing system to determine, during execution of the first query, that the constraint is not satisfied; and computer-executable instructions that, when executed by the computing system, cause the computing system to cause the first query to fail based on the determination that the constraint is not satisfied.

16. The one or more computer-readable storage media of claim 15, further comprising: computer-executable instructions that, when executed by the computing system, cause the computing system to rewrite a second query to provide the first query operation to the first query. The second query comprises query operations for a scalar subquery, and the scalar subquery is rewritten in the first query to include a join operation and a grouping operation.

17. The one or more computer-readable storage media of claim 16, wherein, 18. The one or more computer-readable storage media of claim 17, wherein evaluating the constraint comprises determining whether there is only one value for each different group defined by the grouping operation.

19. The one or more computer-readable storage media of claim 15, further comprising: computer-executable instructions that, when executed by the computing system, cause the computing system to send at least a portion of the one or more query operations of the first query comprising the first query operation to a second database system for execution.

20. The one or more computer-readable storage media of claim 19, further comprising: computer-executable instructions that, when executed by the computing system, cause the computing system to receive, from the second database system, an indicator that the constraint is not satisfied; and computer-executable instructions that, when executed by the computing system, cause the computing system to terminate the query. ​ ​

Images