Grails
  1. Grails
  2. GRAILS-7783

more than one AND block in an OR block of a critieria breaks totalCount and results

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Not A Bug
    • Affects Version/s: 1.3.7
    • Fix Version/s: 2.0-M2
    • Component/s: Plugins
    • Labels:

      Description

      Executing the following two queries I expect the same result. But something goes wrong and the amount of results is different.

      def user = User.get(1)
      def textOrdersSQL = TextOrder.executeQuery("FROM TextOrder to WHERE (status = ? AND type = ?) OR (status = ? AND type = ?) OR (assignedTo = ? AND type = ?)", [TextOrderStatus.UNASSIGNED, TextOrderType.OpenOrder, TextOrderStatus.UNASSIGNED, TextOrderType.CategoryOrder, user, TextOrderType.DirectOrder], [max:20,offset:0])
      
      def textOrderCriteria = TextOrder.createCriteria()
      textOrders = textOrderCriteria.list(max: 20, offset: 0) {
        or {
          and {
            eq('status', TextOrderStatus.UNASSIGNED)
            eq('type', TextOrderType.OpenOrder)
          }
          and {
            eq('status', TextOrderStatus.UNASSIGNED)
            eq('type', TextOrderType.CategoryOrder)
          }
          and {
            eq('type', TextOrderType.DirectOrder)
            eq('assignedTo', user)
          }
        }
      }
      textOrders.size() == textOrdersSQL.size()
      

      As I read in http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html it should be possible to nest the restrictions. But this does probably not work for the grails way of criteria building.

        Activity

        Hide
        Sebastian Kurt added a comment -

        probably it has something to do with Distinct!
        http://floledermann.blogspot.com/2007/10/solving-hibernate-criterias-distinct.html
        but I can not imagine that a bug from 2007 still appears!?

        Show
        Sebastian Kurt added a comment - probably it has something to do with Distinct! http://floledermann.blogspot.com/2007/10/solving-hibernate-criterias-distinct.html but I can not imagine that a bug from 2007 still appears!?
        Hide
        Sebastian Kurt added a comment -

        "The criteria query looks correct." ???

        criteria builder should construct the WHERE-part like:
        (a,b, c, d are boolean conditions like status = 'MODERATED')
        (a AND b) OR (a AND c) OR (b AND d)

        but as I read the query it is resolved to
        (a OR b) AND (a OR c) AND (b OR d)
        which is something totally different.

        Show
        Sebastian Kurt added a comment - "The criteria query looks correct." ??? criteria builder should construct the WHERE-part like: (a,b, c, d are boolean conditions like status = 'MODERATED') (a AND b) OR (a AND c) OR (b AND d) but as I read the query it is resolved to (a OR b) AND (a OR c) AND (b OR d) which is something totally different.
        Hide
        Sebastian Kurt added a comment -

        is there a way to upload/send a zip-file with a example project?

        Show
        Sebastian Kurt added a comment - is there a way to upload/send a zip-file with a example project?
        Hide
        Jeff Scott Brown added a comment -

        I am pretty sure the SQL is correct. It is saying something like "(a AND b) OR (a AND c) OR (b AND d)" wrapping that all up in one condition and then AND'ing that with the tenant_id comparison.

        Show
        Jeff Scott Brown added a comment - I am pretty sure the SQL is correct. It is saying something like "(a AND b) OR (a AND c) OR (b AND d)" wrapping that all up in one condition and then AND'ing that with the tenant_id comparison.
        Hide
        Sebastian Kurt added a comment -

        ah okay, I was wrong. sorry! I did not see the and on the beginning of some lines...

        so I focus on the distinct bug, thanks for helping

        Show
        Sebastian Kurt added a comment - ah okay, I was wrong. sorry! I did not see the and on the beginning of some lines... so I focus on the distinct bug, thanks for helping

          People

          • Assignee:
            Jeff Scott Brown
            Reporter:
            Sebastian Kurt
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development