db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: JDOQL query problems (JPOX issues?)
Date Sun, 29 Oct 2006 03:55:44 GMT
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 <mbo.tech@spree.de>:
>
>> 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>:
>>>
>>>
>>>> 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		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 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
>>
>>
>
>
>

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!


Mime
View raw message