goldmedal commented on code in PR #15212:
URL: https://github.com/apache/datafusion/pull/15212#discussion_r1995622142
##########
datafusion/sql/tests/cases/plan_to_sql.rs:
##########
@@ -1746,3 +1749,153 @@ fn test_unparse_subquery_alias_with_table_pushdown() ->
Result<()> {
assert_eq!(sql.to_string(), expected);
Ok(())
}
+
+#[test]
+fn test_unparse_left_anti_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .project(vec![col("t1.d")])?
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftAnti,
+ vec![col("t1.c").eq(col("__correlated_sq_1.c"))],
+ )?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE NOT EXISTS (SELECT 1
FROM \"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"c\" =
\"__correlated_sq_1\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_left_semi_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
Review Comment:
I think the comment sould be `semi_join`, not `left anti join`.
##########
datafusion/sql/src/unparser/ast.rs:
##########
@@ -176,6 +177,41 @@ impl SelectBuilder {
self.lateral_views = value;
self
}
+
+ /// Replaces the selection with a new value.
+ ///
+ /// This function is used to replace a specific expression within the
selection.
+ /// Unlike the `selection` method which combines existing and new
selections with AND,
+ /// this method searches for and replaces occurrences of a specific
expression.
+ ///
+ /// This method is primarily used to modify LEFT MARK JOIN expressions.
+ /// When processing a LEFT MARK JOIN, we need to replace the placeholder
expression
+ /// with the actual join condition in the selection clause.
+ ///
+ /// # Arguments
+ ///
+ /// * `existing_expr` - The expression to replace
+ /// * `value` - The new expression to set as the selection
+ ///
+ /// # Returns
+ ///
+ /// A mutable reference to self for method chaining
Review Comment:
```suggestion
```
It's redundant.
##########
datafusion/sql/tests/cases/plan_to_sql.rs:
##########
@@ -1746,3 +1749,153 @@ fn test_unparse_subquery_alias_with_table_pushdown() ->
Result<()> {
assert_eq!(sql.to_string(), expected);
Ok(())
}
+
+#[test]
+fn test_unparse_left_anti_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .project(vec![col("t1.d")])?
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftAnti,
+ vec![col("t1.c").eq(col("__correlated_sq_1.c"))],
+ )?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE NOT EXISTS (SELECT 1
FROM \"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"c\" =
\"__correlated_sq_1\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_left_semi_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .project(vec![col("t1.d")])?
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftSemi,
+ vec![col("t1.c").eq(col("__correlated_sq_1.c"))],
+ )?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE EXISTS (SELECT 1 FROM
\"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"c\" =
\"__correlated_sq_1\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_left_mark_join() -> Result<()> {
+ // select t1.id from t1 where t1.id < 0 OR exists(SELECT t2.id FROM t2
WHERE t1.id = t2.id)
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+ // Filter: __correlated_sq_1.mark OR t1.d < Int32(0)
+ // Projection: t1.d
+ // LeftMark Join: Filter: t1.id = __correlated_sq_1.id
+ // TableScan: t1 projection=[c, d]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftMark,
+ vec![col("t1.id").eq(col("__correlated_sq_1.id"))],
+ )?
+ .project(vec![col("t1.d")])?
+ .filter(col("mark").or(col("t1.d").lt(lit(0))))?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE (EXISTS (SELECT 1 FROM
\"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"id\" =
\"__correlated_sq_1\".\"id\")) OR (\"t1\".\"d\" < 0))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_right_semi_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+ let left = table_scan(Some("t1"), &schema, Some(vec![0, 1]))?.build()?;
+ let right_table_scan = table_scan(Some("t2"), &schema, Some(vec![0,
1]))?.build()?;
+ let right = LogicalPlanBuilder::from(right_table_scan)
+ .project(vec![col("c"), col("d")])?
+ .build()?;
+ let plan = LogicalPlanBuilder::from(left)
+ .join(
+ right,
+ datafusion_expr::JoinType::RightSemi,
+ (
+ vec![Column::from_qualified_name("t1.c")],
+ vec![Column::from_qualified_name("t2.c")],
+ ),
+ None,
+ )?
+ .filter(col("t1.c").lt_eq(lit(1i64)))?
+ .build()?;
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t2\".\"c\", \"t2\".\"d\" FROM \"t1\" WHERE
(\"t1\".\"c\" <= 1) AND EXISTS (SELECT 1 FROM \"t1\" WHERE (\"t1\".\"c\" =
\"t2\".\"c\"))", sql.to_string());
Review Comment:
It's a similar problem. The SQL isn't valid.
##########
datafusion/sql/tests/cases/plan_to_sql.rs:
##########
@@ -1746,3 +1749,153 @@ fn test_unparse_subquery_alias_with_table_pushdown() ->
Result<()> {
assert_eq!(sql.to_string(), expected);
Ok(())
}
+
+#[test]
+fn test_unparse_left_anti_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .project(vec![col("t1.d")])?
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftAnti,
+ vec![col("t1.c").eq(col("__correlated_sq_1.c"))],
+ )?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE NOT EXISTS (SELECT 1
FROM \"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"c\" =
\"__correlated_sq_1\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_left_semi_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .project(vec![col("t1.d")])?
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftSemi,
+ vec![col("t1.c").eq(col("__correlated_sq_1.c"))],
+ )?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE EXISTS (SELECT 1 FROM
\"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"c\" =
\"__correlated_sq_1\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_left_mark_join() -> Result<()> {
+ // select t1.id from t1 where t1.id < 0 OR exists(SELECT t2.id FROM t2
WHERE t1.id = t2.id)
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
Review Comment:
I don't think the SQL matches this schema 🤔. Where is `t1.id`?
##########
datafusion/sql/tests/cases/plan_to_sql.rs:
##########
@@ -1746,3 +1749,153 @@ fn test_unparse_subquery_alias_with_table_pushdown() ->
Result<()> {
assert_eq!(sql.to_string(), expected);
Ok(())
}
+
+#[test]
+fn test_unparse_left_anti_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
Review Comment:
Could you add the original SQL in the comment? It would be nice for other
people to know where the plan comes from.
##########
datafusion/sql/tests/cases/plan_to_sql.rs:
##########
@@ -1746,3 +1749,153 @@ fn test_unparse_subquery_alias_with_table_pushdown() ->
Result<()> {
assert_eq!(sql.to_string(), expected);
Ok(())
}
+
+#[test]
+fn test_unparse_left_anti_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .project(vec![col("t1.d")])?
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftAnti,
+ vec![col("t1.c").eq(col("__correlated_sq_1.c"))],
+ )?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE NOT EXISTS (SELECT 1
FROM \"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"c\" =
\"__correlated_sq_1\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_left_semi_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .project(vec![col("t1.d")])?
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftSemi,
+ vec![col("t1.c").eq(col("__correlated_sq_1.c"))],
+ )?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE EXISTS (SELECT 1 FROM
\"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"c\" =
\"__correlated_sq_1\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_left_mark_join() -> Result<()> {
+ // select t1.id from t1 where t1.id < 0 OR exists(SELECT t2.id FROM t2
WHERE t1.id = t2.id)
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+ // Filter: __correlated_sq_1.mark OR t1.d < Int32(0)
+ // Projection: t1.d
+ // LeftMark Join: Filter: t1.id = __correlated_sq_1.id
+ // TableScan: t1 projection=[c, d]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftMark,
+ vec![col("t1.id").eq(col("__correlated_sq_1.id"))],
+ )?
+ .project(vec![col("t1.d")])?
+ .filter(col("mark").or(col("t1.d").lt(lit(0))))?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE (EXISTS (SELECT 1 FROM
\"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"id\" =
\"__correlated_sq_1\".\"id\")) OR (\"t1\".\"d\" < 0))", sql.to_string());
Review Comment:
If the schema of t1 is `t1(c int, b int)`, the SQL is invalid.
##########
datafusion/sql/tests/cases/plan_to_sql.rs:
##########
@@ -1746,3 +1749,153 @@ fn test_unparse_subquery_alias_with_table_pushdown() ->
Result<()> {
assert_eq!(sql.to_string(), expected);
Ok(())
}
+
+#[test]
+fn test_unparse_left_anti_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .project(vec![col("t1.d")])?
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftAnti,
+ vec![col("t1.c").eq(col("__correlated_sq_1.c"))],
+ )?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE NOT EXISTS (SELECT 1
FROM \"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"c\" =
\"__correlated_sq_1\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_left_semi_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+
+ // LeftAnti Join: t1.c = __correlated_sq_1.c
+ // TableScan: t1 projection=[c]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .project(vec![col("t1.d")])?
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftSemi,
+ vec![col("t1.c").eq(col("__correlated_sq_1.c"))],
+ )?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE EXISTS (SELECT 1 FROM
\"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"c\" =
\"__correlated_sq_1\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_left_mark_join() -> Result<()> {
+ // select t1.id from t1 where t1.id < 0 OR exists(SELECT t2.id FROM t2
WHERE t1.id = t2.id)
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+ // Filter: __correlated_sq_1.mark OR t1.d < Int32(0)
+ // Projection: t1.d
+ // LeftMark Join: Filter: t1.id = __correlated_sq_1.id
+ // TableScan: t1 projection=[c, d]
+ // SubqueryAlias: __correlated_sq_1
+ // TableScan: t2 projection=[c]
+ let table_scan1 = table_scan(Some("t1"), &schema, Some(vec![0,
1]))?.build()?;
+ let table_scan2 = table_scan(Some("t2"), &schema, Some(vec![0]))?.build()?;
+ let subquery = subquery_alias(table_scan2, "__correlated_sq_1")?;
+ let plan = LogicalPlanBuilder::from(table_scan1)
+ .join_on(
+ subquery,
+ datafusion_expr::JoinType::LeftMark,
+ vec![col("t1.id").eq(col("__correlated_sq_1.id"))],
+ )?
+ .project(vec![col("t1.d")])?
+ .filter(col("mark").or(col("t1.d").lt(lit(0))))?
+ .build()?;
+
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t1\".\"d\" FROM \"t1\" WHERE (EXISTS (SELECT 1 FROM
\"t2\" AS \"__correlated_sq_1\" WHERE (\"t1\".\"id\" =
\"__correlated_sq_1\".\"id\")) OR (\"t1\".\"d\" < 0))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_right_semi_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+ let left = table_scan(Some("t1"), &schema, Some(vec![0, 1]))?.build()?;
+ let right_table_scan = table_scan(Some("t2"), &schema, Some(vec![0,
1]))?.build()?;
+ let right = LogicalPlanBuilder::from(right_table_scan)
+ .project(vec![col("c"), col("d")])?
+ .build()?;
+ let plan = LogicalPlanBuilder::from(left)
+ .join(
+ right,
+ datafusion_expr::JoinType::RightSemi,
+ (
+ vec![Column::from_qualified_name("t1.c")],
+ vec![Column::from_qualified_name("t2.c")],
+ ),
+ None,
+ )?
+ .filter(col("t1.c").lt_eq(lit(1i64)))?
+ .build()?;
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t2\".\"c\", \"t2\".\"d\" FROM \"t1\" WHERE
(\"t1\".\"c\" <= 1) AND EXISTS (SELECT 1 FROM \"t1\" WHERE (\"t1\".\"c\" =
\"t2\".\"c\"))", sql.to_string());
+ Ok(())
+}
+
+#[test]
+fn test_unparse_right_anti_join() -> Result<()> {
+ let schema = Schema::new(vec![
+ Field::new("c", DataType::Int32, false),
+ Field::new("d", DataType::Int32, false),
+ ]);
+ let left = table_scan(Some("t1"), &schema, Some(vec![0, 1]))?.build()?;
+ let right_table_scan = table_scan(Some("t2"), &schema, Some(vec![0,
1]))?.build()?;
+ let right = LogicalPlanBuilder::from(right_table_scan)
+ .project(vec![col("c"), col("d")])?
+ .build()?;
+ let plan = LogicalPlanBuilder::from(left)
+ .join(
+ right,
+ datafusion_expr::JoinType::RightAnti,
+ (
+ vec![Column::from_qualified_name("t1.c")],
+ vec![Column::from_qualified_name("t2.c")],
+ ),
+ None,
+ )?
+ .filter(col("t1.c").lt_eq(lit(1i64)))?
+ .build()?;
+ let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
+ let sql = unparser.plan_to_sql(&plan)?;
+ assert_eq!("SELECT \"t2\".\"c\", \"t2\".\"d\" FROM \"t1\" WHERE
(\"t1\".\"c\" <= 1) AND NOT EXISTS (SELECT 1 FROM \"t1\" WHERE (\"t1\".\"c\" =
\"t2\".\"c\"))", sql.to_string());
Review Comment:
This result isn't valid for DataFusion and Postgres.
```
psql=# create table t1 (c int, d int);
CREATE TABLE
psql=# create table t2 (c int, d int);
CREATE TABLE
psql=# SELECT "t2"."c", "t2"."d" FROM "t1" WHERE ("t1"."c" <= 1) AND NOT
EXISTS (SELECT 1 FROM "t1" WHERE ("t1"."c" = "t2"."c"));
ERROR: missing FROM-clause entry for table "t2"
LINE 1: SELECT "t2"."c", "t2"."d" FROM "t1" WHERE ("t1"."c" <= 1) AN...
```
I guess the clause `FROM t1` is wrong.
--
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: github-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
