Criterion query gives bizarre results

[Bruce Altner]

Greetings:

I've been experiencing problems with a fairly simple query lately involving 
a single table but several combinations of "OR" and "AND" so I checked the 
criteria and peers how-to files and found the example at the end of the 
peers how-to (http://db.apache.org/torque/peers-howto.html). This example 
is often-cited on this list in answer to questions like mine, but it 
doesn't work for me, so I'm hoping that someone can spot my error and save 
me my sanity.

This is for an events calendar in which I want to find all events which 
"pass through" a date interval defined by date1 and date2. Since the advice 
is that you should use Criterion for "or" instead of Criteria, I prepared a 
bunch of Criterion objects in advance:

          Criteria crit = new Criteria();

          Criteria.Criterion a1 = 
crit.getNewCriterion(VeventsPeer.DTSTART,(Object)date1,Criteria.LESS_EQUAL);
          Criteria.Criterion a2 = 
crit.getNewCriterion(VeventsPeer.DTEND,(Object)date1,Criteria.GREATER_EQUAL);

          Criteria.Criterion b1 = 
crit.getNewCriterion(VeventsPeer.DTSTART,(Object)date1,Criteria.GREATER_EQUAL);
          Criteria.Criterion b2 = 
crit.getNewCriterion(VeventsPeer.DTEND,(Object)date2,Criteria.LESS_EQUAL);

          Criteria.Criterion c1 = 
crit.getNewCriterion(VeventsPeer.DTSTART,(Object)date2,Criteria.LESS_EQUAL);
          Criteria.Criterion c2 = 
crit.getNewCriterion(VeventsPeer.DTEND,(Object)date2,Criteria.GREATER_EQUAL);

          Criteria.Criterion d1 = 
crit.getNewCriterion(VeventsPeer.DTSTART,(Object)date1,Criteria.LESS_EQUAL);
          Criteria.Criterion d2 = 
crit.getNewCriterion(VeventsPeer.DTEND,(Object)date2,Criteria.GREATER_EQUAL);

          crit.add(a1.and(a2)
            .or(b1.and(b2))
            .or(c1.and(c2))
            .or(d1.and(d2)));
Graphically, what I wanted was the following events from my events table, 
where a1, b1, c1, and d1 are start dates and a2, b2, c2, d2 are end dates:

      O----------------------------------------------------------------O 
event
       d1                                                               d2

        O---------------O   O----------O      O-------------------O 
  event(s)
        a1             a2   b1         b2     c1                 c2

--------------|---------------------------------------------|-------------- 
time line
            date1                                         date2

I expected the SQL to come out like this:

WHERE ((a1 AND a2) or (b1 and b2) or (c1 and c2) or (d1 and d2)). Simple, 
right? But when I got bizarre results I checked the logs and found that 
what was being passed to the database was this:

WHERE
(VEVENTS.DTSTART<='2004-02-01 00:00:00'
        OR (VEVENTS.DTSTART>='2004-02-01 00:00:00'
        OR (VEVENTS.DTSTART<='2004-03-01 00:00:00'
        OR (VEVENTS.DTSTART<='2004-02-01 00:00:00'
        AND (VEVENTS.DTEND>='2004-03-01 00:00:00'))
        AND (VEVENTS.DTEND>='2004-03-01 00:00:00'))
        AND (VEVENTS.DTEND<='2004-03-01 00:00:00'))
        AND (VEVENTS.DTEND>='2004-02-01 00:00:00'))
which looks like this, symbolically:

WHERE (a1 OR (b1 OR (c1 OR (d1 AND d2) AND c2) AND b2) AND a2),

which is NOT what I wanted! I tried this with formatted String objects 
instead of Date objects but got the same result. What am I doing wrong?

Thanks, in advance!

Bruce Altner