DSL vs embedded SQL: different results

[JK]

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

[JK]

Sorry about the formatting. I attached the previous text to keep formatting more readable.

[Lukas Eder]

Hello,

Thanks for the very detailed bug report. I will let you know about
further analyses as soon as possible

Cheers
Lukas

2012/10/3 JK <jk3...@gmail.com>:

[Lukas Eder]

I think this could be related to this issue here:
https://github.com/jOOQ/jOOQ/issues/1802

It looks as though the repetition of unqualified field names is
causing issues here. With the SELECT *, jOOQ will depend on what is
returned in ResultSetMetaData.

#1802 (similar, but not exactly the same issue) has been fixed and
included in the latest 2.6.0-SNAPSHOT version. Could you try to
reproduce it with that version?

Cheers
Lukas

2012/10/4 Lukas Eder <lukas...@gmail.com>:

[JK]

Hi

I can try it, but unfortunately not until next week.

Cheers,

Jouni

[Lukas Eder]

> I can try it, but unfortunately not until next week.

OK, don't worry though. I've checked, the mentioned fix was merged to
the recently published 2.5.1 as well, so the issue was related but
probably not the same

[Lukas Eder]

I'm tracking this issue as #1860:
https://github.com/jOOQ/jOOQ/issues/1860

[Lukas Eder]

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

[jk3m41l]

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

[Lukas Eder]

Hello,

Thanks for your detailed test case. The problem here is that there is
no way to distinguish two fields by name, if they have the exact same
name (as in org.jooq.Field.getName()). In case you do have such a
situation, you will have to access values by index. In other words,
your last test should become:

{
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(f); // This line was changed
System.out.println("row = " + r + " " +
field.getName() + " = " + value);
}
}
}

If you access fields by name (or field), and there are two fields with
the same name, jOOQ will return the first value.

Cheers
Lukas

2012/10/28 jk3m41l <jk3...@gmail.com>:

[Lukas Eder]

... Note that when using jOOQ APIs to fetch from plain SQL ResultSets,
jOOQ only consumes the ResultSetMetaData.getColumnLabel(int) for every
column. In order to better distinguish columns, I guess jOOQ could
also consume the column's catalog / schema / table names, although I'm
not sure if there is a general rule that would allow for a clean
distinction without corner-cases. E.g. column labels are important
when renaming columns (using aliasing), in case of which table names
usually become irrelevant. Also, functions behave differently from
table columns, etc.

I'm open to ideas

2012/10/28 Lukas Eder <lukas...@gmail.com>:

[jk3m41l]

Hi

I had a misunderstaning how the API works. I thought that if you get
field by position and then get the value with that field you will get
value of that position.
But anyway, with your proposed change results are now fine also when
using embeded SQL! Thank you!

Cheers,

JK