[ASTERIXDB-3582][COMP] Restricting to one unnest filter pushdown for a datasource
- user model changes: no
- storage format changes: no
- interface changes: no
Currently, storage filter evaluation produces incorrect results when
multiple unnest filters are pushed down. This leads to inaccurate
query outputs.
To prevent incorrect evaluations, this change restricts the pushdown
to at most one unnest filter. This is a temporary safeguard until we
improve the evaluation algorithm.
Ext-ref: MB-65781
Change-Id: I4f335db4c7c455c1276ea5be9b14ce1e8ee92d74
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/19529
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Ali Alsuliman <ali.al.solaiman@gmail.com>
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/processor/AbstractFilterPushdownProcessor.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/processor/AbstractFilterPushdownProcessor.java
index 6134d63..cad9315 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/processor/AbstractFilterPushdownProcessor.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/processor/AbstractFilterPushdownProcessor.java
@@ -207,7 +207,7 @@
}
}
- private boolean pushdownFilter(ScanDefineDescriptor scanDescriptor) throws AlgebricksException {
+ protected boolean pushdownFilter(ScanDefineDescriptor scanDescriptor) throws AlgebricksException {
boolean changed = false;
for (UseDescriptor candidate : scanCandidateFilters) {
changed |= inlineAndPushdownFilter(candidate, scanDescriptor);
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/processor/ColumnFilterPushdownProcessor.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/processor/ColumnFilterPushdownProcessor.java
index 425a78d..1ec64d5 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/processor/ColumnFilterPushdownProcessor.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/processor/ColumnFilterPushdownProcessor.java
@@ -96,7 +96,6 @@
ILogicalOperator useOp = useDescriptor.getOperator();
ILogicalOperator scanOp = scanDescriptor.getOperator();
exprToNodeVisitor.setTypeEnv(PushdownUtil.getTypeEnv(useOp, scanOp, context));
- paths.clear();
}
@Override
@@ -128,8 +127,22 @@
}
@Override
+ protected boolean pushdownFilter(ScanDefineDescriptor scanDescriptor) throws AlgebricksException {
+ paths.clear();
+ checkerVisitor.beforeVisit();
+ return super.pushdownFilter(scanDescriptor);
+ }
+
+ @Override
protected void putFilterInformation(ScanDefineDescriptor scanDefineDescriptor, ILogicalExpression inlinedExpr)
throws AlgebricksException {
+ if (checkerVisitor.containsMultipleArrayPaths(paths.values())) {
+ // Cannot pushdown a filter with multiple unnest
+ // TODO allow rewindable column readers for filters
+ // TODO this is a bit conservative (maybe too conservative) as we can push part of expression down
+ return;
+ }
+
ILogicalExpression filterExpr = scanDefineDescriptor.getFilterExpression();
if (filterExpr != null) {
filterExpr = andExpression(filterExpr, inlinedExpr);
@@ -138,13 +151,6 @@
scanDefineDescriptor.setFilterExpression(inlinedExpr);
}
- if (checkerVisitor.containsMultipleArrayPaths(paths.values())) {
- // Cannot pushdown a filter with multiple unnest
- // TODO allow rewindable column readers for filters
- // TODO this is a bit conservative (maybe too conservative) as we can push part of expression down
- return;
- }
-
scanDefineDescriptor.getFilterPaths().putAll(paths);
}
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/visitor/ArrayPathCheckerVisitor.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/visitor/ArrayPathCheckerVisitor.java
index 46a247b..0752fc3 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/visitor/ArrayPathCheckerVisitor.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/visitor/ArrayPathCheckerVisitor.java
@@ -46,19 +46,27 @@
seenCollections = new HashSet<>();
}
+ public void beforeVisit() {
+ seenCollections.clear();
+ firstPath = true;
+ }
+
public boolean containsMultipleArrayPaths(Collection<ARecordType> paths) throws AlgebricksException {
ARecordType mergedPaths = EMPTY_TYPE;
for (ARecordType path : paths) {
mergedPaths = (ARecordType) RecordMergeTypeComputer.merge(mergedPaths, path);
}
- firstPath = true;
- return !mergedPaths.accept(this, null);
+ boolean multiplePaths = !mergedPaths.accept(this, null);
+ if (multiplePaths) {
+ seenCollections.removeAll(paths);
+ }
+ return multiplePaths;
}
@Override
public Boolean visit(ARecordType recordType, AbstractCollectionType arg) {
for (IAType child : recordType.getFieldTypes()) {
- if (!child.accept(this, null)) {
+ if (!child.accept(this, arg)) {
return Boolean.FALSE;
}
}
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.001.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.001.ddl.sqlpp
new file mode 100644
index 0000000..ba0b695
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.001.ddl.sqlpp
@@ -0,0 +1,37 @@
+/*
+ * 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.
+ */
+
+CREATE DATAVERSE websales;
+CREATE DATAVERSE inventory;
+CREATE DATAVERSE marketing;
+
+CREATE DATASET websales.customers
+ PRIMARY KEY (custid: string) WITH { "storage-format": { "format": "column" }};
+
+CREATE DATASET websales.orders
+ PRIMARY KEY (orderno: int) WITH { "storage-format": { "format": "column" }};
+
+CREATE DATASET inventory.products
+ PRIMARY KEY (itemno: int) WITH { "storage-format": { "format": "column" }};
+
+CREATE DATASET marketing.reviews
+ PRIMARY KEY (itemno: int, custid: string) WITH { "storage-format": { "format": "column" }};
+
+CREATE DATASET marketing.inventory
+ PRIMARY KEY (id: string) WITH { "storage-format": { "format": "column" }};
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.002.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.002.update.sqlpp
new file mode 100644
index 0000000..39a7673
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.002.update.sqlpp
@@ -0,0 +1,413 @@
+/*
+ * 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.
+ */
+
+
+INSERT INTO websales.customers
+([
+ {
+ "custid": "C13",
+ "name": "T. Cody",
+ "address": {
+ "street": "201 Main St.",
+ "city": "St. Louis, MO",
+ "zipcode": "63101"
+ },
+ "rating": 750
+ },
+ {
+ "custid": "C25",
+ "name": "M. Sinclair",
+ "address": {
+ "street": "690 River St.",
+ "city": "Hanover, MA",
+ "zipcode": "02340"
+ },
+ "rating": 690
+ },
+ {
+ "custid": "C31",
+ "name": "B. Pruitt",
+ "address": {
+ "street": "360 Mountain Ave.",
+ "city": "St. Louis, MO",
+ "zipcode": "63101"
+ }
+ },
+ {
+ "custid": "C35",
+ "name": "J. Roberts",
+ "address": {
+ "street": "420 Green St.",
+ "city": "Boston, MA",
+ "zipcode": "02115"
+ },
+ "rating": 565
+ },
+ {
+ "custid": "C37",
+ "name": "T. Henry",
+ "address": {
+ "street": "120 Harbor Blvd.",
+ "city": "Boston, MA",
+ "zipcode": "02115"
+ },
+ "rating": 750
+ },
+ {
+ "custid": "C41",
+ "name": "R. Dodge",
+ "address": {
+ "street": "150 Market St.",
+ "city": "St. Louis, MO",
+ "zipcode": "63101"
+ },
+ "rating": 640
+ },
+ {
+ "custid": "C47",
+ "name": "S. Logan",
+ "address": {
+ "street": "Via del Corso",
+ "city": "Rome, Italy"
+ },
+ "rating": 625
+ }
+]);
+INSERT INTO websales.orders
+([
+ {
+ "orderno": 1001,
+ "custid": "C41",
+ "order_date": "2020-04-29",
+ "ship_date": "2020-05-03",
+ "items": [
+ {
+ "itemno": 347,
+ "qty": 5,
+ "price": 19.99
+ },
+ {
+ "itemno": 193,
+ "qty": 2,
+ "price": 28.89
+ }
+ ]
+ },
+ {
+ "orderno": 1002,
+ "custid": "C13",
+ "order_date": "2020-05-01",
+ "ship_date": "2020-05-03",
+ "items": [
+ {
+ "itemno": 460,
+ "qty": 95,
+ "price": 29.99
+ },
+ {
+ "itemno": 680,
+ "qty": 150,
+ "price": 22.99
+ }
+ ]
+ },
+ {
+ "orderno": 1003,
+ "custid": "C31",
+ "order_date": "2020-06-15",
+ "ship_date": "2020-06-16",
+ "items": [
+ {
+ "itemno": 120,
+ "qty": 2,
+ "price": 88.99
+ },
+ {
+ "itemno": 460,
+ "qty": 3,
+ "price": 29.99
+ }
+ ]
+ },
+ {
+ "orderno": 1004,
+ "custid": "C35",
+ "order_date": "2020-07-10",
+ "ship_date": "2020-07-15",
+ "items": [
+ {
+ "itemno": 680,
+ "qty": 6,
+ "price": 29.99
+ },
+ {
+ "itemno": 195,
+ "qty": 4,
+ "price": 35.00
+ }
+ ]
+ },
+ {
+ "orderno": 1005,
+ "custid": "C37",
+ "order_date": "2020-08-30",
+ "items": [
+ {
+ "itemno": 460,
+ "qty": 2,
+ "price": 29.99
+ },
+ {
+ "itemno": 347,
+ "qty": 120,
+ "price": 22.00
+ },
+ {
+ "itemno": 780,
+ "qty": 1,
+ "price": 1500.00
+ },
+ {
+ "itemno": 375,
+ "qty": 2,
+ "price": 149.98
+ }
+ ]
+ },
+ {
+ "orderno": 1006,
+ "custid": "C41",
+ "order_date": "2020-09-02",
+ "ship_date": "2020-09-04",
+ "items": [
+ {
+ "itemno": 680,
+ "qty": 51,
+ "price": 25.98
+ },
+ {
+ "itemno": 120,
+ "qty": 65,
+ "price": 85.00
+ },
+ {
+ "itemno": 460,
+ "qty": 120,
+ "price": 29.98
+ }
+ ]
+ },
+ {
+ "orderno": 1007,
+ "custid": "C13",
+ "order_date": "2020-09-13",
+ "ship_date": "2020-09-20",
+ "items": [
+ {
+ "itemno": 185,
+ "qty": 5,
+ "price": 21.99
+ },
+ {
+ "itemno": 680,
+ "qty": 1,
+ "price": 20.50
+ }
+ ]
+ },
+ {
+ "orderno": 1008,
+ "custid": "C13",
+ "order_date": "2020-10-13",
+ "items": [
+ {
+ "itemno": 460,
+ "qty": 20,
+ "price": 29.99
+ }
+ ]
+ },
+ {
+ "orderno": 1009,
+ "custid": "C13",
+ "order_date": "2020-10-13",
+ "items": []
+ }
+]);
+
+INSERT INTO inventory.products
+([
+ {
+ "itemno": 120,
+ "category": ["computer"],
+ "name": "16TB External SDD",
+ "descrip": "16TB storage add-on for Pacbook laptops",
+ "manuf": "El Cheapo",
+ "listprice": 99.00,
+ "kind": "electric",
+ "power": "USB"
+ },
+ {
+ "itemno": 185,
+ "category": ["office", "essentials"],
+ "name": "Stapler",
+ "descrip": "Manual stapler for up to 25 sheets of paper",
+ "manuf": "Office Min",
+ "listprice": 21.99
+ },
+ {
+ "itemno": 193,
+ "category": ["office"],
+ "name": "Super Stapler",
+ "descrip": "Electric stapler for up to 250 sheets of paper",
+ "manuf": "Office Min",
+ "listprice": 28.89,
+ "kind": "electric",
+ "power": "115V"
+ },
+ {
+ "itemno": 195,
+ "category": ["computer", "office"],
+ "name": "Computer Stand",
+ "manuf": "ISeeYa",
+ "listprice": 49.00
+ },
+ {
+ "itemno": 347,
+ "category": ["essentials"],
+ "name": "Beer Cooler Backpack",
+ "manuf": "Robo Brew",
+ "listprice": 25.95,
+ "kind": "electric",
+ "power": "D batteries"
+ },
+ {
+ "itemno": 375,
+ "category": ["music"],
+ "name": "Stratuscaster Guitar",
+ "manuf": "Fender Bender",
+ "listprice": 149.99
+ },
+ {
+ "itemno": 460,
+ "category": ["music", "clothing"],
+ "name": "Fender Bender T-Shirt",
+ "descrip": "Extra Large T-Shirt for Fender Bender fans",
+ "manuf": "Fender Bender",
+ "listprice": 34.99,
+ "kind": "clothing",
+ "size": "XL"
+ },
+ {
+ "itemno": 680,
+ "category": ["music", "clothing"],
+ "name": "Fender Bender Tank Top",
+ "descrip": "Medium Tank Top for Fender Bender fans",
+ "manuf": "Fender Bender",
+ "listprice": 29.99,
+ "kind": "clothing",
+ "size": "M"
+ },
+ {
+ "itemno": 780,
+ "category": ["essentials", "kitchen"],
+ "name": "Automatic Beer Opener",
+ "descrip": "Robotic beer bottle opener",
+ "manuf": "Robo Brew",
+ "listprice": 29.95,
+ "kind": "electric",
+ "power": "AA batteries"
+ }
+]);
+
+INSERT INTO marketing.reviews
+([
+ {
+ "itemno": 193,
+ "name": "Super Stapler",
+ "rating": 5,
+ "comment": "This electric stapler is the bomb",
+ "custid": "C41",
+ "rev_date": "2020-05-13"
+ },
+ {
+ "itemno": 347,
+ "name": "Beer Cooler Backpack",
+ "rating": 5,
+ "comment": "Every camper needs one of these for sure",
+ "custid": "C41",
+ "rev_date": "2020-05-13"
+ },
+ {
+ "itemno": 375,
+ "name": "Stratuscaster Guitar",
+ "rating": 4,
+ "comment": "Anxiously awaiting its arrival!",
+ "custid": "C37",
+ "rev_date": "2020-09-07"
+ },
+ {
+ "itemno": 460,
+ "name": "Fender Bender T-Shirt",
+ "rating": 2,
+ "comment": "Had to return it 'cause it shrunk when washed",
+ "custid": "C13",
+ "rev_date": "2020-10-20"
+ },
+ {
+ "itemno": 460,
+ "name": "Fender Bender T-Shirt",
+ "rating": 2,
+ "comment": "Order one size up, it shrinks when you wash it",
+ "custid": "C41",
+ "rev_date": "2020-09-11"
+ },
+ {
+ "itemno": 680,
+ "name": "Fender Bender Tank Top",
+ "rating": 3,
+ "comment": "Like it, but had to reorder in a bigger size",
+ "custid": "C35",
+ "rev_date": "2020-07-25"
+ },
+ {
+ "itemno": 680,
+ "name": "Fender Bender Tank Top",
+ "rating": 2,
+ "comment": "Size seems to be smaller than expected",
+ "custid": "C13",
+ "rev_date": "2020-05-04"
+ },
+ {
+ "itemno": 780,
+ "name": "Automatic Beer Opener",
+ "rating": 1,
+ "comment": "Still waiting for mine to actually arrive...",
+ "custid": "C37",
+ "rev_date": "2020-09-30"
+ }
+]);
+
+INSERT INTO marketing.inventory(
+{
+ "id": "1",
+ "bought": [{ "qty": 2 }, { "qty": 100 }],
+ "sold": [{"lqty": 10}, {"lqty": 40}]
+}
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.003.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.003.query.sqlpp
new file mode 100644
index 0000000..1056e64
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.003.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+
+SELECT o.orderno, o.order_date, i.itemno AS item_number, i.qty AS quantity
+FROM websales.orders AS o, o.items AS i
+WHERE
+(SOME li IN o.items SATISFIES li.qty < 3)
+ AND i.qty >= 100
+ORDER BY o.orderno, item_number;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.004.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.004.query.sqlpp
new file mode 100644
index 0000000..40219e6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.004.query.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+SELECT o.id, o.sold, i.qty
+FROM marketing.inventory AS o, o.bought AS i, o.sold AS s
+WHERE
+i.qty < 3
+AND s.lqty > 19;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.005.query.sqlpp
new file mode 100644
index 0000000..a574d2d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582-2.005.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+EXPLAIN
+SELECT o.id, o.sold, i.qty
+FROM marketing.inventory AS o, o.bought AS i, o.sold AS s
+WHERE
+i.qty < 3
+AND s.lqty > 19;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.003.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.003.adm
new file mode 100644
index 0000000..9fd5328
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.003.adm
@@ -0,0 +1 @@
+{ "orderno": 1005, "order_date": "2020-08-30", "item_number": 347, "quantity": 120 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.004.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.004.adm
new file mode 100644
index 0000000..1fb4f83
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.004.adm
@@ -0,0 +1 @@
+{ "id": "1", "sold": [ { "lqty": 10 }, { "lqty": 40 } ], "qty": 2 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.005.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.005.plan
new file mode 100644
index 0000000..e59ecff
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.005.plan
@@ -0,0 +1,26 @@
+distribute result [$$46] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$46] <- [{"id": $$48, "sold": $$50, "qty": $$47}] project: [$$46] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |PARTITIONED|
+ select (gt($$s.getField("lqty"), 19)) project: [$$48, $$50, $$47] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |PARTITIONED|
+ unnest $$s <- scan-collection($$50) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |PARTITIONED|
+ select (lt($$47, 3)) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$47] <- [$$i.getField("qty")] project: [$$48, $$50, $$47] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |PARTITIONED|
+ unnest $$i <- scan-collection($$49) project: [$$48, $$50, $$i] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |PARTITIONED|
+ assign [$$50, $$49] <- [$$o.getField("sold"), $$o.getField("bought")] project: [$$48, $$50, $$49] [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|
+ data-scan []<-[$$48, $$o] <- marketing.inventory project ({sold:any,bought:[{qty:any}]}) filter on: gt(scan-collection($$o.getField("sold")).getField("lqty"), 19) range-filter on: and(gt(scan-collection($$o.getField("sold")).getField("lqty"), 19), lt(scan-collection($$o.getField("bought")).getField("qty"), 3)) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.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/column/pushdown/other-pushdowns/other-pushdowns.018.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/pushdown/other-pushdowns/other-pushdowns.018.plan
index bf4a1ce..cb4b1f2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/pushdown/other-pushdowns/other-pushdowns.018.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/pushdown/other-pushdowns/other-pushdowns.018.plan
@@ -48,7 +48,7 @@
-- STREAM_PROJECT |PARTITIONED|
exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$66, $$c] <- test.ColumnDataset project ({val2:[{y:any}],val1:[{x:any}]}) filter on: or(eq(scan-collection($$c.getField("val1")).getField("x"), 1), eq(scan-collection($$c.getField("val2")).getField("y"), 2)) range-filter on: or(eq(scan-collection($$c.getField("val1")).getField("x"), 1), eq(scan-collection($$c.getField("val2")).getField("y"), 2)) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ data-scan []<-[$$66, $$c] <- test.ColumnDataset project ({val2:[{y:any}],val1:[{x:any}]}) range-filter on: or(eq(scan-collection($$c.getField("val1")).getField("x"), 1), eq(scan-collection($$c.getField("val2")).getField("y"), 2)) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-- DATASOURCE_SCAN |PARTITIONED|
exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.005.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.005.plan
new file mode 100644
index 0000000..bf18bd2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/ASTERIXDB-3582-2/ASTERIXDB-3582.005.plan
@@ -0,0 +1,26 @@
+distribute result [$$46] [cardinality: 1.0, op-cost: 0.0, total-cost: 1.0]
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ exchange [cardinality: 1.0, op-cost: 0.0, total-cost: 1.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$46] <- [{"id": $$48, "sold": $$50, "qty": $$47}] project: [$$46] [cardinality: 1.0, op-cost: 0.0, total-cost: 1.0]
+ -- ASSIGN |PARTITIONED|
+ select (gt($$s.getField("lqty"), 19)) project: [$$48, $$50, $$47] [cardinality: 1.0, op-cost: 0.0, total-cost: 1.0]
+ -- STREAM_SELECT |PARTITIONED|
+ unnest $$s <- scan-collection($$50) [cardinality: 1.0, op-cost: 0.0, total-cost: 1.0]
+ -- UNNEST |PARTITIONED|
+ select (lt($$47, 3)) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$47] <- [$$i.getField("qty")] project: [$$48, $$50, $$47] [cardinality: 1.0, op-cost: 0.0, total-cost: 1.0]
+ -- ASSIGN |PARTITIONED|
+ unnest $$i <- scan-collection($$49) project: [$$48, $$50, $$i] [cardinality: 1.0, op-cost: 0.0, total-cost: 1.0]
+ -- UNNEST |PARTITIONED|
+ assign [$$50, $$49] <- [$$o.getField("sold"), $$o.getField("bought")] project: [$$48, $$50, $$49] [cardinality: 1.0, op-cost: 0.0, total-cost: 1.0]
+ -- ASSIGN |PARTITIONED|
+ exchange [cardinality: 1.0, op-cost: 0.0, total-cost: 1.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$48, $$o] <- marketing.inventory project ({sold:any,bought:[{qty:any}]}) filter on: gt(scan-collection($$o.getField("sold")).getField("lqty"), 19) range-filter on: and(gt(scan-collection($$o.getField("sold")).getField("lqty"), 19), lt(scan-collection($$o.getField("bought")).getField("qty"), 3)) [cardinality: 1.0, op-cost: 1.0, total-cost: 1.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_cbo/column/pushdown/other-pushdowns/other-pushdowns.018.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.018.plan
index d38ffb1..44a3200 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.018.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.018.plan
@@ -48,7 +48,7 @@
-- STREAM_PROJECT |PARTITIONED|
exchange [cardinality: 2.0, op-cost: 0.0, total-cost: 2.0]
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
- data-scan []<-[$$66, $$c] <- test.ColumnDataset project ({val2:[{y:any}],val1:[{x:any}]}) filter on: or(eq(scan-collection($$c.getField("val1")).getField("x"), 1), eq(scan-collection($$c.getField("val2")).getField("y"), 2)) range-filter on: or(eq(scan-collection($$c.getField("val1")).getField("x"), 1), eq(scan-collection($$c.getField("val2")).getField("y"), 2)) [cardinality: 2.0, op-cost: 2.0, total-cost: 2.0]
+ data-scan []<-[$$66, $$c] <- test.ColumnDataset project ({val2:[{y:any}],val1:[{x:any}]}) range-filter on: or(eq(scan-collection($$c.getField("val1")).getField("x"), 1), eq(scan-collection($$c.getField("val2")).getField("y"), 2)) [cardinality: 2.0, op-cost: 2.0, total-cost: 2.0]
-- DATASOURCE_SCAN |PARTITIONED|
exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-- ONE_TO_ONE_EXCHANGE |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 5552bce..c112b42 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
@@ -16536,6 +16536,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="column">
+ <compilation-unit name="filter/ASTERIXDB-3582-2">
+ <output-dir compare="Text">filter/ASTERIXDB-3582-2</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="column">
<compilation-unit name="delete/001">
<output-dir compare="Text">delete/001</output-dir>
</compilation-unit>