Announcement Announcement Module
Collapse
No announcement yet.
"IN" Clause Not Working for JPA/EclipseLink Repository - MySQL Error Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • "IN" Clause Not Working for JPA/EclipseLink Repository - MySQL Error

    Hi,

    I've created a repo with a method that requires an "IN" clause and I pass it an ArrayList.

    Code:
    public List<Vw_personLocation> findByPersonIdAndOrganizationIdAndRoleCodeIn(Long personId, Integer organizationId, List<String> roleCodes);
    When this runs, it creates the following SQL Statement:

    Code:
    Query: ReadAllQuery(referenceClass=Vw_personLocation sql="SELECT ROLELISTID, ADTDOCTORID, LOCATIONCODE, LOCATIONID, LOCATIONNAME, MRNGROUPID, ORGANIZATIONID, PERSONID, ROLECODE, ENTITY_UID, VERSION FROM VW_PERSONLOCATION WHERE (((PERSONID = ?) AND (ORGANIZATIONID = ?)) AND (ROLECODE IN (?)))")
    I get the following exception:

    Code:
    Caused by: java.sql.SQLException: Operand should contain 1 column(s)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2683)
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2144)
    	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2310)
    	at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172)
    	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:938)
    	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:609)
    	... 73 more
    If I run this in MySQL, it also fails with the same exception. It appears as though it has an issue with all of the parenthesis.

    Any solution to this?

    Thanks,
    Tom

  • #2
    Hi,

    I am confronting something very similar with "in" finder using spring-data-jpa-1.3.0 RELEASE (and all related most recent JARs), and EclipseLink 2.3.

    Given the following interface:

    Code:
    public interface MemberRepository extends JpaRepository<Member, Long> {
        
        @Query(value = "select m from Member m where m.lastname IN ?1")
        List<Member> findByLastnames(List<String> lastname);
        
        List<Member> findByLastnameIn(List<String> lastname);
    }
    the following exception occurs when findByLastnameIn() is executed.

    Code:
    ...
    Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): 
    org.eclipse.persistence.exceptions.DatabaseException
    Internal Exception: java.sql.SQLException: Operand should contain 1 column(s) Error Code: 1241 Call: SELECT ID, FIRSTNAME, LASTNAME FROM member WHERE (LASTNAME IN ((?,?,?))) bind => [3 parameters bound] Query: ReadAllQuery(referenceClass=Member sql="SELECT ID, FIRSTNAME, LASTNAME FROM member WHERE (LASTNAME IN (?))") ... Caused by: java.sql.SQLException: Operand should contain 1 column(s) ...
    Note that the method findByLastnames(...) works just fine.

    As the original poster suggests, it's the number of parentheses in the "where" clause which causes the error. If you take the sql, paste it into the MySQL query editor, remove the outer parens so that it reads as follows:

    Code:
    SELECT ID, FIRSTNAME, LASTNAME FROM member WHERE LASTNAME IN ("Smith", "Jones")
    the query executes just fine.


    I tried switching to EclipseLink 2.4.0 to see if the problem could be isolated to EclipseLink 2.3, but that results in more disastrous results: the JPA EntityManagerFactory for the declared persistence unit closes prematurely due to entirely different issues! Yikes.

    So, for the moment, let's stick to the problem of who or what is introducing the extra set of parens surrounding "LASTNAME IN (?)". I tried stepping into the Spring Data JPA code but could not find where the actual query string was built.

    I'd like to get this resolved because I'm hoping to present the fine features of Spring Data JPA Repositories to our local JUG early next month and am preparing a comprehensive example of the method keywords.

    Thanks.

    OS X: 10.8.2
    JDK 1.7.0_15
    STS: 3.1.0
    Last edited by pfurbacher; Feb 25th, 2013, 02:07 PM.

    Comment


    • #3
      Update:

      I reconfigured things to use Hibernate 4, and the "In" [e.g., findByLastnameIn(...)] query works just fine against MySQL 5. Here is the generated SQL (the select clause elided by me):
      Code:
      select ... from member member0_ where member0_.lastname in (? , ? , ?)
      Notice the single parens around the parameter list in contrast to the double set in my original post. I'm guessing that, when configured to use EclipseLink (2.3.x and 2.4.x), the extra set of parentheses surrounding the parameter list --

      Code:
       select ... WHERE (LASTNAME IN ((?,?,?)))]
      -- is happening on the EclipseLink side. I'll probably file a bug against EclipseLink to see if anyone has any ideas on their side.

      I'm curious -- are unit tests for Spring Data JPA run against Hibernate, EclipseLink, etc., and against lots of databases? Or is it just Hibernate and in-memory HSQL?

      One last thing -- is there a way to turn on debugging statements to output the actual query that Spring Data JPA hands off to the JPA vendor?

      Thanks.

      Comment


      • #4
        Okay. Finally found a bug report by Oliver Gierke, dated June 15, 2011(!), at bugs.eclipse.org:

        Using criteria.in() with ParameterExpression of type Collection creates invalid SQL


        (It was the error message generated by PostgreSQL that was the key to tracking down the bug report.)

        See also:

        org.postgresql.util.PSQLException: ERROR: arguments of row IN must all be row expressions


        and

        Spring-Data JPA adding extra parens which postgreSQL doesn't like

        Comment

        Working...
        X