The rule CoreRules.PROJECT_JOIN_TRANSPOSE is unsound and should not be used by the optimizer.
Have to find a way to create an easy reproduction for the Calcite project.
With this rule the following query from SLT select2.test produces incorrect results:
SELECT abs(b-c),
a+b*2+c*3,
(SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
a-b,
a+b*2+c*3+d*4+e*5,
CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222
WHEN a<b+3 THEN 333 ELSE 444 END
FROM t1
The rule CoreRules.PROJECT_JOIN_TRANSPOSE is unsound and should not be used by the optimizer. Have to find a way to create an easy reproduction for the Calcite project. With this rule the following query from SLT select2.test produces incorrect results: