FAQ
EJBQL Query with relational queries (<, <=, >, >=) are throwing exceptions
--------------------------------------------------------------------------

Key: CAY-919
URL: https://issues.apache.org/cayenne/browse/CAY-919
Project: Cayenne
Issue Type: Bug
Components: Cayenne Core Library
Affects Versions: 3.0
Environment: Mac OS/X (Leopard), Java 1.5 Cayenne 3.M2
Reporter: Garry Watkins
Assignee: Andrus Adamchik
Priority: Blocker


When using a relational operator in an EJBQLQuery it is throwing a ParseException. Additionally shouldn't this return a data row instead a list of an Object[] array? That is assuming that one can alias the selected attributes. However, it appears that you cannot specify an alias.

Source:

EJBQLQuery q = new EJBQLQuery("SELECT at.isUsage, sum(at.amount), sum(at.hours) FROM AccrualTransaction at WHERE at.accrualCode = :accrualCode and at.employeeID = :employeeID and effectiveDate >= :payrollDate GROUP BY at.isUsage");
q.setParameter("accrualCode", bal.getCode());
q.setParameter("employeeID", emp.getEmployeeID());
q.setParameter("effectiveDate", balanceDate);
List results = emp.getObjectContext().performQuery(q); <<== throws exception here
System.out.println(results);

Error follows:

org.apache.cayenne.ejbql.parser.ParseException: Encountered "effectiveDate >=" at line 1, column 149.
Was expecting one of:
"(" ...
"NOT" ...
"EXISTS" ...
"+" ...
"-" ...
<DECIMAL_LITERAL> ...
<INTEGER_LITERAL> ...
":" ...
"?" ...
<IDENTIFIER> ...
<IDENTIFIER> "." ...
"LENGTH" ...
"LOCATE" ...
"ABS" ...
"SQRT" ...
"MOD" ...
"SIZE" ...
"AVG" ...
"MAX" ...
"MIN" ...
"SUM" ...
"COUNT" ...
"SELECT" ...
<STRING_LITERAL> ...
"CONCAT" ...
"SUBSTRING" ...
"TRIM" ...
"LOWER" ...
"UPPER" ...
<BOOLEAN_LITERAL> ...
"CURRENT_DATE" ...
"CURRENT_TIME" ...
"CURRENT_TIMESTAMP" ...
"NEW" ...
"ALL" ...
"ANY" ...
"SOME" ...
"EMPTY" ...
"ASC" ...
"DESC" ...
"ORDER" ...
"IS" ...
"MEMBER" ...
"OF" ...
"LIKE" ...
"ESCAPE" ...
"BETWEEN" ...
"NULL" ...
"OR" ...
"AND" ...
"LEADING" ...
"TRAILING" ...
"BOTH" ...
"DISTINCT" ...
"FROM" ...
"UPDATE" ...
"DELETE" ...
"WHERE" ...
"GROUP" ...
"BY" ...
"HAVING" ...
"AS" ...
"LEFT" ...
"OUTER" ...
"INNER" ...
"JOIN" ...
"FETCH" ...
"IN" ...
"SET" ...
"OBJECT" ...
<IDENTIFIER> "=" ...
<IDENTIFIER> "<>" ...
<IDENTIFIER> "NOT" ...
<IDENTIFIER> "MEMBER" ...


--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Search Discussions

  • Anonymous at Nov 15, 2007 at 7:46 pm
    [ https://issues.apache.org/cayenne/browse/CAY-919?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12601 ]

    Garry Watkins commented on CAY-919:
    -----------------------------------

    This relational operators are working and not an issue, i forgot to prefix the effectiveDate with an "at.", however it would be nice to have a better error message. However, my question about it returning a data row instead of a List of Objects is still a valid question. Additionally, it is throwing an exception if I alias the sum expression which is an issue. Let me know if I should create a new issue about the aliasing.

    Thanks
    Garry
    EJBQL Query with relational queries (<, <=, >, >=) are throwing exceptions
    --------------------------------------------------------------------------

    Key: CAY-919
    URL: https://issues.apache.org/cayenne/browse/CAY-919
    Project: Cayenne
    Issue Type: Bug
    Components: Cayenne Core Library
    Affects Versions: 3.0
    Environment: Mac OS/X (Leopard), Java 1.5 Cayenne 3.M2
    Reporter: Garry Watkins
    Assignee: Andrus Adamchik
    Priority: Blocker

    When using a relational operator in an EJBQLQuery it is throwing a ParseException. Additionally shouldn't this return a data row instead a list of an Object[] array? That is assuming that one can alias the selected attributes. However, it appears that you cannot specify an alias.
    Source:
    EJBQLQuery q = new EJBQLQuery("SELECT at.isUsage, sum(at.amount), sum(at.hours) FROM AccrualTransaction at WHERE at.accrualCode = :accrualCode and at.employeeID = :employeeID and effectiveDate >= :payrollDate GROUP BY at.isUsage");
    q.setParameter("accrualCode", bal.getCode());
    q.setParameter("employeeID", emp.getEmployeeID());
    q.setParameter("effectiveDate", balanceDate);
    List results = emp.getObjectContext().performQuery(q); <<== throws exception here
    System.out.println(results);
    Error follows:
    org.apache.cayenne.ejbql.parser.ParseException: Encountered "effectiveDate >=" at line 1, column 149.
    Was expecting one of:
    "(" ...
    "NOT" ...
    "EXISTS" ...
    "+" ...
    "-" ...
    <DECIMAL_LITERAL> ...
    <INTEGER_LITERAL> ...
    ":" ...
    "?" ...
    <IDENTIFIER> ...
    <IDENTIFIER> "." ...
    "LENGTH" ...
    "LOCATE" ...
    "ABS" ...
    "SQRT" ...
    "MOD" ...
    "SIZE" ...
    "AVG" ...
    "MAX" ...
    "MIN" ...
    "SUM" ...
    "COUNT" ...
    "SELECT" ...
    <STRING_LITERAL> ...
    "CONCAT" ...
    "SUBSTRING" ...
    "TRIM" ...
    "LOWER" ...
    "UPPER" ...
    <BOOLEAN_LITERAL> ...
    "CURRENT_DATE" ...
    "CURRENT_TIME" ...
    "CURRENT_TIMESTAMP" ...
    "NEW" ...
    "ALL" ...
    "ANY" ...
    "SOME" ...
    "EMPTY" ...
    "ASC" ...
    "DESC" ...
    "ORDER" ...
    "IS" ...
    "MEMBER" ...
    "OF" ...
    "LIKE" ...
    "ESCAPE" ...
    "BETWEEN" ...
    "NULL" ...
    "OR" ...
    "AND" ...
    "LEADING" ...
    "TRAILING" ...
    "BOTH" ...
    "DISTINCT" ...
    "FROM" ...
    "UPDATE" ...
    "DELETE" ...
    "WHERE" ...
    "GROUP" ...
    "BY" ...
    "HAVING" ...
    "AS" ...
    "LEFT" ...
    "OUTER" ...
    "INNER" ...
    "JOIN" ...
    "FETCH" ...
    "IN" ...
    "SET" ...
    "OBJECT" ...
    <IDENTIFIER> "=" ...
    <IDENTIFIER> "<>" ...
    <IDENTIFIER> "NOT" ...
    <IDENTIFIER> "MEMBER" ...
    --
    This message is automatically generated by JIRA.
    -
    You can reply to this email to add a comment to the issue online.
  • Anonymous at Nov 15, 2007 at 8:02 pm
    [ https://issues.apache.org/cayenne/browse/CAY-919?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

    Andrus Adamchik updated CAY-919:
    --------------------------------

    Priority: Major (was: Blocker)
    This relational operators are working and not an issue, i forgot to prefix the effectiveDate with an "at.",
    Cool. I reduced the priority of the issue since this actually works.
    however it would be nice to have a better error message.
    Agreed. We'll need to figure out how to interpret such failures in the parser and provide a better message.
    Additionally shouldn't this return a data row instead a list of an Object[] array?
    (this maybe something to discuss on the user list). Since EJBQL sort of grew up out of our JPA provider work AND we haven't supported aggregate queries that well before, we started to follow the JPA pattern for the query results - Object[] for non-object results. This is not yet documented, as we need to figure out how to reconcile it with DataRows conceptually (besides there are still some implementation limitations).
    Additionally, it is throwing an exception if I alias the sum expression which is an issue. Let me know if I should create a new issue about the aliasing.
    Hmm... per EJBQL spec you can't alias aggregate column. That's where Object[] comes in - you can process the results by position instead of by label.
    EJBQL Query with relational queries (<, <=, >, >=) are throwing exceptions
    --------------------------------------------------------------------------

    Key: CAY-919
    URL: https://issues.apache.org/cayenne/browse/CAY-919
    Project: Cayenne
    Issue Type: Bug
    Components: Cayenne Core Library
    Affects Versions: 3.0
    Environment: Mac OS/X (Leopard), Java 1.5 Cayenne 3.M2
    Reporter: Garry Watkins
    Assignee: Andrus Adamchik

    When using a relational operator in an EJBQLQuery it is throwing a ParseException. Additionally shouldn't this return a data row instead a list of an Object[] array? That is assuming that one can alias the selected attributes. However, it appears that you cannot specify an alias.
    Source:
    EJBQLQuery q = new EJBQLQuery("SELECT at.isUsage, sum(at.amount), sum(at.hours) FROM AccrualTransaction at WHERE at.accrualCode = :accrualCode and at.employeeID = :employeeID and effectiveDate >= :payrollDate GROUP BY at.isUsage");
    q.setParameter("accrualCode", bal.getCode());
    q.setParameter("employeeID", emp.getEmployeeID());
    q.setParameter("effectiveDate", balanceDate);
    List results = emp.getObjectContext().performQuery(q); <<== throws exception here
    System.out.println(results);
    Error follows:
    org.apache.cayenne.ejbql.parser.ParseException: Encountered "effectiveDate >=" at line 1, column 149.
    Was expecting one of:
    "(" ...
    "NOT" ...
    "EXISTS" ...
    "+" ...
    "-" ...
    <DECIMAL_LITERAL> ...
    <INTEGER_LITERAL> ...
    ":" ...
    "?" ...
    <IDENTIFIER> ...
    <IDENTIFIER> "." ...
    "LENGTH" ...
    "LOCATE" ...
    "ABS" ...
    "SQRT" ...
    "MOD" ...
    "SIZE" ...
    "AVG" ...
    "MAX" ...
    "MIN" ...
    "SUM" ...
    "COUNT" ...
    "SELECT" ...
    <STRING_LITERAL> ...
    "CONCAT" ...
    "SUBSTRING" ...
    "TRIM" ...
    "LOWER" ...
    "UPPER" ...
    <BOOLEAN_LITERAL> ...
    "CURRENT_DATE" ...
    "CURRENT_TIME" ...
    "CURRENT_TIMESTAMP" ...
    "NEW" ...
    "ALL" ...
    "ANY" ...
    "SOME" ...
    "EMPTY" ...
    "ASC" ...
    "DESC" ...
    "ORDER" ...
    "IS" ...
    "MEMBER" ...
    "OF" ...
    "LIKE" ...
    "ESCAPE" ...
    "BETWEEN" ...
    "NULL" ...
    "OR" ...
    "AND" ...
    "LEADING" ...
    "TRAILING" ...
    "BOTH" ...
    "DISTINCT" ...
    "FROM" ...
    "UPDATE" ...
    "DELETE" ...
    "WHERE" ...
    "GROUP" ...
    "BY" ...
    "HAVING" ...
    "AS" ...
    "LEFT" ...
    "OUTER" ...
    "INNER" ...
    "JOIN" ...
    "FETCH" ...
    "IN" ...
    "SET" ...
    "OBJECT" ...
    <IDENTIFIER> "=" ...
    <IDENTIFIER> "<>" ...
    <IDENTIFIER> "NOT" ...
    <IDENTIFIER> "MEMBER" ...
    --
    This message is automatically generated by JIRA.
    -
    You can reply to this email to add a comment to the issue online.
  • Anonymous at Nov 15, 2007 at 11:24 pm
    [ https://issues.apache.org/cayenne/browse/CAY-919?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12603 ]

    Garry Watkins commented on CAY-919:
    -----------------------------------
    Hmm... per EJBQL spec you can't alias aggregate column. That's where Object[] comes in - you can process the results by position instead of by label.
    I don't know the EJBQL spec, so I was just applying standard SQL logic, and was assuming that it would be in there. So it looks like the EJBQL spec is kind of a step backward in the fact that it doesn't support aliases. Because if I decide to change how things are grouped (added or removed) I would have to change all of my indexes. However, it looks like Hibernate does support aliases. Please look towards the end of section 7.4 of this link: http://www.hibernate.org/hib_docs/entitymanager/reference/en/html/queryhql.html I am not saying that Cayenne should do everything that Hibernate does. However, I believe that if we allowed aliases, then we could pop the data back into DataRows. If left unaliased then we could just return the values in an Object[].

    Just my 2 cents, perhaps I can pose this question in the users mailing list.

    Thanks
    Garry



    EJBQL Query with relational queries (<, <=, >, >=) are throwing exceptions
    --------------------------------------------------------------------------

    Key: CAY-919
    URL: https://issues.apache.org/cayenne/browse/CAY-919
    Project: Cayenne
    Issue Type: Bug
    Components: Cayenne Core Library
    Affects Versions: 3.0
    Environment: Mac OS/X (Leopard), Java 1.5 Cayenne 3.M2
    Reporter: Garry Watkins
    Assignee: Andrus Adamchik

    When using a relational operator in an EJBQLQuery it is throwing a ParseException. Additionally shouldn't this return a data row instead a list of an Object[] array? That is assuming that one can alias the selected attributes. However, it appears that you cannot specify an alias.
    Source:
    EJBQLQuery q = new EJBQLQuery("SELECT at.isUsage, sum(at.amount), sum(at.hours) FROM AccrualTransaction at WHERE at.accrualCode = :accrualCode and at.employeeID = :employeeID and effectiveDate >= :payrollDate GROUP BY at.isUsage");
    q.setParameter("accrualCode", bal.getCode());
    q.setParameter("employeeID", emp.getEmployeeID());
    q.setParameter("effectiveDate", balanceDate);
    List results = emp.getObjectContext().performQuery(q); <<== throws exception here
    System.out.println(results);
    Error follows:
    org.apache.cayenne.ejbql.parser.ParseException: Encountered "effectiveDate >=" at line 1, column 149.
    Was expecting one of:
    "(" ...
    "NOT" ...
    "EXISTS" ...
    "+" ...
    "-" ...
    <DECIMAL_LITERAL> ...
    <INTEGER_LITERAL> ...
    ":" ...
    "?" ...
    <IDENTIFIER> ...
    <IDENTIFIER> "." ...
    "LENGTH" ...
    "LOCATE" ...
    "ABS" ...
    "SQRT" ...
    "MOD" ...
    "SIZE" ...
    "AVG" ...
    "MAX" ...
    "MIN" ...
    "SUM" ...
    "COUNT" ...
    "SELECT" ...
    <STRING_LITERAL> ...
    "CONCAT" ...
    "SUBSTRING" ...
    "TRIM" ...
    "LOWER" ...
    "UPPER" ...
    <BOOLEAN_LITERAL> ...
    "CURRENT_DATE" ...
    "CURRENT_TIME" ...
    "CURRENT_TIMESTAMP" ...
    "NEW" ...
    "ALL" ...
    "ANY" ...
    "SOME" ...
    "EMPTY" ...
    "ASC" ...
    "DESC" ...
    "ORDER" ...
    "IS" ...
    "MEMBER" ...
    "OF" ...
    "LIKE" ...
    "ESCAPE" ...
    "BETWEEN" ...
    "NULL" ...
    "OR" ...
    "AND" ...
    "LEADING" ...
    "TRAILING" ...
    "BOTH" ...
    "DISTINCT" ...
    "FROM" ...
    "UPDATE" ...
    "DELETE" ...
    "WHERE" ...
    "GROUP" ...
    "BY" ...
    "HAVING" ...
    "AS" ...
    "LEFT" ...
    "OUTER" ...
    "INNER" ...
    "JOIN" ...
    "FETCH" ...
    "IN" ...
    "SET" ...
    "OBJECT" ...
    <IDENTIFIER> "=" ...
    <IDENTIFIER> "<>" ...
    <IDENTIFIER> "NOT" ...
    <IDENTIFIER> "MEMBER" ...
    --
    This message is automatically generated by JIRA.
    -
    You can reply to this email to add a comment to the issue online.
  • Anonymous at May 3, 2008 at 1:39 pm
    [ https://issues.apache.org/cayenne/browse/CAY-919?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

    Andrus Adamchik closed CAY-919.
    -------------------------------

    Resolution: Won't Fix

    I guess we need a separate Jira to deal with better error messages
    EJBQL Query with relational queries (<, <=, >, >=) are throwing exceptions
    --------------------------------------------------------------------------

    Key: CAY-919
    URL: https://issues.apache.org/cayenne/browse/CAY-919
    Project: Cayenne
    Issue Type: Bug
    Components: Cayenne Core Library
    Affects Versions: 3.0
    Environment: Mac OS/X (Leopard), Java 1.5 Cayenne 3.M2
    Reporter: Garry Watkins
    Assignee: Andrus Adamchik

    When using a relational operator in an EJBQLQuery it is throwing a ParseException. Additionally shouldn't this return a data row instead a list of an Object[] array? That is assuming that one can alias the selected attributes. However, it appears that you cannot specify an alias.
    Source:
    EJBQLQuery q = new EJBQLQuery("SELECT at.isUsage, sum(at.amount), sum(at.hours) FROM AccrualTransaction at WHERE at.accrualCode = :accrualCode and at.employeeID = :employeeID and effectiveDate >= :payrollDate GROUP BY at.isUsage");
    q.setParameter("accrualCode", bal.getCode());
    q.setParameter("employeeID", emp.getEmployeeID());
    q.setParameter("effectiveDate", balanceDate);
    List results = emp.getObjectContext().performQuery(q); <<== throws exception here
    System.out.println(results);
    Error follows:
    org.apache.cayenne.ejbql.parser.ParseException: Encountered "effectiveDate >=" at line 1, column 149.
    Was expecting one of:
    "(" ...
    "NOT" ...
    "EXISTS" ...
    "+" ...
    "-" ...
    <DECIMAL_LITERAL> ...
    <INTEGER_LITERAL> ...
    ":" ...
    "?" ...
    <IDENTIFIER> ...
    <IDENTIFIER> "." ...
    "LENGTH" ...
    "LOCATE" ...
    "ABS" ...
    "SQRT" ...
    "MOD" ...
    "SIZE" ...
    "AVG" ...
    "MAX" ...
    "MIN" ...
    "SUM" ...
    "COUNT" ...
    "SELECT" ...
    <STRING_LITERAL> ...
    "CONCAT" ...
    "SUBSTRING" ...
    "TRIM" ...
    "LOWER" ...
    "UPPER" ...
    <BOOLEAN_LITERAL> ...
    "CURRENT_DATE" ...
    "CURRENT_TIME" ...
    "CURRENT_TIMESTAMP" ...
    "NEW" ...
    "ALL" ...
    "ANY" ...
    "SOME" ...
    "EMPTY" ...
    "ASC" ...
    "DESC" ...
    "ORDER" ...
    "IS" ...
    "MEMBER" ...
    "OF" ...
    "LIKE" ...
    "ESCAPE" ...
    "BETWEEN" ...
    "NULL" ...
    "OR" ...
    "AND" ...
    "LEADING" ...
    "TRAILING" ...
    "BOTH" ...
    "DISTINCT" ...
    "FROM" ...
    "UPDATE" ...
    "DELETE" ...
    "WHERE" ...
    "GROUP" ...
    "BY" ...
    "HAVING" ...
    "AS" ...
    "LEFT" ...
    "OUTER" ...
    "INNER" ...
    "JOIN" ...
    "FETCH" ...
    "IN" ...
    "SET" ...
    "OBJECT" ...
    <IDENTIFIER> "=" ...
    <IDENTIFIER> "<>" ...
    <IDENTIFIER> "NOT" ...
    <IDENTIFIER> "MEMBER" ...
    --
    This message is automatically generated by JIRA.
    -
    You can reply to this email to add a comment to the issue online.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdev @
categoriescayenne
postedNov 14, '07 at 2:01a
activeMay 3, '08 at 1:39p
posts5
users1
websitecayenne.apache.org

1 user in discussion

Anonymous: 5 posts

People

Translate

site design / logo © 2022 Grokbase