join condition on column = null does not work as expected in non sql compatible mode #9976
Description
Affected Version
0.19
Description
Druid does not support joining against a condition that evaluates against a constant - see #9941, however if you write a SQL query where the join condition is of the type key = NULL, Calcite optimizes this condition to false. This is correct in SQL compatible mode, but in non sql compatible mode (default) this condition should be evaluated to key = '' which is not supported.
NOTE: queries with the shape table1 inner join table2 on table1.key IS NULL and table1 inner join table2 on table1.key = '' do not plan as expected.
The following CalciteQueryTest shows the issue.
@Test
@Parameters(source = QueryContextForJoinProvider.class)
public void testSelectOnLookupUsingInnerJoinOnDimEqualsNullOperator(Map<String, Object> queryContext) throws Exception
{
testQuery(
"SELECT dim2, lookyloo.k\n"
+ "FROM foo INNER JOIN lookup.lookyloo ON foo.dim2 = null\n",
queryContext,
ImmutableList.of(
newScanQueryBuilder()
.dataSource(
join(
new TableDataSource(CalciteTests.DATASOURCE1),
new LookupDataSource("lookyloo"),
"j0.",
equalsCondition(DruidExpression.fromColumn("dim2"), DruidExpression.fromColumn("j0.k")),
JoinType.INNER
)
)
.intervals(querySegmentSpec(Filtration.eternity()))
.columns("dim2", "j0.k", "j0.v")
.context(queryContext)
.build()
),
NullHandling.sqlCompatible() ?
ImmutableList.of() :
ImmutableList.of(
new Object[]{"", "a"},
new Object[]{"", "abc"},
new Object[]{"", "nosuchkey"},
new Object[]{"", "6"}
)
);
}