jonathanc-n commented on issue #17359:
URL: https://github.com/apache/datafusion/issues/17359#issuecomment-3239766288
I'm a bit confused, I believe the logical plan should have already pushed
down the filter into the subquery, removing the filter from the entire join.
When I ran a test in `datafusion/sql/tests/cases/plan_to_sql.rs`, it passed
fine:
```
#[test]
fn roundtrip_cross_join_right_filter_bug_dialect2() -> Result<(),
DataFusionError> {
// Repros the buggy output (outer WHERE duplicated), formatted like
dialect 2.
roundtrip_statement_with_dialect_helper!(
sql: "select j1.j1_id, j2.j2_string from j1, j2 where j2.j2_id = 0;",
parser_dialect: GenericDialect {},
unparser_dialect: UnparserDefaultDialect {},
expected: @"SELECT j1.j1_id, j2.j2_string FROM j1 CROSS JOIN j2
WHERE (j2.j2_id = 0)",
);
Ok(())
}
```
If you manually create the plan like so below the test will reproduce but
this shouldn't happen in the first place from the original query but I think
i'm missing something:
```
#[test]
fn test_unparse_cross_join() -> Result<()> {
let schema = Schema::new(vec![
Field::new("k", DataType::Int32, false),
Field::new("v", DataType::Int32, false),
]);
// Cross Join:
// TableScan: t1 projection=[k]
// Projection: t2.v
// Filter: t2.k = Int32(0)
// TableScan: t2 projection=[k, v]
let t1_scan = table_scan(Some("t1"), &schema, Some(vec![0]))?.build()?;
let t2_scan = table_scan(Some("t2"), &schema, Some(vec![0,
1]))?.build()?;
let t2_proj = LogicalPlanBuilder::from(t2_scan)
.filter(col("t2.k").eq(lit(0)))?
.project(vec![col("t2.v")])?
.build()?;
let plan = LogicalPlanBuilder::from(t1_scan)
.cross_join(t2_proj)?
.filter(col("t2.k").eq(lit(0)))?
.project(vec![col("t1.k"), col("t2.v")])?
.build()?;
let unparser = Unparser::new(&UnparserPostgreSqlDialect {});
let sql = unparser.plan_to_sql(&plan)?;
assert_snapshot!(
sql,
@r#"SELECT "t1"."k", "t2"."v" FROM "t1" CROSS JOIN (SELECT "t2"."v"
FROM "t2" WHERE ("t2"."k" = 0))"#
);
Ok(())
}
```
However this would be incorrect as the outside filter is still kept even
though it would have been pushed down. Are you able to make a specific test
that I can run somewhere that reproduces this?
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
