#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    2
    Rep Power
    0

    Question 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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,166
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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

    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
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,166
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,166
    Rep Power
    4274
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,166
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,166
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo