Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot perform right substring #475

Closed
f-r-a-n-s opened this issue Aug 16, 2013 · 4 comments
Closed

Cannot perform right substring #475

f-r-a-n-s opened this issue Aug 16, 2013 · 4 comments
Assignees
Milestone

Comments

@f-r-a-n-s
Copy link

@f-r-a-n-s f-r-a-n-s commented Aug 16, 2013

I tried to generate the following SQL statement (test equivalence of characters, starting count from the right):

SELECT * FROM account where SUBSTRING(name, -x, 1) = SUBSTRING(name, -y, 1)
  1. Using StringExpression.charAt(i): the value i is changed as follows:

  2. if i >= 0 it is incremented by 1

  3. if i < 0 it is decremented by 1
    => not possible to generate -1
    (Leaving negative indexes unchanged seems like the least intrusive solution)

  4. Using StringExpression.substring()

    QAccount account = ...;
    account.name.substring(account.name.length().subtract(x + 1), account.name.length().subtract(x)).eq(account.name.length().subtract(y + 1), account.name.length().subtract(y))

Gives an exception:

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: Not all named parameters have been set: [4] [select a.name
from Account a
where substring(a.name,length(a.name) - ?2,length(a.name) - ?2-length(a.name) - ?1) = substring(a.name,length(a.name) - ?4,length(a.name) - ?3-length(a.name) - ?3)]
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1374)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:277)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:311)
at com.sun.proxy.$Proxy172.getResultList(Unknown Source)
at com.mysema.query.jpa.impl.AbstractJPAQuery.getResultList(AbstractJPAQuery.java:196)
at com.mysema.query.jpa.impl.AbstractJPAQuery.list(AbstractJPAQuery.java:242)
... 74 more
Caused by: org.hibernate.QueryException: Not all named parameters have been set: [4] [select a.name
from Account a
where substring(a.name,length(a.name) - ?2,length(a.name) - ?2-length(a.name) - ?1) = substring(a.name,length(a.name) - ?4,length(a.name) - ?3-length(a.name) - ?3)]
at org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:344)
at org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:328)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:97)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268)
... 88 more

Thanks!

@ghost ghost assigned timowest Aug 16, 2013
timowest added a commit that referenced this issue Aug 16, 2013
@timowest
Copy link
Member

@timowest timowest commented Aug 16, 2013

Could you try again with the latest SNAPSHOT from here https://oss.sonatype.org/content/repositories/snapshots/ ?

The fourth option should work now.

@f-r-a-n-s
Copy link
Author

@f-r-a-n-s f-r-a-n-s commented Aug 19, 2013

Thanks for the update.

It does a better job, but I think I bump into another issue.
I would love to be able to pass negative indexes in the substring and / or charAt methods and have support for it all the way long to JPQL and SQL, but from what I have found, JPQL does not explicitly support it.
I guess that is the reason you suggest me to use the substring() and length() instead.

So, here is the status using the latest snapshot. From the following code:

 account.name.substring(account.name.length().subtract(1), account.name.length()).eq(account.name.substring(account.name.length().subtract(2), account.name.length().subtract(1)))

I get the correct JPQL:

 select a from Account a where substring(a.name,(length(a.name) - 1)+1,length(a.name)-(length(a.name) - 1)) = substring(a.name,(length(a.name) - 2)+1,(length(a.name) - 1)-(length(a.name) - 2))     

It is much more verbose but I can live with it.
The only thing is that Hibernate does not translate that correctly to MySQL:

 SELECT account0_.name AS col_0_0_ FROM darts.account account0_ WHERE SUBSTRING(account0_.name, LENGTH(account0_.name)-1+1, LENGTH(account0_.name)-(LENGTH(account0_.name)-1))=SUBSTRING(account0_.name, LENGTH(account0_.name)-2+1, LENGTH(account0_.name)-1-(LENGTH(account0_.name)-2));

Instead of

  SELECT account0_.name AS col_0_0_ FROM darts.account account0_ WHERE SUBSTRING(account0_.name, CHAR_LENGTH(account0_.name)-1+1, CHAR_LENGTH(account0_.name)-(CHAR_LENGTH(account0_.name)-1))=SUBSTRING(account0_.name, CHAR_LENGTH(account0_.name)-2+1, CHAR_LENGTH(account0_.name)-1-(CHAR_LENGTH(account0_.name)-2));

Using LENGTH instead of CHAR_LENGTH, I have irrelevant results because LENGTH gives the length in bytes, not in characters, while in JPQL LENGTH is meant to count characters.
But I guess it's Hibernate I have to blame for that. It's just that this would not happen if negative indexes were supported ;-)

So, as long as querydsl is concerned, I think you can consider it as fixed.

Thanks again.

@timowest
Copy link
Member

@timowest timowest commented Aug 19, 2013

The substring function on the Querydsl level follows java.lang.String, that's why it is a bit verbose in the translation.

I will see if I can further optimize the translation at some point.

@timowest
Copy link
Member

@timowest timowest commented Aug 27, 2013

Released in 3.2.3

@timowest timowest closed this Aug 27, 2013
@timowest timowest added this to the 3.2.3 milestone Apr 13, 2014
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants