[infinispan-issues] [JBoss JIRA] (ISPN-2101) Eviction does not work with Apache Derby cache loader-- SQL error

Eviction does not work with Apache Derby cache loader-- SQL error ----------------------------------------------------------------- Key: ISPN-2101 URL: https://issues.jboss.org/browse/ISPN-2101 Project: Infinispan Issue Type: Bug Components: Core API, Eviction Affects Versions: 5.1.4.FINAL Environment: Infinispan 5.1.4 final, Apache Derby JDBC cache loader configured using Eviction Reporter: dex chen Assignee: Tristan Tarrant Fix For: 5.2.0.ALPHA1, 5.2.0.FINAL The cache will not be created because of the following sql error: Running sql 'SELECT DATA_COLUMN, ID_COLUMN FROM ISPN_STRING_TABLE_keychain LIMIT ? ERROR org.infinispan.loaders.jdbc.DataManipulationHelper[253] - ISPN008007: SQL error while fetching all StoredEntries java.sql.SQLSyntaxErrorException: Syntax error: Encountered "?" at line 1, column 69. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source) at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) The problem is that Apache Derby does not support select LIMIT. See here from Derby doc: 5.2. Does Derby support a LIMIT command? ^ Derby does not support the LIMIT syntax. However, Derby 10.4 added the ROW_NUMBER function and Derby 10.7 added the OFFSET and FETCH clauses. Derby also supports limiting the number of rows returned by a query through JDBC. For example, to fetch the first 5 rows of a large table: Statement stmt = con.createStatement(); stmt.setMaxRows(5); ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable"); Some related tuning tips are available in this external article. Starting with the 10.4.1.3 release Derby also supports limiting the number of rows using the ROW_NUMBER function. For example, to fetch the first 5 rows of a large table: SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.* FROM myLargeTable ) AS tmp WHERE rownum <= 5; The ROW_NUMBER function can also be used to select a limited number of rows starting with an offset, for example: SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.* FROM myLargeTable ) AS tmp WHERE rownum > 200000 AND rownum <= 200005; For more information, refer to the ROW_NUMBER built-in function in the Derby Reference Manual (available from the Documentation page). Development notes are available on the OLAPRowNumber wiki page. The LIMIT keyword is not defined in the SQL standard, and is currently not supported.