DELETE with JOIN

[Aurélien Manteaux]

Hi,

I want to express this statement in jOOQ:

DELETE a FROM table_a a
LEFT JOIN table_b b
ON a.id = b.a_id
WHERE a.id IS NULL;

Unfortunately delete statements seem to work only with the WHERE clause :

DSL.using(configuration()).delete(TableA).join(..)
// Does not exist: -------------------------------> ^^^

 

I tried to look in the docs, but the docs server http://jooq.org/learn seems down :(

Cheers,
Aurélien

[Aurélien Manteaux]

Actually my query was:

DELETE a FROM table_a a
LEFT JOIN table_b b
ON a.id = b.a_id

WHERE b.a_id IS NULL;

I found a nice workaround :

DSLContext deleteQuery = DSL.using(configuration());
deleteQuery
.delete(TableA)
.where(TableA.ID.in(
    deleteQuery
    .select(TableA.ID)
    .from(TableA)
    .join(TableB, JoinType.LEFT_OUTER_JOIN)
    .on(TableA.ID.eq(TableB.A_ID))
    .where(TableB.A_ID.isNull())
))
.execute();

I got to admit, jOOQ is powerful :)

However, if it is possible to directly execute the delete statement, I am interested !

Cheers,
Aurélien

[Lukas Eder]

Hello,

That's a valid workaround, although, I suspect that the DELETE FROM .. JOIN .. statement might be faster, as MySQL is not exactly renowned for its SQL transformation capabilities, when it comes to optimising SQL.

Currently, you can express your original query by using the JOIN methods directly on the table, which should work on DELETE statements in MySQL:

TABLE_A.leftOuterJoin(TABLE_).on(...)

Note that the JOIN methods on the Select statement, as in SelectJoinStep are mere convenience for the other ones. JOIN is not a SELECT clause, but a table expression clause. This also allows for nesting joins in more complex situations.

Anyway, I have registered a feature request to improve this for those dialects that support JOIN in DELETE (and UPDATE):

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

Best Regards,

Lukas

--
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/d/optout.

[Aurélien Manteaux]

Thanks for you reply !

jOOQ actually let me write this :

DSLContext deleteQuery = DSL.using(configuration());
deleteQuery
.delete(

TABLE_A
.leftOuterJoin(TABLE_B)
.on(TABLE_A.ID.eq(TABLE_B.A_ID))

)
.where(TABLE_B.A_ID.isNull())
.execute();

However MySQL does not validate the resulted SQL :

DELETE /* missing alias */ FROM table_a
LEFT JOIN table_b
ON table_a.id = table_b.a_id
WHERE table_b.a_id IS NULL;

In my case, the TABLE_A.in() statement will be fast enough since the JOIN subquery will return just a few rows.

Anyway thank you for the feature request !

Cheers,

Aurélien

[Lukas Eder]

Hi Aurélien,

2014-05-19 22:57 GMT+02:00 Aurélien Manteaux <aman...@gmail.com>:

Thanks for you reply !

jOOQ actually let me write this :

DSLContext deleteQuery = DSL.using(configuration());
deleteQuery
.delete(

TABLE_A
.leftOuterJoin(TABLE_B)
.on(TABLE_A.ID.eq(TABLE_B.A_ID))

)
.where(TABLE_B.A_ID.isNull())
.execute();

However MySQL does not validate the resulted SQL :

DELETE /* missing alias */ FROM table_a
LEFT JOIN table_b
ON table_a.id = table_b.a_id
WHERE table_b.a_id IS NULL;

Curious, that might be a bug. Yes, MySQL and MariaDB have a very peculiar DELETE syntax, where you should really write something along the lines of

DELETE t1 FROM table_a t1 LEFT JOIN ...

Have you tried aliasing TABLE_A to something? Would that work? It would still be a bug, though. I have updated #3266 accordingly

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

 

In my case, the TABLE_A.in() statement will be fast enough since the JOIN subquery will return just a few rows.

Alright.

Cheers

Lukas

[Aurélien Manteaux]

Hi Lukas,

If I try to alias TABLE_A, I get :

DELETE /* missing alias */ FROM table_a as a

LEFT JOIN table_b
ON table_a.id = table_b.a_id
WHERE table_b.a_id IS NULL;

But the problem is the same.

However, I just realized the TABLE_A.in() statement does not work because MySQL does not allow to delete from a table and select from the same table in a subquery: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause :(

So to finally overcome this last issue I have to do the following:

DSLContext ctx = DSL.using(configuration);

Table<Record1<Long>> tmpTable = ctx
.select(TABLE_A.ID)
.from(
    TABLE_A
    .leftOuterJoin(TABLE_B)
    .on(TABLE_A.ID.eq(TABLE_B.A_ID))
)
.where(TABLE_B.A_ID.isNull()).asTable("tmp");

ctx
.delete(TABLE_A)
.where(TABLE_A.ID.in(ctx.select((Field<Long>)tmpTable.field("id"))
.from(tmpTable)))
.execute();

 

to get the "correct" SQL code compatible MySQL:

delete from `table_a`
where `table_a`.`id` in (
    select `tmp`.`id`
    from (
        select `table_a`.`id`
        from `table_a`
        left outer join `table_b`
        on `table_a`.`id` = `table_b`.`a_id`
        where `table_b`.`a_id` is null
    ) as `tmp`
)

MySQL can be tricky sometimes !

Cheers,
Aurélien

[Lukas Eder]

2014-05-20 22:49 GMT+02:00 Aurélien Manteaux <aman...@gmail.com>:

Hi Lukas,

If I try to alias TABLE_A, I get :

DELETE /* missing alias */ FROM table_a as a

LEFT JOIN table_b
ON table_a.id = table_b.a_id
WHERE table_b.a_id IS NULL;

But the problem is the same.

Alright, that will need to be fixed

 

However, I just realized the TABLE_A.in() statement does not work because MySQL does not allow to delete from a table and select from the same table in a subquery: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause :(

Yes, that is a very annoying limitation.

Wow, and that worked?? Does that work reliably? If it does, it might be worth implementing some abstraction in jOOQ, which handles this sort of SQL transformation for you, transparently. I.e. whenever the TABLE_A (from which we delete) is also referenced in the predicates, we'll transform the predicates in an equivalent way. Might not be too easy, though. 

[Aurélien Manteaux]

Hello Lukas,

Yes it finally worked, I have tested it this time !
I think it is reliable since the solution is 6 years old (http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause/8620162#8620162) and still nothing has changed.

However, I don't know if because of a MySQL limitation, a (big) hack should be added to jOOQ.
Maybe we can wait to see if there are other MySQL users interested in this feature before starting to think of an implementation into jOOQ.

Alright, that will need to be fixed

Thank you, this will be a great improvement !

Cheers,
Aurélien

[Lukas Eder]

Hi Aurélien,

2014-05-24 1:17 GMT+02:00 Aurélien Manteaux <aman...@gmail.com>:

Hello Lukas,

Yes it finally worked, I have tested it this time !
I think it is reliable since the solution is 6 years old (http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause/8620162#8620162) and still nothing has changed.

Well, what I'd be worried about: The MySQL folks must have had a reason for adding this limitation, but they obviously didn't implement it thoroughly - i.e. preventing reuse of the same table regardless of the SQL transformations that are applied to it. Probably, they just implemented a couple of simple checks on the AST, rather than actually using locks on the table that would fail the query, regardless of syntax. Or maybe, the alternative syntax really *is* different, which would be equally frightening :-)

So your workaround is a hack to work around a limitation for which there probably is a reason. I suspect the reason to be related to guaranteeing ACID-ness. I.e. I wouldn't find it surprising, if by working around this limitation, you might produce race conditions or corrupted data in situations with significant concurrency.

I'm raising the issue on Stack Overflow. I think that clarifying this will help all MySQL users:

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

However, I don't know if because of a MySQL limitation, a (big) hack should be added to jOOQ.
Maybe we can wait to see if there are other MySQL users interested in this feature before starting to think of an implementation into jOOQ.

It wouldn't be the first hack :-) just consider how jOOQ emulates OFFSET .. FETCH (aka. LIMIT .. OFFSET) for Oracle 11g and less, SQL Server 2008 and less, and for DB2. We've done our share of SQL transformations to standardise such common things, e.g.

http://blog.jooq.org/2013/10/09/sql-trick-row_number-is-to-select-what-dense_rank-is-to-select-distinct/

[Aurélien Manteaux]

Hi Lukas,

Thank you for this explanation, I didn't suspect this solution may corrupt the database.

However, as you pointed out in Stack Overflow, the MySQL limitation is only here because the query optimizer is lazy :)
So it might be nice if jOOQ would do the query transformation.
But so you know, for me this feature is not urgent at all.

Anyway, thank you for your time, it is nice to see that jOOQ is that much active ;)

Cheers,
Aurélien

[Lukas Eder]

Hi Aurélien,

2014-06-07 14:58 GMT+02:00 Aurélien Manteaux <aman...@gmail.com>:

Hi Lukas,

Thank you for this explanation, I didn't suspect this solution may corrupt the database. 

However, as you pointed out in Stack Overflow, the MySQL limitation is only here because the query optimizer is lazy :)
So it might be nice if jOOQ would do the query transformation.

Well, as this seems to be common practice, yes, we'll apply this transformation, eventually.

 

But so you know, for me this feature is not urgent at all.

Good, because it won't be that easy :-)

Maybe, you could try to achieve it yourself, by writing a VisitListener?

 

Anyway, thank you for your time, it is nice to see that jOOQ is that much active ;)

Yes, we're pushing!

Cheers

Lukas 

[jens.kl...@gmail.com]

Although this is an old issue it seems that the DELETE-JOIN-Syntax is still not implemented. For future readers (including me^^): is the following helper-method a reasonable workaround until the feature is implemented?

public static Query deleteJoin(Collection<Table<?>> tables,TableOnConditionStep<Record> joinStep, Condition where){

   return DSL.query("DELETE {0} FROM {1} WHERE {2}",DSL.list(tables),joinStep,where);

}

and then call it like

Query query = deleteJoin(Arrays.asList(Table.A,Table.B),Table.A.join(Table.B).on(Table.A.ID.eq(Table.B.ID)),Table.A.ID.eq(someid));
int numrows = dslContext.execute(query);

?

[Lukas Eder]

Hi Jens,

Thanks for your enquiry. Indeed, this syntax is so weird and rare, I'm not sure if it makes sense to support it in jOOQ. There's often a workaround with EXISTS that runs a semi-join instead of the JOIN. In my opinion, that's more formally correct, although it may be slower on MySQL...

Yes, your approach would be the ideal workaround for this case. Thanks for documenting it for future readers!

Lukas

--