Postgresql GROUP BY / ORDER BY

[doh]

I'm writing a rails app with the paginating_find plugin and the code I'm writing is generating the following query:

SELECT
   *
FROM
   orders
WHERE 
   (orders.region_id = 10000033 AND ((is_buyorder = true )))  
GROUP BY
   price
ORDER BY
   price DESC
LIMIT
   30 
OFFSET
   0

Now Postgresql is complaining here:

ERROR: column "orders.id" must appear in the GROUP BY clause or be used in an aggregate function

I don't understand why it's complaining since I'm not doing anything with orders.id here. Indeed Postgres will complain for every column unless I GROUP BY on it. What's going on here?

 

[mikeblas]

That's the way GROUP BY works. What you're doing with orders.id is selecting it, because you've written SELECT *.

Why do you have the GROUP BY in your query?

 

[doh]

That's the way GROUP BY works. What you're doing with orders.id is selecting it, because you've written SELECT *.

Why do you have the GROUP BY in your query?

Fair point.

I'm GROUPing because the plugin does this query beforehand:

SELECT
   count(*) AS count_all
FROM
   orders
WHERE
   (orders.region_id = 10000033 AND ((is_buyorder = true)))
GROUP BY
   price
ORDER BY
   price DESC
LIMIT
   200

Since I'm doing an aggregate function there it obviously needs the GROUP BY. Removing the group by directive will make the subsequent query work (once it figures out how many pages there are) but it will render the query to find the number of pages unusable.

Unfortunately it seems like a limitation of the plugin!

Ninja edit:
The code to get paginated results is just:

    thing.orders.find(:all,
      :order      => 'price DESC',
      :limit      => 200,
      :conditions => ( conds + [ "is_buyorder = true"] ).to_sql,
      :page       => { :size => limit, :current => page })

This runs two queries (the select count(*) as count_all WHERE ... and the one I originally posted).

I don't know of a way to split this line up so that it gets the number of pages (GROUPed) and the actual data (un GROUPed).

 

[mikeblas]

Removing the group by directive will make the subsequent query work (once it figures out how many pages there are) but it will render the query to find the number of pages unusable.

What would make the query usable? Certainly, it isn't GROUP BY, as you can't group by anything in that query because you're not doing any aggregation. In fact, neither of your queries need GROUP BY; I'm surprised the first one executes because it should also return the same error.

To get the number of rows returned, I would do this:

SELECT
   count(*) AS count_all
FROM
   orders
WHERE
   (orders.region_id = 10000033 AND ((is_buyorder = true)))
ORDER BY
   price DESC
LIMIT
   200

To get the actual data, I'd do this, except I'd not use * and select only the columns I intend to actually show or use:

SELECT
   *
FROM
   orders
WHERE
   (orders.region_id = 10000033 AND ((is_buyorder = true)))
ORDER BY
   price DESC
LIMIT
   20	/* figuring I want 20 per page */
OFFSET 
   0	/* first page; second page is 20 */

I can't guess why your library is emitting GROUP BY when it shouldn't.

 

[doh]

What would make the query usable? Certainly, it isn't GROUP BY, as you can't group by anything in that query because you're not doing any aggregation. In fact, neither of your queries need GROUP BY; I'm surprised the first one executes because it should also return the same error.

To get the number of rows returned, I would do this:

SELECT
   count(*) AS count_all
FROM
   orders
WHERE
   (orders.region_id = 10000033 AND ((is_buyorder = true)))
ORDER BY
   price DESC
LIMIT
   200

To get the actual data, I'd do this, except I'd not use * and select only the columns I intend to actually show or use:

SELECT
   *
FROM
   orders
WHERE
   (orders.region_id = 10000033 AND ((is_buyorder = true)))
ORDER BY
   price DESC
LIMIT
   20	/* figuring I want 20 per page */
OFFSET 
   0	/* first page; second page is 20 */

I can't guess why your library is emitting GROUP BY when it shouldn't.

The queries you've pasted is exactly what I had when I started but the output straight from psql is:

evemarket_development=> SELECT count(*) AS count_all FROM orders WHERE (orders.region_id = 10000033 AND ((is_buyorder = true))) ORDER BY price DESC LIMIT 200;
ERROR:  column "orders.price" must appear in the GROUP BY clause or be used in an aggregate function

evemarket_development=> SELECT count(*) AS count_all FROM orders WHERE (orders.region_id = 10000033 AND ((is_buyorder = true))) GROUP BY price ORDER BY price  DESC LIMIT 200;
[returns rows]

evemarket_development=>SELECT * FROM orders WHERE (orders.region_id = 10000033 AND ((is_buyorder = true))) ORDER BY price DESC LIMIT 20 OFFSET 0;
[returns 20 rows]

evemarket_development=> SELECT * FROM orders WHERE (orders.region_id = 10000033 AND ((is_buyorder = true))) GROUP BY price ORDER BY price DESC LIMIT 20 OFFSET 0;
ERROR:  column "orders.id" must appear in the GROUP BY clause or be used in an aggregate function

It fails on the count(*) where I would expect it to succeed and fails on the query I would expect it to fail on.

I don't understand why Postgres wants the GROUP BY with the count(*).

 

[mikeblas]

Is ORDERS a view? Does executing that query without LIMIT work correctly?

The docs for LIMIT/OFFSET say nothing about requiring a grouping operator.

 

[doh]

Is ORDERS a view? Does executing that query without LIMIT work correctly?

The docs for LIMIT/OFFSET say nothing about requiring a grouping operator.

No, Orders is a straight up table. Based on the first two queries in the psql output it seems ORDER BY with count(*) implies GROUP BY. I'll have to bug some proper Postgres gurus to get to the bottom of this I think.

 

[mikeblas]

Hmm, I missed that. Why are you using ORDER BY with COUNT(*)? The COUNT(*) statement should return just one row; the count. There's nothing to order here.

 

[doh]

Hmm, I missed that. Why are you using ORDER BY with COUNT(*)? The COUNT(*) statement should return just one row; the count. There's nothing to order here.

It's the way paginating_find does its thing. It passes everything on to ActiveRecord's handler only requesting a count.

 

[mikeblas]

The way it "does its thing" is busted; it is coughing up meaningless SQL.

 

[doh]

The way it "does its thing" is busted; it is coughing up meaningless SQL.

Yep. I've already emailed the dev and filed Yet Another Bug Report for it.

 

[doh]

Got in touch with the plugin's author and he fixed the issue (along with omitting LIMIT/OFFSET from the count(*) query).