SqlTransform#getSchema for "SELECT DISTINCT + JOIN" has invalid field names
- Dominant language
- Java
- Stars
- 8.7k
- Forks
- 4.7k
- Avg merge
- 1d 20h
- Merged PRs (30d)
- 196
Description
Works in sqlline shell:
```
Welcome to Beam SQL 2.14.0-SNAPSHOT (based on sqlline version 1.4.0)
0: BeamSQL> CREATE EXTERNAL TABLE
s1 (id BIGINT) TYPE 'test';
No rows affected (0.507 seconds)
0: BeamSQL> CREATE EXTERNAL TABLE s2
(id BIGINT) TYPE 'test';
No rows affected (0.004 seconds)
0: BeamSQL> SELECT DISTINCT s1.id as lhs,
s2.id as rhs FROM s1 JOIN s2 USING (id);
+---------------------+---------------------+
| lhs
| rhs |
+---------------------+---------------------+
+---------------------+---------------------+
No
rows selected (2.568 seconds)
```
But doesn't work in the test:
```
Schema inputSchema = Schema.of(
Schema.Field.of("id", Schema.FieldType.INT32));
PCollection i1 = p.apply(Create.of(ImmutableList.of())
.withCoder(SchemaCoder.of(inputSchema)));
PCollection i2 = p.apply(Create.of(ImmutableList.of())
.withCoder(SchemaCoder.of(inputSchema)));
Schema outputSchema = PCollectionTuple
.of("i1", i1)
.and("i2", i2)
.apply(SqlTransform.query("SELECT
DISTINCT s1.id as lhs, s2.id as rhs FROM i1 JOIN i2 USING (id)"))
.getSchema();
assertEquals(ImmutableList.of("lhs",
"rhs"), outputSchema.getFieldNames());
```
Imported from Jira [BEAM-7609](https://issues.apache.org/jira/browse/BEAM-7609). Original Jira may contain additional context.
Reported by: kanterov.
Contributor guide
Research direction
Start at SqlTransform#getSchema and reproduce the shown PCollectionTuple query with the two input schemas. Check why SELECT DISTINCT with JOIN and USING produces invalid field names; done means outputSchema.getFieldNames() is ["lhs", "rhs"].
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- data-engineering
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100