On 21.10.2012 18:34, Lukas Eder wrote:
This issue is fixed on GitHub master. It will be included in the next
2.6.0-SNAPSHOT version. I'll also merge this fix to 2.5.x and 2.4.x
version branches, soon.
Cheers
Lukas
Hello,
Thank you for the update. Unfortunately I still got strange results. In
v2.60 debug printouts for "fetched results" seem to be OK also when
using SQL, but when getting field values from Result<Record> there are
differencies depending on whether you use DSL or SQL. See the attached file.
Cheers,
J
Hello,
I was running JUnit tests with jOOQ v2.6 and got strange results.
Test schema is this:
create schema testing authorization DBA;
set schema testing;
drop table node if exists cascade;
drop table child_node if exists cascade;
drop table link if exists cascade;
create table node (
id integer generated by default as identity (start with 1) primary key,
name varchar_ignorecase(255) not null,
next_id integer,
prev_id integer,
unique (name)
);
create table child_node (
id integer generated by default as identity (start with 1) primary key,
name varchar_ignorecase(255) not null,
next_id integer,
prev_id integer,
parent_id integer not null,
unique (name)
);
create table link (
id integer generated by default as identity (start with 1) primary key,
name varchar_ignorecase(255) not null,
next_node_id integer not null,
prev_node_id integer not null,
unique (name)
);
alter table node
add constraint node_to_next_node
foreign key (next_id)
references node;
alter table node
add constraint node_to_prev_node
foreign key (prev_id)
references node;
alter table child_node
add constraint child_node_to_next_node
foreign key (next_id)
references child_node;
alter table child_node
add constraint child_node_to_prev_node
foreign key (prev_id)
references child_node;
alter table child_node
add constraint child_node_to_node
foreign key (parent_id)
references node;
alter table link
add constraint link_to_next_node
foreign key (next_node_id)
references node;
alter table link
add constraint link_to_prev_node
foreign key (prev_node_id)
references node;
Program is this (part of it):
db.execute("set schema testing");
String initSql = ""
+ "truncate table link;"
+ "truncate table child_child_node;"
+ "truncate table child_node;"
+ "truncate table node;"
+ "insert into node ( id, name ) values ( 1, 'A' );"
+ "insert into node ( id, name ) values ( 2, 'B' );"
+ "insert into node ( id, name ) values ( 3, 'C' );"
+ "insert into node ( id, name ) values ( 4, 'D' );"
+ "insert into child_node ( id, name, parent_id ) values (
1, 'Child 1', 1 );"
+ "insert into child_node ( id, name, parent_id ) values (
2, 'Child 2', 2 );"
+ "insert into child_node ( id, name, parent_id ) values (
3, 'Child 3', 2 );";
String[] tmp = initSql.trim().split(";");
for ( int j = 0; j < tmp.length; j++ ) {
db.query(tmp[j]).execute();
}
{
log.debug("Parsing DSL statement");
Result<Record> rr =
db.select().from(NODE).leftOuterJoin(CHILD_NODE).on(NODE.ID.eq(CHILD_NODE.PARENT_ID)).where(NODE.NAME.eq("D")).fetch();
List<Field<?>> fieldList = rr.getFields();
for ( int r = 0; r < rr.size(); r++ ) {
for ( int f = 0; f < fieldList.size(); f++ ) {
Record record = rr.get(r);
Field field = record.getField(f);
Object value = record.getValue(field);
System.out.println("row = " + r + " " + field.getName() + "
= " + value);
}
}
}
{
log.debug("Parsing SQL statement");
Result<Record> rr = db.fetch("select * from node left outer join
child_node on node.id=child_node.parent_id where node.name = 'D'");
List<Field<?>> fieldList = rr.getFields();
for ( int r = 0; r < rr.size(); r++ ) {
for ( int f = 0; f < fieldList.size(); f++ ) {
Record record = rr.get(r);
Field field = record.getField(f);
Object value = record.getValue(field);
System.out.println("row = " + r + " " + field.getName() + "
= " + value);
}
}
}
{
log.debug("Parsing DSL statement");
Result<Record> rr =
db.select().from(NODE).leftOuterJoin(CHILD_NODE).on(NODE.ID.eq(CHILD_NODE.PARENT_ID)).where(NODE.NAME.eq("B")).fetch();
List<Field<?>> fieldList = rr.getFields();
for ( int r = 0; r < rr.size(); r++ ) {
for ( int f = 0; f < fieldList.size(); f++ ) {
Record record = rr.get(r);
Field field = record.getField(f);
Object value = record.getValue(field);
System.out.println("row = " + r + " " + field.getName() + "
= " + value);
}
}
}
{
log.debug("Parsing SQL statement");
Result<Record> rr = db.fetch("select * from node left outer join
child_node on node.id=child_node.parent_id where node.name = 'B'");
List<Field<?>> fieldList = rr.getFields();
for ( int r = 0; r < rr.size(); r++ ) {
for ( int f = 0; f < fieldList.size(); f++ ) {
Record record = rr.get(r);
Field field = record.getField(f);
Object value = record.getValue(field);
System.out.println("row = " + r + " " + field.getName() + "
= " + value);
}
}
}
}
Program output is this:
20121028 16:13:06.298 [main] DEBUG - Executing query : set schema
testing
20121028 16:13:06.310 [main] DEBUG - Query executed : Total: 17.712ms
20121028 16:13:06.311 [main] DEBUG - Finishing : Total:
19.669ms, +1.956ms
20121028 16:13:06.333 [main] DEBUG - Executing query : truncate table
link
20121028 16:13:06.338 [main] DEBUG - Query executed : Total: 4.868ms
20121028 16:13:06.339 [main] DEBUG - Finishing : Total: 5.761ms,
+0.892ms
20121028 16:13:06.340 [main] DEBUG - Executing query : truncate table
child_child_node
20121028 16:13:06.342 [main] DEBUG - Query executed : Total: 2.367ms
20121028 16:13:06.345 [main] DEBUG - Finishing : Total: 4.961ms,
+2.593ms
20121028 16:13:06.345 [main] DEBUG - Executing query : truncate table
child_node
20121028 16:13:06.346 [main] DEBUG - Query executed : Total: 1.32ms
20121028 16:13:06.347 [main] DEBUG - Finishing : Total: 1.965ms,
+0.644ms
20121028 16:13:06.347 [main] DEBUG - Executing query : truncate table
node
20121028 16:13:06.348 [main] DEBUG - Query executed : Total: 1.147ms
20121028 16:13:06.349 [main] DEBUG - Finishing : Total: 1.678ms,
+0.53ms
20121028 16:13:06.349 [main] DEBUG - Executing query : insert into
node ( id, name ) values ( 1, 'A' )
20121028 16:13:06.351 [main] DEBUG - Query executed : Total: 1.467ms
20121028 16:13:06.351 [main] DEBUG - Finishing : Total: 2.028ms,
+0.561ms
20121028 16:13:06.351 [main] DEBUG - Executing query : insert into
node ( id, name ) values ( 2, 'B' )
20121028 16:13:06.352 [main] DEBUG - Query executed : Total: 1.093ms
20121028 16:13:06.353 [main] DEBUG - Finishing : Total: 1.62ms,
+0.526ms
20121028 16:13:06.354 [main] DEBUG - Executing query : insert into
node ( id, name ) values ( 3, 'C' )
20121028 16:13:06.356 [main] DEBUG - Query executed : Total: 2.466ms
20121028 16:13:06.357 [main] DEBUG - Finishing : Total: 3.033ms,
+0.566ms
20121028 16:13:06.357 [main] DEBUG - Executing query : insert into
node ( id, name ) values ( 4, 'D' )
20121028 16:13:06.358 [main] DEBUG - Query executed : Total: 1.17ms
20121028 16:13:06.359 [main] DEBUG - Finishing : Total: 1.689ms,
+0.519ms
20121028 16:13:06.359 [main] DEBUG - Executing query : insert into
child_node ( id, name, parent_id ) values ( 1, 'Child 1', 1 )
20121028 16:13:06.360 [main] DEBUG - Query executed : Total: 1.191ms
20121028 16:13:06.361 [main] DEBUG - Finishing : Total: 1.657ms,
+0.466ms
20121028 16:13:06.361 [main] DEBUG - Executing query : insert into
child_node ( id, name, parent_id ) values ( 2, 'Child 2', 2 )
20121028 16:13:06.363 [main] DEBUG - Query executed : Total: 1.777ms
20121028 16:13:06.364 [main] DEBUG - Finishing : Total: 2.635ms,
+0.857ms
20121028 16:13:06.364 [main] DEBUG - Executing query : insert into
child_node ( id, name, parent_id ) values ( 3, 'Child 3', 2 )
20121028 16:13:06.367 [main] DEBUG - Query executed : Total: 2.446ms
20121028 16:13:06.367 [main] DEBUG - Finishing : Total: 2.941ms,
+0.494ms
20121028 16:13:06.367 [main] DEBUG - Parsing DSL statement
20121028 16:13:06.404 [main] DEBUG - Executing query : select
"TESTING"."NODE"."ID", "TESTING"."NODE"."NAME", "TESTING"."NODE"."NEXT_ID",
"TESTING"."NODE"."PREV_ID", "TESTING"."CHILD_NODE"."ID",
"TESTING"."CHILD_NODE"."NAME", "TESTING"."CHILD_NODE"."NEXT_ID",
"TESTING"."CHILD_NODE"."PREV_ID", "TESTING"."CHILD_NODE"."PARENT_ID" from
"TESTING"."NODE" left outer join "TESTING"."CHILD_NODE" on
"TESTING"."NODE"."ID" = "TESTING"."CHILD_NODE"."PARENT_ID" where
"TESTING"."NODE"."NAME" = cast(? as varchar(32672))
20121028 16:13:06.405 [main] DEBUG - -> with bind values : select
"TESTING"."NODE"."ID", "TESTING"."NODE"."NAME", "TESTING"."NODE"."NEXT_ID",
"TESTING"."NODE"."PREV_ID", "TESTING"."CHILD_NODE"."ID",
"TESTING"."CHILD_NODE"."NAME", "TESTING"."CHILD_NODE"."NEXT_ID",
"TESTING"."CHILD_NODE"."PREV_ID", "TESTING"."CHILD_NODE"."PARENT_ID" from
"TESTING"."NODE" left outer join "TESTING"."CHILD_NODE" on
"TESTING"."NODE"."ID" = "TESTING"."CHILD_NODE"."PARENT_ID" where
"TESTING"."NODE"."NAME" = 'D'
20121028 16:13:06.412 [main] DEBUG - Query executed : Total: 12.294ms
20121028 16:13:06.428 [main] DEBUG - Fetched result :
+----+----+-------+-------+------+------+-------+-------+---------+
20121028 16:13:06.428 [main] DEBUG - : |
ID|NAME|NEXT_ID|PREV_ID| ID|NAME |NEXT_ID|PREV_ID|PARENT_ID|
20121028 16:13:06.428 [main] DEBUG - :
+----+----+-------+-------+------+------+-------+-------+---------+
20121028 16:13:06.429 [main] DEBUG - : | 4|D |
{null}| {null}|{null}|{null}| {null}| {null}| {null}|
20121028 16:13:06.429 [main] DEBUG - :
+----+----+-------+-------+------+------+-------+-------+---------+
20121028 16:13:06.429 [main] DEBUG - Finishing : Total: 29.05ms,
+16.755ms
row = 0 ID = 4
row = 0 NAME = D
row = 0 NEXT_ID = null
row = 0 PREV_ID = null
row = 0 ID = null
row = 0 NAME = null
row = 0 NEXT_ID = null
row = 0 PREV_ID = null
row = 0 PARENT_ID = null
20121028 16:13:06.431 [main] DEBUG - Parsing SQL statement
20121028 16:13:06.432 [main] DEBUG - Executing query : select * from
node left outer join child_node on node.id=child_node.parent_id where node.name
= 'D'
20121028 16:13:06.434 [main] DEBUG - Query executed : Total: 2.853ms
20121028 16:13:06.437 [main] DEBUG - Fetched result :
+----+----+-------+-------+------+------+-------+-------+---------+
20121028 16:13:06.437 [main] DEBUG - : |
ID|NAME|NEXT_ID|PREV_ID| ID|NAME |NEXT_ID|PREV_ID|PARENT_ID|
20121028 16:13:06.438 [main] DEBUG - :
+----+----+-------+-------+------+------+-------+-------+---------+
20121028 16:13:06.438 [main] DEBUG - : | 4|D |
{null}| {null}|{null}|{null}| {null}| {null}| {null}|
20121028 16:13:06.438 [main] DEBUG - :
+----+----+-------+-------+------+------+-------+-------+---------+
20121028 16:13:06.438 [main] DEBUG - Finishing : Total: 6.587ms,
+3.734ms
row = 0 ID = null
row = 0 NAME = null
row = 0 NEXT_ID = null
row = 0 PREV_ID = null
row = 0 ID = null
row = 0 NAME = null
row = 0 NEXT_ID = null
row = 0 PREV_ID = null
row = 0 PARENT_ID = null
20121028 16:13:06.440 [main] DEBUG - Parsing DSL statement
20121028 16:13:06.441 [main] DEBUG - Executing query : select
"TESTING"."NODE"."ID", "TESTING"."NODE"."NAME", "TESTING"."NODE"."NEXT_ID",
"TESTING"."NODE"."PREV_ID", "TESTING"."CHILD_NODE"."ID",
"TESTING"."CHILD_NODE"."NAME", "TESTING"."CHILD_NODE"."NEXT_ID",
"TESTING"."CHILD_NODE"."PREV_ID", "TESTING"."CHILD_NODE"."PARENT_ID" from
"TESTING"."NODE" left outer join "TESTING"."CHILD_NODE" on
"TESTING"."NODE"."ID" = "TESTING"."CHILD_NODE"."PARENT_ID" where
"TESTING"."NODE"."NAME" = cast(? as varchar(32672))
20121028 16:13:06.442 [main] DEBUG - -> with bind values : select
"TESTING"."NODE"."ID", "TESTING"."NODE"."NAME", "TESTING"."NODE"."NEXT_ID",
"TESTING"."NODE"."PREV_ID", "TESTING"."CHILD_NODE"."ID",
"TESTING"."CHILD_NODE"."NAME", "TESTING"."CHILD_NODE"."NEXT_ID",
"TESTING"."CHILD_NODE"."PREV_ID", "TESTING"."CHILD_NODE"."PARENT_ID" from
"TESTING"."NODE" left outer join "TESTING"."CHILD_NODE" on
"TESTING"."NODE"."ID" = "TESTING"."CHILD_NODE"."PARENT_ID" where
"TESTING"."NODE"."NAME" = 'B'
20121028 16:13:06.445 [main] DEBUG - Query executed : Total: 5.432ms
20121028 16:13:06.463 [main] DEBUG - Fetched result :
+----+----+-------+-------+----+-------+-------+-------+---------+
20121028 16:13:06.463 [main] DEBUG - : |
ID|NAME|NEXT_ID|PREV_ID| ID|NAME |NEXT_ID|PREV_ID|PARENT_ID|
20121028 16:13:06.463 [main] DEBUG - :
+----+----+-------+-------+----+-------+-------+-------+---------+
20121028 16:13:06.464 [main] DEBUG - : | 2|B |
{null}| {null}| 2|Child 2| {null}| {null}| 2|
20121028 16:13:06.464 [main] DEBUG - : | 2|B |
{null}| {null}| 3|Child 3| {null}| {null}| 2|
20121028 16:13:06.464 [main] DEBUG - :
+----+----+-------+-------+----+-------+-------+-------+---------+
20121028 16:13:06.464 [main] DEBUG - Finishing : Total:
24.038ms, +18.606ms
row = 0 ID = 2
row = 0 NAME = B
row = 0 NEXT_ID = null
row = 0 PREV_ID = null
row = 0 ID = 2
row = 0 NAME = Child 2
row = 0 NEXT_ID = null
row = 0 PREV_ID = null
row = 0 PARENT_ID = 2
row = 1 ID = 2
row = 1 NAME = B
row = 1 NEXT_ID = null
row = 1 PREV_ID = null
row = 1 ID = 3
row = 1 NAME = Child 3
row = 1 NEXT_ID = null
row = 1 PREV_ID = null
row = 1 PARENT_ID = 2
20121028 16:13:06.465 [main] DEBUG - Parsing SQL statement
20121028 16:13:06.466 [main] DEBUG - Executing query : select * from
node left outer join child_node on node.id=child_node.parent_id where node.name
= 'B'
20121028 16:13:06.468 [main] DEBUG - Query executed : Total: 2.497ms
20121028 16:13:06.470 [main] DEBUG - Fetched result :
+----+----+-------+-------+----+-------+-------+-------+---------+
20121028 16:13:06.470 [main] DEBUG - : |
ID|NAME|NEXT_ID|PREV_ID| ID|NAME |NEXT_ID|PREV_ID|PARENT_ID|
20121028 16:13:06.470 [main] DEBUG - :
+----+----+-------+-------+----+-------+-------+-------+---------+
20121028 16:13:06.470 [main] DEBUG - : | 2|B |
{null}| {null}| 2|Child 2| {null}| {null}| 2|
20121028 16:13:06.470 [main] DEBUG - : | 2|B |
{null}| {null}| 3|Child 3| {null}| {null}| 2|
20121028 16:13:06.470 [main] DEBUG - :
+----+----+-------+-------+----+-------+-------+-------+---------+
20121028 16:13:06.471 [main] DEBUG - Finishing : Total: 5.007ms,
+2.51ms
row = 0 ID = 2
row = 0 NAME = Child 2
row = 0 NEXT_ID = null
row = 0 PREV_ID = null
row = 0 ID = 2
row = 0 NAME = Child 2
row = 0 NEXT_ID = null
row = 0 PREV_ID = null
row = 0 PARENT_ID = 2
row = 1 ID = 3
row = 1 NAME = Child 3
row = 1 NEXT_ID = null
row = 1 PREV_ID = null
row = 1 ID = 3
row = 1 NAME = Child 3
row = 1 NEXT_ID = null
row = 1 PREV_ID = null
row = 1 PARENT_ID = 2
BUILD SUCCESSFUL (total time: 1 second)
In every case debug printout for fetched results seems to be OK, but when
getting field values from Result<Record> results are different for DSL and SQL.
DSL results are correct while SQL results are not. It seems that if there are
non unique field names jOOQ fethces value for the latest field name.
Cheers,
J
