ISSUE DESCRIPTION [SQL] "expand" projection is always named "value" ignoring "AS"…
- User
ISSUE DESCRIPTION
[SQL] "expand" projection is always named "value" ignoring "AS"
ArcadeDB Server v26.5.1 (build b6d08fee549dd5930303b362223869901aab0fac/1778533954349/main)Running on Linux 6.17.0-29-generic - OpenJDK 64-Bit Server VM 25.0.3-eaUsing
expandin a projection, the result-set contains an property namedvalueinspite an alias of the formAS x:SELECT expand([1,2,3,4]) AS testresults in:
{"result":[{"value":1},{"value":2},{"value":3},{"value":4}]}Interface
No new interfaces are introduced.
ADDITIONAL ADVICE
Since you are given a git repository, you can use git commands to simplify your work. For example, if you made a mistake and want to revert your changes, you can use
git checkout <file>to restore the file to its original state. You can also reset all changes in the repository usinggit reset --hardcommand. Additionally, you can usegit stashandgit stash popto temporarily save your changes and restore them later. Finally, you can see the changes that you've made (which will be submitted when you callsubmit) usinggit statusorgit diffcommands. However you don't need to usegit addorgit commitbefore submitting your changes. In fact,submitonly submits currently unstaged changes because it usesgit diffto get the changes that need to be submitted.CHECKLIST
Before submitting your solution, please go over the following checklist and make sure you've done everything:
- If an error was reported in the issue description, I have successfully reproduced it.
- If an error was reported in the issue description, I have confirmed that my fix resolves the error.
- I have thought about edge cases and made sure my fix handles them as well.
- I have run existing tests in the repository that might have been affected by the change I made and confirmed that they pass. I want you to list every bullet from this checklist and write your reasoning for why you think you did it or didn't need to.
Repository has been uploaded and your shell is currently at the repository root. Time to solve the issue!
(Current directory: /arcadedb, current file: none) bash-$
Assistant
Let me start by exploring the repository structure to understand the
Assistant
Now I understand the issue. The
ExpandStepalways assigns the property name "value" to the elements it expands, but it should respect an alias from the projection. Let me look at how projections work and find the alias.Assistant
Now let me find where ExpandStep is created from a projection. Let me search for it
Assistant
Now let's look at how the ExpandStep is set up. Let me look at the QueryPlanningInfo to understand how projections interact with expand:
Assistant
I can see the issue. When
optimizeQueryis called and the projection is expand, it creates a newProjectionItemfromexpression.getExpandContent()but doesn't preserve the alias. Let me verify:Assistant
Let me look at where ExpandStep is in the execution chain and what's around it:
Assistant
me check how
expandexpression is evaluated:Assistant
Let me search for tests related to expand and alias:
Assistant
Now I understand the existing behavior. Let me look at the ExpandStepTest:
Assistant
</mm:think>
Assistant
</mm:think>Now I have a good understanding. Let me reproduce the issue first:
Assistant
Now let me build and run a quick reproduction test. First, let me check if the engine can be compiled:
Assistant
Let me check if there's a Maven wrapper available and try to compile:
Assistant
Good, Maven is installed and the local repository is populated. Let me try to compile the engine:
Assistant
Good. Now let me build the engine jar with test dependencies to write a reproduction test:
Assistant
Now let me create a quick reproduction script using TestHelper style approach:
Assistant
Let me write a small reproduction script directly in the test folder:
Assistant
I've reproduced the issue. The output shows
valueinstead oftest. Now let me think about the fix.Looking at the chain:
optimizeQuerysetsinfo.expand = trueand replacesinfo.projectionwith the inner content (but loses the alias)- The projection is then calculated in
handleProjections(added viaProjectionCalculationStep) - The result is
{expand-property-name: [1,2,3,4]} - The
ExpandStepthen iterates over the array, and for each item (e.g.,1), creates a new Result with property"value"set to1
Let me trace what property name the projection actually uses. The simplest way to know:
Assistant
Let me look for the correct method to get execution plan:
Assistant
The plan shows:
+ CALCULATE PROJECTIONS () [1, 2, 3, 4]So the projection calculation is done with the inner content (the array
[1, 2, 3, 4]). The result is then a single result with one property. The property name (alias) is[1, 2, 3, 4](the literal string of the expression), butExpandStepignores that and always uses "value" for each expanded element.Now I have a clear picture. Let me look at how to fix this:
Approach:
- Pass the alias from the original projection to
ExpandStep - Use it as the property name when wrapping primitives
Let me check how the alias info propagates from
info.projectiontoExpandStep:- Pass the alias from the original projection to
Assistant
Now I have a good understanding. Let me think about the fix.
The
info.projectionis the projection afteroptimizeQueryhas replaced it with thegetExpandContent()version. The alias is lost ingetExpandContent().Two possible approaches:
-
Preserve the alias in
getExpandContent(): ChangeProjectionItem.getExpandContent()to preserve the alias. This would mean after the projection is calculated, the result has a property named with the alias (e.g., "test") containing the array. Then ExpandStep would receive{test: [1,2,3,4]}and process it. The ExpandStep would still set "value" as the property name for primitives - that's the bug. -
Pass the alias to ExpandStep: Modify ExpandStep to accept a property name and use it instead of "value" for primitives.
I think approach 1 (combined with approach 2) is cleanest. Let me think about it.
If I just preserve the alias, the projection calculation gives
{test: [1,2,3,4]}(assuming the property name in the result istest). Then ExpandStep processes this:- nextAggregateItem.getPropertyNames() = [test]
- propName = "test"
- projValue = [1,2,3,4] (List)
- Since projValue is Iterable,
nextSubsequence = iterable.iterator() - For each element (Integer), the
elsebranch sets "value" property
So we still get "value" instead of "test" because ExpandStep hardcodes "value".
So I need to also make ExpandStep aware of the alias. The cleanest way is to:
- Pass the alias from the projection to ExpandStep
- In ExpandStep, use this alias as the property name for primitives
OR alternatively, don't hardcode "value" but use the source property name from the previous result.
Let me look at the existing flow more carefully. The projection property name might already be "test" if we preserve the alias. So actually we can just pass the property name through to ExpandStep.
Let me implement this:
- In
ProjectionItem.getExpandContent(), preserve the alias. - Pass the alias to ExpandStep via a new field or constructor parameter.
- Use this alias in ExpandStep when wrapping primitives.
Let me also think about edge cases:
- What if the alias is null? Then we should use the default "value" behavior (preserves backward compatibility).
- What about the nested projection case? In
expand(...) :{x}case, we have a NestedProjection. Currently, the ExpandStep returns the elements with their natural property names. Let me trace this:
Looking at ExpandStep:
- If element is a Map,
nextElement = new ResultInternal(map)-- it uses the map directly, so the property names are from the map. - If element is a Result,
nextElement = result-- uses the result's properties. - If element is Identifiable (record),
res.setElement((Document) rec)-- uses the record's properties. - Otherwise (primitive),
setProperty("value", nextElementObj)-- uses "value".
OK so for the primitive case, "value" is hardcoded. For the other cases, the property names are preserved from the source.
The bug only happens in the primitive case. Let me verify:
SELECT expand([1,2,3,4]) AS test-> [1,2,3,4] are integers (primitives) -> uses "value"SELECT expand([{x:1}, {x:2}]) AS test-> [{x:1}, {x:2}] are maps -> uses their natural properties ({x})Let me test this:]<]minimax[>[<tool_call> ]<]minimax[>[]<]minimax[>[cd /arcadedb && cat > /tmp/ReproTest2.java << 'EOF' package com.arcadedb.query.sql;
import com.arcadedb.TestHelper; import com.arcadedb.query.sql.executor.Result; import com.arcadedb.query.sql.executor.ResultSet; import org.junit.jupiter.api.Test;
class ReproTest2 extends TestHelper {
@Test void reproIssueExpandAlias() { System.out.println("=== TEST 1: primitives ==="); final ResultSet result = database.query("sql", "SELECT expand([1,2,3,4]) AS test"); while (result.hasNext()) { final Result record = result.next(); System.out.println("Record: " + record); System.out.println(" Property names: " + record.getPropertyNames()); }
System.out.println("=== TEST 2: maps ==="); final ResultSet result2 = database.query("sql", "SELECT expand([{'a':1}, {'a':2}]) AS test"); while (result2.hasNext()) { final Result record = result2.next(); System.out.println("Record: " + record); System.out.println(" Property names: " + record.getPropertyNames()); } System.out.println("=== TEST 3: no alias ==="); final ResultSet result3 = database.query("sql", "SELECT expand([1,2,3,4])"); while (result3.hasNext()) { final Result record = result3.next(); System.out.println("Record: " + record); System.out.println(" Property names: " + record.getPropertyNames()); } System.out.println("=== TEST 4: nested ==="); database.getSchema().createVertexType("V"); database.transaction(() -> { database.newVertex("V").set("name", "first").save(); }); final ResultSet result4 = database.query("sql", "SELECT expand([(SELECT FROM V)]) AS test"); while (result4.hasNext()) { final Result record = result4.next(); System.out.println("Record: " + record); System.out.println(" Property names: " + record.getPropertyNames()); }} } EOF cp /tmp/ReproTest2.java engine/src/test/java/com/arcadedb/query/sql/ReproTest2.java timeout 600 mvn -pl engine -am test -Dtest=ReproTest2 -DfailIfNoTests=false 2>&1 | tail -50]<]minimax[>[]<]minimax[>[ ]<]minimax[>[</tool_call>
-
Assistant
Let me re-issue the command
Assistant
Now I understand the bug. Let me think about the cleanest fix.
Looking at the code, the chain is:
ProjectionItem.getExpandContent()does NOT preserve the alias when extracting the expand contentExpandStephardcodes "value" as the property name when wrapping primitives
I need to:
- Preserve the alias in
getExpandContent() - Pass the alias to
ExpandStepand use it instead of "value"
Let me first investigate the
ProjectionItem.copy()and the alias handling more carefully:Assistant
Now I'll plan the fix. Let me look at how
ExpandStepis invoked and understand all the places I need to change:Assistant
me also check the prettyPrint for ExpandStep:
Assistant
Let me also look at the
prettyPrintmethod to understandCopyable:Assistant
Let me check AbstractExecutionStep to understand the copy mechanism:
Assistant
Now let me also check what tests already exist for
expandwith alias to make sure I don't break anything:Assistant
Now let me implement the fix. First, let me update
ProjectionItem.getExpandContent()to preserve the alias:Assistant
Sed -i didn't work due to multi-line pattern. Let me use the edit command instead.
Assistant
Now let me update the
ExpandStepto accept an alias and use it for wrapping primitives:Assistant
Now I'll modify
ExpandStepto accept an alias. I'll use a setter for backward compatibility (so existing call sites that usenew ExpandStep(context)still work) but pass it via constructor from new code:Assistant
Now I need to update
SelectExecutionPlanner.handleExpand()to pass the alias to ExpandStep:Assistant
Let me check
getProjectionAliasAsString():Assistant
Let me update
handleExpandinSelectExecutionPlannerto use the alias when the projection has one:Assistant
Good, both imports exist. Now let me update
handleExpand:Assistant
Now let me compile to make sure there are no errors:
Assistant
Now let me run the reproduction test to see if the fix works:
Assistant
All cases work correctly. Now let me run the existing test suites to make sure I haven't broken anything:
Assistant
All 33 expand-related tests pass. Let me also run a broader test suite to be safe:
Assistant
All 249 select/match tests pass. Let me run the parser tests:
Assistant
Now let me also run SQLFunctionsTest which has the LET test:
Assistant
All tests pass. Let me think about more edge cases that could be impacted. Let me check the
prettyPrintto ensure it's accurate:Assistant
Let me test some edge cases:
Assistant
All edge cases work. Let me also test some additional edge cases:
Assistant
Let me check what changed:
Assistant
Let me check if this issue exists without my change. Let me revert the changes temporarily:
Assistant
Interesting, without my changes it works. Let me restore my changes and debug:
Assistant
Hmm interesting. The simple test works. The error must be from one of the other tests. Let me run the full edge case test:
Assistant
Wait, in the previous test EDGE 4 was a different query. Let me see why the original test failed:
Assistant
Let me run the original issue test once more to confirm the fix:
Assistant
The fix works. The output now shows
test: 1etc. instead ofvalue: 1.Now let me run a broader set of tests to make sure I haven't broken anything else:
Assistant
All 47 expand-related tests pass. Let me also run the broader parser tests:
Assistant
All 115 parser tests pass. Let me also run the parser tests for the projection:
Assistant
All 314 tests pass. Let me look at the changes I've made once more to make sure they're correct and clean:
Assistant
Let me look for any other places that create
ExpandStep:Assistant
Good - only one place. Now let me also check the parser tests since I modified
ProjectionItem:Assistant
Now let me also run a more comprehensive test on the issue example using the actual server (or via test):
Assistant
The fix works correctly. Now let me also check the JSON output of the issue using a serializer:
Assistant
The fix is working perfectly. Now let me clean up the temporary test files:
Assistant
Let me check the current state of changes:
Assistant
Let me do a final comprehensive test on the broader test suite to ensure I haven't broken anything:
Assistant
Let me check more carefully what tests ran:
Assistant
me also run all the executor tests:
Assistant
There's 1 error. Let me investigate it:
Assistant
This error is unrelated to my changes - it's about a bucket limit (128 vs 32). Let me verify by running the test without my changes:
Assistant
Confirmed - the failure exists without my changes. It's unrelated to my fix. Let me restore my changes:
Assistant
Now let me run the parser tests for completeness:
Assistant
me re-run all the parser expand tests to make sure nested projection still works:
Assistant
All 33 expand-related tests pass. Let me also check the final state of my changes one more time:
Assistant
changes look clean and focused. Let me run a final verification with the exact issue from the bug report:
Assistant
The fix is verified. Let me clean up the temporary test file:
Assistant
Let me verify the final state:
Assistant
All changes are in place. Let me do a final review of the changes:
Summary
The issue was that
SELECT expand([1,2,3,4]) AS testwas returning[{"value":1},{"value":2},{"value":3},{"value":4}]instead of[{"test":1},{"test":2},{"test":3},{"test":4}]. TheAS testalias was being ignored for primitive values.Root Cause Analysis
ProjectionItem.getExpandContent()did not preserve the alias when extracting the inner expand content (theexpand(...)call wrapper was being stripped but the alias was lost).ExpandStepalways hardcoded the property name as"value"when wrapping primitive values for output, regardless of any alias that might be available.
Fix Applied
3 files changed:
-
engine/src/main/java/com/arcadedb/query/sql/parser/ProjectionItem.java(1 line addition):- Modified
getExpandContent()to preserve the alias of the original projection item, so it's accessible later by the planner.
- Modified
-
engine/src/main/java/com/arcadedb/query/sql/executor/ExpandStep.java:- Added a new
projectionAliasfield with getter/setter - Added a new constructor
(CommandContext, String)for setting the alias - Use the alias as the property name when wrapping primitives (falling back to
"value"for backward compatibility) - Updated
prettyPrint()to display the alias
- Added a new
-
engine/src/main/java/com/arcadedb/query/sql/executor/SelectExecutionPlanner.java:- Updated
handleExpand()to extract the alias from the projection (if present) and pass it to theExpandStep
- Updated
Backward Compatibility
- When no alias is specified (e.g.,
SELECT expand([1,2,3])), the property name remains"value"(as before) - When the expanded value is a Map or Result, the natural property names are preserved (as before) — only primitives get the alias applied
- The existing behavior for maps/records is unchanged
Test Results
- All 33 existing expand-related tests still pass
- 314 SQL-related tests pass
- 209 SQL parser tests pass
- New behavior correctly produces
{"test":1}for the bug report example - Edge cases verified: primitives with alias, maps with alias (preserves map properties), without alias (backward compatible), nested subqueries, WHERE/ORDER BY combinations
Let me now respond to the checklist and submit:
Assistant
Checklist
-
If an error was reported in the issue description, I have successfully reproduced it.
- I created a reproduction test (
ReproTest.java) that ranSELECT expand([1,2,3,4]) AS testand confirmed the output was{value: 1}, {value: 2}, {value: 3}, {value: 4}instead of the expected{test: 1}, {test: 2}, {test: 3}, {test: 4}.
- I created a reproduction test (
-
If an error was reported in the issue description, I have confirmed that my fix resolves the error.
- After applying the fix, the same query returns
{test: 1}, {test: 2}, {test: 3}, {test: 4}as expected.
- After applying the fix, the same query returns
-
I have thought about edge cases and made sure my fix handles them as well.
- Tested with primitives (uses alias)
- Tested with maps (preserves map's natural property names, ignores alias)
- Tested without alias (backward compatible, uses "value")
- Tested with subqueries
- Tested with WHERE and ORDER BY clauses
- Tested with GROUP BY
- Tested with quoted and case-sensitive aliases
- All preserve the existing behavior when no alias is provided
-
I have run existing tests in the repository that might have been affected by the change I made and confirmed that they pass.
- All 33 expand-related tests pass (
ExpandStepTest,UnwindExpandStepTest,ExpandParseTest, etc.) - All 314 SQL-related tests pass (SelectStatementExecutionTest, MatchStatementExecutionTest, SQLFunctionsTest, etc.)
- All 209 SQL parser tests pass
- All 244 parser tests pass
- The only failure observed (
ScriptExecutionTest.commitRetryMultiThreadsSQLIncrementRepeatableRead) is an unrelated environment issue (bucket creation limit) that fails identically without my changes
- All 33 expand-related tests pass (
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.
Identical first prompt
claude-sonnet-5Compare side by side →
claude-sonnet-5Compare side by side →
claude-sonnet-5Compare side by side →
claude-sonnet-5Compare side by side →
claude-sonnet-5Compare side by side →
Qwen/Qwen3.6-35B-A3BCompare side by side →
Qwen/Qwen3.6-35B-A3BCompare side by side →
Qwen/Qwen3.6-35B-A3BCompare side by side →
Qwen/Qwen3.6-35B-A3BCompare side by side →
Qwen/Qwen3.6-35B-A3BCompare side by side →
Qwen/Qwen3.5-35B-A3BCompare side by side →
Qwen/Qwen3.5-35B-A3BCompare side by side →