You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu> on 2023/09/18 17:40:38 UTC
Change in asterixdb[master]: [ASTERIXDB-3266][COMP] Avoid pushing SELECTs of SUBPLAN
From Wail Alkowaileet <wa...@gmail.com>:
Wail Alkowaileet has uploaded this change for review. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/17784 )
Change subject: [ASTERIXDB-3266][COMP] Avoid pushing SELECTs of SUBPLAN
......................................................................
[ASTERIXDB-3266][COMP] Avoid pushing SELECTs of SUBPLAN
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
SELECTs appear in subplan should be in a different
scope. Hence, it should not be pushed down in case
of columnar and external filters.
Change-Id: I3fd509897e4894c3c8ceeedd04695d07d53084f6
---
A asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.100.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.110.adm
M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/PushdownContext.java
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.110.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.111.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.001.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.200.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.201.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.004.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.200.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.005.regexadm
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.005.get.http
M asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
A asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.011.plan
A asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.111.plan
A asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.010.adm
M asterixdb/asterix-app/src/test/resources/runtimets/testsuite_external_dataset_s3.xml
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.003.get.http
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.011.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.002.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.010.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.201.plan
A asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.101.plan
A asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in/not-in.101.plan
A asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in/not-in.201.plan
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.100.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.000.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.003.regexadm
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.101.query.sqlpp
29 files changed, 836 insertions(+), 1 deletion(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/84/17784/1
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/PushdownContext.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/PushdownContext.java
index 219ef9c..17aa803 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/PushdownContext.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/PushdownContext.java
@@ -243,7 +243,7 @@
private static Set<LogicalOperatorTag> getScopeOperators() {
return EnumSet.of(LogicalOperatorTag.INNERJOIN, LogicalOperatorTag.LEFTOUTERJOIN, LogicalOperatorTag.GROUP,
- LogicalOperatorTag.AGGREGATE, LogicalOperatorTag.WINDOW);
+ LogicalOperatorTag.AGGREGATE, LogicalOperatorTag.WINDOW, LogicalOperatorTag.SUBPLAN);
}
}
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.001.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.001.ddl.sqlpp
new file mode 100644
index 0000000..a271778
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.001.ddl.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * 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.
+ */
+
+DROP DATAVERSE test if exists;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE ColumnType AS {
+ id: int
+};
+
+CREATE DATASET ColumnDataset(ColumnType)
+PRIMARY KEY id 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/not-in/not-in.002.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.002.update.sqlpp
new file mode 100644
index 0000000..169c767
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.002.update.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.
+ */
+
+USE test;
+
+INSERT INTO ColumnDataset (
+ {"id": 1, "a": "1", "array": [10, 20, 30]},
+ {"id": 2, "a": "2", "array": [40, 50, 60]},
+ {"id": 3, "a": "3", "array": [70, 80, 90]}
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.003.get.http b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.003.get.http
new file mode 100644
index 0000000..57d830a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.003.get.http
@@ -0,0 +1,19 @@
+/*
+ * 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.
+ */
+/connector?dataverseName=test&datasetName=ColumnDataset
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.004.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.004.update.sqlpp
new file mode 100644
index 0000000..1a6b83d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.004.update.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.
+ */
+
+USE test;
+
+INSERT INTO ColumnDataset (
+ {"id": 4, "a": "4", "array": [100, 200, 300]},
+ {"id": 5, "a": "5", "array": [400, 500, 600]},
+ {"id": 6, "a": "6", "array": [700, 800, 900]}
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.005.get.http b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.005.get.http
new file mode 100644
index 0000000..57d830a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.005.get.http
@@ -0,0 +1,19 @@
+/*
+ * 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.
+ */
+/connector?dataverseName=test&datasetName=ColumnDataset
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.100.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.100.query.sqlpp
new file mode 100644
index 0000000..2694c27
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.100.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 test;
+SET `compiler.column.filter` "true";
+
+
+
+SELECT VALUE d
+FROM ColumnDataset d
+WHERE d.a NOT IN ["1"]
+ORDER BY d.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.101.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.101.query.sqlpp
new file mode 100644
index 0000000..5a1f8c1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.101.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 test;
+SET `compiler.column.filter` "true";
+
+
+EXPLAIN
+SELECT VALUE d
+FROM ColumnDataset d
+WHERE d.a NOT IN ["1"]
+ORDER BY d.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.200.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.200.query.sqlpp
new file mode 100644
index 0000000..2ba719c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.200.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 test;
+SET `compiler.column.filter` "true";
+
+
+
+SELECT VALUE d
+FROM ColumnDataset d
+WHERE d.a NOT IN ["1", "2", "3"]
+ORDER BY d.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.201.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.201.query.sqlpp
new file mode 100644
index 0000000..0b023aa
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/not-in/not-in.201.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 test;
+SET `compiler.column.filter` "true";
+
+
+EXPLAIN
+SELECT VALUE d
+FROM ColumnDataset d
+WHERE d.a NOT IN ["1", "2", "3"]
+ORDER BY d.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.000.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.000.ddl.sqlpp
new file mode 100644
index 0000000..6036e79
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.000.ddl.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * 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.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE TestType AS {
+};
+
+
+CREATE EXTERNAL DATASET Department(TestType) USING %adapter% (
+ %template%,
+ ("container"="playground"),
+ ("definition"="external-filter/department/{department:string}"),
+ ("embed-filter-values" = "false"),
+ ("format"="json")
+);
+
+CREATE EXTERNAL DATASET LastName(TestType) USING %adapter% (
+ %template%,
+ ("container"="playground"),
+ ("definition"="external-filter/last-name/{name.last:string}"),
+ ("embed-filter-values" = "false"),
+ ("format"="json")
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.010.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.010.query.sqlpp
new file mode 100644
index 0000000..45ee543
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.010.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.
+ */
+
+USE test;
+
+
+SELECT VALUE d
+FROM Department d
+WHERE lowercase(d.department) NOT IN ["engineering"]
+ORDER BY d.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.011.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.011.query.sqlpp
new file mode 100644
index 0000000..2923faa
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.011.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.
+ */
+
+USE test;
+
+EXPLAIN
+SELECT VALUE d
+FROM Department d
+WHERE lowercase(d.department) NOT IN ["engineering"]
+ORDER BY d.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.110.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.110.query.sqlpp
new file mode 100644
index 0000000..5c81df4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.110.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.
+ */
+
+USE test;
+
+
+SELECT VALUE d
+FROM Department d
+WHERE lowercase(d.department) NOT IN ["engineering", "accounting"]
+ORDER BY d.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.111.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.111.query.sqlpp
new file mode 100644
index 0000000..b3a7d44
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/dynamic-prefixes/not-in/not-in.111.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.
+ */
+
+USE test;
+
+EXPLAIN
+SELECT VALUE d
+FROM Department d
+WHERE lowercase(d.department) NOT IN ["engineering", "accounting"]
+ORDER BY d.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.003.regexadm b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.003.regexadm
new file mode 100644
index 0000000..81882d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.003.regexadm
@@ -0,0 +1 @@
+\Q{"keys":"id","type":{"type":"org.apache.asterix.om.types.ARecordType","name":"ColumnType","open":true,"fields":[{"id":{"type":"AInt64"}}]},"splits":[\E.*\Q]}\E
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.005.regexadm b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.005.regexadm
new file mode 100644
index 0000000..81882d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.005.regexadm
@@ -0,0 +1 @@
+\Q{"keys":"id","type":{"type":"org.apache.asterix.om.types.ARecordType","name":"ColumnType","open":true,"fields":[{"id":{"type":"AInt64"}}]},"splits":[\E.*\Q]}\E
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.100.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.100.adm
new file mode 100644
index 0000000..5b49cad
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.100.adm
@@ -0,0 +1,5 @@
+{ "id": 2, "a": "2", "array": [ 40, 50, 60 ] }
+{ "id": 3, "a": "3", "array": [ 70, 80, 90 ] }
+{ "id": 4, "a": "4", "array": [ 100, 200, 300 ] }
+{ "id": 5, "a": "5", "array": [ 400, 500, 600 ] }
+{ "id": 6, "a": "6", "array": [ 700, 800, 900 ] }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.101.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.101.plan
new file mode 100644
index 0000000..043246e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.101.plan
@@ -0,0 +1,37 @@
+distribute result [$$d] [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|
+ project ([$$d]) [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]
+ -- SORT_MERGE_EXCHANGE [$$20(ASC) ] |PARTITIONED|
+ order (ASC, $$20) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STABLE_SORT [$$20(ASC)] |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$d, $$20]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ select ($$18) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |PARTITIONED|
+ project ([$$20, $$d, $$18]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ subplan {
+ aggregate [$$18] <- [empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (not(if-missing-or-null(neq($$21, "1"), false))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- SUBPLAN |PARTITIONED|
+ assign [$$21] <- [$$d.getField("a")] [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 []<-[$$20, $$d] <- test.ColumnDataset [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/filter/not-in/not-in.200.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.200.adm
new file mode 100644
index 0000000..8f22331
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.200.adm
@@ -0,0 +1,3 @@
+{ "id": 4, "a": "4", "array": [ 100, 200, 300 ] }
+{ "id": 5, "a": "5", "array": [ 400, 500, 600 ] }
+{ "id": 6, "a": "6", "array": [ 700, 800, 900 ] }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.201.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.201.plan
new file mode 100644
index 0000000..bda6268
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/not-in/not-in.201.plan
@@ -0,0 +1,55 @@
+distribute result [$$d] [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|
+ project ([$$d]) [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]
+ -- SORT_MERGE_EXCHANGE [$$24(ASC) ] |PARTITIONED|
+ project ([$$d, $$24]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ select ($$18) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ group by ([$$24 := $$20]) decor ([$$d]) {
+ aggregate [$$18] <- [empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (not(is-missing($$23))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- PRE_CLUSTERED_GROUP_BY[$$20] |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$20) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STABLE_SORT [$$20(ASC)] |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- HASH_PARTITION_EXCHANGE [$$20] |PARTITIONED|
+ project ([$$d, $$23, $$20]) [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|
+ left outer join (not(if-missing-or-null(neq($$21, $#1), false))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_LOOP |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$21] <- [$$d.getField("a")] [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 []<-[$$20, $$d] <- test.ColumnDataset [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|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ assign [$$23] <- [true] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |UNPARTITIONED|
+ unnest $#1 <- scan-collection(array: [ "1", "2", "3" ]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |UNPARTITIONED|
+ empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.010.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.010.adm
new file mode 100644
index 0000000..f896ea3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.010.adm
@@ -0,0 +1,6 @@
+{ "id": 1, "department": "accounting", "name": { "first": "John", "last": "Smith" } }
+{ "id": 2, "department": "accounting", "name": { "first": "Mike", "last": "Jones" } }
+{ "id": 3, "department": "accounting", "name": { "first": "Alex", "last": "Miller" } }
+{ "id": 7, "department": "hr", "name": { "first": "James", "last": "Smith" } }
+{ "id": 8, "department": "hr", "name": { "first": "David", "last": "Jones" } }
+{ "id": 9, "department": "hr", "name": { "first": "Noah", "last": "Miller" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.011.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.011.plan
new file mode 100644
index 0000000..f5b9fd0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.011.plan
@@ -0,0 +1,37 @@
+distribute result [$$d] [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|
+ project ([$$d]) [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]
+ -- SORT_MERGE_EXCHANGE [$$22(ASC) ] |PARTITIONED|
+ order (ASC, $$22) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STABLE_SORT [$$22(ASC)] |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$d, $$22]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ select ($$19) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |PARTITIONED|
+ project ([$$d, $$22, $$19]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ subplan {
+ aggregate [$$19] <- [empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (not(if-missing-or-null(neq(lowercase($$21), "engineering"), false))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- SUBPLAN |PARTITIONED|
+ assign [$$22, $$21] <- [$$d.getField("id"), $$d.getField("department")] [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 []<-[$$d] <- test.Department [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/external-dataset/common/dynamic-prefixes/not-in/not-in.110.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.110.adm
new file mode 100644
index 0000000..296a908
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.110.adm
@@ -0,0 +1,3 @@
+{ "id": 7, "department": "hr", "name": { "first": "James", "last": "Smith" } }
+{ "id": 8, "department": "hr", "name": { "first": "David", "last": "Jones" } }
+{ "id": 9, "department": "hr", "name": { "first": "Noah", "last": "Miller" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.111.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.111.plan
new file mode 100644
index 0000000..9bf7d82
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/dynamic-prefixes/not-in/not-in.111.plan
@@ -0,0 +1,63 @@
+distribute result [$$d] [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|
+ project ([$$d]) [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]
+ -- SORT_MERGE_EXCHANGE [$$22(ASC) ] |PARTITIONED|
+ order (ASC, $$22) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STABLE_SORT [$$22(ASC)] |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$d, $$22]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ select ($$19) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |PARTITIONED|
+ project ([$$19, $$22, $$d]) [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|
+ group by ([$$25 := $$23]) decor ([$$22; $$d]) {
+ aggregate [$$19] <- [empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (not(is-missing($$24))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- PRE_CLUSTERED_GROUP_BY[$$23] |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$23) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STABLE_SORT [$$23(ASC)] |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- HASH_PARTITION_EXCHANGE [$$23] |PARTITIONED|
+ project ([$$d, $$22, $$24, $$23]) [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|
+ left outer join (not(if-missing-or-null(neq(lowercase($$21), $#1), false))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_LOOP |PARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ running-aggregate [$$23] <- [create-query-uid()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- RUNNING_AGGREGATE |PARTITIONED|
+ assign [$$22, $$21] <- [$$d.getField("id"), $$d.getField("department")] [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 []<-[$$d] <- test.Department [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|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ assign [$$24] <- [true] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |UNPARTITIONED|
+ unnest $#1 <- scan-collection(array: [ "engineering", "accounting" ]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |UNPARTITIONED|
+ empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in/not-in.101.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in/not-in.101.plan
new file mode 100644
index 0000000..951003e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in/not-in.101.plan
@@ -0,0 +1,86 @@
+distribute result [$$d] [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$d]) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$20) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- STABLE_SORT [$$20(ASC)] |PARTITIONED|
+ exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- RANGE_PARTITION_EXCHANGE [$$20(ASC)] |PARTITIONED|
+ forward: shared-variable = $$25 [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- FORWARD |PARTITIONED|
+ exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ replicate [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- REPLICATE |PARTITIONED|
+ exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$d, $$20]) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- STREAM_PROJECT |PARTITIONED|
+ select ($$18) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- STREAM_SELECT |PARTITIONED|
+ project ([$$20, $$d, $$18]) [cardinality: 6.0, op-cost: 0.0, total-cost: 2.1]
+ -- STREAM_PROJECT |PARTITIONED|
+ subplan {
+ aggregate [$$18] <- [empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (not(if-missing-or-null(neq($$21, "1"), false))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 6.0, op-cost: 0.0, total-cost: 2.1]
+ -- SUBPLAN |PARTITIONED|
+ assign [$$21] <- [$$d.getField("a")] [cardinality: 6.0, op-cost: 0.0, total-cost: 2.1]
+ -- ASSIGN |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 2.1]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$20, $$d] <- test.ColumnDataset [cardinality: 6.0, op-cost: 2.1, total-cost: 2.1]
+ -- 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: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ aggregate [$$25] <- [agg-range-map($$23, $$24)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |UNPARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ aggregate [$$23, $$24] <- [agg-local-sampling($$20), agg-null-writer($$20)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |PARTITIONED|
+ project ([$$20]) [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|
+ replicate [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- REPLICATE |PARTITIONED|
+ exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$d, $$20]) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- STREAM_PROJECT |PARTITIONED|
+ select ($$18) [cardinality: 2.1, op-cost: 0.0, total-cost: 2.1]
+ -- STREAM_SELECT |PARTITIONED|
+ project ([$$20, $$d, $$18]) [cardinality: 6.0, op-cost: 0.0, total-cost: 2.1]
+ -- STREAM_PROJECT |PARTITIONED|
+ subplan {
+ aggregate [$$18] <- [empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (not(if-missing-or-null(neq($$21, "1"), false))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 6.0, op-cost: 0.0, total-cost: 2.1]
+ -- SUBPLAN |PARTITIONED|
+ assign [$$21] <- [$$d.getField("a")] [cardinality: 6.0, op-cost: 0.0, total-cost: 2.1]
+ -- ASSIGN |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 2.1]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$20, $$d] <- test.ColumnDataset [cardinality: 6.0, op-cost: 2.1, total-cost: 2.1]
+ -- 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/filter/not-in/not-in.201.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in/not-in.201.plan
new file mode 100644
index 0000000..cc5b51c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in/not-in.201.plan
@@ -0,0 +1,130 @@
+distribute result [$$d] [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$d]) [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$24) [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- STABLE_SORT [$$24(ASC)] |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- RANGE_PARTITION_EXCHANGE [$$24(ASC)] |PARTITIONED|
+ forward: shared-variable = $$27 [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- FORWARD |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ replicate [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- REPLICATE |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$d, $$24]) [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ select ($$18) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ group by ([$$24 := $$20]) decor ([$$d]) {
+ aggregate [$$18] <- [empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (not(is-missing($$23))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- PRE_CLUSTERED_GROUP_BY[$$20] |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$20) [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- STABLE_SORT [$$20(ASC)] |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- HASH_PARTITION_EXCHANGE [$$20] |PARTITIONED|
+ project ([$$d, $$23, $$20]) [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ left outer join (not(if-missing-or-null(neq($$21, $#1), false))) [cardinality: 2.1, op-cost: 6.0, total-cost: 17.0]
+ -- NESTED_LOOP |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$21] <- [$$d.getField("a")] [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ASSIGN |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$20, $$d] <- test.ColumnDataset [cardinality: 6.0, op-cost: 6.0, total-cost: 6.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: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ assign [$$23] <- [true] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |UNPARTITIONED|
+ unnest $#1 <- scan-collection(array: [ "1", "2", "3" ]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |UNPARTITIONED|
+ empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |UNPARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ aggregate [$$27] <- [agg-range-map($$25, $$26)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |UNPARTITIONED|
+ exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ aggregate [$$25, $$26] <- [agg-local-sampling($$24), agg-null-writer($$24)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |PARTITIONED|
+ project ([$$24]) [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|
+ replicate [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- REPLICATE |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$d, $$24]) [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ select ($$18) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ group by ([$$24 := $$20]) decor ([$$d]) {
+ aggregate [$$18] <- [empty-stream()] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ select (not(is-missing($$23))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_SELECT |LOCAL|
+ nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- PRE_CLUSTERED_GROUP_BY[$$20] |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$20) [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- STABLE_SORT [$$20(ASC)] |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- HASH_PARTITION_EXCHANGE [$$20] |PARTITIONED|
+ project ([$$d, $$23, $$20]) [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ left outer join (not(if-missing-or-null(neq($$21, $#1), false))) [cardinality: 2.1, op-cost: 6.0, total-cost: 17.0]
+ -- NESTED_LOOP |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$21] <- [$$d.getField("a")] [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ASSIGN |PARTITIONED|
+ exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$20, $$d] <- test.ColumnDataset [cardinality: 6.0, op-cost: 6.0, total-cost: 6.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: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ assign [$$23] <- [true] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |UNPARTITIONED|
+ unnest $#1 <- scan-collection(array: [ "1", "2", "3" ]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- UNNEST |UNPARTITIONED|
+ empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_external_dataset_s3.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_external_dataset_s3.xml
index b22eed0..c248cc2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_external_dataset_s3.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_external_dataset_s3.xml
@@ -256,6 +256,12 @@
<output-dir compare="Text">embed-multiple-values</output-dir>
</compilation-unit>
</test-case>
+ <test-case FilePath="external-dataset/common/dynamic-prefixes">
+ <compilation-unit name="not-in">
+ <placeholder name="adapter" value="S3" />
+ <output-dir compare="Text">not-in</output-dir>
+ </compilation-unit>
+ </test-case>
<test-case FilePath="external-dataset/common/dynamic-prefixes/parquet">
<compilation-unit name="one-field">
<placeholder name="adapter" value="S3" />
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 6d83fb5..8179c33 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -16281,6 +16281,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="column">
+ <compilation-unit name="filter/not-in">
+ <output-dir compare="Text">filter/not-in</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="column">
<compilation-unit name="big-object">
<output-dir compare="Text">big-object</output-dir>
</compilation-unit>
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/17784
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings
Gerrit-Project: asterixdb
Gerrit-Branch: master
Gerrit-Change-Id: I3fd509897e4894c3c8ceeedd04695d07d53084f6
Gerrit-Change-Number: 17784
Gerrit-PatchSet: 1
Gerrit-Owner: Wail Alkowaileet <wa...@gmail.com>
Gerrit-MessageType: newchange