Re: PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query

In response to DaNieL <daniele(dot)pignedoli(at)gmail(dot)com>:

> Hi guys, this is my first approach to postgresql..
>
> Well, lets say that i have 3 tables: orders, customer, and order_item.
> The tables are really simple:
>
> ---
> CREATE TABLE customer (
> id integer NOT NULL,
> name character(50)
> );
> ---
> CREATE TABLE orders (
> id integer NOT NULL,
> id_customer integer,
> code character(10)
> );
>
> ---
> CREATE TABLE order_item (
> id integer NOT NULL,
> id_order integer,
> text text,
> price numeric(5,2)
> );
> ---
>
> Every id in every table is a PRIMARY KEY, UNIQUE, NOT NULL and serial
> type..
> The query that i have problem with is:
> ---
> SELECT
> orders.code,
> customer.name,
> SUM(order_item.price)
> FROM
> orders
> INNER JOIN customer ON (customer.id = orders.id_customer)
> INNER JOIN order_item ON (order_item.id_order = orders.id)
> GROUP BY orders.id
> ---
>
> thet report the error:
> ---
> ERROR: column "orders.code" must appear in the GROUP BY clause or be
> used in an aggregate function
> ---
>
> ...why?
> My goal is to retrieve something like
> 00001 | Mary | 439.43
> 00002 | Steve | 432.32
> 00003 | Abigail | 243.32
> 00004 | Steve | 156.23
> becose users can have many orders, and every order can have many
> items.
>
> Please, help me.. i really dont understand the error, becose if i
> group for the orders.code, the error point at the customer.name:
>
> ---
> ERROR: column "customer.name" must appear in the GROUP BY clause or
> be used in an aggregate function
> ---
>
> and if i add the customer.name in the GROUP BY statement, it works
> ---
> 0000000002 | Steve | 32
> 0000000001 | Abigail | 69.77
> 0000000003 | Abigail | 25.93
> ---
> .. but, why?
> Isn't the GROUP BY orders.id statement enaught?
> Why the costumers.name is needed?

Because the database serer has no way to know that it can do that.
You're grouping by order.id, because you _know_ that doing so will
capture what you want. PostgreSQL has no way of knowing this, what
it _does_ know is that there are certain possible table layouts in
which doing without grouping by both statements could lead to
incorrect results.

Someone with a more expensive education than me may jump in with
more details, but I'm pretty sure this behaviour is mandated by
the SQL standard. I mean, PostgreSQL _could_ behave the same way
as MySQL, but he designers chose this way because it's more
reliable.

> Sorry, i know that this maybe is a basically problem, but i come from
> mysql.. and in mysql that query works...

That's a shame, really ... that MySQL allows you to execute a query
that could produce indeterminate results.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/