[ https://issues.apache.org/jira/browse/BEAM-8896?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
fdiazgon updated BEAM-8896: --------------------------- Description: The first one of the three 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 "; // Ok String query3 = "WITH query AS " + "( " + " SELECT TO_HEX(id) AS id, fA1, fA1 AS fA1_2 " + " FROM tblA" + ") " + "SELECT fA1, fB1, fA1_2 " + "FROM query " + "JOIN tblB ON (query.id = tblB.id)"; Schema transform3 = PCollectionTuple.of("tblA", inputA) .and("tblB", inputB) .apply(SqlTransform.query(query3)) .getSchema(); System.out.println(transform3); 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`), all queries work fine. was: 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 "; // Ok String query3 = "WITH query AS " + "( " + " SELECT TO_HEX(id) AS id, fA1, fA1 AS fA1_2 " + " FROM tblA" + ") " + "SELECT fA1, fB1, fA1_2 " + "FROM query " + "JOIN tblB ON (query.id = tblB.id)"; Schema transform3 = PCollectionTuple.of("tblA", inputA) .and("tblB", inputB) .apply(SqlTransform.query(query3)) .getSchema(); System.out.println(transform3); 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`), all queries work fine. > 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 > Assignee: Andrew Pilloud > Priority: Major > > The first one of the three 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 "; > // Ok > String query3 = > "WITH query AS " > + "( " > + " SELECT TO_HEX(id) AS id, fA1, fA1 AS fA1_2 " > + " FROM tblA" > + ") " > + "SELECT fA1, fB1, fA1_2 " > + "FROM query " > + "JOIN tblB ON (query.id = tblB.id)"; > Schema transform3 = > PCollectionTuple.of("tblA", inputA) > .and("tblB", inputB) > .apply(SqlTransform.query(query3)) > .getSchema(); > System.out.println(transform3); > 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`), all > queries work fine. -- This message was sent by Atlassian Jira (v8.3.4#803005)