You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by bo...@apache.org on 2020/12/10 11:51:57 UTC
[impala] branch master updated: IMPALA-10360: Allow simple limit to
be treated as sampling hint
This is an automated email from the ASF dual-hosted git repository.
boroknagyz pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git
The following commit(s) were added to refs/heads/master by this push:
new b5ba793 IMPALA-10360: Allow simple limit to be treated as sampling hint
b5ba793 is described below
commit b5ba793227238ef78545765d06ca844a1a94db47
Author: Aman Sinha <am...@cloudera.com>
AuthorDate: Sun Nov 29 16:35:39 2020 -0800
IMPALA-10360: Allow simple limit to be treated as sampling hint
As a follow-up to IMPALA-10314, it is sometimes useful to consider
a simple limit as a way to sample from a table if a relevant hint
has been provided. Doing a sample instead of pure limit serves
dual purposes: (a) it still helps with reducing the planning time
since the scan ranges need be computed only for the sample files,
(b) it allows sufficient number of files/rows to be read from
the table such that after applying filter conditions or joins with
another table, the query may still produce the N rows needed for
limit.
This fuctionality is especially useful if the query is against a
view. Note that TABLESAMPLE clause cannot be applied to a view and
embedding a TABLESAMPLE explicitly on a table within a view will
not work because we don't want to sample if there's no limit.
In this patch, a new table level hint, 'convert_limit_to_sample(n)'
is added. If this hint is attached to a table either in the main
query block or within a view/subquery and simple limit optimization
conditions are satisfied (according to IMPALA-10314), the limit
is converted to a table sample. The parameter 'n' in parenthesis is
required and specifies the sample percentage. It must be an integer
between 1 and 100. For example:
set optimize_simple_limit = true;
CREATE VIEW v1 as SELECT * FROM T [convert_limit_to_sample(5)]
WHERE [always_true] <predicate>;
SELECT * FROM v1 LIMIT 10;
In this case, the limit 10 is applied on top of a 5 percent sample
of T which is applied after partition pruning.
Testing:
- Added a alltypes_date_partition_2 table where the date and
timestamp values match (this helps with setting the
'always_true' hint).
- Added views with 'convert_limit_to_sample' and 'always_true'
hints and added new tests against the views. Modified a few
existing tests to reference the new table variant.
- Added an end-to-end test.
Change-Id: Ife05a5343c913006f7659949b327b63d3f10c04b
Reviewed-on: http://gerrit.cloudera.org:8080/16792
Reviewed-by: Impala Public Jenkins <im...@cloudera.com>
Tested-by: Impala Public Jenkins <im...@cloudera.com>
---
fe/src/main/cup/sql-parser.cup | 10 ++
.../apache/impala/analysis/CompoundPredicate.java | 11 +-
.../main/java/org/apache/impala/analysis/Expr.java | 7 +
.../org/apache/impala/analysis/SelectStmt.java | 21 ++-
.../java/org/apache/impala/analysis/TableRef.java | 46 ++++++-
.../apache/impala/planner/HdfsPartitionPruner.java | 39 +++---
.../org/apache/impala/planner/HdfsScanNode.java | 2 +-
.../apache/impala/analysis/AnalyzeStmtsTest.java | 2 +-
.../org/apache/impala/service/FrontendTest.java | 22 +++-
testdata/bin/compute-table-stats.sh | 3 +-
.../functional/functional_schema_template.sql | 56 ++++++++
.../queries/PlannerTest/optimize-simple-limit.test | 144 +++++++++++++++++++--
.../QueryTest/range-constant-propagation.test | 13 +-
13 files changed, 329 insertions(+), 47 deletions(-)
diff --git a/fe/src/main/cup/sql-parser.cup b/fe/src/main/cup/sql-parser.cup
index 91e24d3..7967ac4 100644
--- a/fe/src/main/cup/sql-parser.cup
+++ b/fe/src/main/cup/sql-parser.cup
@@ -21,6 +21,7 @@ import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import java.math.BigDecimal;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
@@ -3059,6 +3060,15 @@ plan_hint ::=
{: RESULT = new PlanHint(name); :}
| IDENT:name LPAREN ident_list:args RPAREN
{: RESULT = new PlanHint(name, args); :}
+ | IDENT:name LPAREN INTEGER_LITERAL:value RPAREN
+ {:
+ if (value != null) {
+ RESULT = new PlanHint(name,
+ new ArrayList(Arrays.asList(value.toString())));
+ } else {
+ RESULT = new PlanHint(name);
+ }
+ :}
| /* empty */
{: RESULT = null; :}
;
diff --git a/fe/src/main/java/org/apache/impala/analysis/CompoundPredicate.java b/fe/src/main/java/org/apache/impala/analysis/CompoundPredicate.java
index bd8c042..aa40ac8 100644
--- a/fe/src/main/java/org/apache/impala/analysis/CompoundPredicate.java
+++ b/fe/src/main/java/org/apache/impala/analysis/CompoundPredicate.java
@@ -74,7 +74,16 @@ public class CompoundPredicate extends Predicate {
children_.add(e1);
Preconditions.checkArgument(op == Operator.NOT && e2 == null
|| op != Operator.NOT && e2 != null);
- if (e2 != null) children_.add(e2);
+ if (e2 != null) {
+ children_.add(e2);
+ if ((op == Operator.AND &&
+ (Expr.IS_ALWAYS_TRUE_PREDICATE.apply(e1) &&
+ Expr.IS_ALWAYS_TRUE_PREDICATE.apply(e2))) ||
+ ((op == Operator.OR && (Expr.IS_ALWAYS_TRUE_PREDICATE.apply(e1) ||
+ Expr.IS_ALWAYS_TRUE_PREDICATE.apply(e2))))) {
+ setHasAlwaysTrueHint(true);
+ }
+ }
}
/**
diff --git a/fe/src/main/java/org/apache/impala/analysis/Expr.java b/fe/src/main/java/org/apache/impala/analysis/Expr.java
index ec99a0b..de5c411 100644
--- a/fe/src/main/java/org/apache/impala/analysis/Expr.java
+++ b/fe/src/main/java/org/apache/impala/analysis/Expr.java
@@ -513,6 +513,13 @@ abstract public class Expr extends TreeNode<Expr> implements ParseNode, Cloneabl
for (PlanHint hint : predicateHints_) {
if (hint.is("ALWAYS_TRUE")) {
((Predicate) this).setHasAlwaysTrueHint(true);
+ // If the top level expr has always_true hint, its conjuncts must
+ // also have the same hint (note that there's a TODO in the parser
+ // grammar to allow hints on a per expr basis).
+ List<Expr> conjuncts = getConjuncts();
+ if (conjuncts.size() > 1) {
+ for (Expr e : conjuncts) ((Predicate) e).setHasAlwaysTrueHint(true);
+ }
analyzer.setHasPlanHints();
} else {
analyzer.addWarning("Predicate hint not recognized: " + hint);
diff --git a/fe/src/main/java/org/apache/impala/analysis/SelectStmt.java b/fe/src/main/java/org/apache/impala/analysis/SelectStmt.java
index 28f28aa..be1d8c1 100644
--- a/fe/src/main/java/org/apache/impala/analysis/SelectStmt.java
+++ b/fe/src/main/java/org/apache/impala/analysis/SelectStmt.java
@@ -33,7 +33,6 @@ import org.apache.impala.catalog.TableLoadingException;
import org.apache.impala.common.AnalysisException;
import org.apache.impala.common.ColumnAliasGenerator;
import org.apache.impala.common.Pair;
-import org.apache.impala.common.RuntimeEnv;
import org.apache.impala.common.TableAliasGenerator;
import org.apache.impala.common.TreeNode;
import org.apache.impala.rewrite.ExprRewriter;
@@ -178,8 +177,10 @@ public class SelectStmt extends QueryStmt {
/**
* A simple limit statement has a limit but no order-by,
- * group-by, aggregates, joins or analytic functions. It can
- * have a WHERE clause only if it is tagged as always true.
+ * group-by, aggregates or analytic functions. Joins are
+ * allowed if one of the table refs has a limit_to_sample
+ * hint. The statement can have a WHERE clause if it has an
+ * always_true hint.
* This method does not explicitly check for subqueries. If the
* subquery occurs in the WHERE, the initial check for simple
* limit may succeed. Subsequently, in the planning process
@@ -190,7 +191,7 @@ public class SelectStmt extends QueryStmt {
* a table ref.
*/
public Pair<Boolean, Long> checkSimpleLimitStmt() {
- if (getTableRefs().size() == 1
+ if (hasSimpleLimitEligibleTableRefs()
&& !hasGroupByClause() && !hasOrderByClause()
&& !hasMultiAggInfo() && !hasAnalyticInfo()) {
if (hasWhereClause() && !Expr.IS_ALWAYS_TRUE_PREDICATE.apply(getWhereClause())) {
@@ -220,6 +221,18 @@ public class SelectStmt extends QueryStmt {
groupingExprs_.addAll(addtlGroupingExprs);
}
+ private boolean hasSimpleLimitEligibleTableRefs() {
+ // for single table query blocks, limit pushdown can be considered
+ // even if no table hint is present
+ if (getTableRefs().size() == 1) return true;
+ // if there are multiple table refs, at least 1 should have the hint
+ // for limit to sample
+ for (TableRef ref : getTableRefs()) {
+ if (ref.hasConvertLimitToSampleHint()) return true;
+ }
+ return false;
+ }
+
/**
* Remove the group by clause. Used by StmtRewriter. This changes the semantics
* of this statement and should only be called when the query is being rewritten
diff --git a/fe/src/main/java/org/apache/impala/analysis/TableRef.java b/fe/src/main/java/org/apache/impala/analysis/TableRef.java
index c476777..4f8b776 100644
--- a/fe/src/main/java/org/apache/impala/analysis/TableRef.java
+++ b/fe/src/main/java/org/apache/impala/analysis/TableRef.java
@@ -24,7 +24,6 @@ import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
-import java.util.UUID;
import org.apache.impala.authorization.Privilege;
import org.apache.impala.catalog.FeFsTable;
@@ -98,6 +97,9 @@ public class TableRef extends StmtNode {
protected List<PlanHint> tableHints_ = new ArrayList<>();
protected TReplicaPreference replicaPreference_;
protected boolean randomReplica_;
+ // A sampling percent if convert_limit_to_sample hint is supplied. Valid range
+ // is 1-100. -1 indicates no hint.
+ protected int convertLimitToSampleHintPercent_;
// Hinted distribution mode for this table ref; set after analyzeJoinHints()
// TODO: Move join-specific members out of TableRef.
@@ -185,6 +187,7 @@ public class TableRef extends StmtNode {
isAnalyzed_ = false;
replicaPreference_ = null;
randomReplica_ = false;
+ convertLimitToSampleHintPercent_ = -1;
}
/**
@@ -206,6 +209,7 @@ public class TableRef extends StmtNode {
allowEmptyOn_ = other.allowEmptyOn_;
tableHints_ = Lists.newArrayList(other.tableHints_);
replicaPreference_ = other.replicaPreference_;
+ convertLimitToSampleHintPercent_ = other.convertLimitToSampleHintPercent_;
randomReplica_ = other.randomReplica_;
distrMode_ = other.distrMode_;
// The table ref links are created at the statement level, so cloning a set of linked
@@ -261,6 +265,12 @@ public class TableRef extends StmtNode {
public TReplicaPreference getReplicaPreference() { return replicaPreference_; }
public boolean getRandomReplica() { return randomReplica_; }
+ public boolean hasConvertLimitToSampleHint() {
+ return convertLimitToSampleHintPercent_ != -1;
+ }
+ public int getConvertLimitToSampleHintPercent() {
+ return convertLimitToSampleHintPercent_;
+ }
/**
* Returns true if this table ref has a resolved path that is rooted at a registered
@@ -336,6 +346,10 @@ public class TableRef extends StmtNode {
tableHints_ = hints;
}
+ public void setTableSampleClause(TableSampleClause sampleParams) {
+ sampleParams_ = sampleParams;
+ }
+
public boolean isBroadcastJoin() { return distrMode_ == DistributionMode.BROADCAST; }
public boolean isPartitionedJoin() {
@@ -435,14 +449,36 @@ public class TableRef extends StmtNode {
} else if (hint.is("SCHEDULE_RANDOM_REPLICA")) {
analyzer.setHasPlanHints();
randomReplica_ = true;
+ } else if (hint.is("CONVERT_LIMIT_TO_SAMPLE")) {
+ List<String> args = hint.getArgs();
+ if (args == null || args.size() != 1) {
+ addHintWarning(hint, analyzer);
+ return;
+ } else {
+ try {
+ int samplePercent = Integer.parseInt(args.get(0));
+ if (samplePercent < 1 || samplePercent > 100) {
+ addHintWarning(hint, analyzer);
+ return;
+ }
+ analyzer.setHasPlanHints();
+ convertLimitToSampleHintPercent_ = samplePercent;
+ } catch (NumberFormatException e) {
+ addHintWarning(hint, analyzer);
+ }
+ }
} else {
- Preconditions.checkState(getAliases() != null && getAliases().length > 0);
- analyzer.addWarning("Table hint not recognized for table " + getUniqueAlias() +
- ": " + hint);
+ addHintWarning(hint, analyzer);
}
}
}
+ private void addHintWarning(PlanHint hint, Analyzer analyzer) {
+ Preconditions.checkState(getAliases() != null && getAliases().length > 0);
+ analyzer.addWarning("Table hint not recognized for table " + getUniqueAlias() +
+ ": " + hint);
+ }
+
private void analyzeJoinHints(Analyzer analyzer) throws AnalysisException {
if (joinHints_.isEmpty()) return;
for (PlanHint hint: joinHints_) {
@@ -685,4 +721,4 @@ public class TableRef extends StmtNode {
joinHints_ = new ArrayList<>();
tableHints_ = new ArrayList<>();
}
-}
+}
\ No newline at end of file
diff --git a/fe/src/main/java/org/apache/impala/planner/HdfsPartitionPruner.java b/fe/src/main/java/org/apache/impala/planner/HdfsPartitionPruner.java
index 03d8e5e..fa0053b 100644
--- a/fe/src/main/java/org/apache/impala/planner/HdfsPartitionPruner.java
+++ b/fe/src/main/java/org/apache/impala/planner/HdfsPartitionPruner.java
@@ -40,6 +40,7 @@ import org.apache.impala.analysis.NullLiteral;
import org.apache.impala.analysis.SlotId;
import org.apache.impala.analysis.SlotRef;
import org.apache.impala.analysis.TableRef;
+import org.apache.impala.analysis.TableSampleClause;
import org.apache.impala.analysis.TupleDescriptor;
import org.apache.impala.catalog.FeFsPartition;
import org.apache.impala.catalog.FeFsTable;
@@ -190,32 +191,40 @@ public class HdfsPartitionPruner {
/**
* Prune partitions based on eligibility of simple limit optimization:
- * - table ref should not already have a TABLESAMPLE clause
+ * - Either the table ref should not already have a TABLESAMPLE clause
+ * or if it does then the simple_limit_to_sample hint must be set
* - OPTIMIZE_SIMPLE_LIMIT is enabled and the query block satisfies
* simple limit criteria
*/
private List<? extends FeFsPartition> pruneForSimpleLimit(TableRef tblRef,
Analyzer analyzer, List<? extends FeFsPartition> partitions) {
- if (tblRef.getSampleParams() == null
+ if ((tblRef.getSampleParams() == null
+ || tblRef.hasConvertLimitToSampleHint())
&& analyzer.getQueryCtx().client_request.getQuery_options()
.isOptimize_simple_limit()
&& analyzer.getSimpleLimitStatus() != null
&& analyzer.getSimpleLimitStatus().first) {
- List<FeFsPartition> prunedPartitions = new ArrayList<>();
- long numRows = 0;
- // Instead of using the partitions num rows statistic which may be stale,
- // we use a conservative estimate of number of files within a partition and
- // 1 row per file
- for (FeFsPartition p : partitions) {
- numRows += p.getNumFileDescriptors();
- prunedPartitions.add(p);
- if (numRows >= analyzer.getSimpleLimitStatus().second) {
- // here we only limit the partitions; later in HdfsScanNode we will
- // limit the file descriptors within a partition
- break;
+ long limitValue = analyzer.getSimpleLimitStatus().second;
+ if (tblRef.hasConvertLimitToSampleHint()) {
+ tblRef.setTableSampleClause(new TableSampleClause(
+ tblRef.getConvertLimitToSampleHintPercent(), /* seed */ 1L));
+ } else {
+ List<FeFsPartition> prunedPartitions = new ArrayList<>();
+ long numRows = 0;
+ // Instead of using the partitions num rows statistic which may be stale,
+ // we use a conservative estimate of number of files within a partition and
+ // 1 row per file
+ for (FeFsPartition p : partitions) {
+ numRows += p.getNumFileDescriptors();
+ prunedPartitions.add(p);
+ if (numRows >= limitValue) {
+ // here we only limit the partitions; later in HdfsScanNode we will
+ // limit the file descriptors within a partition
+ break;
+ }
}
+ return prunedPartitions;
}
- return prunedPartitions;
}
return partitions;
}
diff --git a/fe/src/main/java/org/apache/impala/planner/HdfsScanNode.java b/fe/src/main/java/org/apache/impala/planner/HdfsScanNode.java
index c0c6312..a697fc5 100644
--- a/fe/src/main/java/org/apache/impala/planner/HdfsScanNode.java
+++ b/fe/src/main/java/org/apache/impala/planner/HdfsScanNode.java
@@ -843,7 +843,7 @@ public class HdfsScanNode extends ScanNode {
maxScanRangeNumRows_ = -1;
fileFormats_ = new HashSet<>();
long simpleLimitNumRows = 0; // only used for the simple limit case
- boolean isSimpleLimit =
+ boolean isSimpleLimit = sampleParams_ == null &&
(analyzer.getQueryCtx().client_request.getQuery_options()
.isOptimize_simple_limit()
&& analyzer.getSimpleLimitStatus() != null
diff --git a/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java b/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
index f523c90..b91370b 100644
--- a/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
+++ b/fe/src/test/java/org/apache/impala/analysis/AnalyzeStmtsTest.java
@@ -4539,7 +4539,7 @@ public class AnalyzeStmtsTest extends AnalyzerTest {
testNumberOfMembers(ValuesStmt.class, 0);
// Also check TableRefs.
- testNumberOfMembers(TableRef.class, 23);
+ testNumberOfMembers(TableRef.class, 24);
testNumberOfMembers(BaseTableRef.class, 0);
testNumberOfMembers(InlineViewRef.class, 9);
}
diff --git a/fe/src/test/java/org/apache/impala/service/FrontendTest.java b/fe/src/test/java/org/apache/impala/service/FrontendTest.java
index 03ccfc4..72c66b7 100644
--- a/fe/src/test/java/org/apache/impala/service/FrontendTest.java
+++ b/fe/src/test/java/org/apache/impala/service/FrontendTest.java
@@ -130,6 +130,8 @@ public class FrontendTest extends FrontendTestBase {
AnalyzesOk("select * from functional.alltypes_parens");
AnalyzesOk("select * from functional.alltypes_view");
AnalyzesOk("select * from functional.alltypes_view_sub");
+ AnalyzesOk("select * from functional.alltypes_dp_2_view_1");
+ AnalyzesOk("select * from functional.alltypes_dp_2_view_2");
TMetadataOpRequest req = new TMetadataOpRequest();
req.opcode = TMetadataOpcode.GET_TABLES;
@@ -141,11 +143,13 @@ public class FrontendTest extends FrontendTestBase {
// HiveServer2 GetTables has 5 columns.
assertEquals(5, resp.schema.columns.size());
assertEquals(5, resp.rows.get(0).colVals.size());
- assertEquals(2, resp.rows.size());
+ assertEquals(3, resp.rows.size());
assertEquals("alltypes_datasource",
resp.rows.get(0).colVals.get(2).string_val.toLowerCase());
assertEquals("alltypes_date_partition",
resp.rows.get(1).colVals.get(2).string_val.toLowerCase());
+ assertEquals("alltypes_date_partition_2",
+ resp.rows.get(2).colVals.get(2).string_val.toLowerCase());
}
@Test
@@ -155,6 +159,8 @@ public class FrontendTest extends FrontendTestBase {
AnalyzesOk("select * from functional.alltypes_parens");
AnalyzesOk("select * from functional.alltypes_view");
AnalyzesOk("select * from functional.alltypes_view_sub");
+ AnalyzesOk("select * from functional.alltypes_dp_2_view_1");
+ AnalyzesOk("select * from functional.alltypes_dp_2_view_2");
TMetadataOpRequest req = new TMetadataOpRequest();
req.opcode = TMetadataOpcode.GET_TABLES;
@@ -166,15 +172,19 @@ public class FrontendTest extends FrontendTestBase {
// HiveServer2 GetTables has 5 columns.
assertEquals(5, resp.schema.columns.size());
assertEquals(5, resp.rows.get(0).colVals.size());
- assertEquals(4, resp.rows.size());
- assertEquals("alltypes_hive_view",
+ assertEquals(6, resp.rows.size());
+ assertEquals("alltypes_dp_2_view_1",
resp.rows.get(0).colVals.get(2).string_val.toLowerCase());
- assertEquals("alltypes_parens",
+ assertEquals("alltypes_dp_2_view_2",
resp.rows.get(1).colVals.get(2).string_val.toLowerCase());
- assertEquals("alltypes_view",
+ assertEquals("alltypes_hive_view",
resp.rows.get(2).colVals.get(2).string_val.toLowerCase());
- assertEquals("alltypes_view_sub",
+ assertEquals("alltypes_parens",
resp.rows.get(3).colVals.get(2).string_val.toLowerCase());
+ assertEquals("alltypes_view",
+ resp.rows.get(4).colVals.get(2).string_val.toLowerCase());
+ assertEquals("alltypes_view_sub",
+ resp.rows.get(5).colVals.get(2).string_val.toLowerCase());
}
@Test
diff --git a/testdata/bin/compute-table-stats.sh b/testdata/bin/compute-table-stats.sh
index 4c119f1..1728c9b 100755
--- a/testdata/bin/compute-table-stats.sh
+++ b/testdata/bin/compute-table-stats.sh
@@ -34,7 +34,8 @@ COMPUTE_STATS_SCRIPT="${IMPALA_HOME}/tests/util/compute_table_stats.py --impalad
${COMPUTE_STATS_SCRIPT} --db_names=functional\
--table_names="alltypes,alltypesagg,alltypesaggmultifilesnopart,alltypesaggnonulls,
alltypessmall,alltypestiny,jointbl,dimtbl,stringpartitionkey,nulltable,nullrows,
- date_tbl,chars_medium,part_strings_with_quotes,alltypes_date_partition"
+ date_tbl,chars_medium,part_strings_with_quotes,alltypes_date_partition,
+ alltypes_date_partition_2"
# We cannot load HBase on s3 and isilon yet.
if [ "${TARGET_FILESYSTEM}" = "hdfs" ]; then
diff --git a/testdata/datasets/functional/functional_schema_template.sql b/testdata/datasets/functional/functional_schema_template.sql
index 467798f..39890b0 100644
--- a/testdata/datasets/functional/functional_schema_template.sql
+++ b/testdata/datasets/functional/functional_schema_template.sql
@@ -2985,3 +2985,59 @@ TBLPROPERTIES('iceberg.file_format'='orc', 'iceberg.catalog'='hadoop.catalog', '
`hadoop fs -mkdir -p /test-warehouse/iceberg_test/hadoop_catalog && \
hadoop fs -put -f ${IMPALA_HOME}/testdata/data/iceberg_test/hadoop_catalog/iceberg_partitioned_orc /test-warehouse/iceberg_test/hadoop_catalog/
====
+---- DATASET
+functional
+---- BASE_TABLE_NAME
+alltypes_date_partition_2
+---- PARTITION_COLUMNS
+date_col date
+---- COLUMNS
+id int COMMENT 'Add a comment'
+bool_col boolean
+tinyint_col tinyint
+smallint_col smallint
+int_col int
+bigint_col bigint
+float_col float
+double_col double
+string_col string
+timestamp_col timestamp
+---- DEPENDENT_LOAD
+INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} PARTITION (date_col)
+SELECT id, bool_col, tinyint_col, smallint_col, int_col, bigint_col,
+float_col, double_col, string_col, timestamp_col,
+cast(timestamp_col as date) date_col
+FROM {db_name}{db_suffix}.alltypes where id < 500;
+---- LOAD
+SET hive.exec.dynamic.partition.mode=nonstrict;
+SET hive.exec.dynamic.partition=true;
+INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} PARTITION (date_col)
+SELECT id, bool_col, tinyint_col, smallint_col, int_col, bigint_col,
+float_col, double_col, string_col, timestamp_col,
+cast(timestamp_col as date) date_col
+FROM {db_name}{db_suffix}.alltypes where id < 500;
+====
+---- DATASET
+functional
+---- BASE_TABLE_NAME
+alltypes_dp_2_view_1
+---- CREATE
+DROP VIEW IF EXISTS {db_name}{db_suffix}.{table_name};
+-- view which references a WHERE clause with hint
+CREATE VIEW {db_name}{db_suffix}.{table_name}
+AS SELECT * FROM {db_name}{db_suffix}.alltypes_date_partition_2 where [always_true] date_col = cast(timestamp_col as date);
+---- LOAD
+====
+---- DATASET
+functional
+---- BASE_TABLE_NAME
+alltypes_dp_2_view_2
+---- CREATE
+DROP VIEW IF EXISTS {db_name}{db_suffix}.{table_name};
+-- view which references a table with hint and a WHERE clause with hint.
+-- WHERE clause has a compound predicate.
+CREATE VIEW {db_name}{db_suffix}.{table_name}
+AS SELECT * FROM {db_name}{db_suffix}.alltypes_date_partition_2 [convert_limit_to_sample(5)]
+where [always_true] date_col = cast(timestamp_col as date) and int_col in (select int_col from {db_name}{db_suffix}.alltypessmall);
+---- LOAD
+====
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/optimize-simple-limit.test b/testdata/workloads/functional-planner/queries/PlannerTest/optimize-simple-limit.test
index b46089b..dd82364 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/optimize-simple-limit.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/optimize-simple-limit.test
@@ -45,17 +45,17 @@ PLAN-ROOT SINK
# limit in outer block, WHERE clause in inline view
# along with an explicit hint
with dp_view as
-(select * from functional.alltypes_date_partition
+(select * from functional.alltypes_date_partition_2
where /* +always_true */ date_col = cast(timestamp_col as date))
select * from dp_view limit 10;
---- PLAN
PLAN-ROOT SINK
|
-00:SCAN HDFS [functional.alltypes_date_partition]
- HDFS partitions=10/55 files=10 size=3.97KB
+00:SCAN HDFS [functional.alltypes_date_partition_2]
+ HDFS partitions=10/50 files=10 size=5.36KB
predicates: date_col = CAST(timestamp_col AS DATE)
limit: 10
- row-size=65B cardinality=8
+ row-size=65B cardinality=10
====
# limit and WHERE with a wrongly specified hint (missing
# underscore)
@@ -144,7 +144,7 @@ PLAN-ROOT SINK
# should not apply optimization since outer query predicate
# is not always true
with dp_view as
-(select * from functional.alltypes_date_partition
+(select * from functional.alltypes_date_partition_2
where /* +always_true */ date_col = cast(timestamp_col as date))
select * from dp_view
where timestamp_col between '2009-01-01' and '2009-02-01'
@@ -152,24 +152,24 @@ limit 10;
---- PLAN
PLAN-ROOT SINK
|
-00:SCAN HDFS [functional.alltypes_date_partition]
+00:SCAN HDFS [functional.alltypes_date_partition_2]
partition predicates: date_col >= DATE '2009-01-01' AND date_col <= DATE '2009-02-01'
- HDFS partitions=32/55 files=32 size=15.99KB
- predicates: functional.alltypes_date_partition.timestamp_col <= TIMESTAMP '2009-02-01 00:00:00', functional.alltypes_date_partition.timestamp_col >= TIMESTAMP '2009-01-01 00:00:00', date_col = CAST(timestamp_col AS DATE)
+ HDFS partitions=32/50 files=32 size=17.41KB
+ predicates: functional.alltypes_date_partition_2.timestamp_col <= TIMESTAMP '2009-02-01 00:00:00', functional.alltypes_date_partition_2.timestamp_col >= TIMESTAMP '2009-01-01 00:00:00', date_col = CAST(timestamp_col AS DATE)
limit: 10
row-size=65B cardinality=10
====
# limit in outer block, WHERE and LIMIT clause in
# inline view
with dp_view as
-(select * from functional.alltypes_date_partition
+(select * from functional.alltypes_date_partition_2
where /* +always_true */ date_col = cast(timestamp_col as date) limit 20)
select * from dp_view limit 10;
---- PLAN
PLAN-ROOT SINK
|
-00:SCAN HDFS [functional.alltypes_date_partition]
- HDFS partitions=20/55 files=20 size=9.42KB
+00:SCAN HDFS [functional.alltypes_date_partition_2]
+ HDFS partitions=20/50 files=20 size=10.85KB
predicates: date_col = CAST(timestamp_col AS DATE)
limit: 10
row-size=65B cardinality=10
@@ -233,7 +233,7 @@ select * from functional.alltypesaggmultifiles limit 10;
PLAN-ROOT SINK
|
00:SCAN HDFS [functional.alltypesaggmultifiles]
- HDFS partitions=3/11 files=10 size=183.75KB
+ HDFS partitions=3/11 files=10 size=182.67KB
limit: 10
row-size=84B cardinality=10
====
@@ -256,3 +256,123 @@ PLAN-ROOT SINK
HDFS partitions=4/4 files=4 size=460B
row-size=89B cardinality=8
====
+# Query against a view that has WHERE clause hint.
+# Both the num partitions and files are pruned.
+select * from functional.alltypes_dp_2_view_1 limit 10;
+---- PLAN
+PLAN-ROOT SINK
+|
+00:SCAN HDFS [functional.alltypes_date_partition_2]
+ HDFS partitions=10/50 files=10 size=5.36KB
+ predicates: date_col = CAST(timestamp_col AS DATE)
+ limit: 10
+ row-size=65B cardinality=10
+====
+# IMPALA-10360
+# Query against a view that has hints for table sampling
+# and WHERE clause. Since table has a convert_limit_to_sample
+# hint, number of files are pruned.
+select * from functional.alltypes_dp_2_view_2 limit 10;
+---- PLAN
+PLAN-ROOT SINK
+|
+03:HASH JOIN [LEFT SEMI JOIN]
+| hash predicates: int_col = functional.alltypessmall.int_col
+| runtime filters: RF000 <- functional.alltypessmall.int_col
+| limit: 10
+| row-size=65B cardinality=3
+|
+|--02:AGGREGATE [FINALIZE]
+| | group by: functional.alltypessmall.int_col
+| | row-size=4B cardinality=10
+| |
+| 01:SCAN HDFS [functional.alltypessmall]
+| HDFS partitions=4/4 files=4 size=6.32KB
+| row-size=4B cardinality=100
+|
+00:SCAN HDFS [functional.alltypes_date_partition_2]
+ HDFS partitions=3/50 files=3 size=1.65KB
+ predicates: date_col = CAST(timestamp_col AS DATE)
+ runtime filters: RF000 -> int_col
+ row-size=65B cardinality=3
+====
+# query against view but without the limit - no optimization
+select * from functional.alltypes_dp_2_view_1;
+---- PLAN
+PLAN-ROOT SINK
+|
+00:SCAN HDFS [functional.alltypes_date_partition_2]
+ HDFS partitions=50/50 files=50 size=27.27KB
+ predicates: date_col = CAST(timestamp_col AS DATE)
+ row-size=65B cardinality=50
+====
+# query against view but without the limit - no optimization
+select * from functional.alltypes_dp_2_view_2;
+---- PLAN
+PLAN-ROOT SINK
+|
+03:HASH JOIN [LEFT SEMI JOIN]
+| hash predicates: int_col = functional.alltypessmall.int_col
+| runtime filters: RF000 <- functional.alltypessmall.int_col
+| row-size=65B cardinality=50
+|
+|--02:AGGREGATE [FINALIZE]
+| | group by: functional.alltypessmall.int_col
+| | row-size=4B cardinality=10
+| |
+| 01:SCAN HDFS [functional.alltypessmall]
+| HDFS partitions=4/4 files=4 size=6.32KB
+| row-size=4B cardinality=100
+|
+00:SCAN HDFS [functional.alltypes_date_partition_2]
+ HDFS partitions=50/50 files=50 size=27.27KB
+ predicates: date_col = CAST(timestamp_col AS DATE)
+ runtime filters: RF000 -> int_col
+ row-size=65B cardinality=50
+====
+# Query against a base table that has convert_limit_to_sample hint
+# and WHERE clause has always_true hint. Num files is pruned.
+select * from functional.alltypes_date_partition_2 [convert_limit_to_sample(5)]
+where [always_true] date_col = cast(timestamp_col as date)
+and int_col in (select int_col from functional.alltypessmall)
+limit 50;
+---- PLAN
+PLAN-ROOT SINK
+|
+03:HASH JOIN [LEFT SEMI JOIN]
+| hash predicates: int_col = functional.alltypessmall.int_col
+| runtime filters: RF000 <- functional.alltypessmall.int_col
+| limit: 50
+| row-size=65B cardinality=3
+|
+|--02:AGGREGATE [FINALIZE]
+| | group by: functional.alltypessmall.int_col
+| | row-size=4B cardinality=10
+| |
+| 01:SCAN HDFS [functional.alltypessmall]
+| HDFS partitions=4/4 files=4 size=6.32KB
+| row-size=4B cardinality=100
+|
+00:SCAN HDFS [functional.alltypes_date_partition_2]
+ HDFS partitions=3/50 files=3 size=1.65KB
+ predicates: date_col = CAST(timestamp_col AS DATE)
+ runtime filters: RF000 -> int_col
+ row-size=65B cardinality=3
+====
+# Query against a base table that has convert_limit_to_sample hint
+# and WHERE clause has always_true hint. Predicate on partition
+# columns. Both num partitions and num files is pruned.
+select * from functional.alltypes_date_partition_2 [convert_limit_to_sample(5)]
+where [always_true] date_col = cast(timestamp_col as date)
+and timestamp_col between '2009-01-01' and '2009-02-01'
+limit 10;
+---- PLAN
+PLAN-ROOT SINK
+|
+00:SCAN HDFS [functional.alltypes_date_partition_2]
+ partition predicates: date_col >= DATE '2009-01-01' AND date_col <= DATE '2009-02-01'
+ HDFS partitions=2/50 files=2 size=1.07KB
+ predicates: timestamp_col <= TIMESTAMP '2009-02-01 00:00:00', timestamp_col >= TIMESTAMP '2009-01-01 00:00:00', date_col = CAST(timestamp_col AS DATE)
+ limit: 10
+ row-size=65B cardinality=2
+====
diff --git a/testdata/workloads/functional-query/queries/QueryTest/range-constant-propagation.test b/testdata/workloads/functional-query/queries/QueryTest/range-constant-propagation.test
index 7f59d57..10ad644 100644
--- a/testdata/workloads/functional-query/queries/QueryTest/range-constant-propagation.test
+++ b/testdata/workloads/functional-query/queries/QueryTest/range-constant-propagation.test
@@ -29,7 +29,7 @@ BIGINT, BIGINT
# WHERE clause has an always_true hint.
set optimize_simple_limit=true;
with dp_view as
-(select * from alltypes_date_partition
+(select * from alltypes_date_partition_2
where /* +always_true */ date_col = cast(timestamp_col as date))
select count(*) from (select * from dp_view limit 10) t;
---- RESULTS
@@ -37,3 +37,14 @@ select count(*) from (select * from dp_view limit 10) t;
---- TYPES
BIGINT
====
+---- QUERY
+# IMPALA-10360
+# Query against a view that has hints for table sampling
+# and WHERE clause.
+set optimize_simple_limit=true;
+select count(*) from (select * from alltypes_dp_2_view_2 limit 10) t;
+---- RESULTS
+10
+---- TYPES
+BIGINT
+====