1

I need help with a problem I've been scratching my head and so far I couldn't solve. This is not a specific question about jOOQ, but I refer it to explain why I have to use joins instead of the ingres specific syntax. That said:

Imagine the following table and values:

create table  orderline
 (orderno integer,
 lineno integer,
 linetime timestamp,
 article varchar(30)
 );
insert into orderline values (1,1,timestamp '2013-10-23 00:05:08.0', 'SMURFS');
insert into orderline values (1,2,timestamp '2013-10-23 00:05:08.0', 'PINKMAN');
insert into orderline values (1,3,timestamp '2013-10-23 00:05:10.0', 'METH FAIRY');
insert into orderline values (1,4,timestamp '2013-10-23 00:05:12.0', 'HEISENBERG');
insert into orderline values (2,1,timestamp '2013-10-23 00:08:13.0', 'HEAR ME ROAR');
insert into orderline values (2,2,timestamp '2013-10-23 00:08:15.0', 'KHAALESI');
insert into orderline values (2,3,timestamp '2013-10-23 00:09:01.0', 'UNSULLIED');
insert into orderline values (2,4,timestamp '2013-10-23 00:09:03.0', 'WHITE WALKERS');
insert into orderline values (2,5,timestamp '2013-10-23 00:09:03.0', 'WILDLING');

Now what I am given is only the an interval to search on this table. The result that I have to give is all order lines from an specific order, where one line at least matches the time interval.

For example, the interval between 2013-10-23 00:05:08.0 and 2013-10-23 00:05:09.0, inclusive,should give as a result on my query the 4 lines with orderno=1, but only 2 lines match the interval: the line with orderno =1, lineno = 1 and the line with orderno=1, lineno=2. A simple query using the interval with only return those 2 lines.

On old ingres times, I would search like this:

select * from orderline
where orderno in (
select orderno from orderline
  where linetime >= timestamp '2013-10-23 00:05:08.0'
  and linetime <= timestamp '2013-10-23 00:05:09.0'
)
order by orderno, lineno

this gives exactly the 4 lines I need. But I can't use this syntax because I have to use jOOQ and this syntax doesn't exist there (have to use joins). I can't make it work with joins though.

The following query:

select * from orderline a
join orderline b
on a.orderno = b.orderno 
where b.linetime >= timestamp '2013-10-23 00:05:08.0'
and b.linetime <= '2013-10-23 00:05:09.0'

produces 8 lines, i.e each line comes duplicated.

If I use the 2 conditions (orderno and lineno) as join conditions, i.e

select * from orderline a
join orderline b
on a.orderno = b.orderno and a.lineno = b.lineno
where b.linetime >= timestamp '2013-10-23 00:05:08.0'
and b.linetime <= '2013-10-23 00:05:09.0'

I get only 2 lines, exactly as if I had made a direct simple query on the interval. Experiments with right, left, inner, etc joins didn't yield any result so far so I'm clueless as to how to do it (and therefore be able to write it on jOOQ).

Anyone knows how should I make this query on SQL92 (i.e. join) syntax?

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Luis Matos
  • 347
  • 3
  • 14

2 Answers2

1

All standard SQL statements and clauses are supported in jOOQ. E.g. your statement:

select * from orderline
where orderno in (
select orderno from orderline
  where linetime >= timestamp '2013-10-23 00:05:08.0'
  and linetime <= timestamp '2013-10-23 00:05:09.0'
)
order by orderno, lineno

Corresponds to this in jOOQ (3.2 syntax):

// Assuming this static import:
import static org.jooq.impl.DSL.*;

using(configuration)
.select().from(ORDERLINE)
.where(ORDERLINE.ORDERNO.in(
    select(ORDERLINE.ORDERNO)
    .from(ORDERLINE)
    .where(ORDERLINE.LINETIME.ge(Timestamp.valueOf("2013-10-23 00:05:08.0")))
    .and(ORDERLINE.LINETIME.le(Timestamp.valueOf("2013-10-23 00:05:09.0")))
))
.orderBy(ORDERLINE.ORDERNO, ORDERLINE.LINENO)
.fetch();

About your JOIN alternatives:

The best way to solve this is using a semi-join the way you did, using an IN or EXISTS predicate. You could use a regular equi-join on orderno, but you'd have to join a derived table where you get only distinct orderno values:

select * 
from orderline a
join (
    select distinct orderno
    from orderline
    where linetime >= timestamp '2013-10-23 00:05:08.0'
    and linetime <= timestamp '2013-10-23 00:05:09.0'
) b
on a.orderno = b.orderno
order by orderno, lineno

I doubt that this variant is faster or easier to maintain

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Hi Lukas, thanks a lot for your reply. Yes, I do believe that using the "in" is more intuitive but I couldn't find documentation about using it in the way that you described above in the jooq documentation. The "in" clause being somehow "de facto" non-standard sql (different databases implement it in seemingly similar but annoyingly incompatible ways), I thought that jooq didn't have it because I found only docs about the joins. I'll try both clauses on the table to see what performs better because it's millions of rows to select from. Thanks a lot for the answer and for your gr+ project. – Luis Matos Oct 24 '13 at 07:50
  • Oh and btw I'll make sure I write jOOQ whenever I do a new question about it ^^ – Luis Matos Oct 24 '13 at 07:53
  • No worries about the branding ;-) This manual page shows the various variants of the `IN` predicate: http://www.jooq.org/doc/3.2/manual/sql-building/conditional-expressions/in-predicate/. I'm not sure what you mean by it not being standard SQL. The `IN` predicate has been in the standard forever and only very few databases get `NOT IN` wrong with `NULLs`: http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-null-values/. What did you have in mind? – Lukas Eder Oct 24 '13 at 10:48
  • I guess that our current sql guru on my company has had some misapprehension. There's been a strong current pro "join" and against "in" here, to the point that I was told that "in" was out of favor and non-standard (pun intended). But as I stand corrected, so does the guru who is reading me writing this answer to you :) It's all good and I'm happy to have backup to use my "in" again. In is in, after all... – Luis Matos Oct 24 '13 at 13:58
  • Aaha, awesome wording. Well, [... it depends!](http://blog.jooq.org/2012/07/27/not-in-vs-not-exists-vs-left-join-is-null-mysql/) The "join myth" with join being slower sometimes isn't completely wrong. It mostly depends on the database, and I have no production experience with Ingres. Anyway, I can recommend [a good book](http://books.dzone.com/reviews/sql-performance-explained) to get a deeper understanding about performance in general. – Lukas Eder Oct 24 '13 at 15:07
  • Thanks Lukas :) But as I found out, the point is moot anyway. Ingres doesn't not support the syntax of query that you gave and the "in" clause is working satisfactorily. Thanks again for your help. – Luis Matos Oct 24 '13 at 22:04
  • At least not ingres 9. Ingres 10 says it supports it but I don't have it handy to test right now. – Luis Matos Oct 24 '13 at 22:17
  • I see. Hm, yes, jOOQ has never been integration tested with Ingres 9, only with Ingres 10: http://www.jooq.org/licensing#databases – Lukas Eder Oct 25 '13 at 06:40
0

You wrote:

I get only 2 lines, exactly as if I had made a direct simple query on the interval.

But there is only one time matching the interval.

I think there is a typo, difference. And probably this second line causes the issue.

Anyway you can easily remove duplications if you change the first select to select distinct.

Lajos Veres
  • 13,595
  • 7
  • 43
  • 56
  • I corrected the typo, thanks. It's the time on the second line. Actually, the table that I have to work on has millions of lines and many columns, so I would avoid using select distinct on it. – Luis Matos Oct 23 '13 at 22:49
  • But I will of course have to resort to it if there's no other solution. Actually I had used it before but I made select distinct * and that doesn't work. But select distinct a.* works :) – Luis Matos Oct 23 '13 at 22:57
  • 2
    *"Anyway you can easily remove duplications if you change the first select to select distinct."* - Don't do this! The "join query" is semantically wrong. Fixing this with adding another wrong `distinct` clause just adds more problems! See §6 in this blog post: http://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/ – Lukas Eder Oct 24 '13 at 06:28
  • Yes I had that gut feeling that this was wrong but couldn't find the right join syntax to do the thing work - as in using a derived table like you said on the other answer. Now that I've seen how it's done, it seems clear like water, just as it was dark as mud before.. – Luis Matos Oct 24 '13 at 09:07
  • @LuisMatos: *"it seems clear like water, just as it was dark as mud before"* - I will need to cite that somewhere! :D – Lukas Eder Oct 24 '13 at 10:49