Back to results
US 11,893,016 B1Granted
Secure predicate derivation of queries using metadata
AssigneeSnowflake Inc.
InventorsZixuan Geng, Sangyong Hwang, Nitish Jindal
Life of this patent
Estimating the term…
Abstract
The subject technology provides embodiments for enabling derivation of predicates not only from other predicates but also from metadata such as expression properties. In examples, predicates are derived, avoiding unwanted impact on cardinality estimation. In other examples, predicates are derived, avoiding artificial runtime errors and providing a way to avoid security issues with secure views.
Claims30
A system comprising:
- at least one processor;
- and
- a memory device including instructions, which when executed by the at least one processor, cause the at least one processor to perform operations comprising:
- receiving a first query plan, the first query plan including a set of statements, the set of statements including at least one statement with at least a reference to a particular column in a first table;
- identifying a first predicate from the at least one statement, the first predicate corresponding to a join operation;
- determining, for the particular column, a set of expression properties;
- based on the set of expression properties, determining a first derived predicate, the first derived predicate corresponding to a first filter operation;
- determining a second derived predicate based at least in part on the first predicate, the second derived predicate corresponding to a second filter operation that is derived based on analyzing the first derived predicate and the set of expression properties;
- generating a new operation based on the second derived predicate;
- and
- generating a new query plan based at least in part on the join operation of first predicate, the first derived predicate, and the new operation of the second derived predicate, the new query plan comprising a tree structure with a plurality of nodes,
- the new operation, comprising the second filter operation, being included as a first particular node below a second particular node corresponding to the join operation in the tree structure of the new query plan, the first filter operation being included as a third node below the second particular node corresponding to the join operation in the tree structure of the new query plan, and the new operation of the second derived predicate facilitating removing data prior to performing the join operation and thereby reducing utilization of computing resources when executing the new query plan.
9 more elements
A method comprising:
- receiving a first query plan, the first query plan including a set of statements, the set of statements including at least one statement with at least a reference to a particular column in a first table;
- identifying a first predicate from the at least one statement, the first predicate corresponding to a join operation;
- determining, for the particular column, a set of expression properties;
- based on the set of expression properties, determining a first derived predicate, the first derived predicate corresponding to a first filter operation;
- determining a second derived predicate based at least in part on the first predicate, the second derived predicate corresponding to a second filter operation that is derived based on analyzing the first derived predicate and the set of expression properties;
- generating a new operation based on the second derived predicate;
- and
- generating a new query plan based at least in part on the join operation of first predicate, the first derived predicate, and the new operation of the second derived predicate, the new query plan comprising a tree structure with a plurality of nodes,
- the new operation, comprising the second filter operation, being included as a first particular node below a second particular node corresponding to the join operation in the tree structure of the new query plan, the first filter operation being included as a third node below the second particular node corresponding to the join operation in the tree structure of the new query plan, and the new operation of the second derived predicate facilitating removing data prior to performing the join operation and thereby reducing utilization of computing resources when executing the new query plan.
6 more elements
A non-transitory computer-readable medium comprising instructions that, when executed by at least one processor, configure the at least one processor to perform operations comprising:
- receiving a first query plan, the first query plan including a set of statements, the set of statements including at least one statement with at least a reference to a particular column in a first table;
- identifying a first predicate from the at least one statement, the first predicate corresponding to a join operation, the first derived predicate corresponding to a first filter operation;
- determining, for the particular column, a set of expression properties;
- based on the set of expression properties, determining a first derived predicate;
- determining a second derived predicate based at least in part on the first predicate, the second derived predicate corresponding to a second filter operation that is derived based on analyzing the first derived predicate and the set of expression properties;
- generating a new operation based on the second derived predicate;
- and
- generating a new query plan based at least in part on the join operation of first predicate, the first derived predicate, and the new operation of the second derived predicate, the new query plan comprising a tree structure with a plurality of nodes,
- the new operation, comprising the second filter operation, being included as a first particular node below a second particular node corresponding to the join operation in the tree structure of the new query plan, the first filter operation being included as a third node below the second particular node corresponding to the join operation in the tree structure of the new query plan, and the new operation of the second derived predicate facilitating removing data prior to performing the join operation and thereby reducing utilization of computing resources when executing the new query plan.
6 more elements
Dependencies are inferred from the claim text.