mxtymoshyk commented on issue #26911:
URL: https://github.com/apache/beam/issues/26911#issuecomment-6438717520
Checked this on current master (6c36587bc89) and it no longer reproduces.
I ran the exact DDL from the issue (TYPE text, format json, ARRAY<ROW<id,
author ROW<name, email>>>) with the CROSS JOIN UNNEST query, and SELECT * on
the same table. I also ran both queries against an in-memory PCollection with
the same schema. All four runs pass. The UNNEST query returns:
```
main | c1 | alice
main | c2 | bob
```
UnnestFn in BeamUnnestRel hasn't changed since this was filed, but Calcite
now plans the query differently. BeamUnnestRel gets a 5-field input (ref,
commits, id, name, email), which matches how UnnestFn flattens the nested
author row, so the field counts line up. My guess is the fix came with the move
to vendored Calcite 1.40.0 (31ce80fa6d2, July 2025). I didn't rerun on 2.47.0
to confirm the old failure, so I can't say which release fixed it.
<details>
<summary>Repro I used (SqlTransform with withDdlString,
DirectRunner)</summary>
Input file `input.jsonl`:
```json
{"ref":"main","commits":[{"id":"c1","author":{"name":"alice","email":"a@x"}},{"id":"c2","author":{"name":"bob","email":"b@x"}}]}
```
DDL and query:
```sql
CREATE EXTERNAL TABLE test_tmp(
`ref` VARCHAR,
`commits` ARRAY<ROW<`id` VARCHAR, `author` ROW<`name` VARCHAR, `email`
VARCHAR>>>
)
TYPE text
LOCATION '/tmp/.../input.jsonl'
TBLPROPERTIES '{"format":"json", "deadLetterFile":"/tmp/.../dead"}';
SELECT
test_tmp.`ref` AS branch_name,
commit_item.`id` AS commit_hash,
commit_item.`author`.`name` AS author_name
FROM test_tmp
CROSS JOIN UNNEST(test_tmp.commits) AS commit_item;
```
Plan on master:
```
BeamCalcRel(expr#0..4=[{inputs}], branch_name=[$t0], commit_hash=[$t2],
author_name=[$t3])
BeamUnnestRel(unnestIndices=[[1]])
BeamIOSourceRel(table=[[beam, test_tmp]])
```
</details>
Unless someone still hits this on a recent release, I think it can be closed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
