I think there are more issues.
For instance JdbcTest.testSelfJoinDifferentColumns
select e1."full_name"
from "foodmart"."employee" as e1
join "foodmart"."employee" as e2 on e1."first_name" = e2."last_name"
order by e1."last_name" limit 3
gives the following plan (note how JdbcJoin, JdbcCalc, and JdbcSort
are not used):
EnumerableCalc(expr#0..1=[{inputs}], full_name=[$t0])
EnumerableLimit(fetch=[3])
EnumerableSort(sort0=[$1], dir0=[ASC])
EnumerableCalc(expr#0..3=[{inputs}], full_name=[$t0], last_name=[$t2])
EnumerableJoin(condition=[=($1, $3)], joinType=[inner])
JdbcToEnumerableConverter
JdbcProject(full_name=[$1], first_name=[$2], last_name=[$3])
JdbcTableScan(table=[[foodmart, employee]])
JdbcToEnumerableConverter
JdbcProject(last_name=[$3])
JdbcTableScan(table=[[foodmart, employee]])
Overriding JdbcSort.computeSelfCost and JdbcJoin.computeSelfCost with
"multiplyby JdbcConvention.COST_MULTIPLIER" does not help.
If I reduce COST_MULTIPLIER from 0.8 to 0.08, then "better" plan is used:
EnumerableCalc(expr#0..1=[{inputs}], full_name=[$t0])
EnumerableLimit(fetch=[3])
EnumerableSort(sort0=[$1], dir0=[ASC])
JdbcToEnumerableConverter
JdbcProject(full_name=[$1], last_name=[$3])
JdbcJoin(condition=[=($2, $0)], joinType=[inner])
JdbcProject(last_name=[$3])
JdbcTableScan(table=[[foodmart, employee]])
JdbcProject(full_name=[$1], first_name=[$2], last_name=[$3])
JdbcTableScan(table=[[foodmart, employee]])
Those "cost_multiplier" tricks are sloppy.
Vladimir
