Transitive local predicated across multiple joins
In an example, a transitive local predicates may be inferred across multiple joins, wherein the multiple outer joins include multiple outer joins. A query connecting tables by the multiple joins is analyzed. A local predicate may then be identified from the analyzed query and may be determined to be either a where-clause local predicate or an on-clause local predicate. Accordingly, a transitive local predicate may be inferred across a selection of the tables based on the determination.
1. A method to infer transitive local predicates across multiple joins, comprising:
analyzing a query to a database connecting tables by the multiple joins, wherein the multiple joins include multiple outer joins;
identifying, utilizing a processor, a local predicate from analyzing the query;
determining whether the local predicate is a where-clause local predicate or an on-clause local predicate, wherein the where-clause local predicate is a local predicate appearing in a where-clause of a Structured Query Language (SQL) statement and the on-clause local predicate is a local predicate appearing in an on-clause of a SQL statement;
determining whether the local predicate is a strong local predicate;
determining whether a plurality of join predicates are equality join predicates;
inferring a transitive local predicate across a selection of the tables based on the determining whether the local predicate is a where-clause local predicate or an on-clause local predicate, the determining whether the local predicate is a strong local predicate, and the determining whether the plurality of join predicates are equality join predicates; and
performing, utilizing the transitive local predicate, join operations for the query to the database.
2. The method of claim 1 , wherein the inferring of the transitive local predicate across the selection of the tables comprises:
in response to determining that the local predicate is a where-clause local predicate, inferring the transitive local predicate across all the tables if the where-clause local predicate is a strong local predicate and all join predicates are equality join predicates.
3. The method of claim 1 , wherein the inferring of the transitive local predicate across the selection of the tables comprises:
in response to determining that the local predicate is an on-clause local predicate,
identifying a local table associated with the on-clause local predicate;
determining a table from the tables that is connected to the local table by a left outer join or an inner join; and
inferring a transitive local predicate to the table if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.
4. The method of claim 3 , further comprising:
determining each table from the tables that is connected to the local table by a sequence of joins consisting of at least one of a left outer join and an inner join; and
inferring the transitive local predicate across each table that is connected to the local table by the sequence of joins if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.
5. The method of claim 1 , wherein the multiple joins comprise at least one of a left outer join, a right outer join, a full outer join, and an inner join.
6. The method of claim 1 , wherein the local predicate is a strong local predicate that does not evaluate to true on NULL values for a predicate column.
7. The method of claim 1 , wherein the local predicate includes at least one of a Boolean operator and a comparison operator.
8. A system to infer transitive local predicates across multiple joins, comprising:
a query evaluator engine, executed by a processor, to:
analyze a query connecting tables by the multiple joins, wherein the multiple joins include multiple outer joins,
identify a local predicate from analyzing the query,
determine whether the local predicate is a where-clause local predicate or an on-clause local predicate, wherein the where-clause local predicate is a local predicate appearing in a where-clause of a Structured Query Language (SQL) statement and the on-clause local predicate is a local predicate appearing in an on-clause of a SQL statement;
determine whether the local predicate is a strong local predicate; and
determine whether a plurality of join predicates are equality join predicates; and
a predicate generator engine to infer a transitive local predicate across a selection of the tables based on the determination whether the local predicate is a where-clause local predicate or an on-clause local predicate, the determination whether the local predicate is a strong local predicate, and the determination whether the plurality of join predicates are equality join predicates.
9. The system of claim 8 , wherein the predicate generator engine is to, in response to a determination that the local predicate is a where-clause local predicate, infer the transitive local predicate across all the tables if the where-clause local predicate is a strong local predicate and all join predicates are equality join predicates.
10. The system of claim 8 , wherein the predicate generator engine is to, in response to a determination that the local predicate is an on-clause local predicate,
identify a local table associated with the on-clause local predicate;
determine a table from the tables that is connected to the local table by a left outer join or an inner join; and
infer a transitive local predicate to the table if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.
11. The system of claim 10 , wherein the predicate generator engine is to:
determine each table from the tables that is connected to the local table by a sequence of joins consisting of at least one of a left outer join and an inner join; and
infer the transitive local predicate across each table that is connected to the local table by the sequence of joins if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.
12. A non-transitory computer readable medium including machine readable instructions executable by a processor to:
analyze a query connecting tables by multiple joins, wherein the multiple joins include multiple outer joins;
identify a local predicate from analyzing the query;
determine whether the local predicate is a where-clause local predicate or an on-clause local predicate, wherein the where-clause local predicate is a local predicate appearing in a where-clause of a Structured Query Language (SQL) statement and the on-clause local predicate is a local predicate appearing in an on-clause of the SQL statement;
determine whether the local predicate is a strong local predicate;
determine whether a plurality of join predicates are equality join predicates; and
infer a transitive local predicate across a selection of the tables based on the determination whether the local predicate is a where-clause local predicate or an on-clause local predicate, the determination whether the local predicate is a strong local predicate, and the determination whether the plurality of join predicates are equality join predicates; and
perform, utilizing the transitive local predicate, join operations relating to the query to the database.
13. The non-transitory computer readable medium of claim 12 , wherein the machine readable instructions are executable by the processor to, in response to a determination that the local predicate is a where-clause local predicate, infer the transitive local predicate across all the tables if the where-clause local predicate is a strong local predicate and all join predicates are equality join predicates.
14. The non-transitory computer readable medium of claim 12 , wherein the machine readable instructions are executable by the processor to, in response to a determination that the local predicate is an on-clause local predicate,
identify a local table associated with the on-clause local predicate;
determine a table from the tables that is connected to the local table by a left outer join or an inner join; and
infer a transitive local predicate to the table if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.
15. The non-transitory computer readable medium of claim 14 , wherein the machine readable instructions are executable by the processor to:
determine each table from the tables that is connected to the local table by a sequence of joins consisting of at least one of a left outer join and an inner join; and
infer the transitive local predicate across each table that is connected to the local table by the sequence of joins if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.
16. The method of claim 1 , wherein the identifying includes converting the query into an outputted parse tree.
17. The method of claim 16 , wherein the determining whether the local predicate is a where-clause local predicate or an on-clause local predicate includes searching the outputted parse tree.
18. The system of claim 8 , wherein to identify the local predicate, the query evaluator engine is to convert the query into an outputted parse tree.
19. The system of claim 18 , wherein to determine whether the local predicate is a where-clause local predicate or an on-clause local predicate, the query evaluator engine is to search the outputted parse tree.
20. The non-transitory computer readable medium of claim 12 , wherein to identify the local predicate, the machine readable instructions are executable by the processor to convert the query into an outputted parse tree.