You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by "David B. Johnson" <DB...@eentertainment.com> on 2007/01/12 08:57:42 UTC
Problem w/ SQL Query in 1.2RC
Hi,
I am running a repository on the 1.2 Release Candidate that I upgraded
from 1.1.1, i.e., I did not reload the data, I am using the same
repository. It is a JNDI DataSource Repository with a MySQL backend,
running as a JCA rar in JBoss. I am using an SQL query that was working
fine in 1.1.1 and now appears to not work in 1.2, specifically the query
is of the form:
select * from Story where jcr:path like '/News/Stories/%' and
publishDate >= DATE '2006-11-01' and publishDate <= DATE '2006-11-15'
Instead of only returning stories with a publishDate within the date
range, the system appears to return all stories.
Do I need to create a fresh repository, and re-import the data?
If I can help with any additional information, please feel free to
contact me.
-David B. Johnson
Re: Problem w/ SQL Query in 1.2RC
Posted by Christoph Kiehl <ki...@subshell.com>.
David Johnson wrote:
> So for this case, everything seems to be working fine. Nevertheless, in my
> case of a much larger repository, the upgrade did not seem to work. I have
> also tried deleting both the repository and workspace index directories,
> and
> still get all nodes rather than only those matching the date range.
Actually we are experiencing a similar behaviour. Somehow date comparison
queries on a large repository do not return the expected results. I was not able
to reproduce this behaviour with a small test repository and had not time yet to
investigate any further. But there seems to be something wrong.
My XPath query looks like this:
//*[@test:date > xs:dateTime('2004-09-17T16:43:52.546+01:00')]
This query also return nodes with a test:date value of
"2004-05-18T13:55:48.546+01:00".
I tried to rebuild the index as well, but this didn't change anything. May be
some of the core guys have an idea what's going wrong?
Cheers,
Christoph
Re: Problem w/ SQL Query in 1.2RC
Posted by David Johnson <db...@gmail.com>.
Hi Marcel,
Thanks for the quick response. I implemented your test - creating the
repository in 1.1.1, inserting the data and querying the data - with an
answer of 30 nodes. Upgraded the repository to the 1.2, queried the data
and indeed did get an answer of 30 nodes. Just for clarification, this was
a JCA rar running in JBoss with a JNDI Datasource using a MySQL Database.
So for this case, everything seems to be working fine. Nevertheless, in my
case of a much larger repository, the upgrade did not seem to work. I have
also tried deleting both the repository and workspace index directories, and
still get all nodes rather than only those matching the date range.
I will do further tests, if there are suggestions to where to look, I will
give them a try. Also, I am willing to look into the code and/or run it
through a debugger, if you can give me some direction where to look.
Thanks,
-Dave
On 1/12/07, Marcel Reutegger <ma...@gmx.net> wrote:
>
> Hi David,
>
> David B. Johnson wrote:
> > I am running a repository on the 1.2 Release Candidate that I upgraded
> >>from 1.1.1, i.e., I did not reload the data, I am using the same
> > repository. It is a JNDI DataSource Repository with a MySQL backend,
> > running as a JCA rar in JBoss. I am using an SQL query that was working
> > fine in 1.1.1 and now appears to not work in 1.2, specifically the query
> > is of the form:
> >
> > select * from Story where jcr:path like '/News/Stories/%' and
> > publishDate >= DATE '2006-11-01' and publishDate <= DATE '2006-11-15'
> >
> > Instead of only returning stories with a publishDate within the date
> > range, the system appears to return all stories.
> >
> > Do I need to create a fresh repository, and re-import the data?
>
> no, that shouldn't be necessary. all changes in 1.2 are backward
> compatible. at
> least that's the goal.
>
> I quickly tried to reproduce the issue with a simple test case [1]. First
> I ran
> the test on a jackrabbit 1.1 and then again on a jackrabbit 1.2 pointing
> to the
> existing 1.1 repository data. Both returned the same 30 results.
>
> > If I can help with any additional information, please feel free to
> > contact me.
>
> Is there anything else you changed when you upgraded jackrabbit?
>
> Can you please check if you can reproduce the issue with the below test
> case in
> your environment?
>
> regards
> marcel
>
>
> [1] test class:
>
> package org.apache.jackrabbit.core.query;
>
> import javax.jcr.RepositoryException;
> import javax.jcr.Node;
> import javax.jcr.query.QueryManager;
> import javax.jcr.query.Query;
> import java.util.Calendar;
> import java.text.SimpleDateFormat;
>
> /**
> * <code>DateRangeTest</code>...
> */
> public class DateRangeTest extends AbstractQueryTest {
>
> private static final SimpleDateFormat FORMAT = new
> SimpleDateFormat("yyyy-MM-dd");
>
> public void testRange() throws RepositoryException {
> if (!superuser.itemExists("/data")) {
> System.out.println("creating test data...");
> Node data = superuser.getRootNode().addNode("data");
> for (int i = 0; i < 100; i++) {
> Calendar c = Calendar.getInstance();
> c.add(Calendar.DATE, i);
> data.addNode("node" + i).setProperty("date", c);
> }
> superuser.save();
> }
>
> QueryManager qm = superuser.getWorkspace().getQueryManager();
> Calendar lower = Calendar.getInstance();
> lower.add(Calendar.DATE, 20);
> Calendar upper = Calendar.getInstance();
> upper.add(Calendar.DATE, 50);
> String sql = "select * from nt:unstructured " +
> "where " +
> "jcr:path like '/data/%' and " +
> "date >= DATE '" + FORMAT.format(lower.getTime()) + "' "
> +
> "and " +
> "date <= DATE '" + FORMAT.format(upper.getTime()) + "'";
> Query q = qm.createQuery(sql, Query.SQL);
> System.out.println("result size: " + q.execute
> ().getNodes().getSize());
> }
> }
>
>
Re: Problem w/ SQL Query in 1.2RC
Posted by Jukka Zitting <ju...@gmail.com>.
Hi,
On 1/13/07, Jukka Zitting <ju...@gmail.com> wrote:
> The query works as expected all the way to 225 created /test/nodeN
> nodes, but after that the date comparison just gets ignored and the
> query returns all the nodes (including everything in /jcr:system) in
> the workspace.
I file the issue JCR-707 for tracking this, please comment there if
you have more details that could help solve this issue.
Interestingly my test case works fine if the comparison is reversed or
if the test nodes are created as a single transient set of changes.
I'm considering this a blocker for the 1.2 release.
BR,
Jukka Zitting
Re: Problem w/ SQL Query in 1.2RC
Posted by Jukka Zitting <ju...@gmail.com>.
Hi,
On 1/12/07, David Johnson <db...@gmail.com> wrote:
> More interesting details - I printed out the publishDate for each Story. It
> seem that the upper bound is being properly matched, and it is the lower
> bound that is being ignored.
Interesting, thanks for the efforts to track this!
I did some more testing on this with the following code snippet:
Repository repository = new TransientRepository();
Session session = repository.login(
new SimpleCredentials("admin", "admin".toCharArray()));
Node test = session.getRootNode().addNode("test");
session.save();
for (int i = 0; i < 1000; i++) {
Node node = test.addNode("node" + i);
node.setProperty("date", Calendar.getInstance());
session.save();
QueryManager manager = session.getWorkspace().getQueryManager();
String xpath = "//*[@date > xs:dateTime('2001-01-01T00:00:00.000Z')]";
Query query = manager.createQuery(xpath, Query.XPATH);
NodeIterator iterator = query.execute().getNodes();
if (iterator.getSize() != i + 1) {
System.out.println((i + 1) + " != " + iterator.getSize());
while (iterator.hasNext()) {
System.out.println(iterator.nextNode().getPath());
}
break;
}
}
test.remove();
session.save();
session.logout();
The query works as expected all the way to 225 created /test/nodeN
nodes, but after that the date comparison just gets ignored and the
query returns all the nodes (including everything in /jcr:system) in
the workspace.
Very strange indeed, perhaps this is a Lucene issue? I'll dig deeper...
BR,
Jukka Zitting
Re: Problem w/ SQL Query in 1.2RC
Posted by David Johnson <db...@gmail.com>.
More interesting details - I printed out the publishDate for each Story. It
seem that the upper bound is being properly matched, and it is the lower
bound that is being ignored.
Also, I ran a nonsensical query:
select * from eol:Story where jcr:path like '/EOL/News/Stories/%' and
publishDate >= DATE '2006-11-15' and publishDate <= DATE '2006-11-01'
Again, the publishdate >= DATE '2006-11-15' was ignored, and all results
with a publishDate <= DATE '2006-11-01' were returned.
While I can reimport the data again, I would like to help track down any
issues that my upgrade may be exposing.
Thanks,
-Dave
On 1/12/07, Marcel Reutegger <ma...@gmx.net> wrote:
>
> Hi David,
>
> David B. Johnson wrote:
> > I am running a repository on the 1.2 Release Candidate that I upgraded
> >>from 1.1.1, i.e., I did not reload the data, I am using the same
> > repository. It is a JNDI DataSource Repository with a MySQL backend,
> > running as a JCA rar in JBoss. I am using an SQL query that was working
> > fine in 1.1.1 and now appears to not work in 1.2, specifically the query
> > is of the form:
> >
> > select * from Story where jcr:path like '/News/Stories/%' and
> > publishDate >= DATE '2006-11-01' and publishDate <= DATE '2006-11-15'
> >
> > Instead of only returning stories with a publishDate within the date
> > range, the system appears to return all stories.
> >
> > Do I need to create a fresh repository, and re-import the data?
>
> no, that shouldn't be necessary. all changes in 1.2 are backward
> compatible. at
> least that's the goal.
>
> I quickly tried to reproduce the issue with a simple test case [1]. First
> I ran
> the test on a jackrabbit 1.1 and then again on a jackrabbit 1.2 pointing
> to the
> existing 1.1 repository data. Both returned the same 30 results.
>
> > If I can help with any additional information, please feel free to
> > contact me.
>
> Is there anything else you changed when you upgraded jackrabbit?
>
> Can you please check if you can reproduce the issue with the below test
> case in
> your environment?
>
> regards
> marcel
>
>
> [1] test class:
>
> package org.apache.jackrabbit.core.query;
>
> import javax.jcr.RepositoryException;
> import javax.jcr.Node;
> import javax.jcr.query.QueryManager;
> import javax.jcr.query.Query;
> import java.util.Calendar;
> import java.text.SimpleDateFormat;
>
> /**
> * <code>DateRangeTest</code>...
> */
> public class DateRangeTest extends AbstractQueryTest {
>
> private static final SimpleDateFormat FORMAT = new
> SimpleDateFormat("yyyy-MM-dd");
>
> public void testRange() throws RepositoryException {
> if (!superuser.itemExists("/data")) {
> System.out.println("creating test data...");
> Node data = superuser.getRootNode().addNode("data");
> for (int i = 0; i < 100; i++) {
> Calendar c = Calendar.getInstance();
> c.add(Calendar.DATE, i);
> data.addNode("node" + i).setProperty("date", c);
> }
> superuser.save();
> }
>
> QueryManager qm = superuser.getWorkspace().getQueryManager();
> Calendar lower = Calendar.getInstance();
> lower.add(Calendar.DATE, 20);
> Calendar upper = Calendar.getInstance();
> upper.add(Calendar.DATE, 50);
> String sql = "select * from nt:unstructured " +
> "where " +
> "jcr:path like '/data/%' and " +
> "date >= DATE '" + FORMAT.format(lower.getTime()) + "' "
> +
> "and " +
> "date <= DATE '" + FORMAT.format(upper.getTime()) + "'";
> Query q = qm.createQuery(sql, Query.SQL);
> System.out.println("result size: " + q.execute
> ().getNodes().getSize());
> }
> }
>
>
Re: Problem w/ SQL Query in 1.2RC
Posted by Marcel Reutegger <ma...@gmx.net>.
Hi David,
David B. Johnson wrote:
> I am running a repository on the 1.2 Release Candidate that I upgraded
>>from 1.1.1, i.e., I did not reload the data, I am using the same
> repository. It is a JNDI DataSource Repository with a MySQL backend,
> running as a JCA rar in JBoss. I am using an SQL query that was working
> fine in 1.1.1 and now appears to not work in 1.2, specifically the query
> is of the form:
>
> select * from Story where jcr:path like '/News/Stories/%' and
> publishDate >= DATE '2006-11-01' and publishDate <= DATE '2006-11-15'
>
> Instead of only returning stories with a publishDate within the date
> range, the system appears to return all stories.
>
> Do I need to create a fresh repository, and re-import the data?
no, that shouldn't be necessary. all changes in 1.2 are backward compatible. at
least that's the goal.
I quickly tried to reproduce the issue with a simple test case [1]. First I ran
the test on a jackrabbit 1.1 and then again on a jackrabbit 1.2 pointing to the
existing 1.1 repository data. Both returned the same 30 results.
> If I can help with any additional information, please feel free to
> contact me.
Is there anything else you changed when you upgraded jackrabbit?
Can you please check if you can reproduce the issue with the below test case in
your environment?
regards
marcel
[1] test class:
package org.apache.jackrabbit.core.query;
import javax.jcr.RepositoryException;
import javax.jcr.Node;
import javax.jcr.query.QueryManager;
import javax.jcr.query.Query;
import java.util.Calendar;
import java.text.SimpleDateFormat;
/**
* <code>DateRangeTest</code>...
*/
public class DateRangeTest extends AbstractQueryTest {
private static final SimpleDateFormat FORMAT = new
SimpleDateFormat("yyyy-MM-dd");
public void testRange() throws RepositoryException {
if (!superuser.itemExists("/data")) {
System.out.println("creating test data...");
Node data = superuser.getRootNode().addNode("data");
for (int i = 0; i < 100; i++) {
Calendar c = Calendar.getInstance();
c.add(Calendar.DATE, i);
data.addNode("node" + i).setProperty("date", c);
}
superuser.save();
}
QueryManager qm = superuser.getWorkspace().getQueryManager();
Calendar lower = Calendar.getInstance();
lower.add(Calendar.DATE, 20);
Calendar upper = Calendar.getInstance();
upper.add(Calendar.DATE, 50);
String sql = "select * from nt:unstructured " +
"where " +
"jcr:path like '/data/%' and " +
"date >= DATE '" + FORMAT.format(lower.getTime()) + "' " +
"and " +
"date <= DATE '" + FORMAT.format(upper.getTime()) + "'";
Query q = qm.createQuery(sql, Query.SQL);
System.out.println("result size: " + q.execute().getNodes().getSize());
}
}