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 2026/09/24 04:42:21 UTC

[L] Change in asterixdb[master]: [ASTERIXDB-3892][COMP] Do not use a vector index under an UNNEST

From Ali Alsuliman <al...@gmail.com>:

Ali Alsuliman has uploaded this change for review. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/21879?usp=email )


Change subject: [ASTERIXDB-3892][COMP] Do not use a vector index under an UNNEST
......................................................................

[ASTERIXDB-3892][COMP] Do not use a vector index under an UNNEST

The LIMIT's count reaches the vector search as k, but it counts the rows
that leave the pipeline above the search while k counts the records
entering it. An UNNEST between the two breaks that correspondence: its
n = 0 case -- an empty array, an absent field, a non-array value --
drops a row after the search has capped its candidates, and what is lost
cannot be replaced by reaching further down the distance order. On the
index-only plan it is worse than short: the record is gone by then, so
the UNNEST's source collapses to MISSING and every candidate is dropped.

No vector index is offered for such a query, which is the answer already
given to a predicate that cannot be pushed into the search.

Ext-ref: MB-74179

Co-Authored-By: Claude Opus 5 <no...@anthropic.com>
Change-Id: Iea1f17c1a49d984421e59cee2c5830526309a880
---
M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceTopKAccessMethodRule.java
M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/VectorIndexAccessMethod.java
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/VectorQueries.xml
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.3.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.4.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.5.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.6.plans.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.7.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.8.plans.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.9.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.99.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.5.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.6.plan
A asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.7.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.8.plan
A asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.9.adm
18 files changed, 373 insertions(+), 1 deletion(-)



  git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/79/21879/1

diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceTopKAccessMethodRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceTopKAccessMethodRule.java
index ed04b1f..2e283c9 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceTopKAccessMethodRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceTopKAccessMethodRule.java
@@ -604,6 +604,16 @@
                     + "can install one; not offering a vector index", numSelectOps);
             return;
         }
+        // The LIMIT's count reaches the search as k, so everything in between must map one row to one row.
+        // An UNNEST does not: its n = 0 case (empty array, absent field, non-array value) drops the row
+        // after the search has capped its candidates, exactly as a predicate left above the search would.
+        for (AbstractLogicalOperator pipelineOp : subTree.getAssignsAndUnnests()) {
+            if (pipelineOp.getOperatorTag() != LogicalOperatorTag.ASSIGN) {
+                LOGGER.trace("chooseVectorIndex: {} between the ORDER and the scan does not preserve the row "
+                        + "count; not offering a vector index", pipelineOp.getOperatorTag());
+                return;
+            }
+        }
 
         // Iterate over candidate vector indexes
         Iterator<Map.Entry<Index, List<Pair<Integer, Integer>>>> indexIt =
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/VectorIndexAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/VectorIndexAccessMethod.java
index 44e315f..967724a 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/VectorIndexAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/VectorIndexAccessMethod.java
@@ -795,6 +795,10 @@
      * are fully repaired (the second ASSIGN references {@code $$237}, not {@code $$rec} directly, and would
      * otherwise dangle and fail type inference). The live-out check guarantees nothing above LIMIT depends
      * on these values, but the type system still walks them.
+     * <p>
+     * Nothing below the LIMIT may depend on them either; that is not checked here, it is what index
+     * selection refuses (see {@code IntroduceTopKAccessMethodRule.chooseVectorIndex}). Widening the plan
+     * shape it admits without widening that refusal re-opens ASTERIXDB-3892.
      */
     private static void neutralizeDanglingExpressions(ILogicalOperator root, List<LogicalVariable> oldRecordVars) {
         Set<LogicalVariable> dead = new HashSet<>(oldRecordVars);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/VectorQueries.xml b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/VectorQueries.xml
index babbaa7..e9350e2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/VectorQueries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/VectorQueries.xml
@@ -233,4 +233,9 @@
             <output-dir compare="Text">vtree-scan-choice</output-dir>
         </compilation-unit>
     </test-case>
-</test-group>
\ No newline at end of file
+    <test-case FilePath="vector">
+        <compilation-unit name="vtree-unnest-not-indexable">
+            <output-dir compare="Clean-JSON">vtree-unnest-not-indexable</output-dir>
+        </compilation-unit>
+    </test-case>
+</test-group>
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.1.ddl.sqlpp
new file mode 100644
index 0000000..2e5390b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.1.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.
+ */
+
+// An UNNEST between the vector search and the LIMIT drops a row whenever the array is empty, so the
+// candidate set the search capped can run out before the LIMIT is satisfied. No vector index is
+// offered for such a query (ASTERIXDB-3892); these queries must take the exact scan.
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+CREATE TYPE MovieType AS {
+  id: int,
+  genres: [string],
+  embedding: [double]
+};
+CREATE DATASET Movies(MovieType) PRIMARY KEY id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.2.update.sqlpp
new file mode 100644
index 0000000..98e3f26
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.2.update.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.
+ */
+
+USE test;
+INSERT INTO Movies ([
+  {"id": 1, "genres": [], "embedding": [0.0, 0.0, 0.0, 0.1]},
+  {"id": 2, "genres": [], "embedding": [0.0, 0.0, 0.0, 0.2]},
+  {"id": 3, "genres": [], "embedding": [0.0, 0.0, 0.0, 0.3]},
+  {"id": 4, "genres": [], "embedding": [0.0, 0.0, 0.0, 0.4]},
+  {"id": 5, "genres": [], "embedding": [0.0, 0.0, 0.0, 0.5]},
+  {"id": 6, "genres": [], "embedding": [0.0, 0.0, 0.0, 0.6]},
+  {"id": 7, "genres": [], "embedding": [0.0, 0.0, 0.0, 0.7]},
+  {"id": 8, "genres": [], "embedding": [0.0, 0.0, 0.0, 0.8]},
+  {"id": 9, "genres": [], "embedding": [0.0, 0.0, 0.0, 0.9]},
+  {"id": 10, "genres": [], "embedding": [0.0, 0.0, 0.0, 1.0]},
+  {"id": 11, "genres": [], "embedding": [0.0, 0.0, 0.0, 1.1]},
+  {"id": 12, "genres": [], "embedding": [0.0, 0.0, 0.0, 1.2]},
+  {"id": 13, "genres": [], "embedding": [0.0, 0.0, 0.0, 1.3]},
+  {"id": 14, "genres": [], "embedding": [0.0, 0.0, 0.0, 1.4]},
+  {"id": 15, "genres": [], "embedding": [0.0, 0.0, 0.0, 1.5]},
+  {"id": 16, "genres": ["drama"], "embedding": [0.0, 0.0, 0.0, 1.6]},
+  {"id": 17, "genres": ["drama"], "embedding": [0.0, 0.0, 0.0, 1.7]},
+  {"id": 18, "genres": ["drama"], "embedding": [0.0, 0.0, 0.0, 1.8]},
+  {"id": 19, "genres": ["drama"], "embedding": [0.0, 0.0, 0.0, 1.9]},
+  {"id": 20, "genres": ["drama"], "embedding": [0.0, 0.0, 0.0, 2.0]}
+]);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.3.ddl.sqlpp
new file mode 100644
index 0000000..09e5145
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.3.ddl.sqlpp
@@ -0,0 +1,21 @@
+/*
+ * 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.
+ */
+
+// A sample index is required before creating a vector index (quantization training scans it).
+ANALYZE DATASET test.Movies WITH {"sample-seed": 1000};
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.4.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.4.ddl.sqlpp
new file mode 100644
index 0000000..024a830
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.4.ddl.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.
+ */
+
+USE test;
+CREATE INDEX idx_emb
+  ON Movies(embedding VECTOR)
+  TYPE VTREE
+  WITH { "dimension": 4, "similarity": "euclidean", "num_clusters": 2, "train_list_fraction": 1.0, "seed": 42 };
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.5.query.sqlpp
new file mode 100644
index 0000000..0c26018
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.5.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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;
+// Only the primary key leaves the pipeline, so the projection is covered and this query used to
+// reach the index-only plan, where the UNNEST read a source neutralized to MISSING and every row
+// was dropped. The nearest 15 have no genres; the answer is 16, 17, 18.
+SELECT VALUE m.id
+FROM Movies m UNNEST m.genres g
+ORDER BY ann_distance(m.embedding, [0.0, 0.0, 0.0, 0.0], "euclidean", 1.0)
+LIMIT 3;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.6.plans.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.6.plans.sqlpp
new file mode 100644
index 0000000..ca295eb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.6.plans.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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.
+ */
+
+-- param compile-only:string=true
+-- param optimized-logical-plan:string=true
+// CBO cost estimates vary with the cluster shape (partition count / core count), which
+// differs between a local run and CI. Disable the CBO so the plan is stable everywhere.
+SET `compiler.cbo` "false";
+USE test;
+// The .5 query as a plan assertion: DATASOURCE_SCAN, no VECTOR_SEARCH.
+SELECT VALUE m.id
+FROM Movies m UNNEST m.genres g
+ORDER BY ann_distance(m.embedding, [0.0, 0.0, 0.0, 0.0], "euclidean", 1.0)
+LIMIT 3;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.7.query.sqlpp
new file mode 100644
index 0000000..bdf4fbd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.7.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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;
+// The unnested value is projected, so the record is fetched and its values are right -- but the
+// candidate set was sized in records while the LIMIT counts unnested rows, and the query returned
+// too few. Same answer as .5, one row per genre.
+SELECT m.id, g AS genre
+FROM Movies m UNNEST m.genres g
+ORDER BY ann_distance(m.embedding, [0.0, 0.0, 0.0, 0.0], "euclidean", 1.0)
+LIMIT 3;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.8.plans.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.8.plans.sqlpp
new file mode 100644
index 0000000..83d9002
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.8.plans.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.
+ */
+
+-- param compile-only:string=true
+-- param optimized-logical-plan:string=true
+// CBO cost estimates vary with the cluster shape (partition count / core count), which
+// differs between a local run and CI. Disable the CBO so the plan is stable everywhere.
+SET `compiler.cbo` "false";
+USE test;
+// The .7 query as a plan assertion: the lookup-and-rerank shape is refused too, not just
+// index-only, so this is a DATASOURCE_SCAN as well.
+SELECT m.id, g AS genre
+FROM Movies m UNNEST m.genres g
+ORDER BY ann_distance(m.embedding, [0.0, 0.0, 0.0, 0.0], "euclidean", 1.0)
+LIMIT 3;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.9.query.sqlpp
new file mode 100644
index 0000000..e7b8a83
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.9.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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;
+// Unnesting the embedding reaches the same failure by a shorter route: the unnested field is the
+// ANN argument, already part of the chain the index-only branch neutralizes. Each embedding has 4
+// elements, so the answer is the nearest id three times.
+SELECT VALUE m.id
+FROM Movies m UNNEST m.embedding e
+ORDER BY ann_distance(m.embedding, [0.0, 0.0, 0.0, 0.0], "euclidean", 1.0)
+LIMIT 3;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.99.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.99.ddl.sqlpp
new file mode 100644
index 0000000..20dc6fd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.99.ddl.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * 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;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.5.adm
new file mode 100644
index 0000000..558980f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.5.adm
@@ -0,0 +1,3 @@
+16
+17
+18
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.6.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.6.plan
new file mode 100644
index 0000000..4df721f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.6.plan
@@ -0,0 +1,32 @@
+distribute result [$$35]
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  exchange
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    limit 3
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      project ([$$35])
+      -- STREAM_PROJECT  |PARTITIONED|
+        exchange
+        -- SORT_MERGE_EXCHANGE [$$34(ASC) ]  |PARTITIONED|
+          limit 3
+          -- STREAM_LIMIT  |PARTITIONED|
+            exchange
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              order (topK: 3) (ASC, $$34)
+              -- STABLE_SORT [topK: 3] [$$34(ASC)]  |PARTITIONED|
+                exchange
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  assign [$$34] <- [euclidean-distance($$m.getField(2), array: [ 0.0, 0.0, 0.0, 0.0 ])] project: [$$35, $$34]
+                  -- ASSIGN  |PARTITIONED|
+                    unnest $$g <- scan-collection($$36) project: [$$35, $$m]
+                    -- UNNEST  |PARTITIONED|
+                      assign [$$36] <- [$$m.getField(1)]
+                      -- ASSIGN  |PARTITIONED|
+                        exchange
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          data-scan []<-[$$35, $$m] <- test.Movies
+                          -- DATASOURCE_SCAN  |PARTITIONED|
+                            exchange
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              empty-tuple-source
+                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.7.adm
new file mode 100644
index 0000000..cb9d381
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.7.adm
@@ -0,0 +1,3 @@
+{ "id": 16, "genre": "drama" }
+{ "id": 17, "genre": "drama" }
+{ "id": 18, "genre": "drama" }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.8.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.8.plan
new file mode 100644
index 0000000..969df55
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.8.plan
@@ -0,0 +1,32 @@
+distribute result [$$31]
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  exchange
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    limit 3
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      project ([$$31])
+      -- STREAM_PROJECT  |PARTITIONED|
+        exchange
+        -- SORT_MERGE_EXCHANGE [$$35(ASC) ]  |PARTITIONED|
+          limit 3
+          -- STREAM_LIMIT  |PARTITIONED|
+            exchange
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              order (topK: 3) (ASC, $$35)
+              -- STABLE_SORT [topK: 3] [$$35(ASC)]  |PARTITIONED|
+                exchange
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  assign [$$35, $$31] <- [euclidean-distance($$39, array: [ 0.0, 0.0, 0.0, 0.0 ]), {"id": $$36, "genre": $$g}] project: [$$31, $$35]
+                  -- ASSIGN  |PARTITIONED|
+                    unnest $$g <- scan-collection($$37) project: [$$36, $$39, $$g]
+                    -- UNNEST  |PARTITIONED|
+                      assign [$$39, $$37] <- [$$m.getField(2), $$m.getField(1)] project: [$$36, $$39, $$37]
+                      -- ASSIGN  |PARTITIONED|
+                        exchange
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          data-scan []<-[$$36, $$m] <- test.Movies
+                          -- DATASOURCE_SCAN  |PARTITIONED|
+                            exchange
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              empty-tuple-source
+                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.9.adm
new file mode 100644
index 0000000..e8183f0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/vector/vtree-unnest-not-indexable/vtree-unnest-not-indexable.9.adm
@@ -0,0 +1,3 @@
+1
+1
+1

-- 
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/21879?usp=email
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings?usp=email

Gerrit-MessageType: newchange
Gerrit-Project: asterixdb
Gerrit-Branch: master
Gerrit-Change-Id: Iea1f17c1a49d984421e59cee2c5830526309a880
Gerrit-Change-Number: 21879
Gerrit-PatchSet: 1
Gerrit-Owner: Ali Alsuliman <al...@gmail.com>