DB2 'WITH' clause in select statement

[martin...@csgi.com]

DB2 allows the specification of an 'isolation-clause' on the select statement.  Our DBAs has requested us to use this clause on many select statements so that DB2 won't escalate a lock to a table lock.  (These queries are mainly used to select data for some of our reports that we have to generate from a batch job).

I haven't figured out how to specify it with jOOQ.

Specifically, we have to add ' WITH UR' at the end of the sql statement  (UR = uncommittted read).

I am using a SelectQuery object to dynamically build the query.  Obviously I can get the SQL string when the query is build & append it myself; but I was hoping for a more elegant solution.

Would creating a custom QueryPart be the way to accomplish what i need?

[Lukas Eder]

Hello,

The DB2 isolation-clause is currently not supported in jOOQ. I have created a feature request for this:

https://github.com/jOOQ/jOOQ/issues/2537

I might not be able to integrate such a change any time soon, as I currently cannot integration test DB2 extensions on my Windows 8 machine.

Indeed, the most "elegant" solution for you to implement right now would be to create an ExecuteListener patching the rendered SQL statement on the renderEnd() event.

ExecuteListeners are documented here

- http://www.jooq.org/doc/3.0/manual/sql-execution/execute-listeners/

- http://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html

You could then hook this ExecuteListener into those Configurations that should generate this clause.

Cheers

Lukas

2013/6/21 <martin...@csgi.com>

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Lukas Eder]

As a matter of fact, I think that jOOQ should have a new, generally applicable feature that would allow for adding clauses at arbitrary positions in a SQL statement. Currently, jOOQ supports Oracle-style hints

    select(A, B, C)

    .hint("/*+ SOME_HINT */")

    .from(...)

And there are also plans of supporting SQL Server style options:

https://github.com/jOOQ/jOOQ/issues/1952

But in fact, what users really want is to be able to insert any clause that might not (yet) be supported by jOOQ into any statement. E.g.

    select(A, B, C)

    .sql("/*+ SOME_HINT */")

    .from(...)

    .sql("/* just a regular comment */")

    .sql("AND SOME SQL")

This would then allow you to add the required DB2-specific isolation clauses to your statements

    select(A, B, C)

    .from(...)

    .where(...)

    .sql("WITH UR")

    .fetch();

I have registered #2541 for this:

https://github.com/jOOQ/jOOQ/issues/2541

Cheers

Lukas

2013/6/21 Lukas Eder <lukas...@gmail.com>

[Durchholz, Joachim]

> As a matter of fact, I think that jOOQ should have a
> new, generally applicable feature that would allow
> for adding clauses at arbitrary positions in a SQL
> statement.

+1

Another use case: Adding comments.
I found them VERY helpful when identifying a query in the stream of SQL that my app spews out.
Also, I'm generating views. These are used by third parties, and I've been making a point of documenting them to people can see what the view does. It would be cool if I could do that via Jooq-generated views, too.
(Not sure whether comments are already in the DSL. Or whether making them part of this mechanism is a good idea in the first place - it's possible that they need to be handled specially.)

[Lukas Eder]

2013/6/24 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

> As a matter of fact, I think that jOOQ should have a
> new, generally applicable feature that would allow
> for adding clauses at arbitrary positions in a SQL
> statement.

+1

 

Let's see how this goes. A first attempt of implementing this failed as I have a choice between:

 

- Letting the API explode completely (including sql() methods before/after AND, OR, IN, NOT, CASE, WHEN, THEN, etc. etc.

- Offering only few sql() methods e.g. before or after the SELECT statement keywords, in case of which the usefulness of this feature is dubious...

 

Another use case: Adding comments.
I found them VERY helpful when identifying a query in the stream of SQL that my app spews out.

 

Yes, that's useful for various reasons. One of them is to enforce a hard-parse in the database (at least in Oracle), and thus a new cursor and a new execution plan. This can help prevent bind-value peeking issues.

 

jOOQ already supports a .hint() method, mainly aimed at Oracle users who want to inject query hints:

 

select(a, b).hint("/*+ALL_ROWS*/").from(t)

 

You can use this for other purposes than hints of course.

 

Also, I'm generating views. These are used by third parties, and I've been making a point of documenting them to people can see what the view does. It would be cool if I could do that via Jooq-generated views, too.

 

What do you consider a "jOOQ-generated view"? 

 

(Not sure whether comments are already in the DSL. Or whether making them part of this mechanism is a good idea in the first place - it's possible that they need to be handled specially.)

 

 As with any type of plain SQL, you're on your own. You'll risk to run into syntax errors and SQL injection.

 

Cheers

Lukas

[Durchholz, Joachim]

> Let's see how this goes. A first attempt of implementing
> this failed as I have a choice between:
>
> - Letting the API explode completely (including sql()
> methods before/after AND, OR, IN, NOT, CASE, WHEN, THEN,
> etc. etc.
> - Offering only few sql() methods e.g. before or after
> the SELECT statement keywords, in case of which the
> usefulness of this feature is dubious...

Can't sql() be added to the superclass of all these SQL-generating classes? You'd need to play some tricks with generics to make that type-safe, but the technique for that is well-established nowadays (I've been playing with this enough to use it routinely, so I'm ready to answer questions on that).
Its semantics would be to append the given string to the generated SQL. No guarantees about syntactical validity.

Plays havoc with any formatting that the SQL generators might attempt.
Also, runs into some trouble whenever SQL generation looks at multiple AST nodes. (Does it?)

> jOOQ already supports a .hint() method, mainly
> aimed at Oracle users who want to inject query hints:
>
> select(a, b).hint("/*+ALL_ROWS*/").from(t)
>
> You can use this for other purposes than hints of course.

Ah yes, right, thanks for the reminder.

>> Also, I'm generating views. These are used by third
>> parties, and I've been making a point of documenting
>> them to people can see what the view does.
>> It would be cool if I could do that via Jooq-generated
>> views, too.
>
> What do you consider a "jOOQ-generated view"?

I'm currently generating some overly complicated but regular views from Java. That keeps the Java and Oracle side of things in sync, the software simply has a maintenance mode where it generates the view definitions, ready to be pasted into an SQL command line.
Doing the SQL generation from Jooq would probably simplify the code.

> As with any type of plain SQL, you're on your own.
> You'll risk to run into syntax errors and SQL injection.

Heh. Fortunately, that type of SQL generation does not process external input, just hardcoded definitions.

[Lukas Eder]

2013/6/25 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

> Let's see how this goes. A first attempt of implementing
> this failed as I have a choice between:
 >
> - Letting the API explode completely (including sql()
> methods before/after AND, OR, IN, NOT, CASE, WHEN, THEN,
> etc. etc.
> - Offering only few sql() methods e.g. before or after
> the SELECT statement keywords, in case of which the
> usefulness of this feature is dubious...

Can't sql() be added to the superclass of all these SQL-generating classes? You'd need to play some tricks with generics to make that type-safe, but the technique for that is well-established nowadays (I've been playing with this enough to use it routinely, so I'm ready to answer questions on that).
Its semantics would be to append the given string to the generated SQL. No guarantees about syntactical validity.

Plays havoc with any formatting that the SQL generators might attempt.
Also, runs into some trouble whenever SQL generation looks at multiple AST nodes. (Does it?)

 

Yes, it would certainly be possible to add a sql() method to a top-level type, e.g. QueryPart. But the huge drawback of this is that the whole QueryPart type tree would need to be generified to allow for:

 

interface QueryPart<Q extends QueryPart> {

    Q sql(String sql);

}

 

Q is necessary to allow for fluent injection of SQL. We'd probably have to check whether appending or prepending is more useful (or both?). In the context of a SELECT statement, it could only be "appending". But the slim added value clearly doesn't pull the weight of the new generic type parameter. Think about Field:

 

interface Field<T, Q extends QueryPart> extend QueryPart<Q> {

}

 

interface TableField<R extends Record, T, Q extends QueryPart> extends Field<T, Q> {

}

 

That would be a very "unfriendly" addition for the end-user :-)

 

> jOOQ already supports a .hint() method, mainly
> aimed at Oracle users who want to inject query hints:
>
> select(a, b).hint("/*+ALL_ROWS*/").from(t)
>
> You can use this for other purposes than hints of course.

Ah yes, right, thanks for the reminder.

>> Also, I'm generating views. These are used by third
>> parties, and I've been making a point of documenting
>> them to people can see what the view does.
>> It would be cool if I could do that via Jooq-generated
>> views, too.
>
> What do you consider a "jOOQ-generated view"?

I'm currently generating some overly complicated but regular views from Java. That keeps the Java and Oracle side of things in sync, the software simply has a maintenance mode where it generates the view definitions, ready to be pasted into an SQL command line.
Doing the SQL generation from Jooq would probably simplify the code.

 

Should be simple:

 

// Construct your statement:

Select<?> select = //...

 

// Render it with inlined bind values:

String sql = DSL

.using((Connection) null, dialect, new Settings()

            .withStatementType(STATIC_STATEMENT)

            .withRenderFormatted(true) // possibly?

).render(select);

 

String view = "CREATE OR REPLACE VIEW my_view AS " + sql;

 

 

> As with any type of plain SQL, you're on your own.
> You'll risk to run into syntax errors and SQL injection.

Heh. Fortunately, that type of SQL generation does not process external input, just hardcoded definitions.

[Durchholz, Joachim]

> Yes, it would certainly be possible to add a sql()
> method to a top-level type, e.g. QueryPart. But
> the huge drawback of this is that the whole
> QueryPart type tree would need to be generified to
> allow for:
>
> interface QueryPart<Q extends QueryPart> {
> Q sql(String sql);
> }

That's indeed necessary.
Pervase change: Yes.
Huge Problem? I don't see that.

> Q is necessary to allow for fluent injection of SQL.

Ack.

> We'd probably have to check whether appending or
> prepending is more useful (or both?).

For comments, appending would be enough.
Other use cases that I can see off the top of my head are appending, too, so that is the default case.

However, to deal with the odd "but I need something prepended" case, I'd offer a variant of sql() that wraps the SQL between a prefix and a postfix. sql(String) would then be

Q sql(String after) {
return sql("", after);
}
abstract Q sql(String before, after);

> In the context of a SELECT statement, it could only
> be "appending".

A use case for wrapping over appending could be that the programmer wants to wrap an expression or select in a subselect, using database-specific functionality that Jooq doesn't support yet.

> But the slim added value clearly doesn't pull the weight
> of the new generic type parameter.

Ehm... replacing all of hint() and similar extension points with a single, uniform-semantics function isn't "slim" in my book.
It's taking the pressure out of "this database-specific isn't implemented yet", which is a huge bonus for programmers who live with a not-yet-well-supported-by-Jooq database.

> Think about Field:
>
> interface Field<T, Q extends QueryPart> extend QueryPart<Q> {
> }
>
> interface TableField<R extends Record, T, Q extends QueryPart> extends Field<T, Q> {
> }

Maybe more like

interface Field<T, Q extends QueryPart<Q>> extends QueryPart<Q>
interface TableField<R extends Record, T, Q extends QueryPart<Q>> extends Field<T, Q>

> That would be a very "unfriendly" addition for the end-user :-)

Yes, the dependent-typing syntax of Java sucks. Mainly because you can't name type subexpressions, so it gets repetitive ad nauseam.
For application programmers implementing these interfaces, it's essentially still

interface Field<T, Q>
interface TableField<R, T, Q>

With the additional constraint that Q must be a QueryPart - that's doable for an application programmer.

Maybe a paragraph "reading generic declarations" in the Jooq docs is in order, saying that
a) One should read a generic declaration twice
b) First pass: just collect the type names (R, T, Q for TableField)
c) Second pass: for each type, read its constraint
d) Just fill all type parameters you need filled. Something that implements QueryPart<String> won't work, but Jooq doesn't declare classes with that kind of nonsensical definition (nor would Java allow the existence of such a class).

These interfaces are actually quite straightforward. The Q extends QueryPart<Q> thing is a bit hairy because it's circular, but then application programmers aren't supposed to write subclasses of QueryPart.

Mastering generics at that level is a bit of a wild ride I'll admit. I did a bit of that recently and don't find it THAT esoteric anymore. It would be an experiment I guess, and it could fail.

> Should be simple:
>
> // Construct your statement:
> Select<?> select = //...
>
> // Render it with inlined bind values:
> String sql = DSL
> .using((Connection) null, dialect, new Settings()
> .withStatementType(STATIC_STATEMENT)
> .withRenderFormatted(true) // possibly?
> ).render(select);
>
> String view = "CREATE OR REPLACE VIEW my_view AS " + sql;

Yes.
The point was that I'd want to insert comments, sometimes at the field level.

Here's an example:

CREATE OR REPLACE FORCE VIEW HF_LAGERMENGEN AS
select
-- Used in:
-- <modules that need to be updated if this view changes>
fi_nr,
lgnr,
identnr,
lamenge,
-- nondispositive (<some more explanations>)
nd,
-- reserved (not in arrival)
nvl (res, 0) as res,
-- in arrival (not reserved)
arr - nvl (res_arr, 0) as arr,
-- reserved, in arrival
nvl (res_arr, 0) as res_arr
from
... etc. pp. ...

I have other views where the join conditions have comments. Or where the selection of tables that got joined warrants an explanation.

Heh. Here's an idea to toss around:
For a single-database developer, Jooq would become even more useful if he could simply plop in some SQL as string constant, using Jooq just to generate those parts that are variable. He'd use strings where the SQL is constant, and build AST fragments where he needs to be variable. It would take the syntactic pain out of generated SQL, without imposing the somewhat higher verbosity of the DSL on those parts that could simply be written in SQL directly.

[Lukas Eder]

2013/6/26 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

> Yes, it would certainly be possible to add a sql()
> method to a top-level type, e.g. QueryPart. But
> the huge drawback of this is that the whole
> QueryPart type tree would need to be generified to
> allow for:
>
> interface QueryPart<Q extends QueryPart> {
>     Q sql(String sql);
> }

That's indeed necessary.
Pervase change: Yes.
Huge Problem? I don't see that.

 

I'll get to that...

 

> Q is necessary to allow for fluent injection of SQL.

Ack.

> We'd probably have to check whether appending or
> prepending is more useful (or both?).

For comments, appending would be enough.
Other use cases that I can see off the top of my head are appending, too, so that is the default case.

However, to deal with the odd "but I need something prepended" case, I'd offer a variant of sql() that wraps the SQL between a prefix and a postfix. sql(String) would then be

Q sql(String after) {
  return sql("", after);
}
abstract Q sql(String before, after);

 

I had thought about this at first, but the meaning of "before" is very ambiguous. Consider:

 

SelectFromStep s1 = DSL.select(a, b);

SelectWhereStep s2 = DSL.select(a, b);

 

We know that the SELECT clause has already been added. Now we have APIs to append FROM and/or WHERE clauses. Since the above assignments have no effect on the actual implementation, sql(...) would clearly add stuff immediately after the SELECT clause, i.e. after the previously added clause. From a fluent point of view, this makes sense, as the following two would intuitively be the same:

 

s1.sql("/* some comment */").where(...)

s2.sql("/* some comment */").where(...)

 

But "before"? Is it before the SELECT keyword? How would you know the semantics of "before" given only the type of s2?

 

This shows that before/after injection is not useful in more complex QueryPart chains.

 

> In the context of a SELECT statement, it could only
> be "appending".

A use case for wrapping over appending could be that the programmer wants to wrap an expression or select in a subselect, using database-specific functionality that Jooq doesn't support yet.

 

In my opinion, jOOQ offers enough capabilities of wrapping tables, fields, selects in plain SQL or custom QueryParts. Example:

 

Field<?> f = DSL.field("/* custom stuff */ {0} /* surrounding... */", MY_EXPRESSION);

 

> But the slim added value clearly doesn't pull the weight
> of the new generic type parameter.

Ehm... replacing all of hint() and similar extension points with a single, uniform-semantics function isn't "slim" in my book.
It's taking the pressure out of "this database-specific isn't implemented yet", which is a huge bonus for programmers who live with a not-yet-well-supported-by-Jooq database.

 

Feel free to list 1-2 examples from your book, adding "huge bonuses" (which aren't covered by existing API). And, no, I don't consider putting hundreds of comments into your SQL statement a huge bonus. You can comment your jOOQ SQL statement in Java, already :-)

 

// here we create that odd DSLContext

DSL.using(...)

// let's project stuff

   .select(

// Oh, and the ID is important

      MY_TABLE.ID

// And this needs aliasing bla bla

      MY_TABLE.TITLE.as("x")

   )...

 

> Think about Field:
>
> interface Field<T, Q extends QueryPart> extend QueryPart<Q> {
> }
>
> interface TableField<R extends Record, T, Q extends QueryPart> extends Field<T, Q> {
> }

Maybe more like

interface Field<T, Q extends QueryPart<Q>> extends QueryPart<Q>
interface TableField<R extends Record, T, Q extends QueryPart<Q>> extends Field<T, Q>

 

You're right, minor glitch on my side.

 

> That would be a very "unfriendly" addition for the end-user :-)

Yes, the dependent-typing syntax of Java sucks. Mainly because you can't name type subexpressions, so it gets repetitive ad nauseam.
For application programmers implementing these interfaces, it's essentially still

interface Field<T, Q>
interface TableField<R, T, Q>

With the additional constraint that Q must be a QueryPart - that's doable for an application programmer.

 

Let's review actual use-site types. Today, I can write:

 

Field<Integer> i = DSL.val(1);

 

This would become...

 

Field<Integer, ?> i1; // if you're lazy

Field<Integer, ? extends Field<Integer, ?>> i2;

 

So many generics for so little effect? I'd rather not :-) 

And there's no concrete type to bind Q to. You're always stuck with wildcards, unless you subtype Field yourself

 

Maybe a paragraph "reading generic declarations" in the Jooq docs is in order, saying that
a) One should read a generic declaration twice
b) First pass: just collect the type names (R, T, Q for TableField)
c) Second pass: for each type, read its constraint
d) Just fill all type parameters you need filled. Something that implements QueryPart<String> won't work, but Jooq doesn't declare classes with that kind of nonsensical definition (nor would Java allow the existence of such a class).

These interfaces are actually quite straightforward. The Q extends QueryPart<Q> thing is a bit hairy because it's circular, but then application programmers aren't supposed to write subclasses of QueryPart.

Mastering generics at that level is a bit of a wild ride I'll admit. I did a bit of that recently and don't find it THAT esoteric anymore. It would be an experiment I guess, and it could fail.

 

I won't keep you from doing an experiment ;-)

 

My take is: I don't want to unleash unnecessary generic typing upon my users. It is easy to understand Field<T>. It is OK to understand Select<R extends Record>. It is hard to understand TableField<R extends Record, T, Q extends QueryPart<Q>>.

 

During my jOOQ developments, I've discovered and reported a couple of bugs to both javac and Eclipse compilers. I found no one on Stack Overflow who could prove that one or the other (or even the specs) was wrong in the odd corner case:

http://stackoverflow.com/q/5361513/521799

 

I personall have no clue what the true problem really was, according to the JLS

 

Java generics are not easy. Some may understand them to a certain extent. But getting them right in an API is challenging, in my opinion. Getting them right in a DSL is extremely tough, as varargs, overloading and generics aren't best friends. There are lots of blunders in the jOOQ API, regarding generics. Adding <Q extends QueryPart<Q>> risks being another one. Adding it must be considered extremely carefully. But my gut feeling clearly says: little value addition, huge impact.

 

> Should be simple:
>
> // Construct your statement:
> Select<?> select = //...
>
> // Render it with inlined bind values:
> String sql = DSL
> .using((Connection) null, dialect, new Settings()
>             .withStatementType(STATIC_STATEMENT)
>             .withRenderFormatted(true) // possibly?
> ).render(select);
>
> String view = "CREATE OR REPLACE VIEW my_view AS " + sql;

Yes.
The point was that I'd want to insert comments, sometimes at the field level.

 

DSL.field("/* comment before */ {0} \n--comment after\n", MY_FIELD);

 

Here's an example:

CREATE OR REPLACE FORCE VIEW HF_LAGERMENGEN AS
select
  -- Used in:
  --   <modules that need to be updated if this view changes>
  fi_nr,
  lgnr,
  identnr,
  lamenge,
  -- nondispositive (<some more explanations>)
  nd,
  -- reserved (not in arrival)
  nvl (res, 0) as res,
  -- in arrival (not reserved)
  arr - nvl (res_arr, 0) as arr,
  -- reserved, in arrival
  nvl (res_arr, 0) as res_arr
from
  ... etc. pp. ...

I have other views where the join conditions have comments. Or where the selection of tables that got joined warrants an explanation.

 

I understand this use-case, but I fear that using sql() methods might make this statement slightly unmaintainable on the Java side. And if this is a use-case, a custom Field implementation adding comments to the projection might not be too tough.

 

Heh. Here's an idea to toss around:
For a single-database developer, Jooq would become even more useful if he could simply plop in some SQL as string constant, using Jooq just to generate those parts that are variable. He'd use strings where the SQL is constant, and build AST fragments where he needs to be variable. It would take the syntactic pain out of generated SQL, without imposing the somewhat higher verbosity of the DSL on those parts that could simply be written in SQL directly.

 

But you can do that already...? Just create your

 

DSL.using(...).resultQuery(

    "SELECT a, b, /* complex field ahead */ {0} " +

    "FROM t JOIN {1} /* and s jOOQ table */",

    myField, myTable

).fetch();

[Durchholz, Joachim]

> I had thought about this at first, but the meaning
> of "before" is very ambiguous. Consider:
>
> SelectFromStep s1 = DSL.select(a, b);
> SelectWhereStep s2 = DSL.select(a, b);
>
> We know that the SELECT clause has already been added.
>
> Now we have APIs to append FROM and/or WHERE clauses.
> Since the above assignments have no effect on the actual
> implementation, sql(...) would clearly add stuff
> immediately after the SELECT clause, i.e. after the
> previously added clause.

I'm having trouble relating to "before" and "after" here.
Is this an implementation issue? Then before/after make sense, but I can't comment, just accept that reworking the SQL generator would be too much work (which I could well understand).

From a specification perspective, the DSL just specifies an AST.
In that sense, "before" and "after" don't make sense, not as a description of when things are done anyway, but I don't quite understand what you're describing here.

> From a fluent point of view,
> this makes sense, as the following two would intuitively be the same:
>
> s1.sql("/* some comment */").where(...)
> s2.sql("/* some comment */").where(...)
>
> But "before"? Is it before the SELECT keyword? How would
> you know the semantics of "before" given only the type of s2?

You don't know it with "after" either.
Imagine s1.sql("ORDER BY foo"). That's not a Condition anymore.

> This shows that before/after injection is not useful in more
> complex QueryPart chains.

It shows that it's losing some desirable properties.
Assuming proper warnings in the docs, that's acceptable I think.

>> A use case for wrapping over appending could be that the
>> programmer wants to wrap an expression or select in a subselect,
>> using database-specific functionality that Jooq doesn't support yet.
>
> In my opinion, jOOQ offers enough capabilities of wrapping
> tables, fields, selects in plain SQL or custom QueryParts.

I'm just seeing an opportunity to replace a rather large mixture of ad-hoc customization features with a single, semantically straightforward feature. Anything that makes the API narrower without restricting expressiveness is a net win for new code.
It's possible that sql(l, r) isn't as expressive, or that it has other undesirable side effects, and shouldn't be done. Ultimately, it's a judgement call, and I certainly don't have the whole picture here.

>> But the slim added value clearly doesn't pull the weight
>> of the new generic type parameter.
>
> Ehm... replacing all of hint() and similar extension points
> with a single, uniform-semantics function isn't "slim" in my
> book.
> It's taking the pressure out of "this database-specific
> isn't implemented yet", which is a huge bonus for
> programmers who live with a not-yet-well-supported-by-Jooq
> database.
>
> Feel free to list 1-2 examples from your book, adding
> "huge bonuses" (which aren't covered by existing API).

Anything not already in Jooq.
There was that guy who recently wanted to leverage Jooq for his almost-SQL database; I suspect he could get a stopgap solution up and running far easier if he could simply use whatever non-SQL contortion his database wants without having to hack Jooq itself.
Imagine Sqlite introducing something that's similar to windowed functions, but requires some extra keywords in outlandish places that Jooq doesn't cover (yet).

> And, no, I don't consider putting hundreds of comments into
> your SQL statement a huge bonus. You can comment your jOOQ
> SQL statement in Java, already :-)

The issue with views is that their sources get read by people who know and do SQL, not Java.
They'd have trouble even finding the class source that generates the SQL. They'd be unable to interpret the Jooq calls that code would be doing. They'd be unable to do any data flow analysis if the SQL generation is spread over several functions.
That's not an option. And yes the ability to add comments at the point where the code decides to include the code-requiring SQL would be a real enabler.

>> Think about Field:
>>
>> interface Field<T, Q extends QueryPart> extend QueryPart<Q> {
>> }
>>
>> interface TableField<R extends Record, T, Q extends QueryPart> extends Field<T, Q> {
>> }
>
>
> Maybe more like
>
> interface Field<T, Q extends QueryPart<Q>> extends QueryPart<Q>
> interface TableField<R extends Record, T, Q extends QueryPart<Q>> extends Field<T, Q>
>
> You're right, minor glitch on my side.

Thought so :-)

> Let's review actual use-site types. Today, I can write:
>
> Field<Integer> i = DSL.val(1);
>
> This would become...
>
> Field<Integer, ?> i1; // if you're lazy
> Field<Integer, ? extends Field<Integer, ?>> i2;
>
> So many generics for so little effect? I'd rather not :-)

One could do

interface Field<T> extends QueryPart<Field<T>>

This works that the subclasses of Field do not need to vary the return type of sql().

> And there's no concrete type to bind Q to.
> You're always stuck with wildcards,
> unless you subtype Field yourself

Always having a wildcard can point to something that could be redesigned to avoid that type parameter.
I'm not sure how the use case for that would look like so I don't know whether that applies in this particular case.

>> Mastering generics at that level is a bit of a wild
>> ride I'll admit. I did a bit of that recently and
>> don't find it THAT esoteric anymore. It would be an
>> experiment I guess, and it could fail.
>
> I won't keep you from doing an experiment ;-)

I'd have to get reasonably intimate with the Jooq codebase first, which isn't going to happen this year :-(

> My take is: I don't want to unleash unnecessary
> generic typing upon my users.

"Unnecessary" is an overly broad term here.

> It is easy to understand Field<T>.

Yup.

> It is OK to understand Select<R extends Record>.

That already depends on your level of generics expertise.

> It is hard to understand
> TableField<R extends Record, T, Q extends QueryPart<Q>>.

It's just as hard as grokking Comparable<T extends Comparable<T>>.

> During my jOOQ developments, I've discovered and reported
> a couple of bugs to both javac and Eclipse compilers.
> I found no one on Stack Overflow who could prove that one
> or the other (or even the specs) was wrong in the odd corner
> case: http://stackoverflow.com/q/5361513/521799

I doubt that there's even a good semantics for overloading if generics come into play.
Similar issues exist with
func(Object... o)
func(long... o)
and pass in an array of longs. Java has disambiguation rules for that, but that's got a decidedly PL/I feel to it - it's an ambiguity that most code maintainers won't even spot because definition and call are so far apart, and even if they spot it they usually won't know the disambiguation rule.

> I personall have no clue what the true problem really was,

From a language designer's point of view, combining multiple methods into the same signature is always messy. Java now has three: Run-time polymorphism, overloading, and generics, plus language design space restrictions introduced by type erasure.
Such a mess always requires disambiguation, and disambiguation tends to have counterintuitive results - that's what I meant with "PL/I feel" above.

> Java generics are not easy. Some may understand them to a
> certain extent. But getting them right in an API is
> challenging, in my opinion. Getting them right in a DSL is
> extremely tough, as varargs, overloading and generics
> aren't best friends.

Totally agree with that.

I have stopped using varargs in general, and have made all my varargs functions into Collection ones:
+ No more design hassles if I suddenly need two varargs parameters.
+ No more explosion of API signatures to cover all of Integer..., Collection<Integer>, Iterator<Integer>, and whatnot.
+ No more warnings for creating a generic varargs array.
- Programmers need to call Arrays.asList(T...) for what was previously a varargs call.
* If they feel that's uncomfortable, they can always import asList statically. Or wrap it in a function with a one-letter name (I once defined an l(Object...), but I removed it because I didn't think it was worth the extra indirection for code readers).

> There are lots of blunders in the jOOQ API, regarding generics.
> Adding <Q extends QueryPart<Q>> risks being another one.

Agreed.

> Adding it must be considered extremely carefully.
> But my gut feeling clearly says: little value addition, huge impact.

I think it's a considerable value addition.
Impact can't be eliminated, but it can be mitigated.
My gut feeling is that the effort-to-effect ratio isn't overwhelming but far more favorable than your initial assessment indicates. But then I don't think my guts have much say in this :-)

>> Here's an example:
>>
>> CREATE OR REPLACE FORCE VIEW HF_LAGERMENGEN AS
>> select
>> -- Used in:
>> -- <modules that need to be updated if this view changes>
>> fi_nr,
>> lgnr,
>> identnr,
>> lamenge,
>> -- nondispositive (<some more explanations>)
>> nd,
>> -- reserved (not in arrival)
>> nvl (res, 0) as res,
>> -- in arrival (not reserved)
>> arr - nvl (res_arr, 0) as arr,
>> -- reserved, in arrival
>> nvl (res_arr, 0) as res_arr
>> from
>> ... etc. pp. ...
>

> I understand this use-case, but I fear that using sql() methods
> might make this statement slightly unmaintainable on the Java side.

How that?
It should be something like
.projection("nd").sql("\n-- nondispositive")

> Heh. Here's an idea to toss around:
> For a single-database developer, Jooq would become
> even more useful if he could simply plop in some SQL as
> string constant, using Jooq just to generate those parts
> that are variable. He'd use strings where the SQL is
> constant, and build AST fragments where he needs to be
> variable. It would take the syntactic pain out of
> generated SQL, without imposing the somewhat higher
> verbosity of the DSL on those parts that could simply be
> written in SQL directly.
>
> But you can do that already...?

At arbitrary points in the AST?
If that's the case, you don't need sql(), you have it already.

> Just create your
>
> DSL.using(...).resultQuery(
> "SELECT a, b, /* complex field ahead */ {0} " +
> "FROM t JOIN {1} /* and s jOOQ table */",
> myField, myTable
> ).fetch();

This can insert DSL-generated SQL into a string, but not vice versa.

[Lukas Eder]

2013/6/26 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

> I had thought about this at first, but the meaning
> of "before" is very ambiguous. Consider:
>
> SelectFromStep s1 = DSL.select(a, b);
> SelectWhereStep s2 = DSL.select(a, b);
>
> We know that the SELECT clause has already been added.
>
> Now we have APIs to append FROM and/or WHERE clauses.
> Since the above assignments have no effect on the actual
> implementation, sql(...) would clearly add stuff
> immediately after the SELECT clause, i.e. after the
> previously added clause.

I'm having trouble relating to "before" and "after" here.
Is this an implementation issue? Then before/after make sense, but I can't comment, just accept that reworking the SQL generator would be too much work (which I could well understand).

From a specification perspective, the DSL just specifies an AST.
In that sense, "before" and "after" don't make sense, not as a description of when things are done anyway, but I don't quite understand what you're describing here.

Me neither. I'm lost on that part of the conversation :-) Let's just focus on XX.sql(), which will add SQL at the "intuitive" location, i.e. precisely at its "lexicographical" location within the DSL method chain.

Again. In most cases, you don't want some obscure .sql() method just "anywhere". In most cases, you want one of the pre-existing plain SQL methods that return a well-defined type. These are mostly Table, Field, Condition.

I agree, if you want to add a *clause* to a jOOQ statement, sql() would be the only way. So I'm looking for real-world examples. Note that Tim Webster, who requested to add support for CMIS in jOOQ [1] could still use the existing plain SQL construction methods for his missing functions. I don't see any "clause" that would not be possible to implement with the existing API [2].

Of course, the Oracle MODEL clause would be a real-world example [3], as I don't see myself adding support for that crazy monster any time soon ;-)

[1]: https://groups.google.com/forum/#!topic/jooq-user/af-5m56Y3dI

[2]: http://wiki.alfresco.com/wiki/CMIS_Query_Language#Query_structure

[3]: http://docs.oracle.com/cd/B28359_01/server.111/b28313/sqlmodel.htm

 

> And, no, I don't consider putting hundreds of comments into
> your SQL statement a huge bonus. You can comment your jOOQ
> SQL statement in Java, already :-)

The issue with views is that their sources get read by people who know and do SQL, not Java.
They'd have trouble even finding the class source that generates the SQL. They'd be unable to interpret the Jooq calls that code would be doing. They'd be unable to do any data flow analysis if the SQL generation is spread over several functions.
That's not an option. And yes the ability to add comments at the point where the code decides to include the code-requiring SQL would be a real enabler.

I understand the idea.

 

>> Think about Field:
>>
>> interface Field<T, Q extends QueryPart> extend QueryPart<Q> {
>> }
>>
>> interface TableField<R extends Record, T, Q extends QueryPart> extends Field<T, Q> {
>> }
>
>
> Maybe more like
>
> interface Field<T, Q extends QueryPart<Q>> extends QueryPart<Q>
> interface TableField<R extends Record, T, Q extends QueryPart<Q>> extends Field<T, Q>
>
> You're right, minor glitch on my side.

Thought so :-)

> Let's review actual use-site types. Today, I can write:
>
> Field<Integer> i = DSL.val(1);
>
> This would become...
>
> Field<Integer, ?> i1; // if you're lazy
> Field<Integer, ? extends Field<Integer, ?>> i2;
>
> So many generics for so little effect? I'd rather not :-)

One could do

  interface Field<T> extends QueryPart<Field<T>>

This works that the subclasses of Field do not need to vary the return type of sql().

I think that this is a common mistake in OO design. Or let's call it a caveat of Java recursive generics usage. Let's take Field<T>. It is subtyped as such:

interface WindowPartitionByStep<T>

extends WindowOrderByStep<T>

extends WindowFinalStep<T>

extends Field<T>

Inheritance is how "optional" DSL keywords are modelled in jOOQ [4]. This design (i.e. using inheritance) was challenged by Christoper Deckers in this thread [5]. He proposed an improvement that could do without so much inheritance, but that improvement won't be feasible before the new DSL API code generator project is implemented [6]. Back to your example. If you write

    Field<T> extends QueryPart<Field<T>> 

instead of 

    Field<T, Q extends Field<T, Q>> extends QueryPart<Q>

You would no longer be able to write:

    // WindowPartitionByStep<Integer>:

    rowNumber().over()

    // WindowOrderByStep<Integer>:

        .partitionBy(FOO)

    // Field<Integer> !! Instead of the more correct but horrible

    // WindowOrderByStep<T, Q extends WindowOrderByStep<T, Q>>

        .sql("blah")

    // Ouch. Doesn't compile anymore:

        .orderBy(BAR);

I will soon blog about this common caveat when mixing subtype polymorphism with generic polymorphism on the same type. It's a very subtle caveat, but the only type in the type hierarchy that is "allowed" to terminate the generic type recursion is the "lowest" type, which should be a final class! A good example for this is the java.lang.Enum class, whose "recursion-terminating" types are final

[4]: blog.jooq.org/2012/01/05/the-java-fluent-api-designer-crash-course

[5]: https://groups.google.com/forum/#!msg/jooq-user/jJvZEkb403A/WjTps2GkmvEJ

[6]: https://groups.google.com/forum/#!topic/jooq-user/mLcxnwvglf0

 

> And there's no concrete type to bind Q to.
> You're always stuck with wildcards,
> unless you subtype Field yourself

Always having a wildcard can point to something that could be redesigned to avoid that type parameter.
I'm not sure how the use case for that would look like so I don't know whether that applies in this particular case.

>> Mastering generics at that level is a bit of a wild
>> ride I'll admit. I did a bit of that recently and
>> don't find it THAT esoteric anymore. It would be an
>> experiment I guess, and it could fail.
>
> I won't keep you from doing an experiment ;-)

I'd have to get reasonably intimate with the Jooq codebase first, which isn't going to happen this year :-(

> My take is: I don't want to unleash unnecessary
> generic typing upon my users.

"Unnecessary" is an overly broad term here.

That's my gut feeling :-)

 

> It is easy to understand Field<T>.

Yup.

> It is OK to understand Select<R extends Record>.

That already depends on your level of generics expertise.

> It is hard to understand
> TableField<R extends Record, T, Q extends QueryPart<Q>>.

It's just as hard as grokking Comparable<T extends Comparable<T>>.

Wonderful example! Another interesting blog topic where generics went wrong. :-)

The subtle difference here is the fact that T in Comparable<T> itself is not recursive [7], although it might make sense. I'll have to do some more research on that topic to explain how this type is a clear invitation to shoot yourself in the foot. A good preview of what I mean is

    com.google.common.collect.Cut<C extends Comparable>

With a raw type Comparable (!)

[7] http://docs.oracle.com/javase/7/docs/api/java/lang/Comparable.html

 

> During my jOOQ developments, I've discovered and reported
> a couple of bugs to both javac and Eclipse compilers.
> I found no one on Stack Overflow who could prove that one
> or the other (or even the specs) was wrong in the odd corner
> case: http://stackoverflow.com/q/5361513/521799

I doubt that there's even a good semantics for overloading if generics come into play.
Similar issues exist with
  func(Object... o)
  func(long... o)
and pass in an array of longs. Java has disambiguation rules for that, but that's got a decidedly PL/I feel to it - it's an ambiguity that most code maintainers won't even spot because definition and call are so far apart, and even if they spot it they usually won't know the disambiguation rule.

Yes, I've made that mistake. Object... is an obvious pitfall. T... is more subtle, if T is not constrained to something "below" Object

 

> I personall have no clue what the true problem really was,

From a language designer's point of view, combining multiple methods into the same signature is always messy. Java now has three: Run-time polymorphism, overloading, and generics, plus language design space restrictions introduced by type erasure.
Such a mess always requires disambiguation, and disambiguation tends to have counterintuitive results - that's what I meant with "PL/I feel" above.

That's the easy way out, accepting the facts of life :-)

 

> Java generics are not easy. Some may understand them to a
> certain extent. But getting them right in an API is
> challenging, in my opinion. Getting them right in a DSL is
> extremely tough, as varargs, overloading and generics
> aren't best friends.

Totally agree with that.

I have stopped using varargs in general, and have made all my varargs functions into Collection ones:
+ No more design hassles if I suddenly need two varargs parameters.

Oh well ;-)

 

+ No more explosion of API signatures to cover all of Integer..., Collection<Integer>, Iterator<Integer>, and whatnot.

Try Ceylon's union types.

 

+ No more warnings for creating a generic varargs array.

Ask Steven Colebourne about that one. He'll revive all his anger.

 

- Programmers need to call Arrays.asList(T...) for what was previously a varargs call.
* If they feel that's uncomfortable, they can always import asList statically. Or wrap it in a function with a one-letter name (I once defined an l(Object...), but I removed it because I didn't think it was worth the extra indirection for code readers).

> There are lots of blunders in the jOOQ API, regarding generics.
> Adding <Q extends QueryPart<Q>> risks being another one.

Agreed.

> Adding it must be considered extremely carefully.
> But my gut feeling clearly says: little value addition, huge impact.

I think it's a considerable value addition.
Impact can't be eliminated, but it can be mitigated.
My gut feeling is that the effort-to-effect ratio isn't overwhelming but far more favorable than your initial assessment indicates. But then I don't think my guts have much say in this :-)

No they don't :-)

But I never excluded adding sql() methods all over the API. I'm just strongly opposing to adding it to QueryPart.

 

>> Here's an example:
>>
>>      CREATE OR REPLACE FORCE VIEW HF_LAGERMENGEN AS
>>      select
>>        -- Used in:
>>        --   <modules that need to be updated if this view changes>
>>        fi_nr,
>>        lgnr,
>>        identnr,
>>        lamenge,
>>        -- nondispositive (<some more explanations>)
>>        nd,
>>        -- reserved (not in arrival)
>>        nvl (res, 0) as res,
>>        -- in arrival (not reserved)
>>        arr - nvl (res_arr, 0) as arr,
>>        -- reserved, in arrival
>>        nvl (res_arr, 0) as res_arr
>>      from
>>        ... etc. pp. ...
>

> I understand this use-case, but I fear that using sql() methods
> might make this statement slightly unmaintainable on the Java side.

How that?
It should be something like
  .projection("nd").sql("\n-- nondispositive")

Or just...

    DSL.field("\n-- nondispositive\n{0}", MY_TABLE.ND);

 

>       Heh. Here's an idea to toss around:
>       For a single-database developer, Jooq would become
> even more useful if he could simply plop in some SQL as
> string constant, using Jooq just to generate those parts
> that are variable. He'd use strings where the SQL is
> constant, and build AST fragments where he needs to be
> variable. It would take the syntactic pain out of
> generated SQL, without imposing the somewhat higher
> verbosity of the DSL on those parts that could simply be
> written in SQL directly.
>
> But you can do that already...?

At arbitrary points in the AST?
If that's the case, you don't need sql(), you have it already.

> Just create your
>
> DSL.using(...).resultQuery(
>    "SELECT a, b, /* complex field ahead */ {0} " +
>    "FROM t JOIN {1} /* and s jOOQ table */",
>    myField, myTable
> ).fetch();

This can insert DSL-generated SQL into a string, but not vice versa.

What would be a good example for the "vice-versa" case? 

[Lukas Eder]

I will soon blog about this common caveat when mixing subtype polymorphism with generic polymorphism on the same type. It's a very subtle caveat, but the only type in the type hierarchy that is "allowed" to terminate the generic type recursion is the "lowest" type, which should be a final class! A good example for this is the java.lang.Enum class, whose "recursion-terminating" types are final

 

Here's some more insight on this subject:

http://blog.jooq.org/2013/06/28/the-dangers-of-correlating-subtype-polymorphism-with-generic-polymorphism

I'm sure this will be republished on DZone, too and trigger some more interesting discussions.

[Durchholz, Joachim]

> I will soon blog about this common caveat when mixing
> subtype polymorphism with generic polymorphism on the
> same type. It's a very subtle caveat, but the only type
> in the type hierarchy that is "allowed" to terminate
> the generic type recursion is the "lowest" type, which
> should be a final class! A good example for this is the
> java.lang.Enum class, whose "recursion-terminating" types are final
>
> Here's some more insight on this subject:
> http://blog.jooq.org/2013/06/28/the-dangers-of-correlating-subtype-polymorphism-with-generic-polymorphism

You can always set up the type hierarchy using generics, and make subtypable nongeneric subclasses inside your type hierarchy.

I.e.
abstract class FooImpl <T extends FooImp<T>> {
... lots of code ...
}
class Foo extends FooImpl<Foo> {
... no code except maybe constructors ...
}

I haven't read and grokked the entire post (will hopefully find time to to that later), so I may be on a non-sequitur with that.

[Lukas Eder]

2013/6/28 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

In essence, I'm saying that Foo needs to be final, or you'll shoot yourself into the foot 

[Durchholz, Joachim]

> I.e.
> abstract class FooImpl <T extends FooImp<T>> {
> ... lots of code ...
> }
> class Foo extends FooImpl<Foo> {
> ... no code except maybe constructors ...
> }
>

> In essence, I'm saying that Foo needs to be final,
> or you'll shoot yourself into the foot

And I'm challenging that. I think there are cases where it's safe and cases where it isn't.

However, I have to carefully read and understand which of the many deeper issues with subtyping are actually at work with that. Which means I'll have to reread the blog post, think about it for a bit, possibly clean up my own mess, decide whether the unsafe stuff is even compile-time-checkable, find examples... so it's going to take me some time to back that challenge with arguments.
It's entirely possible that it can't be made safe in Java's type system. I'll have to see :-)

[Lukas Eder]

2013/6/28 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

> I.e.
> abstract class FooImpl <T extends FooImp<T>> {
>   ... lots of code ...
> }
> class Foo extends FooImpl<Foo> {
>   ... no code except maybe constructors ...
> }
>

> In essence, I'm saying that Foo needs to be final,
> or you'll shoot yourself into the foot

And I'm challenging that. I think there are cases where it's safe and cases where it isn't.

Yes. It's not wrong. But it's dangerous. And it was wrong in the case of org.jooq.Field :-)

 

However, I have to carefully read and understand which of the many deeper issues with subtyping are actually at work with that. Which means I'll have to reread the blog post, think about it for a bit, possibly clean up my own mess, decide whether the unsafe stuff is even compile-time-checkable, find examples... so it's going to take me some time to back that challenge with arguments.
It's entirely possible that it can't be made safe in Java's type system. I'll have to see :-)

Enjoy! 

[Durchholz, Joachim]

This:
> http://blog.jooq.org/2013/06/28/the-dangers-of-correlating-subtype-polymorphism-with-generic-polymorphism

I'm seeing the point you're making, but I don't think that making everything final is a good solution. It's certainly a massive restriction on what one can do with the classes.
It's a bit like making everything private so that subclasses cannot do wrong, instead of accepting that subclasses aren't very much encapsulated against their superclasses and have an obligation to be particularly cautious.

In particular since I make subclasses of classes that you'd advocating be made final all the time.

The real problem, BTW, is the parameter types.
You can't vary them covariantly without breaking type safety (a caller that expects a supertype may call the function with a too general type).

IOW if we have

class Parent<E> {
void f(E e);
}
class Child extends Parent<Number> {
void f(Number e) {
// do something that assumes e is a Number
}
}

and we can do

breakage(Parent<?> p, Object o) {
p.f(o);
}

then that's already breaking, even without having a subclass of Child.

The same can already happen if Child just narrows the type, without actually substituting in a type parameter:

class Child <N extends Number> extends Parent<N> {
void f(N e) {
// do something that assumes e is a Number
}
}

In a sense, I suspect the blog post is itself mixing subtype and generic polymorphism up :-)
Maybe we're just seeing things from different perspectives. Mine is that Java's subtyping guarantees are only "best effort", I'm not surprised if Java recognizes something as a subtype that isn't, and am prepared to live with ClassCastExceptions. Your perspective seems to be that if Java recognizes something as a subtype that isn't, that must be prevented because it's wrong if there's a loophole for ClassCastExceptions. My answer to that would be: You already have ClassCastExceptions, trying to prevent these is just going to make your life even more miserable. YMMV :-)

I won't claim that my perspective is the more valid one.
I do claim, however, that the blog post is stating things as if these were conceptual, basic limitations and that generically instantiated types must always be made final to achieve type safety; this is only marginally correct in Java, and entirely incorrect in languages with differently constructed type systems (most FPLs fall into the latter category atually).

[Lukas Eder]

2013/7/1 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

This:
> http://blog.jooq.org/2013/06/28/the-dangers-of-correlating-subtype-polymorphism-with-generic-polymorphism

I'm seeing the point you're making, but I don't think that making everything final is a good solution. It's certainly a massive restriction on what one can do with the classes.
It's a bit like making everything private so that subclasses cannot do wrong, instead of accepting that subclasses aren't very much encapsulated against their superclasses and have an obligation to be particularly cautious.

I'm not advocating making everything final. I'm saying that making any recursive / self-bound *terminator* final is the only way to *guarantee* that the contracts introduced by the recursive self-bound in the first place won't be broken. In other words, I'm saying that it is extremely dangerous to "terminate" a recursive self-bound in an interface or public API class in terms of API consistency and evolution, because any subtype of the "terminator" type is likely to break "uptype" contracts.

That is quite an academic claim in every-day programming life, of course. It is not academic in an API like jOOQ.

 

In particular since I make subclasses of classes that you'd advocating be made final all the time.

The real problem, BTW, is the parameter types.
You can't vary them covariantly without breaking type safety (a caller that expects a supertype may call the function with a too general type).

IOW if we have

class Parent<E> {
  void f(E e);
}
class Child extends Parent<Number> {
  void f(Number e) {
    // do something that assumes e is a Number
  }
}

and we can do

  breakage(Parent<?> p, Object o) {
    p.f(o);
  }

then that's already breaking, even without having a subclass of Child.

The same can already happen if Child just narrows the type, without actually substituting in a type parameter:

class Child <N extends Number> extends Parent<N> {
  void f(N e) {
    // do something that assumes e is a Number
  }
}

In a sense, I suspect the blog post is itself mixing subtype and generic polymorphism up :-)

On purpose. The whole post is about the common mistake of blending the two polymorphism axes without sufficient care. Adding a recursive self-bound on an API super-type is asking for trouble across all subtypes, be they part of the API or part of the user code. Or more concretely - to get back in jOOQ context, and to your original example which made me write that post:

Adding <Q extends QueryPart<Q>> would be a big mistake.

That's all that post is saying :-)

Maybe we're just seeing things from different perspectives.

Probably. Mine is that of an API designer who has committed to the rules of semantic versioning. I'm very "afraid" of such bold moves in my API's type system.

 

Mine is that Java's subtyping guarantees are only "best effort", I'm not surprised if Java recognizes something as a subtype that isn't, and am prepared to live with ClassCastExceptions. Your perspective seems to be that if Java recognizes something as a subtype that isn't, that must be prevented because it's wrong if there's a loophole for ClassCastExceptions. My answer to that would be: You already have ClassCastExceptions, trying to prevent these is just going to make your life even more miserable. YMMV :-)

I won't claim that my perspective is the more valid one.
I do claim, however, that the blog post is stating things as if these were conceptual, basic limitations and that generically instantiated types must always be made final to achieve type safety; this is only marginally correct in Java, and entirely incorrect in languages with differently constructed type systems (most FPLs fall into the latter category atually).

Then, you might not have gotten the point of my blog post - or it wasn't written concisely enough. The whole post was about:

- Correlating subtype and parametric polymorphism in a given type hierarchy in general

- Correlating the two on the same type through recursion in particular

Your "Child<N extends Number> extends Parent<N>" example does not correlate Child/Parent (subtype) with <N> (parametric) type axes. The two axes are independent from one another. So, my post does not apply to your example and you won't run into the trouble I was trying to point out.

[Durchholz, Joachim]

> I'm not advocating making everything final. I'm saying that making
> any recursive / self-bound *terminator* final is the only way to
> *guarantee* that the contracts introduced by the recursive
> self-bound in the first place won't be broken.

Ah right. I was so occupied with sorting out the point with covariance and contravariance that that got out of sight. (I first thought that text were saying "covariance is a property of generics"; I had to re-read the text thrice to really see that "of course generics are just one way to have covariance.)

I also got misled by the title. It's talking about "dangers", which led me towards looking for type holes. It's just that I don't see any - I have to admit I'm still losing track of what parts of the post describe just preliminary explanations and what parts define the conditions under which a problem pops up, and I've been missing any argument how the conditions are responsible for the problem.
At least I can say that Java does not make Foo<Subtype> assignable to Foo<E extends Supertype>, or Bar extends Foo<AnyType> assignable to Foo<AnyType>, so the usual "parameter covariance makes a subclass SO not a subtype" problem is not present. (Unsurprisingly. Gilad Bracha and the other guys who designed Java's generics are widely-recognized type system experts.)

So... I'm not seeing a type hole.
What I do see is an annoying type error message from the compiler if you try to subclass AppleContainer with GalaContainer and want GalaContainer.container() have return the GalaContainer.
But that's easily fixable! Just override container() to return GalaContainer. No relationship to generics at all, it's a standard problem in subtype definitions.

... I just hope I'm not overlooking something glaringly obvious :-)

[Lukas Eder]

2013/7/3 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

It's not obvious. It's subtle. You cannot actually create a meaningful GalaContainer type, because a GalaContainer will not contain Gala objects, it will contain Apples, which defeats its purpose. Apples terminate meaningful subtype inheritance without enforcing that termination. All subtypes of Apple will no longer fulfil the intent of AnyObject<O extends AnyObject<O>> and AnyContainer<E extends AnyObject<E>>.

... which leads again to my original claim that self-bounds in generics are a dangerous field because they can cripple a type hierarchy, which cannot be fixed easily if adhering to semantic versioning.

Anyway, I feel that this discussion starts getting a bit off-topic for the jOOQ User Group... :-)

The various .sql() methods can be added to a DSL API without using recursive generics.

Cheers

Lukas 

[Durchholz, Joachim]

> It's not obvious. It's subtle. You cannot actually
> create a meaningful GalaContainer type, because a
> GalaContainer will not contain Gala objects, it
> will contain Apples, which defeats its purpose.

I agree that subclassing a nongeneric AppleContainer to create a GalaContainer doesn't work. GalaContainer isn't a subtype of AppleContainer, it cannot be because add(Gala) isn't Liskov substitutable with add(Apple).

But the container of Galas wouldn't be allowed to be a subtype of the container of Apples, regardless of whether the classes involved are generic or not. At best, if Java allowed that, it would be another type hole in the language.

So... I don't understand what problem that example presents that wouldn't be present without generics, or without generic instantiation.

> Anyway, I feel that this discussion starts getting
> a bit off-topic for the jOOQ User Group... :-)

Ah well.

> The various .sql() methods can be added to a DSL
> API without using recursive generics.

If you happen to have .sql in a class and there are subclasses, and .sql() is to return "this", then you'll need an override to change the result type.
The exact same issues reappear in that situation:
- If you need to change what .sql() does, you just tack on that boilerplate "return this;" and be done with it. The associated cost is nonzero but small.
- If you don't need to change what .sql() does, you still need to override the function to change the return type. Which means a semantic-devoid override - that's a net cost, particularly if you have a large number of functions that "return this".
- Using generics automates the override for the return type. You'd want to write a generic-parameter-instantiating subclass for each generic type to avoid imposing the burden of generic instantiation on library users, so we're again at boilerplate with a nonzero but small cost.

I think the trade-off depends on how many subclasses inherit how many this-returning functions.
If there is a huge number (fifty attributes in the base class that get inherited by several handful of subclasses), then generics are pretty much unavoidable.
If there are very few inherited attributes, it doesn't pay off.
Working with generics will also make the design scale to an arbitrary number of attributes and classes. That may or may not be a relevant consideration, depending on the future plans for any library that has to make that decision.