[jira] [Created] (BEAM-8896) WITH query AS + SELECT query JOIN other throws invalid type

["fdiazgon (Jira)" <ji...@apache.org>]

fdiazgon created BEAM-8896:
------------------------------

             Summary: WITH query AS + SELECT query JOIN other throws invalid type
                 Key: BEAM-8896
                 URL: https://issues.apache.org/jira/browse/BEAM-8896
             Project: Beam
          Issue Type: Bug
          Components: dsl-sql
    Affects Versions: 2.16.0
            Reporter: fdiazgon


The first one of the two following queries fails, despite queries being equivalent:
{code:java}
Pipeline p = Pipeline.create();

Schema schemaA =
    Schema.of(
        Schema.Field.of("id", Schema.FieldType.BYTES),
        Schema.Field.of("fA1", Schema.FieldType.STRING));

Schema schemaB =
    Schema.of(
        Schema.Field.of("id", Schema.FieldType.STRING),
        Schema.Field.of("fB1", Schema.FieldType.STRING));

PCollection<Row> inputA =
    p.apply(Create.of(ImmutableList.<Row>of()).withCoder(SchemaCoder.of(schemaA)));

PCollection<Row> inputB =
    p.apply(Create.of(ImmutableList.<Row>of()).withCoder(SchemaCoder.of(schemaB)));

// Fails
String query1 =
    "WITH query AS "
        + "( "
        + " SELECT id, fA1, fA1 AS fA1_2 "
        + " FROM tblA"
        + ") "
        + "SELECT fA1, fB1, fA1_2 "
        + "FROM query "
        + "JOIN tblB ON (TO_HEX(query.id) = tblB.id)";

// Ok
String query2 =
    "WITH query AS "
        + "( "
        + " SELECT fA1, fB1, fA1 AS fA1_2 "
        + " FROM tblA "
        + " JOIN tblB "
        + " ON (TO_HEX(tblA.id) = tblB.id) "
        + ")"
        + "SELECT fA1, fB1, fA1_2 "
        + "FROM query ";

Schema transform2 =
    PCollectionTuple.of("tblA", inputA)
        .and("tblB", inputB)
        .apply(SqlTransform.query(query2))
        .getSchema();
System.out.println(transform2);

Schema transform1 =
    PCollectionTuple.of("tblA", inputA)
        .and("tblB", inputB)
        .apply(SqlTransform.query(query1))
        .getSchema();
System.out.println(transform1);
{code}
 

The error is:
{noformat}
Exception in thread "main" java.lang.AssertionError: Field ordinal 2 is invalid for  type 'RecordType(VARBINARY id, VARCHAR fA1)'Exception in thread "main" java.lang.AssertionError: Field ordinal 2 is invalid for  type 'RecordType(VARBINARY id, VARCHAR fA1)' at org.apache.beam.repackaged.sql.org.apache.calcite.rex.RexBuilder.makeFieldAccess(RexBuilder.java:197){noformat}
 

If I change `schemaB.id` to `BYTES` (while also avoid using `TO_HEX`), both queries work fine. 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)