Re: [h2] Implementation of RANK function

[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.

On Mon, Jan 29, 2018 at 6:17 AM, Dhaval Shewale <dhaval1...@gmail.com>
wrote:

> Hi Boris,
>
> If you have implemented the rank function in java for H2, can you pls
> share the complete code.
>
> Dhaval
>
>
> On Thursday, July 21, 2016 at 12:59:21 AM UTC+5:30, Boris Granveaud wrote:
>>
>> 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 partitions, 
>> ArrayList 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.
>>
>>
>>
>> Le dimanche 17 juillet 2016 21:25:25 UTC+2, Noel Grandin a écrit :
>>>
>>> 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-

[h2] problem with union all / order by / wrapping select

[Boris Granveaud]

Hello,

I have an unexpected result with this request (tested with H2 1.4.193, it 
works fine with Oracle):

create table test(id number(5));

insert into test values(1);
insert into test values(2);

(select id from test
union all
select id from test
) order by id;


ID   

1
1
2
2

select t1.* from (
(select id from test
union all
select id from test
) order by id
) t1;


ID   

ROWNUM()   

1 1
2 2
1 3
2 4

it looks like order is lost when adding a wrapping select.

is it a bug or expected behavior?

My goal is to do something like that:

select t1.* from (
(select id from test
union all
select id from test
) order by id
) t1 where rownum <3;

I know I should use LIMIT and OFFSET syntax but I would like to keep the 
Oracle syntax. 

thanks,
Boris.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] SELECT * FROM (SELECT ? FROM DUAL) fails

[Boris Granveaud]

Hello,

I have a problem with the following statement: 

SELECT * FROM (SELECT ? FROM DUAL)

it gives this error with H2 1.4.191:

org.h2.jdbc.JdbcSQLException: Unknown data type: """?1"""; SQL statement:
SELECT * FROM (SELECT ? FROM DUAL) [50004-192]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.table.Table.setColumns(Table.java:425)
at org.h2.table.TableView.initColumnsAndTables(TableView.java:220)
at org.h2.table.TableView.init(TableView.java:98)
at org.h2.table.TableView.(TableView.java:65)
at org.h2.table.TableView.createTempView(TableView.java:535)
at org.h2.command.Parser.readTableFilter(Parser.java:1168)

Code to reproduce:

PreparedStatement st = cnx.prepareStatement("SELECT * FROM (SELECT ? FROM 
DUAL)");
st.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
rs = st.executeQuery();
rs.next();
System.out.println(rs.getObject(1));
rs.close();
st.close();

"SELECT ? FROM DUAL" works fine.

The problems is that the type of the parameter cannot be known when the 
statement is prepared so I'm not sure how the parser should deal with that.

Boris.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Implementation of RANK function

[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 partitions, 
ArrayList 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.



Le dimanche 17 juillet 2016 21:25:25 UTC+2, Noel Grandin a écrit :
>
> 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.
>
> ​
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] 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  ac3   1
3  aa1   1
43


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 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 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 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  ac3   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.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Possible to filter SQL statements before execution?

[Boris Granveaud]

Hello,

Is there a mean to set a filter class to transform SQL statements 
on-the-fly before execution?

I'm using MySQL as a main DB and H2 for unit tests and I'm stuck by SQL 
compatibility problems. My idea is to replace for example DROP TEMPORARY 
TABLE by DROP TABLE, or CREATE TABLE (...) ENGINE=MEMORY by CREATE TABLE 
(...)

Thanks,
Boris.

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/h2-database/-/QfFY4vFQUbEJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

COUNT(DISTINCT several columns) doesn't work?

[Boris Granveaud]

Hello,

I'm struggling with another statement so that it works unchanged on
MySQL 5.1 and H2 1.3.164:

MySQL:

SELECT DISTINCT p.s_id,p.edition FROM publication p = OK
SELECT COUNT(DISTINCT p.s_id,p.edition) FROM publication p = OK

H2:

SELECT DISTINCT p.s_id,p.edition FROM publication p = OK
SELECT COUNT(DISTINCT p.s_id,p.edition) FROM publication p = Syntax
error

but SELECT COUNT(DISTINCT p.s_id) FROM publication p is ok.

What do you think?

For the moment, I've found a workaround with a user defined CONCAT_WS
function (which exists in MySQL):

SELECT COUNT(DISTINCT(CONCAT_WS('-', CAST(p.s_id AS CHAR),
CAST(p.edition AS CHAR FROM publication p

with
CREATE ALIAS CONCAT_WS FOR H2Functions.concatWS;

public class H2Functions {
public static String concatWS(String separator, String... args) {
return com.google.common.base.Joiner.on(separator).join(args);
}
}

not very nice though.

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

MySQL date functions

[Boris Granveaud]

Hello,

I need to be able to execute MySQL statements on H2 for my unit tests.
And I'm blocked by the DATE_ADD(...) function.

I tried to define my own function in Java like this:

public class H2Functions {
public static Date dateAdd(Date date, String expr) {
...
}
}

CREATE ALIAS DATE_ADD FOR H2Functions.dateAdd;
SELECT DATE_ADD(now(), INTERVAL 1 DAY);

Syntax error in SQL statement SELECT DATE_ADD(NOW(), INTERVAL 1[*]
DAY) ; expected ., (, [, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE,
REGEXP, IS, IN, BETWEEN, AND, OR, ), ,; SQL statement:
SELECT DATE_ADD(now(), INTERVAL 1 DAY) [42001-164] 42001/42001

I understand that INTERVAL 1 DAY is not a string, and so my function
is not called.

Is there a mean to have a SQL request with DATE_ADD which works on
both H2 and MySQL?

Thanks.

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: resultSet.getObject(tableAlias.column) doesn't work

[Boris Granveaud]

Very strange. I've used MySQL 5.1.49 for my tests and Connector/J
5.0.8 and 5.1.18. Did you test with an InnoDB table? I can send you my
test project if you want.

Anyway, from what I understand after googling about this problem, this
is not in the specifications so this behavior is not consistent
between different databases.

Boris.

On 14 nov, 02:49, Thomas Mueller thomas.tom.muel...@gmail.com wrote:
 Hi,

 According to my tests, also MySQL throws an exception
 for resultSet.getObject(u.id). The same as any other database I tested. I
 guess you used a different (old?) version of MySQL.

 Yes, you need to use an alias.

 Regards,
 Thomas

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

resultSet.getObject(tableAlias.column) doesn't work

[Boris Granveaud]

Hello,

I would like to handle result sets with duplicate column names by
using resultSet.getXXX(tableAlias.column). This seems to work with
MySQL but not with H2:

CREATE TABLE users (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
)

INSERT INTO users VALUES(1,'user 1')
INSERT INTO users VALUES(2,'user 2')

SELECT u.*,u2.* FROM users u, users u2 WHERE u.id=1 AND u2.id=2

With MySQL:
resultSet.getObject(u.id) returns 1
resultSet.getObject(u.name) returns user 1
resultSet.getObject(u2.id) returns 2
resultSet.getObject(u2.name) returns user 2

With H2 1.3.161:
throws a SQLException 'column XXX not found' for each call.

I'm not sure of what the JDBC specs say in such a case. All that I can
say is that MySQL 5.1 (+Connect/J 5.1.18) and H2 1.3.161 behave
differently.

Of course I know I can set an alias in SQL for each selected column
but this would be very tedious.

Any idea?

Boris.

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

MySQL multi-table delete

[Boris Granveaud]

Hello,

I'm using H2 in embedded mode to execute unit tests, but my production
DB is MySQL.

Everything works fine except that some SQL statements which work with
MySQL don't with H2. This is the case of the multi-table delete:

http://dev.mysql.com/doc/refman/5.0/en/delete.html

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]

As far as I understand, this is not standard SQL, but is there any
change that this is supported in a future release of H2?

For the moment, we solved this by doing the join in Java but this a
not very efficient and my colleagues are not very happy with that.

Oh yes, same question with the multiple-table update :)

Thanks,
Boris.

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.