JOOQ alias construction

We are using JOOQ 3.7.2 and it seems that JOOQ computes different hashcodes for the same request if it contains different binding parameter values, and as a result, parts of the request have different aliases if executed with different inputs

In our code, we use the construct asTable

and the alias name is JOOQ, for example

public final Table<R> asTable() {
    return (new DerivedTable(this)).as("alias_" + Utils.hash(this));
}

      

        
        
        
      

    

and the hashcode is computed from org.jooq.impl.AbstractQueryPart

@Override
public int hashCode() {
    // This is a working default implementation. It should be overridden by
    // concrete subclasses, to improve performance

    return create().renderInlined(this).hashCode();
}

      

        
        
        
      

    

renderInlined(this)

is different for the same request with different input values. for example

where rownum = 1
    order by MYFIELD asc
  ) alias_132316169
) alias_55254251

      

        
        
        
      

    

and

where rownum = 2
    order by MYFIELD asc
  ) alias_117501160
) alias_82323306

      

        
        
        
      

    

The value to check for rownum equality is a binding variable

Is it possible to create the same alias code for a query with bind variables? Since JOOQ is now generating various aliases and this is the cause of the problem for the Oracle query cache

Or maybe another approach exists.

Example

Lets create a table

create table JOOQ_TEST (id number, val varchar2(100))

      

        
        
        
      

    

and run the following code

@Test
public void testSelect() throws Exception {
    List<Long> ids = new ArrayList<Long>();
    ids.add(1L);
    ids.add(2L);

    for (Long i : ids) {
        Table table = dsl.select(JOOQ_TEST.VAL, JOOQ_TEST.ID)
                .from(JOOQ_TEST)
                .where(JOOQ_TEST.ID.eq(i))
                .orderBy(JOOQ_TEST.ID).asTable();

        dsl.select().from(table).fetch();
    }
}

      

        
        
        
      

    

and we got

2017-04-28 17:37:58,235 DEBUG [main] org.jooq.tools.LoggerListener Executing query          : 
select 
  alias_116981037.VAL, 
  alias_116981037.ID
from (
  select 
    JOOQ_TEST.VAL, 
    JOOQ_TEST.ID
  from JOOQ_TEST
  where JOOQ_TEST.ID = ?
  order by JOOQ_TEST.ID asc
) alias_116981037
2017-04-28 17:37:58,236 DEBUG [main] org.jooq.tools.LoggerListener -> with bind values      : 
select 
  alias_116981037.VAL, 
  alias_116981037.ID
from (
  select 
    JOOQ_TEST.VAL, 
    JOOQ_TEST.ID
  from JOOQ_TEST
  where JOOQ_TEST.ID = 1
  order by JOOQ_TEST.ID asc
) alias_116981037
2017-04-28 17:37:58,593 DEBUG [main] org.jooq.tools.StopWatch Query executed           : Total: 361.112ms
2017-04-28 17:37:58,613 DEBUG [main] org.jooq.tools.LoggerListener Fetched result           : +----+----+
2017-04-28 17:37:58,613 DEBUG [main] org.jooq.tools.LoggerListener                          : |VAL |  ID|
2017-04-28 17:37:58,613 DEBUG [main] org.jooq.tools.LoggerListener                          : +----+----+
2017-04-28 17:37:58,613 DEBUG [main] org.jooq.tools.StopWatch Finishing                : Total: 381.65ms, +20.538ms
2017-04-28 17:37:58,614 DEBUG [main] org.jooq.tools.LoggerListener Executing query          : 
select 
  alias_4853230.VAL, 
  alias_4853230.ID
from (
  select 
    JOOQ_TEST.VAL, 
    JOOQ_TEST.ID
  from JOOQ_TEST
  where JOOQ_TEST.ID = ?
  order by JOOQ_TEST.ID asc
) alias_4853230
2017-04-28 17:37:58,615 DEBUG [main] org.jooq.tools.LoggerListener -> with bind values      : 
select 
  alias_4853230.VAL, 
  alias_4853230.ID
from (
  select 
    JOOQ_TEST.VAL, 
    JOOQ_TEST.ID
  from JOOQ_TEST
  where JOOQ_TEST.ID = 2
  order by JOOQ_TEST.ID asc
) alias_4853230
2017-04-28 17:37:58,633 DEBUG [main] org.jooq.tools.StopWatch Query executed           : Total: 18.899ms
2017-04-28 17:37:58,637 DEBUG [main] org.jooq.tools.LoggerListener Fetched result           : +----+----+
2017-04-28 17:37:58,638 DEBUG [main] org.jooq.tools.LoggerListener                          : |VAL |  ID|
2017-04-28 17:37:58,638 DEBUG [main] org.jooq.tools.LoggerListener                          : +----+----+
2017-04-28 17:37:58,638 DEBUG [main] org.jooq.tools.StopWatch Finishing                : Total: 23.931ms, +5.031ms