July 24th, 2012, 10:01 AM
-
HSQL view ERROR: expression not in aggregate or GROUP BY columns
Hi all,
i have a CREATE VIEW query in hsql, but whenever i run it, it throws me this error:
expression not in aggregate or GROUP BY columns: AGV.ID
I understand that GROUP BY would not work without any aggregate expressions(AVG, SUM, MIN, MAX), but i cant figure out how to fix my query.. because each record need to be grouped by manifestID value.
Basically, im trying to create a VIEW by combining 3 set of select queries.
I tried to use distinct but no luck, since it will not work if i have multiple selected columns.
This query works fine in MYSQL.
Please help...
My Query:
Code:
CREATE VIEW local_view_event_manifest(
manifest_id,
eventId,
eventType,
eventDate,
manifestID,
businessStepStr,
manifestVersion,
externalLocation,
remark,
epcCode,
locationCode
)
AS
SELECT
agm.manifest_id AS manifest_id,
agv.id AS eventId,
'AGGREGATION_EVENT' AS eventType,
agv.event_time AS eventDate,
md.manifest_id AS manifestID,
agv.business_step_code AS businessStepStr,
md.manifest_version AS manifestVersion,
md.external_location AS externalLocation,
md.remark AS remark,
epc.code as epcCode,
bloc.location_code as locationCode
FROM
"local".local_MANIFEST_DATA AS md,
"local".local_AGGREGATION_EVENT AS agv,
"local".local_AGGREGATION_EVENT_EPCS AS agv_epc,
"local".local_EPC AS epc,
"local".local_BUSINESS_LOCATION AS bloc,
"local".local_AGGREGATION_EVENT_MANIFEST_DATA AS agm
WHERE
md.id=agm.manifest_id
AND agv.deleted=0
AND md.deleted=0
AND agv.id=agm.aggregation_event_id
AND agv.id=agv_epc.aggregation_event_id
AND agv.business_location_id=bloc.id
AND bloc.id=agv.business_location_id
AND agv_epc.epc_id=epc.id
GROUP BY agm.manifest_id
UNION
SELECT
om.manifest_id AS manifest_id,
ov.id AS eventId,
'OBJECT_EVENT' AS eventType,
ov.event_time AS eventDate,
md.manifest_id AS manifestID,
ov.business_step_code AS businessStepStr,
md.manifest_version AS manifestVersion,
md.external_location AS externalLocation,
md.remark AS remark,
epc.code as epcCode,
bloc.location_code as locationCode
FROM
"local".local_MANIFEST_DATA AS md,
"local".local_OBJECT_EVENT AS ov,
"local".local_OBJECT_EVENT_EPCS AS ov_epc,
"local".local_EPC AS epc,
"local".local_BUSINESS_LOCATION AS bloc,
"local".local_OBJECT_EVENT_MANIFEST_DATA AS om
WHERE
md.id=om.manifest_id
AND ov.deleted=0
AND md.deleted=0
AND ov.id=ov_epc.object_event_id
AND ov.id=om.object_event_id
AND bloc.id=ov.business_location_id
AND ov_epc.epc_id=epc.id
GROUP BY om.manifest_id
UNION
SELECT
trm.manifest_id AS manifest_id,
trv.id AS eventId,
'TRANSACTION_EVENT' AS eventType,
trv.event_time AS eventDate,
md.manifest_id AS manifestID,
trv.business_step_code AS businessStepStr,
md.manifest_version AS manifestVersion,
md.external_location AS externalLocation,
md.remark AS remark,
epc.code as epcCode,
bloc.location_code as locationCode
FROM
"local".local_MANIFEST_DATA AS md,
"local".local_TRANSACTION_EVENT AS trv,
"local".local_TRANSACTION_EVENT_EPCS AS trv_epc,
"local".local_EPC AS epc,
"local".local_BUSINESS_LOCATION AS bloc,
"local".local_TRANSACTION_EVENT_MANIFEST_DATA AS trm
WHERE
md.id=trm.manifest_id
AND trv.deleted=0
AND md.deleted=0
AND trv.id=trv_epc.transaction_event_id
AND trv.id=trm.transaction_event_id
AND bloc.id=trv.business_location_id
AND trv_epc.epc_id=epc.id
GROUP BY trm.manifest_id
below are snapshot of query result in mysql if using GROUP BY & without GROUP BY:
using group by:

i106.photobucket.com/albums/m256/mag_geo8/group_by.png
without GROUP BY:
i106.photobucket.com/albums/m256/mag_geo8/without_group.png
Thanks in advanced..
Last edited by mary8; July 24th, 2012 at 10:10 AM.
Reason: added query result details
July 24th, 2012, 10:55 AM
-
Originally Posted by mary8
This query works fine in MYSQL.
because mysql has a relaxed GROUP BY requirement
according to the standard, every non-aggregate expression (other than constants) in the SELECT clause must also be in the GROUP BY clause
apparently, hsql, whatever that is, follows the standard
July 24th, 2012, 11:25 AM
-
Originally Posted by r937
according to the standard, every non-aggregate expression (other than constants) in the SELECT clause must also be in the GROUP BY clause
In case you are interested: the above is true unless there is a unique key selected. In that case it's sufficient to include that unique key in the group by clause. This part of the standard is now supported e.g. by PostgreSQL
A Java based (embedded/server side) DBMS: http://www.hsqldb.org
Quite nice actually.
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code
Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html
July 24th, 2012, 11:41 AM
-
Originally Posted by shammat
In case you are interested: the above is true unless there is a unique key selected. In that case it's sufficient to include that unique key in the group by clause. This part of the standard is now supported e.g. by PostgreSQL
oh really? would you happen to have a link?
so you're saying this would be okay? --
Code:
SELECT orders.id
, order_items.product_id
FROM orders
INNER
JOIN orderitems
ON orderitems.order_id = orders.id
GROUP
BY orders.id
mysql has allowed that construction for, like, years
July 24th, 2012, 12:17 PM
-
Originally Posted by r937
oh really? would you happen to have a link?
From http://www.postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY
Originally Posted by Postgres manual
When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or if the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column
Here is one of the threads from the PG mailing list that mention that this is defined in the standard:
http://postgresql.1045698.n5.nabble.com/Improving-GROUP-BY-tt1849812.html
so you're saying this would be okay? --
Code:
SELECT orders.id
, order_items.product_id
FROM orders
INNER
JOIN orderitems
ON orderitems.order_id = orders.id
GROUP
BY orders.id
mysql has allowed that construction for, like, years
Yes, that is valid since Postgres 9.1 (provided orders.id is the primary key)
But in contrast to MySQL the result of such a query is well defined. Btw. MySQL is the only DBMS that allows this "relaxed" syntax (especially if the grouping column is not the PK)
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code
Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html
July 24th, 2012, 12:37 PM
-
Originally Posted by shammat
But in contrast to MySQL the result of such a query is well defined.
if you wouldn't mind, just one more question
which product does it show?
July 24th, 2012, 01:22 PM
-
Originally Posted by r937
if you wouldn't mind, just one more question
which product does it show?
PostgreSQL - or any DBMS that only allows grouping when the result can be clearly identified e.g. by requiring all non-aggregate columns to be in the group by clause or a primary key (although I don't know of any other DBMS that implements that currently)
MySQL instead allows any combination of columns in the group by even if violating the rules for grouping. In that case the results will be non-deterministic (it's that MySQL habit of "guessing" what you mean instead of reporting an error or a warning).
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code
Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html
July 24th, 2012, 02:31 PM
-
Originally Posted by shammat
MySQL instead allows ...
perhaps we misunderstood each other
you said that the result of the query i posted is well defined in postgresql, and that the query is valid
perhaps you did not notice that orders-orderitems is one-to-many, and that order_items.product_id is of course not functionally dependent on orders.id
and therefore the query i posted is ~not~ valid in postgresql after all
July 24th, 2012, 02:51 PM
-
Originally Posted by r937
perhaps you did not notice that orders-orderitems is one-to-many, and that order_items.product_id is of course not functionally dependent on orders.id
You are right. I didn' notice that. And that statement is indeed not valid then. Sorry for the confusion
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code
Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html
July 24th, 2012, 09:35 PM
-
Originally Posted by shammat
Sorry for the confusion
es macht ja gar nichts 
if you've read http://rpbouman.blogspot.com/2007/05...-by-myths.html, a carefully detailed explanation of mysql's behaviour, it seems that mysql engineers were on the same track with this functionally dependent idea... and did it many years ago
unfortunately, i think mysql's relaxed syntax, while it works beautifully for true cases of functional dependence, has caused countless n00bs immeasurable grief because when they write queries like the one i posted above, they get indeterminate results