apache / apache/beam

SqlTransform#getSchema for "SELECT DISTINCT + JOIN" has invalid field names

Open
#19,656 0 comments 0 reactions 0 assignees View on GitHub
bug dsl P3 sql
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.