Issues With Sort Ordering On GROUP BY values

SQL++
1

I was just working on a query, and noticed that the ORDER BY clause doesn’t appear to be sorting correctly. I’m assuming this has something to do with the clauses also being part of the GROUP BY, or perhaps the fact that the second clause is a function call.

This is running on Couchbase Server 4.5.0 Enterprise. If this was already fixed in 4.5.1 the my apologies.

SELECT orders.siteId, DATE_PART_STR(orders.createDate, "month") as month,
    COUNT(*) as orders, SUM(ARRAY_COUNT(orders.orderEventItems)) as events 
FROM Stores orders where type = 'order'
GROUP BY orders.siteId, DATE_PART_STR(orders.createDate, "month")
ORDER BY orders.siteId, DATE_PART_STR(orders.createDate, "month")

Result:

[
  {
    "events": 11,
    "month": 8,
    "orders": 133,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 13,
    "month": 10,
    "orders": 51,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 14,
    "month": 9,
    "orders": 111,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 0,
    "month": 6,
    "orders": 145,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 4,
    "month": 7,
    "orders": 183,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 22,
    "month": 10,
    "orders": 88,
    "siteId": "73ab6243-eff5-49ad-be6a-4f06210380b7"
  },
  {
    "events": 21,
    "month": 9,
    "orders": 120,
    "siteId": "73ab6243-eff5-49ad-be6a-4f06210380b7"
  },
  {
    "events": 0,
    "month": 8,
    "orders": 7,
    "siteId": "73ab6243-eff5-49ad-be6a-4f06210380b7"
  }
]

The first time, the first clause appeared to work but not the second. I think this was coincidence, because the next time I ran it I got this:

[
  {
    "events": 21,
    "month": 9,
    "orders": 120,
    "siteId": "73ab6243-eff5-49ad-be6a-4f06210380b7"
  },
  {
    "events": 0,
    "month": 8,
    "orders": 7,
    "siteId": "73ab6243-eff5-49ad-be6a-4f06210380b7"
  },
  {
    "events": 11,
    "month": 8,
    "orders": 133,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 13,
    "month": 10,
    "orders": 51,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 14,
    "month": 9,
    "orders": 111,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 0,
    "month": 6,
    "orders": 145,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 4,
    "month": 7,
    "orders": 183,
    "siteId": "ed358a45-2991-40a4-ad80-af118004d32b"
  },
  {
    "events": 22,
    "month": 10,
    "orders": 88,
    "siteId": "73ab6243-eff5-49ad-be6a-4f06210380b7"
  }
]
2

Interesting. The query looks correct. Could you give us a bit of sanitized test data to work with?

3

@johan_larson

I ran this query:

SELECT * FROM Stores as orders where type = 'order' LIMIT 10

and sanitized the results to get this file:

orders.zip (9.2 KB)

Let me know if I can do anything else to help.

Brant

4

Checking now. Thanks for the data.

5

Brant, could you just email me the data? I am having trouble downloading it.

johan.larson@coucbase.com

6

@johan_larson

Sent to you.

Thanks,
Brant

7

OK, I see the problem. You have two things named “orders” (bolded above). If you switch the field name of the COUNT(*) to something else (like “num_orders”) the problem should go away.

1 Like
8

@johan_larson

Ah, thanks. I didn’t realize that the ORDER BY clause could pull values from the SELECT clause.

Thanks,
Brant

1 Like