Repository navigation
A computed column comes back as a one-element list, and a projection of computed columns only returns every source field: SELECT 2 AS k FROM t #418
Description
Two defects in how a row is built from a search that projects computed columns (Elasticsearch script_fields). DuckDB 1.5.5 answers one plain value per SELECT item.
Measured through the gateway on Elasticsearch 8.18.3, on core main (7fe1d66, #412 merged) and on the set-operation work that follows it; both builds answer the same. Table t: 5 documents with id (r1 … r5), n (3, 5, -2, absent, 10), d, ts and other fields.
1. A computed value is a one-element list
| statement | core (the gateway's rows) | DuckDB 1.5.5 |
|---|---|---|
SELECT id, n + 1 AS v FROM t |
v = [4], [6], [-1], [null], [11] |
v INTEGER = 4, 6, -1, NULL, 11 |
SELECT id, 2 AS k FROM t |
k = [2] on every row |
k INTEGER = 2 |
SELECT id, CURRENT_DATE AS v FROM t |
v = [2026-10-06] on every row |
v DATE |
SELECT id, CAST(d AS DATE) AS v FROM t |
v = [2024-01-31], [2024-03-01], [2023-12-25], [null], [2024-02-29] |
v DATE |
SELECT id, n FROM t (control) |
plain values: 3, 5, -2, NULL, 10 | the same |
Elasticsearch returns every script_fields value as an array, and the row keeps the array. A client that infers a column's type from its values sees ARRAY. A plain column is not wrapped.
2. A projection of computed columns only returns the whole _source
| statement | core | DuckDB 1.5.5 |
|---|---|---|
SELECT 2 AS k FROM t |
k = [2] plus every field of the document's _source (b, d, g, n, id, ts, …) |
one column, k INTEGER = 2 |
SELECT n + 1 AS v FROM t |
v = [4], … plus every field of the document's _source |
one column, v INTEGER |
SELECT CAST(d AS DATE) AS k FROM t |
k = [2023-12-25], … plus every source field |
one column, k DATE |
SELECT CAST(ts AS VARCHAR) AS k FROM t |
k = [2023-12-25T23:59:59.999Z] (a timestamp object, not text) plus every source field |
one column, k VARCHAR = '2023-12-25 23:59:59.999' |
With at least one plain column (SELECT id, 2 AS k), only the selected fields come back. When every SELECT item is computed, the request has no source filter (sourceFiltering(Nil) applies none), so the whole _source comes back and each field becomes a column.
In a set operation
The same two defects reach a UNION ALL branch, so one column mixes plain values with lists, and the computed branch's rows carry the source fields:
| statement | core | DuckDB 1.5.5 |
|---|---|---|
SELECT 2 AS k FROM t UNION ALL SELECT n AS k FROM t |
n's rows: -2, 10, 3, 5, NULL; the literal's rows: [2] plus every source field |
k INTEGER: -2, 10, 3, 5, NULL, 2, 2, 2, 2, 2 |
SELECT CURRENT_DATE AS k FROM t UNION ALL SELECT d AS k FROM t |
d's rows: dates; CURRENT_DATE's rows: [2026-10-06] plus the source columns |
k DATE |
SELECT NULL AS k FROM t UNION ALL SELECT n AS k FROM t |
the NULL branch's rows: [null] plus the source columns |
k INTEGER, NULL on those rows |
Expected
One column per SELECT item, holding the plain value, as DuckDB answers.
Fix direction
- Unwrap a one-element
script_fieldsarray when the row is built, on every route (row search,_msearch, scroll, the per-leg route). - Send
_source: falsewhen the projection has no plain column.
Evidence level
RUN through the gateway on Elasticsearch 8.18.3, on both builds, with the row values printed as returned (lists not unwrapped). DuckDB 1.5.5 answers measured.
Activity
fupelaqu commentedon Oct 7, 2026
Effect on arrow's set operations (measured)
Measured through softclient4es-arrow
main(264eac3, ADBC, in process) with core0.24.0-SNAPSHOT(#412), on Elasticsearch 8.18.3. Every set operation that arrow plans fails when one branch is a computed item, whatever the types:SELECT n AS k FROM t UNION SELECT 2 AS k FROM tBinder Error: Set operations can only apply to expressions with the same number of result columnsINTEGERSELECT d AS k FROM t UNION SELECT CURRENT_DATE AS k FROM tDATESELECT n AS k FROM t UNION SELECT CAST(n AS BIGINT) AS k FROM tBIGINTSELECT n AS k FROM t UNION SELECT NULL AS k FROM tINTEGERSELECT n AS k FROM t UNION SELECT '1' AS k FROM tVARCHARThe same holds for
INTERSECT,EXCEPT, and aUNION ALLwhose branch is a derived table or a CTE. The computed branch reaches DuckDB with 18 columns: the one-element list plus every source field this issue describes, beside the other branch's single column.Once this issue is fixed, those set operations can run, and arrow's set-operation measurements can be re-run.
fupelaqu commentedon Oct 7, 2026
The one-element arrays also mix classes in a
UNION ALLcolumn, beside a plain number column.Measured on Elasticsearch 8.18.3, core
main(7fe1d66), on the three ways aUNION ALLruns: the gateway run, one search per branch, and aLIMITed search. All three answer identically.The column is reported as
INT. Its rows, as value:class:So one column holds:
Integervalues;Double;NaN(forSQRT(-2));The same happens with
POWER(n, 2),LOG(x),EXP(n)andROUND(n)as the second branch.ROUND(x, 1)also loses its decimal place there, which is #416.The set-operation work in progress does not convert these columns, because a function branch's number width is not known. Once the arrays are fixed here, each such column should hold one value per row, of one class. These statements will be measured again then.
fupelaqu commentedon Oct 7, 2026
A computed date shows the same one-element arrays and the same mix of classes.
Measured on Elasticsearch 8.18.3, on one search per branch and on a
LIMITed search, with the set-operation work in progress applied:Rows, as value:class:
With
tsinstead ofd, the same column also holds the timestamps as they were stored: aZonedDateTime, aLong(epoch milliseconds) and aLocalDate(a date-only value).DATE_TRUNCoverCURRENT_TIMESTAMPis typed as a temporal of unknown kind. Like the numeric functions in the comment above, the set-operation conversion does not reach such a column. DuckDB answers aTIMESTAMPcolumn here. Once the arrays are fixed, this column should hold one value per row, of one class. It will be measured again with the numeric statements.