This is an automated email from the ASF dual-hosted git repository. mblow pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/asterixdb.git
commit 0249eb936d872b357cdcf8f83728373178d0ed65 Author: Ali Alsuliman <[email protected]> AuthorDate: Mon Dec 8 17:36:15 2025 -0800 [ASTERIXDB-3677][COMP] Push limit to index-search of UNNEST_MAP op - user model changes: no - storage format changes: no - interface changes: no Details: - Push limit to index-search of UNNEST_MAP op. - Enable query-index() on array indexes. - Fix FD computation for query-index(). Ext-ref: MB-69534 Change-Id: I4a7d0fe690fcefd845f60814d5a4477cd6bc4e6f Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20592 Reviewed-by: Ali Alsuliman <[email protected]> Tested-by: Jenkins <[email protected]> Reviewed-by: Preetham Poluparthi <[email protected]> Integration-Tests: Jenkins <[email protected]> Reviewed-by: Peeyush Gupta <[email protected]> --- .../rules/PushLimitIntoPrimarySearchRule.java | 12 +- .../asterix/app/function/QueryIndexDatasource.java | 22 ++- .../asterix/app/function/QueryIndexRewriter.java | 23 ++- .../queries/limit/pushdown_to_index_search.sqlpp} | 28 ++- .../results/limit/pushdown_to_index_search.plan | 44 +++++ ...push-limit-to-join-primary-lookup.01.ddl.sqlpp} | 15 +- ...h-limit-to-join-primary-lookup.02.update.sqlpp} | 19 +- ...sh-limit-to-join-primary-lookup.03.query.sqlpp} | 15 +- ...push-limit-to-join-primary-lookup.99.ddl.sqlpp} | 4 +- .../query_index/q01/q01.001.ddl.sqlpp | 7 +- .../query_index/q01/q01.002.update.sqlpp | 7 + .../q01.032.query.sqlpp} | 5 +- .../q01.033.query.sqlpp} | 5 +- .../q01.034.query.sqlpp} | 5 +- .../q01.035.query.sqlpp} | 5 +- .../queries_sqlpp/tpcds/q90/q90.4.query.sqlpp | 43 +++++ .../push-limit-to-join-primary-lookup.03.adm | 2 + .../runtimets/results/query_index/q01/q01.032.adm | 16 ++ .../runtimets/results/query_index/q01/q01.033.adm | 7 + .../runtimets/results/query_index/q01/q01.034.plan | 20 ++ .../runtimets/results/query_index/q01/q01.035.plan | 20 ++ .../runtimets/results/tpcds/q90/q90.2.plan | 210 +++++++++++++++++++++ .../src/test/resources/runtimets/sqlpp_queries.xml | 6 +- .../asterix/metadata/utils/KeyFieldTypeUtil.java | 19 ++ 24 files changed, 526 insertions(+), 33 deletions(-) diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushLimitIntoPrimarySearchRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushLimitIntoPrimarySearchRule.java index d95a95257a..3e6e575fd5 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushLimitIntoPrimarySearchRule.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushLimitIntoPrimarySearchRule.java @@ -142,6 +142,16 @@ public class PushLimitIntoPrimarySearchRule implements IAlgebraicRewriteRule { case UNNEST_MAP: UnnestMapOperator unnestMap = (UnnestMapOperator) child; if (isUnnestMapPushable(unnestMap, selectedVariables)) { + // set only the limit if the unnest-map is propagating the input vars. + // when the unnest-map op is propagating the input vars, currently the index-search runtime fails to + // evaluate the select-condition because at the time the select-condition is evaluated, the tuple + // has only the index-search vars while the select-condition expects the tuple to have both the + // input vars + index-search vars. the index-search runtime needs to be fixed to construct the + // complete tuple vars first, then evaluate the select-condition. + if (unnestMap.propagatesInput()) { + unnestMap.setOutputLimit(outputLimit); + return true; + } unnestMap.setSelectCondition(selectConditionRef); unnestMap.setOutputLimit(outputLimit); changed = true; @@ -180,7 +190,7 @@ public class PushLimitIntoPrimarySearchRule implements IAlgebraicRewriteRule { return false; } ILogicalExpression unnestExpr = op.getExpressionRef().getValue(); - if (op.propagatesInput() || unnestExpr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) { + if (unnestExpr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) { return false; } AbstractFunctionCallExpression f = (AbstractFunctionCallExpression) unnestExpr; diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/QueryIndexDatasource.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/QueryIndexDatasource.java index 1c036f87e4..1c6d2cc964 100644 --- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/QueryIndexDatasource.java +++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/QueryIndexDatasource.java @@ -21,6 +21,7 @@ package org.apache.asterix.app.function; import java.util.ArrayList; import java.util.List; import java.util.Objects; +import java.util.Set; import org.apache.asterix.common.cluster.IClusterStateManager; import org.apache.asterix.metadata.api.IDatasourceFunction; @@ -33,6 +34,7 @@ import org.apache.asterix.om.types.IAType; import org.apache.hyracks.algebricks.common.constraints.AlgebricksAbsolutePartitionConstraint; import org.apache.hyracks.algebricks.common.constraints.AlgebricksPartitionConstraint; import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException; +import org.apache.hyracks.algebricks.common.utils.ListSet; import org.apache.hyracks.algebricks.common.utils.Pair; import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable; import org.apache.hyracks.algebricks.core.algebra.expressions.IVariableTypeEnvironment; @@ -41,12 +43,14 @@ import org.apache.hyracks.algebricks.core.algebra.metadata.IDataSourceProperties import org.apache.hyracks.algebricks.core.algebra.metadata.IProjectionInfo; import org.apache.hyracks.algebricks.core.algebra.operators.logical.IOperatorSchema; import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator; +import org.apache.hyracks.algebricks.core.algebra.properties.FunctionalDependency; import org.apache.hyracks.algebricks.core.algebra.properties.ILocalStructuralProperty; import org.apache.hyracks.algebricks.core.algebra.properties.INodeDomain; +import org.apache.hyracks.algebricks.core.algebra.properties.IPartitioningProperty; import org.apache.hyracks.algebricks.core.algebra.properties.LocalOrderProperty; import org.apache.hyracks.algebricks.core.algebra.properties.OrderColumn; -import org.apache.hyracks.algebricks.core.algebra.properties.RandomPartitioningProperty; import org.apache.hyracks.algebricks.core.algebra.properties.StructuralPropertiesVector; +import org.apache.hyracks.algebricks.core.algebra.properties.UnorderedPartitionedProperty; import org.apache.hyracks.algebricks.core.jobgen.impl.JobGenContext; import org.apache.hyracks.api.dataflow.IOperatorDescriptor; import org.apache.hyracks.api.job.JobSpecification; @@ -93,6 +97,11 @@ public class QueryIndexDatasource extends FunctionDataSource { return storageLocations; } + @Override + public void computeFDs(List<LogicalVariable> scanVariables, List<FunctionalDependency> fdList) { + // no op + } + @Override public boolean isScanAccessPathALeaf() { // the index scan op is not a leaf op. the ETS op will start the scan of the index. we need the ETS op below @@ -122,14 +131,21 @@ public class QueryIndexDatasource extends FunctionDataSource { @Override public IDataSourcePropertiesProvider getPropertiesProvider() { return scanVariables -> { + // scanVariables should be SKs + PKs List<ILocalStructuralProperty> propsLocal = new ArrayList<>(1); - //TODO(ali): consider primary keys? + //TODO(ali): local ordering should be gone in compute-storage separation setup similar to data-scan List<OrderColumn> secKeys = new ArrayList<>(numSecKeys); for (int i = 0; i < numSecKeys; i++) { secKeys.add(new OrderColumn(scanVariables.get(i), OrderOperator.IOrder.OrderKind.ASC)); } propsLocal.add(new LocalOrderProperty(secKeys)); - return new StructuralPropertiesVector(new RandomPartitioningProperty(domain), propsLocal); + int numPKs = ds.getPrimaryKeys().size(); + Set<LogicalVariable> pVars = new ListSet<>(); + for (int i = 0, j = numSecKeys; i < numPKs; i++, j++) { + pVars.add(scanVariables.get(j)); + } + IPartitioningProperty pp = new UnorderedPartitionedProperty(pVars, domain); + return new StructuralPropertiesVector(pp, propsLocal); }; } diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/QueryIndexRewriter.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/QueryIndexRewriter.java index e0ff9c2ee0..d4789239a7 100644 --- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/QueryIndexRewriter.java +++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/QueryIndexRewriter.java @@ -127,8 +127,10 @@ public class QueryIndexRewriter extends FunctionRewriter implements IResultTypeC AlgebricksAbsolutePartitionConstraint secPartitionConstraint = (AlgebricksAbsolutePartitionConstraint) secIdxHelper.getSecondaryPartitionConstraint(); INodeDomain domain = mp.findNodeDomain(ds.getNodeGroupName()); + ARecordType dsType = (ARecordType) mp.findType(ds); + ARecordType metaType = DatasetUtil.getMetaType(mp, ds); ARecordType recType = computeRecType(f, mp, null, null, null); - int numSecKeys = ((Index.ValueIndexDetails) idx.getIndexDetails()).getKeyFieldNames().size(); + int numSecKeys = KeyFieldTypeUtil.getNumSecondaryKeys(idx, dsType, metaType); return new QueryIndexDatasource(ds, idx.getIndexName(), domain, secPartitionConstraint, recType, numSecKeys); } @@ -144,7 +146,9 @@ public class QueryIndexRewriter extends FunctionRewriter implements IResultTypeC ARecordType dsType = (ARecordType) metadataProvider.findType(dataset); ARecordType metaType = DatasetUtil.getMetaType(metadataProvider, dataset); List<IAType> dsKeyTypes = KeyFieldTypeUtil.getPartitoningKeyTypes(dataset, dsType, metaType); - List<Pair<IAType, Boolean>> secKeyTypes = KeyFieldTypeUtil.getBTreeIndexKeyTypes(index, dsType, metaType); + List<Pair<IAType, Boolean>> secKeyTypes = index.getIndexType() == DatasetConfig.IndexType.BTREE + ? KeyFieldTypeUtil.getBTreeIndexKeyTypes(index, dsType, metaType) + : KeyFieldTypeUtil.getArrayIndexKeyTypes(index, dsType, metaType); int numPrimaryKeys = dsKeyTypes.size(); int numSecKeys = secKeyTypes.size(); String[] fieldNames = new String[numSecKeys + numPrimaryKeys]; @@ -187,6 +191,9 @@ public class QueryIndexRewriter extends FunctionRewriter implements IResultTypeC if (dataset == null) { throw new CompilationException(ErrorCode.UNKNOWN_DATASET_IN_DATAVERSE, loc, dsName, dvName); } + if (dataset.isCorrelated()) { + throw new CompilationException(ErrorCode.OPERATION_NOT_SUPPORTED_ON_PRIMARY_INDEX, loc, dsName); + } return dataset; } @@ -200,12 +207,12 @@ public class QueryIndexRewriter extends FunctionRewriter implements IResultTypeC throw new CompilationException(ErrorCode.OPERATION_NOT_SUPPORTED_ON_PRIMARY_INDEX, loc, idxName); } DatasetConfig.IndexType idxType = index.getIndexType(); - // currently, only normal secondary indexes are supported - if (idxType != DatasetConfig.IndexType.BTREE || Index.IndexCategory.of(idxType) != Index.IndexCategory.VALUE - || index.isPrimaryKeyIndex()) { - throw new CompilationException(ErrorCode.COMPILATION_FUNC_EXPRESSION_CANNOT_UTILIZE_INDEX, - f.getSourceLocation(), LogRedactionUtil.userData(f.toString())); + // currently, only normal and array secondary indexes are supported + if ((idxType == DatasetConfig.IndexType.BTREE && !index.isPrimaryKeyIndex()) + || idxType == DatasetConfig.IndexType.ARRAY) { + return index; } - return index; + throw new CompilationException(ErrorCode.COMPILATION_FUNC_EXPRESSION_CANNOT_UTILIZE_INDEX, + f.getSourceLocation(), LogRedactionUtil.userData(f.toString())); } } diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/limit/pushdown_to_index_search.sqlpp similarity index 58% copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp copy to asterixdb/asterix-app/src/test/resources/optimizerts/queries/limit/pushdown_to_index_search.sqlpp index cf3a0e362e..abdc0a40c7 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/limit/pushdown_to_index_search.sqlpp @@ -17,6 +17,30 @@ * under the License. */ +/* + * Description: Tests that LIMIT is pushed down to index-search in indexnl join + */ + +DROP DATAVERSE test IF EXISTS; +CREATE DATAVERSE test; USE test; -// cannot use array index -FROM query_index("test", "ds1", "ds1_array_idx") as v SELECT VALUE v; \ No newline at end of file + +CREATE TYPE openType AS OPEN { + id:string +}; + +CREATE DATASET ds1(openType) PRIMARY KEY id; +CREATE DATASET ds2(openType) PRIMARY KEY id; + +SELECT + ds1.field1, + ds1.field2, + ds1.field3, + ds1.field4, + comp_field, + ds2.fieldX +FROM ds1 AS ds1 +JOIN ds2 AS ds2 + ON ds2.id /*+ indexnl */ = (ds1.field2 || ":" || TO_STRING(ds1.field3) || ":" || ds1.field4) + LET comp_field = (REPLACE(ds1.field1, "-", "") || "03") +LIMIT 2; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/limit/pushdown_to_index_search.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/limit/pushdown_to_index_search.plan new file mode 100644 index 0000000000..172833102e --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/limit/pushdown_to_index_search.plan @@ -0,0 +1,44 @@ +distribute result [$$54] +-- DISTRIBUTE_RESULT |UNPARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| + limit 2 + -- STREAM_LIMIT |UNPARTITIONED| + project ([$$54]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- SORT_MERGE_EXCHANGE [$$60(ASC) ] |PARTITIONED| + project ([$$60, $$54]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$54] <- [{"field1": $$55, "field2": $$56, "field3": $$57, "field4": $$58, "comp_field": string-concat(ordered-list-constructor(replace($$55, "-", ""), "03")), "fieldX": $$ds2.getField("fieldX")}] + -- ASSIGN |PARTITIONED| + limit 2 + -- STREAM_LIMIT |PARTITIONED| + project ([$$55, $$58, $$57, $$56, $$60, $$ds2]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$60, $$ds2] <- index-search("ds2", 0, "test", "ds2", true, true, 1, $$62, 1, $$62, true, true, true) limit 2 + -- BTREE_SEARCH |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$62) + -- STABLE_SORT [$$62(ASC)] |PARTITIONED| + exchange + -- HASH_PARTITION_EXCHANGE [$$62] |PARTITIONED| + assign [$$62] <- [string-concat(ordered-list-constructor($$56, ":", to-string($$57), ":", $$58))] + -- ASSIGN |PARTITIONED| + project ([$$55, $$58, $$57, $$56]) + -- STREAM_PROJECT |PARTITIONED| + assign [$$55, $$58, $$57, $$56] <- [$$ds1.getField("field1"), $$ds1.getField("field4"), $$ds1.getField("field3"), $$ds1.getField("field2")] + -- ASSIGN |PARTITIONED| + project ([$$ds1]) + -- STREAM_PROJECT |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$59, $$ds1] <- test.ds1 + -- DATASOURCE_SCAN |PARTITIONED| + exchange + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source + -- EMPTY_TUPLE_SOURCE |PARTITIONED| \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.01.ddl.sqlpp similarity index 74% copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.01.ddl.sqlpp index cf3a0e362e..25bfa4ef4c 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.01.ddl.sqlpp @@ -17,6 +17,17 @@ * under the License. */ +/* + * Description: Tests that LIMIT is pushed down to index-search in indexnl join + */ + +DROP DATAVERSE test IF EXISTS; +CREATE DATAVERSE test; USE test; -// cannot use array index -FROM query_index("test", "ds1", "ds1_array_idx") as v SELECT VALUE v; \ No newline at end of file + +CREATE TYPE openType AS OPEN { + id:string +}; + +CREATE DATASET ds1(openType) PRIMARY KEY id; +CREATE DATASET ds2(openType) PRIMARY KEY id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.02.update.sqlpp similarity index 58% copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.02.update.sqlpp index cf3a0e362e..868ce4e5f2 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.02.update.sqlpp @@ -18,5 +18,20 @@ */ USE test; -// cannot use array index -FROM query_index("test", "ds1", "ds1_array_idx") as v SELECT VALUE v; \ No newline at end of file + +UPSERT INTO ds1([ +{"id": "1", "field1": "f1", "field2": "1", "field3": "1", "field4": "1"}, +{"id": "2", "field1": "f2", "field2": "1", "field3": "1", "field4": "2"}, +{"id": "3", "field1": "f3", "field2": "1", "field3": "1", "field4": "3"}, +{"id": "4", "field1": "f4", "field2": "1", "field3": "1", "field4": "4"}, +{"id": "5", "field1": "f5", "field2": "1", "field3": "1", "field4": "5"}, +{"id": "6", "field1": "f6", "field2": "1", "field3": "1", "field4": "6"} +] +); + +UPSERT INTO ds2([ +{"id": "1:1:1", "fieldX": 10}, +{"id": "1:1:2", "fieldX": 11}, +{"id": "1:1:3", "fieldX": 12} +] +); \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.03.query.sqlpp similarity index 73% copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.03.query.sqlpp index cf3a0e362e..39e4ce97a0 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.03.query.sqlpp @@ -18,5 +18,16 @@ */ USE test; -// cannot use array index -FROM query_index("test", "ds1", "ds1_array_idx") as v SELECT VALUE v; \ No newline at end of file + +SELECT + ds1.field1, + ds1.field2, + ds1.field3, + ds1.field4, + comp_field, + ds2.fieldX +FROM ds1 AS ds1 +JOIN ds2 AS ds2 + ON ds2.id /*+ indexnl */ = (ds1.field2 || ":" || TO_STRING(ds1.field3) || ":" || ds1.field4) + LET comp_field = (REPLACE(ds1.field1, "-", "") || "03") +LIMIT 2; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.99.ddl.sqlpp similarity index 88% copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.99.ddl.sqlpp index cf3a0e362e..36b2bab543 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.99.ddl.sqlpp @@ -17,6 +17,4 @@ * under the License. */ -USE test; -// cannot use array index -FROM query_index("test", "ds1", "ds1_array_idx") as v SELECT VALUE v; \ No newline at end of file +DROP DATAVERSE test IF EXISTS; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.001.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.001.ddl.sqlpp index 270a1d47d1..1a6023f20e 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.001.ddl.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.001.ddl.sqlpp @@ -24,10 +24,12 @@ USE test; CREATE TYPE t1 AS { id: int }; CREATE TYPE t2 AS { id: int, age: int }; CREATE TYPE t3 AS { id: int, age: int?, dept: string? }; +CREATE TYPE t4 AS { id: int, ages: [int]}; CREATE DATASET ds1(t1) PRIMARY KEY id; CREATE DATASET ds2(t2) PRIMARY KEY id; CREATE DATASET ds3(t3) PRIMARY KEY id; +CREATE DATASET ds4(t4) PRIMARY KEY id; CREATE INDEX ds1_age ON ds1(age: int); CREATE INDEX ds1_dept ON ds1(dept: string); @@ -42,4 +44,7 @@ CREATE INDEX ds2_dept_age ON ds2(dept: string, age); CREATE INDEX ds3_age ON ds3(age); CREATE INDEX ds3_dept ON ds3(dept); CREATE INDEX ds3_age_dept ON ds3(age, dept); -CREATE INDEX ds3_dept_age ON ds3(dept, age); \ No newline at end of file +CREATE INDEX ds3_dept_age ON ds3(dept, age); + +CREATE INDEX ds4_array_idx1 ON ds4(UNNEST ages) EXCLUDE UNKNOWN KEY; +CREATE INDEX ds4_array_idx2 ON ds4(UNNEST ages_op1 : int) EXCLUDE UNKNOWN KEY; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.002.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.002.update.sqlpp index 04fd66f6ef..3dd8ffa3f2 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.002.update.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.002.update.sqlpp @@ -62,4 +62,11 @@ UPSERT INTO ds3 ([ {"id":12, "age": null, "dept": null}, {"id":13, "age": null }, {"id":14, "dept": null} +]); + +UPSERT INTO ds4 ([ +{"id":1, "ages": [1,9,2,3], "ages_op1": [111,999,222,333]}, +{"id":2, "ages": [1,9,2,3], "ages_op1": null}, +{"id":3, "ages": [1,9,2,3]}, +{"id":4, "ages": [1,9,2,7], "ages_op1": [11,99,null,77]} ]); \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.032.query.sqlpp similarity index 85% copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.032.query.sqlpp index cf3a0e362e..de6f4e92d4 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.032.query.sqlpp @@ -18,5 +18,6 @@ */ USE test; -// cannot use array index -FROM query_index("test", "ds1", "ds1_array_idx") as v SELECT VALUE v; \ No newline at end of file +SET `compiler.sort.parallel` "false"; +SELECT v.SK0 AS age, v.PK0 +FROM query_index("test", "ds4", "ds4_array_idx1") as v ORDER BY v.SK0, v.PK0; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.033.query.sqlpp similarity index 85% copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.033.query.sqlpp index cf3a0e362e..03487ed891 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.033.query.sqlpp @@ -18,5 +18,6 @@ */ USE test; -// cannot use array index -FROM query_index("test", "ds1", "ds1_array_idx") as v SELECT VALUE v; \ No newline at end of file +SET `compiler.sort.parallel` "false"; +SELECT v.SK0 AS age, v.PK0 +FROM query_index("test", "ds4", "ds4_array_idx2") as v ORDER BY v.SK0, v.PK0; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.034.query.sqlpp similarity index 84% copy from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.034.query.sqlpp index cf3a0e362e..6dc41b3f66 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.034.query.sqlpp @@ -18,5 +18,6 @@ */ USE test; -// cannot use array index -FROM query_index("test", "ds1", "ds1_array_idx") as v SELECT VALUE v; \ No newline at end of file +SET `compiler.sort.parallel` "false"; +EXPLAIN SELECT v.SK0 AS age, v.PK0 +FROM query_index("test", "ds4", "ds4_array_idx1") as v ORDER BY v.SK0, v.PK0; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.035.query.sqlpp similarity index 84% rename from asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp rename to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.035.query.sqlpp index cf3a0e362e..1144f5a7cd 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/negative/negative.005.query.sqlpp +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/query_index/q01/q01.035.query.sqlpp @@ -18,5 +18,6 @@ */ USE test; -// cannot use array index -FROM query_index("test", "ds1", "ds1_array_idx") as v SELECT VALUE v; \ No newline at end of file +SET `compiler.sort.parallel` "false"; +EXPLAIN SELECT v.SK0 AS age, v.PK0 +FROM query_index("test", "ds4", "ds4_array_idx2") as v ORDER BY v.SK0, v.PK0; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.4.query.sqlpp new file mode 100644 index 0000000000..d23d33e637 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q90/q90.4.query.sqlpp @@ -0,0 +1,43 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + + +use tpcds; +set `compiler.parallelism` "3"; +explain select ((array_count((select 1 as amc + from web_sales ws1, household_demographics , time_dim, web_page + where ws1.ws_sold_time_sk = time_dim.t_time_sk + and ws1.ws_ship_hdemo_sk = household_demographics.hd_demo_sk + and ws1.ws_web_page_sk = web_page.wp_web_page_sk + and time_dim.t_hour >= 6 + and time_dim.t_hour <= 6+1 + and household_demographics.hd_dep_count = 8 + and web_page.wp_char_count >= 5000 + and web_page.wp_char_count <= 5200 limit 10))) *1.0 /(array_count((select 1 as pmc + from web_sales ws2, household_demographics , time_dim, web_page + where ws2.ws_sold_time_sk = time_dim.t_time_sk + and ws2.ws_ship_hdemo_sk = household_demographics.hd_demo_sk + and ws2.ws_web_page_sk = web_page.wp_web_page_sk + and time_dim.t_hour >= 14 + and time_dim.t_hour <= 14+1 + and household_demographics.hd_dep_count = 8 + and web_page.wp_char_count >= 5000 + and web_page.wp_char_count <= 5200 limit 10)))) am_pm_ratio + order by am_pm_ratio + limit 100; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.03.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.03.adm new file mode 100644 index 0000000000..c61af0f3dc --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/limit/push-limit-to-join-primary-lookup/push-limit-to-join-primary-lookup.03.adm @@ -0,0 +1,2 @@ +{ "comp_field": "f103", "field1": "f1", "field2": "1", "field3": "1", "field4": "1", "fieldX": 10 } +{ "comp_field": "f203", "field1": "f2", "field2": "1", "field3": "1", "field4": "2", "fieldX": 11 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.032.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.032.adm new file mode 100644 index 0000000000..93f066504f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.032.adm @@ -0,0 +1,16 @@ +{ "age": 1, "PK0": 1 } +{ "age": 1, "PK0": 2 } +{ "age": 1, "PK0": 3 } +{ "age": 1, "PK0": 4 } +{ "age": 2, "PK0": 1 } +{ "age": 2, "PK0": 2 } +{ "age": 2, "PK0": 3 } +{ "age": 2, "PK0": 4 } +{ "age": 3, "PK0": 1 } +{ "age": 3, "PK0": 2 } +{ "age": 3, "PK0": 3 } +{ "age": 7, "PK0": 4 } +{ "age": 9, "PK0": 1 } +{ "age": 9, "PK0": 2 } +{ "age": 9, "PK0": 3 } +{ "age": 9, "PK0": 4 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.033.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.033.adm new file mode 100644 index 0000000000..2e728ef6a3 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.033.adm @@ -0,0 +1,7 @@ +{ "age": 11, "PK0": 4 } +{ "age": 77, "PK0": 4 } +{ "age": 99, "PK0": 4 } +{ "age": 111, "PK0": 1 } +{ "age": 222, "PK0": 1 } +{ "age": 333, "PK0": 1 } +{ "age": 999, "PK0": 1 } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.034.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.034.plan new file mode 100644 index 0000000000..30f0d1e7a1 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.034.plan @@ -0,0 +1,20 @@ +distribute result [$$16] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] +-- DISTRIBUTE_RESULT |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$16]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- STREAM_PROJECT |PARTITIONED| + assign [$$16] <- [{"age": $$19, "PK0": $$20}] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ASSIGN |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- SORT_MERGE_EXCHANGE [$$19(ASC), $$20(ASC) ] |PARTITIONED| + order (ASC, $$19) (ASC, $$20) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- STABLE_SORT [$$19(ASC), $$20(ASC)] |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$19, $$20] <- test.ds4.ds4_array_idx1.query-index [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- DATASOURCE_SCAN |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- EMPTY_TUPLE_SOURCE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.035.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.035.plan new file mode 100644 index 0000000000..5255c02f54 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/query_index/q01/q01.035.plan @@ -0,0 +1,20 @@ +distribute result [$$16] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] +-- DISTRIBUTE_RESULT |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$16]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- STREAM_PROJECT |PARTITIONED| + assign [$$16] <- [{"age": $$19, "PK0": $$20}] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ASSIGN |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- SORT_MERGE_EXCHANGE [$$19(ASC), $$20(ASC) ] |PARTITIONED| + order (ASC, $$19) (ASC, $$20) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- STABLE_SORT [$$19(ASC), $$20(ASC)] |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$19, $$20] <- test.ds4.ds4_array_idx2.query-index [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- DATASOURCE_SCAN |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- EMPTY_TUPLE_SOURCE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q90/q90.2.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q90/q90.2.plan new file mode 100644 index 0000000000..9021e07eae --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q90/q90.2.plan @@ -0,0 +1,210 @@ +distribute result [$$166] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] +-- DISTRIBUTE_RESULT |LOCAL| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |LOCAL| + limit 100 [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_LIMIT |LOCAL| + project ([$$166]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |LOCAL| + assign [$$166] <- [{"am_pm_ratio": $#1}] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ASSIGN |LOCAL| + limit 100 [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_LIMIT |LOCAL| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |LOCAL| + order (topK: 100) (ASC, $#1) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STABLE_SORT [topK: 100] [$#1(ASC)] |LOCAL| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |LOCAL| + project ([$#1]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |LOCAL| + assign [$#1] <- [numeric-divide(numeric-multiply($$196, 1.0), $$183)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ASSIGN |LOCAL| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |LOCAL| + join (true) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- NESTED_LOOP |LOCAL| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- ONE_TO_ONE_EXCHANGE |LOCAL| + aggregate [$$196] <- [agg-sql-sum($$197)] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- AGGREGATE |LOCAL| + aggregate [$$197] <- [agg-sql-count(1)] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- AGGREGATE |LOCAL| + limit 10 [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- STREAM_LIMIT |UNPARTITIONED| + project ([]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- SORT_MERGE_EXCHANGE [$$173(ASC) ] |PARTITIONED| + limit 10 [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- STREAM_LIMIT |PARTITIONED| + project ([$$173]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- STREAM_PROJECT |PARTITIONED| + select (and(ge($$167, 5000), le($$167, 5200))) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1.3E7] + -- STREAM_SELECT |PARTITIONED| + project ([$$173, $$167]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- STREAM_PROJECT |PARTITIONED| + assign [$$167] <- [$$web_page.getField(10)] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ASSIGN |PARTITIONED| + project ([$$173, $$web_page]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$173, $$web_page] <- index-search("web_page", 0, "tpcds", "web_page", true, true, 1, $$181, 1, $$181, true, true, true) limit 10 [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- BTREE_SEARCH |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$181) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STABLE_SORT [$$181(ASC)] |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- HASH_PARTITION_EXCHANGE [$$181] |PARTITIONED| + project ([$$181]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + select (and(ge($$168, 6), le($$168, 7))) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 9000000.0] + -- STREAM_SELECT |PARTITIONED| + project ([$$181, $$168]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + assign [$$168] <- [$$time_dim.getField(3)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ASSIGN |PARTITIONED| + project ([$$181, $$time_dim]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$172, $$time_dim] <- index-search("time_dim", 0, "tpcds", "time_dim", true, true, 1, $$184, 1, $$184, true, true, true) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- BTREE_SEARCH |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$184) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STABLE_SORT [$$184(ASC)] |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- HASH_PARTITION_EXCHANGE [$$184] |PARTITIONED| + project ([$$181, $$184]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + select (eq($$household_demographics.getField(3), 8)) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 5000000.0] + -- STREAM_SELECT |PARTITIONED| + project ([$$181, $$184, $$household_demographics]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$171, $$household_demographics] <- index-search("household_demographics", 0, "tpcds", "household_demographics", true, true, 1, $$186, 1, $$186, true, true, true) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- BTREE_SEARCH |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$186) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STABLE_SORT [$$186(ASC)] |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- HASH_PARTITION_EXCHANGE [$$186] |PARTITIONED| + project ([$$181, $$184, $$186]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + assign [$$181, $$184, $$186] <- [$$ws1.getField(12), $$ws1.getField(1), $$ws1.getField(10)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ASSIGN |PARTITIONED| + project ([$$ws1]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + assign [$$ws1] <- [$$ws2] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ASSIGN |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + replicate [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- REPLICATE |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$ws2]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$174, $$175, $$ws2] <- tpcds.web_sales [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- DATASOURCE_SCAN |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- EMPTY_TUPLE_SOURCE |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- ONE_TO_ONE_EXCHANGE |LOCAL| + aggregate [$$183] <- [agg-sql-sum($$198)] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- AGGREGATE |LOCAL| + aggregate [$$198] <- [agg-sql-count(1)] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- AGGREGATE |LOCAL| + limit 10 [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- STREAM_LIMIT |UNPARTITIONED| + project ([]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- SORT_MERGE_EXCHANGE [$$178(ASC) ] |PARTITIONED| + limit 10 [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- STREAM_LIMIT |PARTITIONED| + project ([$$178]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1.3E7] + -- STREAM_PROJECT |PARTITIONED| + select (and(ge($$179, 5000), le($$179, 5200))) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1.3E7] + -- STREAM_SELECT |PARTITIONED| + project ([$$178, $$179]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- STREAM_PROJECT |PARTITIONED| + assign [$$179] <- [$$web_page.getField(10)] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ASSIGN |PARTITIONED| + project ([$$178, $$web_page]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$178, $$web_page] <- index-search("web_page", 0, "tpcds", "web_page", true, true, 1, $$190, 1, $$190, true, true, true) limit 10 [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- BTREE_SEARCH |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$190) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STABLE_SORT [$$190(ASC)] |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- HASH_PARTITION_EXCHANGE [$$190] |PARTITIONED| + project ([$$190]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + select (and(ge($$180, 14), le($$180, 15))) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 9000000.0] + -- STREAM_SELECT |PARTITIONED| + project ([$$190, $$180]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + assign [$$180] <- [$$time_dim.getField(3)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ASSIGN |PARTITIONED| + project ([$$190, $$time_dim]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$177, $$time_dim] <- index-search("time_dim", 0, "tpcds", "time_dim", true, true, 1, $$188, 1, $$188, true, true, true) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- BTREE_SEARCH |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$188) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STABLE_SORT [$$188(ASC)] |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- HASH_PARTITION_EXCHANGE [$$188] |PARTITIONED| + project ([$$190, $$188]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + select (eq($$household_demographics.getField(3), 8)) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 5000000.0] + -- STREAM_SELECT |PARTITIONED| + project ([$$190, $$188, $$household_demographics]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + unnest-map [$$176, $$household_demographics] <- index-search("household_demographics", 0, "tpcds", "household_demographics", true, true, 1, $$192, 1, $$192, true, true, true) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- BTREE_SEARCH |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + order (ASC, $$192) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STABLE_SORT [$$192(ASC)] |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- HASH_PARTITION_EXCHANGE [$$192] |PARTITIONED| + project ([$$190, $$192, $$188]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + assign [$$190, $$192, $$188] <- [$$ws2.getField(12), $$ws2.getField(10), $$ws2.getField(1)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ASSIGN |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + replicate [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- REPLICATE |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + project ([$$ws2]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- STREAM_PROJECT |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + data-scan []<-[$$174, $$175, $$ws2] <- tpcds.web_sales [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0] + -- DATASOURCE_SCAN |PARTITIONED| + exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- ONE_TO_ONE_EXCHANGE |PARTITIONED| + empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0] + -- EMPTY_TUPLE_SOURCE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml index 44bbeacc18..edba9c2b4f 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml @@ -9841,7 +9841,6 @@ <expected-error>ASX1105: Operation not supported on primary index ds1</expected-error> <expected-error>ASX1026: The given function expression query-index("test", "ds1", "pk_idx") cannot utilize index</expected-error> <expected-error>ASX1026: The given function expression query-index("test", "ds1", "sample_idx_1_ds1") cannot utilize index</expected-error> - <expected-error>ASX1026: The given function expression query-index("test", "ds1", "ds1_array_idx") cannot utilize index</expected-error> </compilation-unit> </test-case> </test-group> @@ -15508,6 +15507,11 @@ <output-dir compare="Text">push-limit-to-external-scan-select</output-dir> </compilation-unit> </test-case> + <test-case FilePath="limit"> + <compilation-unit name="push-limit-to-join-primary-lookup"> + <output-dir compare="Text">push-limit-to-join-primary-lookup</output-dir> + </compilation-unit> + </test-case> <test-case FilePath="limit"> <compilation-unit name="push-limit-to-primary-scan"> <output-dir compare="Text">push-limit-to-primary-scan</output-dir> diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/KeyFieldTypeUtil.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/KeyFieldTypeUtil.java index fd20b3c337..fcbe2988d3 100644 --- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/KeyFieldTypeUtil.java +++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/KeyFieldTypeUtil.java @@ -161,6 +161,25 @@ public class KeyFieldTypeUtil { return indexKeyTypes; } + /** + * @see KeyFieldTypeUtil#getBTreeIndexKeyTypes(Index, ARecordType, ARecordType) + */ + public static List<Pair<IAType, Boolean>> getArrayIndexKeyTypes(Index index, ARecordType recordType, + ARecordType metaRecordType) throws AlgebricksException { + Index.ArrayIndexDetails indexDetails = (Index.ArrayIndexDetails) index.getIndexDetails(); + List<Pair<IAType, Boolean>> indexKeyTypes = new ArrayList<>(); + for (Index.ArrayIndexElement e : indexDetails.getElementList()) { + for (int i = 0; i < e.getProjectList().size(); i++) { + ARecordType sourceType = + (e.getSourceIndicator() == Index.RECORD_INDICATOR) ? recordType : metaRecordType; + Pair<IAType, Boolean> keyPairType = ArrayIndexUtil.getNonNullableOpenFieldType(e.getTypeList().get(i), + e.getUnnestList(), e.getProjectList().get(i), sourceType); + indexKeyTypes.add(keyPairType); + } + } + return indexKeyTypes; + } + /** * Get the types of RTree index key fields *
