mihaibudiu commented on code in PR #4829:
URL: https://github.com/apache/calcite/pull/4829#discussion_r3722854328
##########
core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java:
##########
@@ -12711,6 +12758,138 @@ public Sql schema(CalciteAssert.SchemaSpec
schemaSpec) {
sql(sql).schema(CalciteAssert.SchemaSpec.JDBC_SCOTT).ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7439";>[CALCITE-7439]
+ * RelToSqlConverter emits ambiguous GROUP BY after LEFT JOIN USING with
+ * semi-join rewrite.</a>. */
+ @Test void
testPostgresqlRoundTripDistinctLeftJoinInSubqueryWithSemiJoinRules() {
+ final String generated =
+ postgresqlDistinctJoinSql("\"product_id\"", "LEFT", "USING
(\"product_id\")");
+ assertPostgresqlSqlValid(generated);
+ }
+
+ @Test void testDistinctRightJoinUsing() {
+ final String generated =
+ postgresqlDistinctJoinSql("\"product_id\"", "RIGHT", "USING
(\"product_id\")");
+ assertThat(
+ generated, isLinux("SELECT \"product1\".\"product_id\"\n"
+ + "FROM (SELECT \"$cor0\".\"product_id\", \"t1\".\"EXPR$0\" AS
\"mx\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"$cor0\",\n"
+ + "LATERAL (SELECT MAX(\"product_id\") AS \"EXPR$0\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_id\" = \"$cor0\".\"product_id\") AS \"t1\") AS
\"t2\"\n"
+ + "RIGHT JOIN \"foodmart\".\"product\" AS \"product1\""
+ + " ON \"t2\".\"product_id\" = \"product1\".\"product_id\"\n"
+ + "WHERE EXISTS (SELECT 1\n"
+ + "FROM (SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\") AS \"t3\"\n"
+ + "WHERE \"t2\".\"product_id\" = \"t3\".\"product_id\")\n"
+ + "GROUP BY \"product1\".\"product_id\""));
+ assertPostgresqlSqlValid(generated);
+ }
+
+ @Test void testDistinctFullJoinUsing() {
+ final String generated =
+ postgresqlDistinctJoinSql("\"product_id\"", "FULL", "USING
(\"product_id\")");
+ assertThat(generated,
+ isLinux("SELECT COALESCE(\"t2\".\"product_id\","
+ + " \"product1\".\"product_id\") AS \"product_id\"\n"
+ + "FROM (SELECT \"$cor0\".\"product_id\", \"t1\".\"EXPR$0\" AS
\"mx\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"$cor0\",\n"
+ + "LATERAL (SELECT MAX(\"product_id\") AS \"EXPR$0\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_id\" = \"$cor0\".\"product_id\") AS \"t1\") AS
\"t2\"\n"
+ + "FULL JOIN \"foodmart\".\"product\" AS \"product1\""
+ + " ON \"t2\".\"product_id\" = \"product1\".\"product_id\"\n"
+ + "WHERE EXISTS (SELECT 1\n"
+ + "FROM (SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\") AS \"t3\"\n"
+ + "WHERE \"t2\".\"product_id\" = \"t3\".\"product_id\")\n"
+ + "GROUP BY COALESCE(\"t2\".\"product_id\","
+ + " \"product1\".\"product_id\")"));
+ assertPostgresqlSqlValid(generated);
+ }
+
+ @Test void testDistinctFullJoinOnKeepsSelectedSide() {
+ final String condition = "ON pk.\"product_id\" = p2.\"product_id\"";
+ final String generated =
+ postgresqlDistinctJoinSql("pk.\"product_id\"", "FULL", condition);
+ assertThat(
+ generated, isLinux("SELECT \"t2\".\"product_id\"\n"
+ + "FROM (SELECT \"$cor0\".\"product_id\", \"t1\".\"EXPR$0\" AS
\"mx\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"$cor0\",\n"
+ + "LATERAL (SELECT MAX(\"product_id\") AS \"EXPR$0\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_id\" = \"$cor0\".\"product_id\") AS \"t1\") AS
\"t2\"\n"
+ + "FULL JOIN \"foodmart\".\"product\" AS \"product1\""
+ + " ON \"t2\".\"product_id\" = \"product1\".\"product_id\"\n"
+ + "WHERE EXISTS (SELECT 1\n"
+ + "FROM (SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\") AS \"t3\"\n"
+ + "WHERE \"t2\".\"product_id\" = \"t3\".\"product_id\")\n"
+ + "GROUP BY \"t2\".\"product_id\""));
+ assertPostgresqlSqlValid(generated);
+ }
+
+ @Test void testDistinctOverSemiJoinAndCorrelate() {
+ final Function<RelBuilder, RelNode> relFn = b -> {
+ final Holder<RexCorrelVariable> v = Holder.empty();
+ b.values(new String[]{"id"}, 1)
+ .variable(v::set);
+ b.values(new String[]{"id"}, 1)
+ .filter(
+ b.equals(b.field("id"),
+ b.getRexBuilder().makeFieldAccess(v.get(), 0)));
+ final RexNode correlateId = b.field(2, 0, 0);
+ b.correlate(JoinRelType.INNER, v.get().id, correlateId);
+ b.values(new String[]{"id"}, 1);
+ final RexNode leftId = b.field(2, 0, 0);
+ return b.join(JoinRelType.SEMI,
+ b.equals(leftId, b.field(2, 1, 0)))
+ .project(leftId)
+ .distinct()
+ .build();
+ };
+ relFn(relFn).ok("SELECT \"$cor0\".\"id\"\n"
Review Comment:
I like "assertPostgresSqlValid", this one is harder to tell by naked eye.
Why do you think this result is fine?
--
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]
