Apache Ignite含TOP关键字的SQL查询执行报错问题

问题背景 #

普通SQL查询在Apache Ignite中可正常执行，但包含TOP关键字的查询在Continuous Query场景下报错。

报错查询语句 #

SELECT TOP(5) a.* FROM "Orders".ORDER a WHERE IsOptionTrade=FALSE AND (ACCOUNT IN ('10001', '10002', '10003'))

报错发生的代码片段 #

public IEnumerable QueryContinous(string initalquery, string predicateExpression, string queryKey, ref long seekinfo)
{
    if (string.IsNullOrEmpty(predicateExpression))
        predicateExpression = "1 == 1";

    if (ListenerLookup.ContainsKey(queryKey))
    {
        if (predicateExpression == ListenerLookup[queryKey].PredicateExpr)
            return Query(initalquery, ref seekinfo);
        else
        {
            logger.Warn($"PredicateExpression:{predicateExpression} is Different from ListenerLookUp PredicateExpression:{ListenerLookup[queryKey].PredicateExpr}");
            Unsubscribe(queryKey);
        }
        
    }

    var listener = new Listener(queryKey);
    listener.OnUpdate += Listener_OnUpdate;

    var qry = new ContinuousQuery(listener)
    {
        Filter = new RemoteFilter()
        {
            Query = predicateExpression,
            IsWildListener = string.IsNullOrEmpty(predicateExpression)
        }
        
    };

    var initialQry = new SqlQuery(typeof(TV), initalquery);
    var queryHandle = Cache.QueryContinuous(qry, initialQry);// **报错发生位置**

    var listenerDetails = new ListenerDetails()
    {
        QueryHandle = queryHandle,
        listener = listener

    };


    listenerDetails.PredicateExpr = predicateExpression;
    listenerDetails.Predicate = System.Linq.Dynamic.Core.DynamicExpressionParser.ParseLambda(ParsingConfig.Default, true, predicateExpression).Compile();

    
    ListenerLookup.Add(queryKey, listenerDetails);

    return createList(queryHandle.GetInitialQueryCursor(), ref seekInfo);
}

错误信息 #

* Error Message : Apache.Ignite.Core.Common.IgniteException: Failed to parse query. Syntax error in SQL statement SELECT TOP 10 A._KEY, TOP 10[*] A._VAL FROM ""Orders""".""ORDER"" A WHERE ISOPTIONTRADE=FALSE AND  ( ACCOUNT IN (  '10001', '10002', '10003' ) ) ; 
 SQL statement: SELECT TOP 10 a._KEY, TOP 10 a._VAL FROM "Orders"."ORDER" a WHERE IsOptionTrade=FALSE AND  ( Account IN (  '10001', '10002', '10003' ) ) [42000-197] ---> Apache.Ignite.Core.Common.JavaException: class org.apache.ignite.IgniteCheckedException: Failed to parse query. Syntax error in SQL statement SELECT TOP 10 A._KEY, TOP 10[*] A._VAL FROM ""Orders""".""ORDER"" A WHERE ISOPTIONTRADE=FALSE AND  ( ACCOUNT IN (  '10001', '10002', '10003' ) ) ; 
 SQL statement:SELECT TOP 10 a._KEY, TOP 10 a._VAL FROM "Orders"."ORDER" a WHERE IsOptionTrade=FALSE AND  ( Account IN (  '10001', '10002','10003') ) [42000-197] 
 at org.apache.ignite.internal.processors.platform.utils.PlatformUtils.unwrapQueryException(PlatformUtils.java:520) 
 at org.apache.ignite.internal.processors.platform.cache.PlatformCache.runQuery(PlatformCache.java:1242) 
 at org.apache.ignite.internal.processors.platform.cache.PlatformCache.processInStreamOutObject(PlatformCache.java:883) 
 at org.apache.ignite.internal.processors.platform.PlatformTargetProxyImpl.inStreamOutObject(PlatformTargetProxyImpl.java:79)
* Caused by: javax.cache.CacheException: Failed to parse query.Syntax error in SQL statement SELECT TOP 10 A._KEY, TOP 10[*] A._VAL FROM ""Orders""".""ORDER"" A WHERE ISOPTIONTRADE=FALSE AND  ( ACCOUNT IN (  '10001', '10002', '10003' ) ) ; 
SQL statement: SELECT TOP 10 a._KEY, TOP 10 a._VAL FROM "Orders"."ORDER" a WHERE IsOptionTrade=FALSE AND  ( Account IN (  '10001', '10002', '10003' ) ) [42000-197] 
 at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:697) 
 at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:376) 
 at org.apache.ignite.internal.processors.platform.cache.PlatformCache.runQuery(PlatformCache.java:1236)
... 2 more
* Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to parse query. Syntax error in SQL statement SELECT TOP 10 A._KEY, TOP 10[*] A._VAL FROM ""Orders""".""ORDER"" A WHERE ISOPTIONTRADE=FALSE AND  ( ACCOUNT IN (  '10001', '10002', '10003' ) ) ; 
SQL statement:SELECT TOP 10 a._KEY, TOP 10 a._VAL FROM "Orders"."ORDER" a WHERE IsOptionTrade=FALSE AND  ( Account IN (  '10001', '10002', '10003' ) ) [42000-197] 
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2653) 
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:2356) 
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:2196) 
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSql(IgniteH2Indexing.java:1873) 
 at org.apache.ignite.internal.processors.query.GridQueryProcessor$7.applyx(GridQueryProcessor.java:2282) 
 at org.apache.ignite.internal.processors.query.GridQueryProcessor$7.applyx(GridQueryProcessor.java:2280) 
 at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36) 
 at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2693) 
 at org.apache.ignite.internal.processors.query.GridQueryProcessor.queryDistributedSql(GridQueryProcessor.java:2279) 
 at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySql(GridQueryProcessor.java:2260) 
 at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:682)
... 4 more
* Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement SELECT TOP 10 A._KEY, TOP 10[*] A._VAL FROM ""Orders""".""ORDER"" A WHERE ISOPTIONTRADE=FALSE AND  ( ACCOUNT IN (  '10001', '10002', '10003' ) ) ;
 SQL statement: SELECT TOP 10 a._KEY, TOP 10 a._VAL FROM "Orders"."ORDER" a WHERE IsOptionTrade=FALSE AND  ( Account IN (  '10001', '10002', '10003' ) ) [42000-197] 
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
 at org.h2.message.DbException.get(DbException.java:179)
 at org.h2.message.DbException.get(DbException.java:155)
 at org.h2.message.DbException.getSyntaxError(DbException.java:203)
 at org.h2.command.Parser.getSyntaxError(Parser.java:548)
 at org.h2.command.Parser.prepareCommand(Parser.java:281)
 at org.h2.engine.Session.prepareLocal(Session.java:611)
 at org.h2.engine.Session.prepareCommand(Session.java:549)
 at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)
 at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:76)
 at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:694)
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepare0(IgniteH2Indexing.java:539) 
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:509) 
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:476) 
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2635)
... 14 more
 at Apache.Ignite.Core.Impl.Unmanaged.Jni.Env.ExceptionCheck()
 at Apache.Ignite.Core.Impl.Unmanaged.Jni.Env.CallObjectMethod(GlobalRef obj, IntPtr methodId, Int64* argsPtr)
 at Apache.Ignite.Core.Impl.Unmanaged.UnmanagedUtils.TargetInStreamOutObject(GlobalRef target, Int32 opType, Int64 inMemPtr)
 at Apache.Ignite.Core.Impl.PlatformJniTarget.InStreamOutObject(Int32 type, Action`1 writeAction)

报错原因分析 #

从错误信息可见，Ignite自动将原始查询转换为SELECT TOP 10 a._KEY, TOP 10 a._VAL FROM ...的形式——TOP关键字被错误地重复应用到每个查询字段前，违反SQL语法规范，导致H2解析器报错。

问题根源是使用SqlQuery类作为Continuous Query的初始查询时，Ignite会自动将用户提供的SELECT ...语句转换为查询缓存内部_KEY和_VAL字段的语句，但未正确处理TOP关键字的位置，导致TOP被错误添加到每个字段上。

解决方法 #

方法1：改用SqlFieldsQuery替代SqlQuery #

SqlFieldsQuery不会自动修改用户提供的SQL语句，直接执行原始查询，避免Ignite自动转换逻辑带来的问题。

修改代码中的初始查询创建逻辑：

// 替换原有SqlQuery创建代码
// var initialQry = new SqlQuery(typeof(TV), initalquery);
var initialQry = new SqlFieldsQuery(initalquery);

注意：使用SqlFieldsQuery后，GetInitialQueryCursor()返回IFieldsQueryCursor<IList<object>>，需调整createList方法实现，适配字段集合的处理逻辑。

方法2：替换TOP为标准SQL的LIMIT子句 #

Ignite支持标准SQL的LIMIT语法，将查询语句中的TOP替换为LIMIT，可避免Ignite转换查询时的语法错误：

SELECT a.* FROM "Orders".ORDER a WHERE IsOptionTrade=FALSE AND (ACCOUNT IN ('10001', '10002', '10003')) LIMIT 5

内容的提问来源于stack exchange，提问作者adnan zafar