Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: jdo-dev@db.apache.org
Received-SPF: neutral (herse.apache.org: local policy)
Message-ID: <1162122789.45449625677a1@webmail.jpox.org>
Date: Sun, 29 Oct 2006 03:53:09 -0800
From: Erik Bengtson <erik@jpox.org>
To: jdo-dev@db.apache.org
References: <4542764E.8020105@spree.de>
 <24878E87-227A-4A45-9217-3F6E56609417@SUN.com>
 <1162045186.454367021f005@webmail.jpox.org> <45438A4D.6040605@spree.de>
 <1162072964.4543d384ea3b2@webmail.jpox.org>
 <E146040F-E143-4E02-83E7-F74F4B4A1DC7@SUN.com> <45447C51.5000002@spree.de>
In-Reply-To: <45447C51.5000002@spree.de>
MIME-Version: 1.0
User-Agent: Internet Messaging Program (IMP) 3.2.3
Subject: Re: JDOQL query problems (JPOX issues?)
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

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