Re: delete from ... where ... in (...)

From: Borut Bolčina (borut.bolcin..mail.com)
Date: Thu Jul 24 2008 - 05:27:48 EDT

  • Next message: Ian Jamieson: "Configuring which dataDomain to use in servlet container"

    Hi,

    the database is MySQL 5.0.45, the driver is jConnector 5.1.6.

    2008/7/24 Andrus Adamchik <andrus@objectstyle.org>:

    > The only difference in generated SQL syntax is using alias in the WHERE
    > clause. The most weird thing is the deletion of 10 rows in the second case.
    > This could be a bug in EJBQL processing I guess, resulting in incorrect
    > value bindings (String vs. numeric or something like that). Which DB is
    > this?
    >
    > Andrus
    >
    >
    > On Jul 24, 2008, at 10:10 AM, Borut Bolčina wrote:
    >
    > Hi,
    >>
    >> I tried removing with
    >>
    >> Expression qual = ExpressionFactory.inExp("id", eventIds);
    >> DeleteQuery deleteQuery = new DeleteQuery(Event.class, qual); //
    >> DEPRECATED
    >>
    >> which gives:
    >> INFO [24 Jul 2008 08:52:01.319] SELECT t0.location, t0.date_to,
    >> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
    >> t0.url_provider, t0.description, t0.subcateg
    >> ory, t0.date_from, t0.name_provider, t0.time_from_list FROM event t0 WHERE
    >> t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/']
    >> INFO [24 Jul 2008 08:52:01.421] === returned 410 rows. - took 102 ms.
    >> INFO [24 Jul 2008 08:52:01.422] +++ transaction committed.
    >> INFO [24 Jul 2008 08:52:01.422] Removing all(410) previous events...
    >> INFO [24 Jul 2008 08:52:01.441] --- will run 1 query.
    >> INFO [24 Jul 2008 08:52:01.442] --- transaction started.
    >> INFO [24 Jul 2008 08:52:01.459] DELETE FROM event WHERE id IN (?, ... ?)
    >>
    > ...
    >
    >>
    >> INFO [24 Jul 2008 08:52:01.474] === updated 410 rows.
    >> INFO [24 Jul 2008 08:52:01.474] +++ transaction committed.
    >>
    >> and is correct,
    >>
    >>
    >> where as with
    >> EJBQLQuery deleteQuery = new EJBQLQuery("delete from Event e where e.idin
    >> (:eventIds)");
    >> deleteQuery.setParameter("eventIds", eventIds);
    >>
    >> gives me:
    >>
    >> INFO [24 Jul 2008 08:39:00.470] SELECT t0.location, t0.date_to,
    >> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
    >> t0.url_provider, t0.description, t0.subcatego
    >> ry, t0.date_from, t0.name_provider, t0.time_from_list FROM event t0 WHERE
    >> t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/']
    >> INFO [24 Jul 2008 08:39:00.565] === returned 410 rows. - took 95 ms.
    >> INFO [24 Jul 2008 08:39:00.566] +++ transaction committed.
    >> INFO [24 Jul 2008 08:39:00.566] Removing all(410) previous events...
    >> INFO [24 Jul 2008 08:39:00.591] --- will run 1 query.
    >> INFO [24 Jul 2008 08:39:00.591] --- transaction started.
    >> INFO [24 Jul 2008 08:39:00.642] DELETE FROM event WHERE event.id IN (
    >> ?,?...,?)
    >> INFO [24 Jul 2008 08:39:00.674] === updated 10 rows.
    >>
    >

    This archive was generated by hypermail 2.0.0 : Thu Jul 24 2008 - 05:28:26 EDT