db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Bouschen <mbo.t...@spree.de>
Subject Re: JDOQL query problems (JPOX issues?)
Date Sun, 29 Oct 2006 10:02:57 GMT
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 <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!
>


-- 
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			


Mime
View raw message