Thanks, Indeed by playing with (and overriding) the cost of the Jdbc rel nodes i'm managed to expand the trees (in many cases).
How can i set the HepPlanner instead of Volcano one? On 30 November 2016 at 20:50, [email protected] < [email protected]> wrote: > Which planner are you using? If the rule is being fired, what you may be > missing is that the cost of the converted expression is more than the cost > of the input expression, resulting in the VolcanoPlanner throwing out the > converted expression. You should use the HepPlanner for this. > > > On Nov 29, 2016, at 2:07 PM, Christian Tzolov <[email protected]> > wrote: > > > > We're working on a JDBC wrapper for HAWQ (SQL-on-HADOOP) that hopefully > > would allow us "emulate" UPDATE and DELETE operations while using only > > append-only (e.g. INSERT) in the background. > > > > Using the JDBC adapter i've been trying to convert an input sql query > like > > this: > > > > SELECT store_id, account_id, exp_date, time_id, category_id, currency_id, > > amount FROM foodmart.expense_fact > > > > Into one that looks like this: > > > > SELECT store_id, account_id, exp_date, time_id, category_id, currency_id, > > amount > > FROM ( > > SELECT store_id, account_id, exp_date, time_id, category_id, > > currency_id, amount > > FROM ( > > SELECT *, MAX(exp_date) OVER (PARTITION BY account_id) AS > > last_version_number > > FROM foodmart.expense_fact > > ) as link_last > > WHERE exp_date = last_version_number > > ) as current_version; > > > > If you run the second query directly the output relation is: > > > > "PLAN=JdbcToEnumerableConverter > > JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], time_id=[$3], > > category_id=[$4], currency_id=[$5], amount=[$6]) > > JdbcFilter(condition=[=($2, $7)]) > > JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], > > time_id=[$3], category_id=[$4], currency_id=[$5], amount=[$6], > > last_version_number=[MAX($2) OVER (PARTITION BY $1 RANGE > > BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)]) > > JdbcTableScan(table=[[foodmart, expense_fact]]) > > > > > > I've created a rule meant to do this: > > https://gist.github.com/tzolov/a426d10a55fdd712a67830b0154b33db On > match > > it creates an expression that matches the one above: > > > > "Expanded RelNode: > > JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], time_id=[$3], > > category_id=[$4], currency_id=[$5], amount=[$6]) > > JdbcFilter(condition=[=($2, $7)]) > > JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], > > time_id=[$3], category_id=[$4], currency_id=[$5], amount=[$6], > > last_version_number=[MAX($2) OVER (PARTITION BY $1 ROWS BETWEEN UNBOUNDED > > PRECEDING AND UNBOUNDED FOLLOWING)]) > > JdbcTableScan(table=[[foodmart, expense_fact]])" > > > > But after the Planner completes the optimization the final output is this > > (e.g. the initial query): > > > > "PLAN=JdbcToEnumerableConverter > > JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], time_id=[$3], > > category_id=[$4], currency_id=[$5]) > > JdbcTableScan(table=[[foodmart, expense_fact]]) > > > > 1. What am i missing? Are the field/type references copied correctly? > > > > 2. Also what is the right approach to prevent this Rule get in loop? > > > > I'm a bit stuck so any ideas and suggestions appreciated! > > > > Cheers, > > Christian > > > > -- > > Christian Tzolov <http://www.linkedin.com/in/tzolov> | Solution > Architect, > > EMEA Practice Team | Pivotal <http://pivotal.io/> > > [email protected]|+31610285517 > -- Christian Tzolov <http://www.linkedin.com/in/tzolov> | Solution Architect, EMEA Practice Team | Pivotal <http://pivotal.io/> [email protected]|+31610285517
