Here is my Flexi query:
SELECT {quote:pk} FROM {Quote as quote} WHERE {quote:version} = ({{ SELECT MAX({version}) FROM {Quote} WHERE {code} = {quote:code} }})
Query works fine when I run it from HAC.
I am using mssql server docker image.
Same Query I am using for full solr-indexing.
But the query fails when I run the solr indexing.
Here is the message:
-------------------START-----------
de.hybris.platform.servicelayer.search.exceptions.FlexibleSearchException: SQL search error - The index 2 is out of range. query = 'SELECT item_t0.PK FROM quotes item_t0 WHERE ( item_t0.p_version = (SELECT MAX( item_t1.p_version ) FROM quotes item_t1 WHERE ( item_t1.p_code = item_t0.p_code ) AND (item_t1.TypePkString=? ))") AND (item_t0.TypePkString=? )', values = [8796094070866, 8796094070866]
--------------END-----------------
Complete log:
ERROR [full-nkQuoteType1-cronJob::de.hybris.platform.servicelayer.internal.jalo.ServicelayerJob] (full-nkQuoteType1-cronJob) [Job] Caught throwable SQL search error - The index 2 is out of range. query = 'SELECT item_t0.PK FROM quotes item_t0 WHERE ( item_t0.p_version = (SELECT MAX( item_t1.p_version ) FROM quotes item_t1 WHERE ( item_t1.p_code = item_t0.p_code ) AND (item_t1.TypePkString=? ))") AND (item_t0.TypePkString=? )', values = [8796094070866, 8796094070866]
de.hybris.platform.servicelayer.search.exceptions.FlexibleSearchException: SQL search error - The index 2 is out of range. query = 'SELECT item_t0.PK FROM quotes item_t0 WHERE ( item_t0.p_version = (SELECT MAX( item_t1.p_version ) FROM quotes item_t1 WHERE ( item_t1.p_code = item_t0.p_code ) AND (item_t1.TypePkString=? ))") AND (item_t0.TypePkString=? )', values = [8796094070866, 8796094070866]
at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$3.execute(DefaultFlexibleSearchService.java:541)
at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$3.execute(DefaultFlexibleSearchService.java:511)
at de.hybris.platform.servicelayer.session.impl.DefaultSessionService.executeInLocalView(DefaultSessionService.java:82)
at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService.getJaloResult(DefaultFlexibleSearchService.java:510)
at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService.search(DefaultFlexibleSearchService.java:180)
at de.hybris.platform.solrfacetsearch.indexer.impl.DefaultIndexerQueriesExecutor.getPks(DefaultIndexerQueriesExecutor.java:80)
at de.hybris.platform.solrfacetsearch.indexer.strategies.impl.AbstractIndexerStrategy.executeIndexerQuery(AbstractIndexerStrategy.java:239)
at de.hybris.platform.solrfacetsearch.indexer.strategies.impl.AbstractIndexerStrategy.resolvePks(AbstractIndexerStrategy.java:331)
at de.hybris.platform.solrfacetsearch.indexer.strategies.impl.AbstractIndexerStrategy.doExecute(AbstractIndexerStrategy.java:153)
at de.hybris.platform.solrfacetsearch.indexer.strategies.impl.AbstractIndexerStrategy.execute(AbstractIndexerStrategy.java:119)
at de.hybris.platform.solrfacetsearch.indexer.impl.DefaultIndexerService.performFullIndex(DefaultIndexerService.java:61)
at de.hybris.platform.solrfacetsearch.indexer.cron.SolrIndexerJob.indexItems(SolrIndexerJob.java:71)
at de.hybris.platform.solrfacetsearch.indexer.cron.SolrIndexerJob.performIndexingJob(SolrIndexerJob.java:50)
at de.hybris.platform.solrfacetsearch.indexer.cron.AbstractIndexerJob.perform(AbstractIndexerJob.java:33)
at de.hybris.platform.servicelayer.internal.jalo.ServicelayerJob.performCronJob(ServicelayerJob.java:31)
at de.hybris.platform.cronjob.jalo.Job.performCronJobWithReadOnlySetting(Job.java:1493)
at de.hybris.platform.cronjob.jalo.Job.execute(Job.java:1428)
at de.hybris.platform.cronjob.jalo.Job.performImpl(Job.java:820)
at de.hybris.platform.cronjob.jalo.Job$JobRunable.run(Job.java:688)
at de.hybris.platform.util.threadpool.PoolableThread.internalRun(PoolableThread.java:204)
at de.hybris.platform.core.threadregistry.RegistrableThread.run(RegistrableThread.java:124)
Caused by: de.hybris.platform.jalo.flexiblesearch.FlexibleSearchException: SQL search error - The index 2 is out of range. query = 'SELECT item_t0.PK FROM quotes item_t0 WHERE ( item_t0.p_version = (SELECT MAX( item_t1.p_version ) FROM quotes item_t1 WHERE ( item_t1.p_code = item_t0.p_code ) AND (item_t1.TypePkString=? ))") AND (item_t0.TypePkString=? )', values = [8796094070866, 8796094070866][HY-0]
at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:292)
at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.executeSearch(FlexibleSearch.java:2193)
at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.executeSearch(FlexibleSearch.java:2213)
at de.hybris.platform.jalo.flexiblesearch.FlexibleSearchCacheUnit.compute(FlexibleSearchCacheUnit.java:71)
at de.hybris.platform.cache.AbstractCacheUnit.privateGetNoLock(AbstractCacheUnit.java:297)
at de.hybris.platform.cache.AbstractCacheUnit.privateGet(AbstractCacheUnit.java:270)
at de.hybris.platform.cache.AbstractCacheUnit.get(AbstractCacheUnit.java:172)
at de.hybris.platform.jalo.flexiblesearch.FlexibleSearchCacheUnit.myGet(FlexibleSearchCacheUnit.java:158)
at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.executeSearchUsingFlexibleSearchDialect(FlexibleSearch.java:1741)
at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.search(FlexibleSearch.java:1468)
at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.search(FlexibleSearch.java:1459)
at de.hybris.platform.servicelayer.search.impl.DefaultFlexibleSearchService$3.execute(DefaultFlexibleSearchService.java:533)
... 20 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:1211)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:1225)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setLong(SQLServerPreparedStatement.java:1525)
at de.hybris.platform.jdbcwrapper.interceptor.PreparedStatementWithJDBCInterceptor.lambda$setLong$16(PreparedStatementWithJDBCInterceptor.java:133)
at de.hybris.platform.jdbcwrapper.interceptor.JDBCInterceptor.lambda$run$0(JDBCInterceptor.java:54)
at de.hybris.platform.jdbcwrapper.interceptor.recover.SQLRecoverableExceptionHandler.passThrough(SQLRecoverableExceptionHandler.java:93)
at de.hybris.platform.jdbcwrapper.interceptor.recover.SQLRecoverableExceptionHandler.get(SQLRecoverableExceptionHandler.java:51)
at de.hybris.platform.jdbcwrapper.interceptor.JDBCInterceptor.run(JDBCInterceptor.java:53)
at de.hybris.platform.jdbcwrapper.interceptor.PreparedStatementWithJDBCInterceptor.setLong(PreparedStatementWithJDBCInterceptor.java:133)
at de.hybris.platform.jdbcwrapper.PreparedStatementImpl.setLong(PreparedStatementImpl.java:481)
at de.hybris.platform.persistence.property.JDBCValueMappings$PKReaderWriter.writeValue(JDBCValueMappings.java:930)
at de.hybris.platform.persistence.property.JDBCValueMappings$PKReaderWriter.writeValue(JDBCValueMappings.java:909)
at de.hybris.platform.persistence.property.JDBCValueMappings$AbstractValueReaderWriter.setValue(JDBCValueMappings.java:581)
at de.hybris.platform.persistence.property.JDBCValueMappings.setValue(JDBCValueMappings.java:537)
at de.hybris.platform.persistence.property.JDBCValueMappings.fillStatement(JDBCValueMappings.java:292)
at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:231)
... 31 more