|
Home > Archive > Apache JDO Project > October 2006 > JDOQL query problems (JPOX issues?)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
JDOQL query problems (JPOX issues?)
|
|
| Michael Bouschen 2006-10-27, 7:11 pm |
| Hi,
I'm having problems running JDOQL queries that group by a variable. I
think the queries below are valid, but I would like to double check
this. If you agree that the queries are valid JDOQL, I will check the
TCK to add these queries to existing TCK tests or add new test cases. I
tried the queries with JPOX version 1.1.3 and with the nightly build
from Oct 27 (no difference). I will send a test case to reproduce the
problem to Erik and Andy, since I cannot attach archives here.
The class model is simple: pc class A has a field stringCol which is a
collection of strings and another field bCol which is a collection of
instances of class B.
The following query groups the class A instances by the strings in their
string collection:
Query q = pm.newQuery(A.class);
q.declareVariables("java.lang.String str");
q.setFilter("this.stringCol.contains(str)");
q.setGrouping("str");
q.setResult("str");
This results in an exception:
JDOUserException: Unable to find the field "str" in the candidate
class. It is possible that this field is a field in a subclass, but it
is illegal to reference fields directly when they are in a subclass.
I get a different exception when adding an aggregate to the result clause
q.setResult("str, count(this)");
JDOUserException: Unconstrained variable referenced: str
The behavior is different when iterating a collection of pc instances:
Query q = pm.newQuery(A.class);
q.declareVariables("model.B b");
q.setFilter("this.bCol.contains(b)");
q.setGrouping("b");
q.setResult("count(this), b");
This results in:
JDOUserException: The result clause has a field expression
"UnboundVariable "UNBOUND_B.ID"" that doesnt appear in the grouping. Any
result specification has to be present in the grouping when grouping is
specified.
Any help is appreciated. Thanks!
Regards Michael
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
| |
| Erik Bengtson 2006-10-28, 7:11 am |
| Quoting Michael Bouschen <mbo.tech@spree.de>:
> The following query groups the class A instances by the strings in their
> string collection:
> Query q = pm.newQuery(A.class);
> q.declareVariables("java.lang.String str");
> q.setFilter("this.stringCol.contains(str)");
> q.setGrouping("str");
> q.setResult("str");
> This results in an exception:
> JDOUserException: Unable to find the field "str" in the candidate
> class. It is possible that this field is a field in a subclass, but it
> is illegal to reference fields directly when they are in a subclass.
>
The query should work, but grouping on a literal has not a lot of sense. The
compiler should ignore the grouping.
> I get a different exception when adding an aggregate to the result clause
> q.setResult("str, count(this)");
> JDOUserException: Unconstrained variable referenced: str
>
Should work again.
> The behavior is different when iterating a collection of pc instances:
> Query q = pm.newQuery(A.class);
> q.declareVariables("model.B b");
> q.setFilter("this.bCol.contains(b)");
> q.setGrouping("b");
> q.setResult("count(this), b");
> This results in:
> JDOUserException: The result clause has a field expression
> "UnboundVariable "UNBOUND_B.ID"" that doesnt appear in the grouping. Any
> result specification has to be present in the grouping when grouping is
> specified.
>
And should work too.
| |
| Craig L Russell 2006-10-28, 1:11 pm |
| | |
| Erik Bengtson 2006-10-28, 1:11 pm |
| Michael,
I was reading Craig comments and noted that "str" is an unbound variable and not
a parameter, and now I think the query is invalid since there is no value for
str.
Unbound variables are not null values.
Quoting Craig L Russell <Craig.Russell@Sun.COM>:
> Hi Michael,
>
> From the spec,
> <spec>
> The candidate tuples
> are the cartesian product of the candidate class and all variables
> used in the result. The re-
> sult tuples are the tuples of the candidate class and all variables
> used in the result that sat-
> isfy the filter. The result is the collection of result expressions
> projected from the result
> tuples.
> </spec>
>
> On Oct 27, 2006, at 2:12 PM, Michael Bouschen wrote:
>
>
> The cartesian product of the candidate class and all variables is a
> the cartesian product of all A instances and all strings contained in
> any stringCol. The result tuples consist of tuples of (A, String)
> where the elements of stringCol are projected and associated with the
> instances of A whence they came. The result comes from grouping and
> projecting the String from the result tuple. So,
>
> This query should collect all of the unique strings in all instances
> of A stringCol. The result is a List<String>. I don't know offhand
> how the implementation can do this trick (returning a List<Object> in
> which each element is a String is easy).
>
>
> This query should collect all of the unique strings in all instances
> of A stringCol, count them, and return the string and count of
> occurrences. The result is a List<Object[ ]> Each element consists of
> an Object[ ] containing a String in element 0 and a Long in element 1.
>
> This query should collect all of the unique B instances in all
> instances of A bCol, count them, and return the B and count of
> occurrences. The result is a List<Object[ ]> Each element consists of
> an Object[ ] containing a B in element 0 and a Long in element 1.
>
> Craig
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
>
| |
| Michael Bouschen 2006-10-28, 1:11 pm |
| Hi Erik,
I fully agree with Craig's description of what the queries should return.
> Michael,
>
> I was reading Craig comments and noted that "str" is an unbound variable and not
> a parameter, and now I think the query is invalid since there is no value for
> str.
>
Yes, str is a varaible and not a parameter. But why is it unbound? There
is a contains clause binding the variable to field stringCol which is a
collection of strings:
q.setFilter("this.stringCol.contains(str)");
The third query binds the variable b to the collection of B instances
called bCol:
q.setFilter("this.bCol.contains(b)");
Here are the three queries in single string JDOQL:
SELECT str FROM A WHERE this.stringCol.contains(str) VARIABLES String
str GROUP BY str
SELECT str, count(this) FROM A WHERE this.stringCol.contains(str)
VARIABLES String str GROUP BY str
SELECT b count(this) FROM A WHERE this.bCol.contains(b) VARIABLES B b
GROUP BY b
Here is the SQL query for the second JDOQL query. Suppose class A is
mapped to table TABLE_A and the string collection to table STRING_TABLE.
This table has a foreign key to table A and a VARCHAR column called text.
SELECT s.text, COUNT(a.id) FROM TABLE_A a, STRING_TABLE s WHERE s.a_id
= a.id GROUP BY s.text
I'm currently using this SQL query as a workaround and create a Query
instance with the "javax.jdo.query.SQL" language parameter. It returns
the expected result!
Regards Michael
> Unbound variables are not null values.
>
> Quoting Craig L Russell <Craig.Russell@Sun.COM>:
>
>
>
>
>
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
| |
| Craig L Russell 2006-10-28, 7:11 pm |
| | |
| Erik Bengtson 2006-10-28, 7:11 pm |
| Craig,
I understand that variables of PC get bound to a set of pc type, but strings...
What is it bound to?
rgds
Quoting Craig L Russell <Craig.Russell@Sun.COM>:
> Hi Erik,
>
> On Oct 28, 2006, at 7:19 AM, Erik Bengtson wrote:
>
>
> The variable "str" is bound by the filter q.setFilter
> ("this.stringCol.contains(str)").
>
> Craig
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
>
| |
| Michael Bouschen 2006-10-28, 7:11 pm |
| Hi Erik,
my 0.02$:
According to section "14.6.5 Variable declaration" a variable is a
constrained variable, if there is a contains clause for this variable.
W/o a contains clause this (so called unconstrained) variable is
constrained by the extent of the pc class. An unconstrained variable of
type String would not make sense, because there is no such extent. But
the variable "str" in the example has a contains clause constraining it
to the string collection field in A.
Regards Michael
> Craig,
>
> I understand that variables of PC get bound to a set of pc type, but strings...
> What is it bound to?
>
> rgds
>
> Quoting Craig L Russell <Craig.Russell@Sun.COM>:
>
>
>
>
>
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
| |
| Erik Bengtson 2006-10-28, 7:11 pm |
| Michael,
When compiling this query we do the following:
- define result sets
- set1 * setN (fails here since not constrained)
- restriction
We would have to change that to:
- define result sets
- restriction (this would constraint the str to stringCol)
- set1 * setN
If the query was evaluated by the JDO implementation the second one is certainly
the fastest.
I agree that this way the query should work, but needs clarification in the spec
on the evaluation order (unless I missed it).
Quoting Michael Bouschen <mbo.tech@spree.de>:
> Hi Erik,
>
> I fully agree with Craig's description of what the queries should return.
> and not
> for
> Yes, str is a varaible and not a parameter. But why is it unbound? There
> is a contains clause binding the variable to field stringCol which is a
> collection of strings:
> q.setFilter("this.stringCol.contains(str)");
> The third query binds the variable b to the collection of B instances
> called bCol:
> q.setFilter("this.bCol.contains(b)");
>
> Here are the three queries in single string JDOQL:
> SELECT str FROM A WHERE this.stringCol.contains(str) VARIABLES String
> str GROUP BY str
> SELECT str, count(this) FROM A WHERE this.stringCol.contains(str)
> VARIABLES String str GROUP BY str
> SELECT b count(this) FROM A WHERE this.bCol.contains(b) VARIABLES B b
> GROUP BY b
>
> Here is the SQL query for the second JDOQL query. Suppose class A is
> mapped to table TABLE_A and the string collection to table STRING_TABLE.
> This table has a foreign key to table A and a VARCHAR column called text.
> SELECT s.text, COUNT(a.id) FROM TABLE_A a, STRING_TABLE s WHERE s.a_id
> = a.id GROUP BY s.text
> I'm currently using this SQL query as a workaround and create a Query
> instance with the "javax.jdo.query.SQL" language parameter. It returns
> the expected result!
>
> Regards Michael
>
>
> --
> Michael Bouschen Tech@Spree Engineering GmbH
> mailto:mbo.tech@spree.de http://www.tech.spree.de/
> Tel.:++49/30/235 520-33 Buelowstr. 66
> Fax.:++49/30/2175 2012 D-10783 Berlin
>
>
| |
| Craig L Russell 2006-10-29, 1:11 am |
| | |
| Michael Bouschen 2006-10-29, 7:11 am |
| Hi Craig,
I added the SQL to my previous email:
SELECT s.text, COUNT(a.id) FROM TABLE_A a, STRING_TABLE s
WHERE s.a_id = a.id GROUP BY s.text
As a workaround I use the above SQL to create a "javax.jdo.query.SQL"
Query instance and it returns the expect result.
It uses the following schema:
CREATE TABLE TABLE_A (
id BIGINT PRIMARY KEY
);
CREATE TABLE TABLE_STRING (
a_id BIGINT REFERENCES TABLE_A(id),
text VARCHAR(255)
);
Erik,
I think the spec should not specify the evaluation order unless the
semantics of the query is affected. Theoretically you could join the set
of A instances with the set of all strings stored in the database and
then evaluate the restriction. The result would be same. A datastore
having an extent for Strings could work like this. Practically, this
approach does not work with relational databases.
Regards Michael
> Hi,
>
> I think it might be useful to look at what SQL we expect to generate
> based on the JDOQL here.
>
> Would someone like to try the JDOQL to SQL mapping? It might help
> understanding of the issue.
>
> Thanks,
>
> Craig
>
> On Oct 28, 2006, at 3:02 PM, Erik Bengtson wrote:
>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
| |
| Erik Bengtson 2006-10-29, 7:11 am |
| >
> Erik,
> I think the spec should not specify the evaluation order unless the
> semantics of the query is affected. Theoretically you could join the set
> of A instances with the set of all strings stored in the database and
> then evaluate the restriction. The result would be same. A datastore
> having an extent for Strings could work like this. Practically, this
> approach does not work with relational databases.
>
I agree with these terms. However for some object database the query "SELECT
str, this FROM A VARIABLES String str" would be fine, but others would become
very difficult to implement, but not impossible.
Besides theory, I will work in JPOX to fix the compiler.
> Regards Michael
>
>
> --
> Michael Bouschen Tech@Spree Engineering GmbH
> mailto:mbo.tech@spree.de http://www.tech.spree.de/
> Tel.:++49/30/235 520-33 Buelowstr. 66
> Fax.:++49/30/2175 2012 D-10783 Berlin
>
>
|
|
|
|
|