SQL-2 Child Axis in order by clause

8 messages

SQL-2 Child Axis in order by clause

Reply Threaded

Hello,

I am using Jackrabbit 2.0 alpha10 and I am testing the following SQL-2
query:

select press.* from [web_templates:pressContainer] as press inner join
[jnt:translation] as translation on ischildnode(translation, press) where
contains(press.*, '${searchTerm}') or contains(translation.*,
'${searchTerm}') order by translation.[jcr:title_en] desc

but the ordering does not work. Debugging the SimpleScoreDocComparator I see
that the values for comparing are null all the time. Looking at
*JCR-800<http://issues.apache.org/jira/browse/JCR-800>
*I assume that RelPathScoreDocComparator should be used instead. Does this
already work with SQL-2 queries or do I have to change the query in order to
make it work ?

Regards,
Benjamin

Marcel Reutegger

Re: SQL-2 Child Axis in order by clause

Reply Threaded

Hi,

On Wed, Oct 21, 2009 at 10:01, Benjamin Papez <[hidden email]> wrote:

> Hello,
>
> I am using Jackrabbit 2.0 alpha10 and I am testing the following SQL-2
> query:
>
> select press.* from [web_templates:pressContainer] as press inner join
> [jnt:translation] as translation on ischildnode(translation, press) where
> contains(press.*, '${searchTerm}') or contains(translation.*,
> '${searchTerm}') order by translation.[jcr:title_en] desc
>
> but the ordering does not work.

does it work without the join?

otherwise please file a jira issue and provide steps how to reproduce
the issue. thanks.

> Debugging the SimpleScoreDocComparator I see
> that the values for comparing are null all the time. Looking at
> *JCR-800<http://issues.apache.org/jira/browse/JCR-800>
> *I assume that RelPathScoreDocComparator should be used instead.

that one is only used when there is a relative path in the order by
clause with more than one segment. otherwise the
SimpleScoreDocComparator is used.

regards
marcel

> Does this
> already work with SQL-2 queries or do I have to change the query in order to
> make it work ?
>
> Regards,
> Benjamin
>

Benjamin Papez

Re: SQL-2 Child Axis in order by clause

Reply Threaded

Hello Marcel,

thanks for your reply. You asked me if ordering on child axis works without
the join and that RelPathScoreDocComparator only works when using a relative
path in the order by. I have tried to use a relative path, but I was getting
syntax errors in SQL-2.

Could you tell me the right SQL-2 syntax for my query using a relative path
in the order by ? And how can I make the same fulltext search select on
web_templates:pressContainer and its jnt:transalation child node without
join? I also only want to get one result even if there is a match in the
pressContainer and the child node.

Thanks for your help.

Regards,
Benjamin

2009/10/26 Marcel Reutegger <[hidden email]>

> Hi,
>
> On Wed, Oct 21, 2009 at 10:01, Benjamin Papez <[hidden email]>
> wrote:
> > Hello,
> >
> > I am using Jackrabbit 2.0 alpha10 and I am testing the following SQL-2
> > query:
> >
> > select press.* from [web_templates:pressContainer] as press inner join
> > [jnt:translation] as translation on ischildnode(translation, press) where
> > contains(press.*, '${searchTerm}') or contains(translation.*,
> > '${searchTerm}') order by translation.[jcr:title_en] desc
> >
> > but the ordering does not work.
>
> does it work without the join?
>
> otherwise please file a jira issue and provide steps how to reproduce
> the issue. thanks.
>
> > Debugging the SimpleScoreDocComparator I see
> > that the values for comparing are null all the time. Looking at
> > *JCR-800<http://issues.apache.org/jira/browse/JCR-800>
> > *I assume that RelPathScoreDocComparator should be used instead.
>
> that one is only used when there is a relative path in the order by
> clause with more than one segment. otherwise the
> SimpleScoreDocComparator is used.
>
> regards
> marcel
>
> > Does this
> > already work with SQL-2 queries or do I have to change the query in order
> to
> > make it work ?
> >
> > Regards,
> > Benjamin
> >
>

Thomas Müller-2

Re: SQL-2 Child Axis in order by clause

Reply Threaded

Hi,

> I have tried to use a relative path, but I was getting
> syntax errors in SQL-2.

Could you tell me the exact statement you have used and the exception
message and stack trace?

Regards,
Thomas

Benjamin Papez

Re: SQL-2 Child Axis in order by clause

Reply Threaded

Hello,

my wanted query is like this, which seems to be a valid query from
specification point of view:

select press.* from [web_templates:pressContainer] as press inner join
[jnt:translation] as translation on ischildnode(translation, press) where
contains(press.*, '${searchTerm}') or contains(translation.*,
'${searchTerm}') order by translation.[jcr:title_en] desc

unfortunately it does not work, because it looks like
SimpleScoreDocComparator is looking in Lucene documents representing
pressContainer and not translation (child node).

I saw that Jackrabbit implemented a RelPathScoreDocComparator for use cases
like mine, but that comparator implementation is not picked with queries
like the one above. Like Marcel mentioned it is only used when there is a
relative path in the order by statement.

Now according to JSR-283 specifications it looks like relative path is not
allowed in the order by statement. That is also why I get the following
error, trying it:

2009-11-04 12:42:08,750: ERROR [JCRSQLTag] - InvalidQueryException --->
[select press.* from [web_templates:pressContainer] as press inner join
[jnt:translation] as translation on ischildnode(translation, press) inner
join [nt:file] as file on translation.pdfVersion_en = file.[jcr:uuid] inner
join [nt:resource] as filecontent on ischildnode(filecontent, file) where
contains(filecontent.*, 'Europe') order by
press.[jnt:translation/jcr:title_en] desc] is not valid.
javax.jcr.query.InvalidQueryException: '/' not allowed in name
at
org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.checkPropertyName(QueryObjectModelFactoryImpl.java:1032)
at
org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.propertyValue(QueryObjectModelFactoryImpl.java:668)
at
org.apache.jackrabbit.spi.commons.query.sql2.Parser.parsePropertyValue(Parser.java:425)
at
org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseDynamicOperand(Parser.java:386)
at
org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseOrder(Parser.java:542)
at
org.apache.jackrabbit.spi.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:121)
at
org.apache.jackrabbit.spi.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
at
org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
at
org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
at
org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)

Is there another way to achieve my use case?
Or is there none so far and should I open a JIRA to once make my original
query work, which seems to be a valid SQL-2 query by specification ? Perhaps
a patch could check in the JoinQuery for the child node join condition and
internally when creating the lucene orderings create the relative path if
the ordering is on the child node ? Or is there a better place to fix that ?

Regards,
Benjamin

2009/10/28 Thomas Müller <[hidden email]>

> Hi,
>
> > I have tried to use a relative path, but I was getting
> > syntax errors in SQL-2.
>
> Could you tell me the exact statement you have used and the exception
> message and stack trace?
>
> Regards,
> Thomas
>

Thomas Müller-2

Re: SQL-2 Child Axis in order by clause

Reply Threaded

Hi,

You wrote:
order by press.[jnt:translation/jcr:title_en] desc

This is not valid SQL-2 syntax. What about:
order by translation.[jcr:title_en] desc

Regards,
Thomas

On Wed, Nov 4, 2009 at 1:41 PM, Benjamin Papez <[hidden email]> wrote:

> Hello,
>
> my wanted query is like this, which seems to be a valid query from
> specification point of view:
>
> select press.* from [web_templates:pressContainer] as press inner join
> [jnt:translation] as translation on ischildnode(translation, press) where
> contains(press.*, '${searchTerm}') or contains(translation.*,
> '${searchTerm}') order by translation.[jcr:title_en] desc
>
> unfortunately it does not work, because it looks like
> SimpleScoreDocComparator is looking in Lucene documents representing
> pressContainer and not translation (child node).
>
> I saw that Jackrabbit implemented a RelPathScoreDocComparator for use cases
> like mine, but that comparator implementation is not picked with queries
> like the one above. Like Marcel mentioned it is only used when there is a
> relative path in the order by statement.
>
> Now according to JSR-283 specifications it looks like relative path is not
> allowed in the order by statement. That is also why I get the following
> error, trying it:
>
> 2009-11-04 12:42:08,750: ERROR [JCRSQLTag] - InvalidQueryException --->
> [select press.* from [web_templates:pressContainer] as press inner join
> [jnt:translation] as translation on ischildnode(translation, press) inner
> join [nt:file] as file on translation.pdfVersion_en = file.[jcr:uuid] inner
> join [nt:resource] as filecontent on ischildnode(filecontent, file) where
> contains(filecontent.*, 'Europe') order by
> press.[jnt:translation/jcr:title_en] desc] is not valid.
> javax.jcr.query.InvalidQueryException: '/' not allowed in name
> at
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.checkPropertyName(QueryObjectModelFactoryImpl.java:1032)
> at
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.propertyValue(QueryObjectModelFactoryImpl.java:668)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parsePropertyValue(Parser.java:425)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseDynamicOperand(Parser.java:386)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseOrder(Parser.java:542)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:121)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
> at
> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
> at
> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
> at
> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
>
>
> Is there another way to achieve my use case?
> Or is there none so far and should I open a JIRA to once make my original
> query work, which seems to be a valid SQL-2 query by specification ? Perhaps
> a patch could check in the JoinQuery for the child node join condition and
> internally when creating the lucene orderings create the relative path if
> the ordering is on the child node ? Or is there a better place to fix that ?
>
> Regards,
> Benjamin
>
>
> 2009/10/28 Thomas Müller <[hidden email]>
>
>> Hi,
>>
>> > I have tried to use a relative path, but I was getting
>> > syntax errors in SQL-2.
>>
>> Could you tell me the exact statement you have used and the exception
>> message and stack trace?
>>
>> Regards,
>> Thomas
>>
>

Benjamin Papez

Re: SQL-2 Child Axis in order by clause

Reply Threaded

Hello Thomas,

thanks again, but thats exactly what I was talking about in my comment.
Please read it carefully.

I already wrote why " order by translation.[jcr:title_en] desc " does not
work.

Then I explained why I tried using a relative path and that this seems not
to be a valid SQL-2 syntax.

And at last I am talking about a possible patch, so I would be happy if you
could answer to my questions in my previous comment.

Regards,
Benjamin

2009/11/4 Thomas Müller <[hidden email]>

> Hi,
>
> You wrote:
> order by press.[jnt:translation/jcr:title_en] desc
>
> This is not valid SQL-2 syntax. What about:
> order by translation.[jcr:title_en] desc
>
> Regards,
> Thomas
>
>
> On Wed, Nov 4, 2009 at 1:41 PM, Benjamin Papez <[hidden email]>
> wrote:
> > Hello,
> >
> > my wanted query is like this, which seems to be a valid query from
> > specification point of view:
> >
> > select press.* from [web_templates:pressContainer] as press inner join
> > [jnt:translation] as translation on ischildnode(translation, press) where
> > contains(press.*, '${searchTerm}') or contains(translation.*,
> > '${searchTerm}') order by translation.[jcr:title_en] desc
> >
> > unfortunately it does not work, because it looks like
> > SimpleScoreDocComparator is looking in Lucene documents representing
> > pressContainer and not translation (child node).
> >
> > I saw that Jackrabbit implemented a RelPathScoreDocComparator for use
> cases
> > like mine, but that comparator implementation is not picked with queries
> > like the one above. Like Marcel mentioned it is only used when there is a
> > relative path in the order by statement.
> >
> > Now according to JSR-283 specifications it looks like relative path is
> not
> > allowed in the order by statement. That is also why I get the following
> > error, trying it:
> >
> > 2009-11-04 12:42:08,750: ERROR [JCRSQLTag] - InvalidQueryException --->
> > [select press.* from [web_templates:pressContainer] as press inner join
> > [jnt:translation] as translation on ischildnode(translation, press) inner
> > join [nt:file] as file on translation.pdfVersion_en = file.[jcr:uuid]
> inner
> > join [nt:resource] as filecontent on ischildnode(filecontent, file) where
> > contains(filecontent.*, 'Europe') order by
> > press.[jnt:translation/jcr:title_en] desc] is not valid.
> > javax.jcr.query.InvalidQueryException: '/' not allowed in name
> > at
> >
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.checkPropertyName(QueryObjectModelFactoryImpl.java:1032)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.propertyValue(QueryObjectModelFactoryImpl.java:668)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parsePropertyValue(Parser.java:425)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseDynamicOperand(Parser.java:386)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseOrder(Parser.java:542)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:121)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
> > at
> >
> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
> > at
> >
> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
> > at
> >
> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
> >
> >
> > Is there another way to achieve my use case?
> > Or is there none so far and should I open a JIRA to once make my original
> > query work, which seems to be a valid SQL-2 query by specification ?
> Perhaps
> > a patch could check in the JoinQuery for the child node join condition
> and
> > internally when creating the lucene orderings create the relative path if
> > the ordering is on the child node ? Or is there a better place to fix
> that ?
> >
> > Regards,
> > Benjamin
> >
> >
> > 2009/10/28 Thomas Müller <[hidden email]>
> >
> >> Hi,
> >>
> >> > I have tried to use a relative path, but I was getting
> >> > syntax errors in SQL-2.
> >>
> >> Could you tell me the exact statement you have used and the exception
> >> message and stack trace?
> >>
> >> Regards,
> >> Thomas
> >>
> >
>

Marcel Reutegger

Re: SQL-2 Child Axis in order by clause

Reply Threaded

In reply to this post by Benjamin Papez

Hi,

On Wed, Nov 4, 2009 at 13:41, Benjamin Papez <[hidden email]> wrote:

that sounds like a bug. could you please file a jira issue? thanks.

regards
marcel

> I saw that Jackrabbit implemented a RelPathScoreDocComparator for use cases
> like mine, but that comparator implementation is not picked with queries
> like the one above. Like Marcel mentioned it is only used when there is a
> relative path in the order by statement.
>
> Now according to JSR-283 specifications it looks like relative path is not
> allowed in the order by statement. That is also why I get the following
> error, trying it:
>
> 2009-11-04 12:42:08,750: ERROR [JCRSQLTag] - InvalidQueryException --->
> [select press.* from [web_templates:pressContainer] as press inner join
> [jnt:translation] as translation on ischildnode(translation, press) inner
> join [nt:file] as file on translation.pdfVersion_en = file.[jcr:uuid] inner
> join [nt:resource] as filecontent on ischildnode(filecontent, file) where
> contains(filecontent.*, 'Europe') order by
> press.[jnt:translation/jcr:title_en] desc] is not valid.
> javax.jcr.query.InvalidQueryException: '/' not allowed in name
> at
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.checkPropertyName(QueryObjectModelFactoryImpl.java:1032)
> at
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.propertyValue(QueryObjectModelFactoryImpl.java:668)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parsePropertyValue(Parser.java:425)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseDynamicOperand(Parser.java:386)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseOrder(Parser.java:542)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:121)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
> at
> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
> at
> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
> at
> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
>
>
> Is there another way to achieve my use case?
> Or is there none so far and should I open a JIRA to once make my original
> query work, which seems to be a valid SQL-2 query by specification ? Perhaps
> a patch could check in the JoinQuery for the child node join condition and
> internally when creating the lucene orderings create the relative path if
> the ordering is on the child node ? Or is there a better place to fix that ?
>
> Regards,
> Benjamin
>
>
> 2009/10/28 Thomas Müller <[hidden email]>
>
>> Hi,
>>
>> > I have tried to use a relative path, but I was getting
>> > syntax errors in SQL-2.
>>
>> Could you tell me the exact statement you have used and the exception
>> message and stack trace?
>>
>> Regards,
>> Thomas
>>
>