HSQL view ERROR: expression not in aggregate or GROUP BY columns

Dev Shed Forums Sponsor:

July 24th, 2012, 10:01 AM

mary8

Registered User

Join Date: Apr 2012

Posts: 2

Time spent in forums: 30 m 59 sec
Reputation Power: 0

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:
URL
i106.photobucket.com/albums/m256/mag_geo8/group_by.png

without GROUP BY:
URL
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

r937

SQL Consultant

Dev Shed God 43rd Plane (26000 - 26499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

July 24th, 2012, 11:25 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,701

Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 27 m 48 sec
Reputation Power: 284

Quote:

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

Quote:

hsql, whatever that is

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

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141

Quote:

Originally Posted by shammat

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

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,701

Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 27 m 48 sec
Reputation Power: 284

Quote:

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

Quote:

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

Quote:

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)

July 24th, 2012, 12:37 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141

Quote:

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

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,701

Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 27 m 48 sec
Reputation Power: 284

Quote:

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).

July 24th, 2012, 02:31 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141

Quote:

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

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,701

Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 27 m 48 sec
Reputation Power: 284

Quote:

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

#10

July 24th, 2012, 09:35 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 3 sec
Reputation Power: 4141

Quote:

Originally Posted by shammat

Sorry for the confusion

es macht ja gar nichts

if you've read http://rpbouman.blogspot.com/2007/0...p-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

HSQL view ERROR: expression not in aggregate or GROUP BY columns

Developer Shed Advertisers and Affiliates