Implementation of RANK function

[Boris Granveaud]

Hello,

I'm trying to implement Oracle RANK function (the analytical version, see https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm).

This is my first try at modifying H2 sources, so I'm not familiar with its internals.

I'm working with the following testcase:

create table testRank (id number(9), txt1 varchar(16), txt2 varchar(16), num number(9, 0));
insert into testRank(id, txt1, txt2, num)  values(1, 'a', 'c', 3);
insert into testRank(id, txt1, txt2, num) values(2, 'b', 'a', 2);
insert into testRank(id, txt1, txt2, num) values(3, 'a', 'a', 1);
insert into testRank(id, txt1, txt2, num) values(4, null, null, null);


SELECT id,txt1,txt2,num,RANK () OVER (ORDER BY txt1 ASC NULLS LAST) rnk
FROM testRank WHERE num IS NULL OR num<>2 ORDER BY id ASC;


ID txt1 txt2 num rnk
1  a    c    3   1
3  a    a    1   1
4                3

I've successfully defined a Rank function and parsed its parameters. Now, to compute the rank values, my idea is basically

to execute a statement derived from initial SELECT but with order defined by RANK like this:

SELECT txt1 FROM testRank WHERE num IS NULL OR num<>2 ORDER BY txt1 ASC NULLS LAST

Then I build an index txt1=>rank with the result and so for each row of the main select, I am able to return its rank.

The problem is when I'm trying to execute the derived select. I need to copy some elements from the main select. 

For the moment, my code is like this:

public class Rank extends Expression {
   
    public Rank(Select mainSelect, ArrayList<SelectOrderBy> rankOrderList) {
        this.mainSelect = mainSelect;
        this.rankOrderList = rankOrderList;
    }

    @Override
    public Value getValue(Session session) {
        // populate rankValues first time
        if (rankValues == null) {
            rankValues = executeRankQuery(session);
        }
        ...
    }


   private Map<ValueArray, Integer> executeRankQuery(Session session) {
        Select rankSelect = new Select(session);

        for (TableFilter tf : mainSelect.getTopFilters()) {
            rankSelect.addTableFilter(tf, true);
        }

        rankSelect.addCondition(mainSelect.getCondition());
        rankSelect.setOrder(rankOrderList);

        ArrayList<Expression> expressions = New.arrayList();
        for (SelectOrderBy orderBy : rankOrderList) {
            expressions.add(orderBy.expression);
        }

        rankSelect.setExpressions(expressions);

        // execute query
        rankSelect.init();
        rankSelect.prepare();
        ResultInterface result = rankSelect.query(0);

        // compute ranks
        ...
   }

There is no exception but mainSelect now returns only 1 row:

ID txt1 txt2 num rnk
1  a    c    3   0

This seems to be related to the fact of reusing TableFilter and Condition in rankSelect.

Is there another way to do this? Do I have to rebuild a SQL text statement and let the parser build the query?? clone some elements?

Thanks,

Boris.

[Noel Grandin]

I think you're going to need to run your new query before the main select and then re-init the main select

Running it on-demand like that is going to lead to it trying to run somewhere inside the main select and confusing things.

Which means that the top-level select code will probably have to somewhere do an explicit walk over the tree to run RANK-type queries, but that is fine, I always assumed that RANK-type stuff would need special handling at the top level.

​

[Boris Granveaud]

hum it sounds not easy to implement, at least for me because it requires significant changes in Select / Parser.

in the meantime, I have finished a first working implementation which builds a new query by picking elements from the main request. The main drawback is that the query is built in text and then reparsed. This looks like this:

public class Rank extends Expression {

...

public Rank(Select mainSelect, ArrayList<Expression> partitions, ArrayList<SelectOrderBy> rankOrderList, boolean dense) {
...
}

private Prepared buildRankSelect(Session session) {
    // build rank map select
    StatementBuilder sb = new StatementBuilder();

    sb.append("SELECT ");

    sb.resetCount();
    for (Expression e : partitions) {
        sb.appendExceptFirst(",");
        sb.append(e.getSQL());
    }
    for (SelectOrderBy o : rankOrderList) {
        sb.appendExceptFirst(",");
        sb.append(o.expression.getSQL());
    }

    sb.append(" FROM ");

    TableFilter filter = mainSelect.getTopTableFilter();
    if (filter != null) {
        sb.resetCount();
        int i = 0;
        do {
            sb.appendExceptFirst(" ");
            sb.append(filter.getPlanSQL(i++ > 0));
            filter = filter.getJoin();
        } while (filter != null);
    } else {
        sb.resetCount();
        int i = 0;
        for (TableFilter f : mainSelect.getTopFilters()) {
            do {
                sb.appendExceptFirst(" ");
                sb.append(f.getPlanSQL(i++ > 0));
                f = f.getJoin();
            } while (f != null);
        }
    }

    if (mainSelect.getCondition() != null) {
        sb.append(" WHERE ").append(
                StringUtils.unEnclose(mainSelect.getCondition().getSQL()));
    }

    sb.append(" ORDER BY ");

    sb.resetCount();
    for (Expression e : partitions) {
        sb.appendExceptFirst(",");
        sb.append(StringUtils.unEnclose(e.getSQL()));
    }
    for (SelectOrderBy o : rankOrderList) {
        sb.appendExceptFirst(",");
        sb.append(StringUtils.unEnclose(o.getSQL()));
    }

    System.out.println("SQL=" + sb.toString());

    // execute
    Parser parser = new Parser(session);
    return parser.prepare(sb.toString());
}

on the good side, it works without changes to H2 classes. I've tested it successfully in my project where I execute Oracle requests in H2 based unit tests.

what do you think? is it too "hackish"? or am I missing something? 

I can provide a complete patch if you want.

BTW, I have difficulties when I execute H2 tests with 'build test': some tests generate error messages and when I reach testIndex, it just runs forever. 

Boris.

[Dhaval Shewale]

Hi Boris,

If you have implemented the rank function in java for H2, can you pls share the complete code.

Dhaval

[Boris Granveaud]

Hello,

Here is my patch against 1.4.196 sources. 

I haven't submitted it officially because I think the implementation is not clean: I build an auxiliary request by taking main request SQL text instead of using internal structures of the parsed request. The reason is that it seems not obvious by looking at H2 code how to do that. 

Anyway, this works in my case. Feel free to modify and improve it!

Boris.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/I6QNmf_0Ic4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.