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:
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 <[EMAIL PROTECTED]>:
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 <[EMAIL PROTECTED]>:
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:
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");
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 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
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.
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.
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
Any help is appreciated. Thanks!
Regards Michael
--
Michael Bouschen [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
Craig Russell
Architect, Sun Java Enterprise System
http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
--
Michael Bouschen [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
--
Michael Bouschen [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
