Hello
I was writing JUnit tests and found couple of strange results. I have used
jOOQ version 2.5.1 and HSQLDB version 2.2.9 here. 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;
Test case 1: Joining two different tables
=========================================
DB initialization (just copied from java code):
+ "truncate table child_node;"
+ "truncate table node;"
+ "truncate table link;"
+ "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 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 );"
SQL query: "select * from node left join child_node on
node.id=child_node.parent_id;",
SQL query results:
ID NAME NEXT_ID PREV_ID ID NAME NEXT_ID PREV_ID
PARENT_ID
1 A <null> <null> 1 Child 1 <null> <null> 1
2 B <null> <null> 2 Child 2 <null> <null> 2
2 B <null> <null> 3 Child 3 <null> <null> 2
3 C <null> <null> <null> <null> <null> <null> <null>
Java code using DSL API:
db.select().from(NODE).leftOuterJoin(CHILD_NODE).on(NODE.ID.eq(CHILD_NODE.PARENT_ID))
Results:
20121003 23:04:37.580 [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"
20121003 23:04:37.584 [main] DEBUG - Query executed : Total:
6.937ms
20121003 23:04:37.773 [main] DEBUG - Fetched result :
+----+----+-------+-------+------+-------+-------+-------+---------+
20121003 23:04:37.774 [main] DEBUG - : |
ID|NAME|NEXT_ID|PREV_ID| ID|NAME |NEXT_ID|PREV_ID|PARENT_ID|
20121003 23:04:37.774 [main] DEBUG - :
+----+----+-------+-------+------+-------+-------+-------+---------+
20121003 23:04:37.775 [main] DEBUG - : | 1|A |
{null}| {null}| 1|Child 1| {null}| {null}| 1|
20121003 23:04:37.775 [main] DEBUG - : | 2|B |
{null}| {null}| 2|Child 2| {null}| {null}| 2|
20121003 23:04:37.775 [main] DEBUG - : | 2|B |
{null}| {null}| 3|Child 3| {null}| {null}| 2|
20121003 23:04:37.775 [main] DEBUG - : | 3|C |
{null}| {null}|{null}|{null} | {null}| {null}| {null}|
20121003 23:04:37.775 [main] DEBUG - :
+----+----+-------+-------+------+-------+-------+-------+---------+
20121003 23:04:37.776 [main] DEBUG - Finishing : Total:
198.349ms, +191.412ms
Java code using SQL:
db.resultQuery("select * from node left join child_node on
node.id=child_node.parent_id");
Results:
20121003 23:11:47.088 [main] DEBUG - Executing query : select *
from node left join child_node on node.id=child_node.parent_id
20121003 23:11:47.090 [main] DEBUG - Query executed : Total:
2.012ms
20121003 23:11:47.412 [main] DEBUG - Fetched result :
+------+-------+-------+-------+------+-------+-------+-------+---------+
20121003 23:11:47.412 [main] DEBUG - : |
ID|NAME |NEXT_ID|PREV_ID| ID|NAME |NEXT_ID|PREV_ID|PARENT_ID|
20121003 23:11:47.412 [main] DEBUG - :
+------+-------+-------+-------+------+-------+-------+-------+---------+
20121003 23:11:47.413 [main] DEBUG - : |
1|Child 1| {null}| {null}| 1|Child 1| {null}| {null}| 1|
20121003 23:11:47.413 [main] DEBUG - : |
2|Child 2| {null}| {null}| 2|Child 2| {null}| {null}| 2|
20121003 23:11:47.413 [main] DEBUG - : |
3|Child 3| {null}| {null}| 3|Child 3| {null}| {null}| 2|
20121003 23:11:47.413 [main] DEBUG - :
|{null}|{null} | {null}| {null}|{null}|{null} | {null}| {null}| {null}|
20121003 23:11:47.414 [main] DEBUG - :
+------+-------+-------+-------+------+-------+-------+-------+---------+
20121003 23:11:47.414 [main] DEBUG - Finishing : Total:
325.906ms, +323.893ms
Test case 2: Joining one table
==============================
DB initialization (just copied from java code):
+ "truncate table child_node;"
+ "truncate table node;"
+ "truncate table link;"
+ "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, next_id ) values ( 4,
'D', 1 );"
+ "insert into node ( id, name, next_id ) values ( 5,
'E', 1 );"
SQL query: "select * from node n1 left join node n2 on
n1.id=n2.next_id;",
SQL query results:
ID NAME NEXT_ID PREV_ID ID NAME NEXT_ID PREV_ID
1 A <null> <null> 4 D 1 <null>
1 A <null> <null> 5 E 1 <null>
2 B <null> <null> <null> <null> <null> <null>
3 C <null> <null> <null> <null> <null> <null>
4 D 1 <null> <null> <null> <null> <null>
5 E 1 <null> <null> <null> <null> <null>
Java code using DSL API:
jooq.testing.tables.Node n1 = NODE.as("n1");
jooq.testing.tables.Node n2 = NODE.as("n2");
db.select().from(n1).leftOuterJoin(n2).on(n1.ID.eq(n2.NEXT_ID))
Results:
20121004 00:32:58.086 [main] DEBUG - Executing query : select
"n1"."ID", "n1"."NAME", "n1"."NEXT_ID", "n1"."PREV_ID", "n2"."ID",
"n2"."NAME", "n2"."NEXT_ID",
"n2"."PREV_ID" from "TESTING"."NODE" as "n1" left outer join
"TESTING"."NODE" as "n2" on "n1"."ID" = "n2"."NEXT_ID"
20121004 00:32:58.089 [main] DEBUG - Query executed : Total:
4.992ms
20121004 00:32:58.233 [main] DEBUG - Fetched result :
+----+----+-------+-------+------+------+-------+-------+
20121004 00:32:58.234 [main] DEBUG - : |
ID|NAME|NEXT_ID|PREV_ID| ID|NAME |NEXT_ID|PREV_ID|
20121004 00:32:58.234 [main] DEBUG - :
+----+----+-------+-------+------+------+-------+-------+
20121004 00:32:58.234 [main] DEBUG - : | 1|A |
{null}| {null}| 4|D | 1| {null}|
20121004 00:32:58.234 [main] DEBUG - : | 1|A |
{null}| {null}| 5|E | 1| {null}|
20121004 00:32:58.235 [main] DEBUG - : | 2|B |
{null}| {null}|{null}|{null}| {null}| {null}|
20121004 00:32:58.235 [main] DEBUG - : | 3|C |
{null}| {null}|{null}|{null}| {null}| {null}|
20121004 00:32:58.235 [main] DEBUG - : | 4|D
| 1| {null}|{null}|{null}| {null}| {null}|
20121004 00:32:58.235 [main] DEBUG - :
+----+----+-------+-------+------+------+-------+-------+
20121004 00:32:58.235 [main] DEBUG - : |...1
record(s) truncated...
20121004 00:32:58.236 [main] DEBUG - Finishing : Total:
152.295ms, +147.302ms
Java code using SQL:
db.resultQuery("select * from node n1 left join node n2 on
n1.id=n2.next_id");
Results:
20121003 23:52:23.254 [main] DEBUG - Executing query : select *
from node n1 left join node n2 on n1.id=n2.next_id;
20121003 23:52:23.257 [main] DEBUG - Query executed : Total:
3.246ms
20121003 23:52:23.509 [main] DEBUG - Fetched result :
+------+------+-------+-------+------+------+-------+-------+
20121003 23:52:23.509 [main] DEBUG - : |
ID|NAME |NEXT_ID|PREV_ID| ID|NAME |NEXT_ID|PREV_ID|
20121003 23:52:23.509 [main] DEBUG - :
+------+------+-------+-------+------+------+-------+-------+
20121003 23:52:23.510 [main] DEBUG - : |
4|D | 1| {null}| 4|D | 1| {null}|
20121003 23:52:23.510 [main] DEBUG - : |
5|E | 1| {null}| 5|E | 1| {null}|
20121003 23:52:23.510 [main] DEBUG - :
|{null}|{null}| {null}| {null}|{null}|{null}| {null}| {null}|
20121003 23:52:23.510 [main] DEBUG - :
|{null}|{null}| {null}| {null}|{null}|{null}| {null}| {null}|
20121003 23:52:23.511 [main] DEBUG - :
|{null}|{null}| {null}| {null}|{null}|{null}| {null}| {null}|
20121003 23:52:23.511 [main] DEBUG - :
+------+------+-------+-------+------+------+-------+-------+
20121003 23:52:23.511 [main] DEBUG - : |...1
record(s) truncated...
20121003 23:52:23.511 [main] DEBUG - Finishing : Total:
257.586ms, +254.34ms
Question:
In both test cases DSL API code seems to work, but when embedding SQL into
resultQuery results seem to be incorrect. Is there a error in jOOQ or am I
using it somehow
incorrectly?
Cheers,
Jouni
