Apache ignite is giving error when execute a sql query with Top keyword

Question

• When I execute a simple query on apache ignite it's work fine but when I execute with the "TOP" keyword it's give error. The query is "SELECT TOP(5) a.* FROM "Orders".ORDER a WHERE IsOptionTrade=FALSE AND (ACCOUNT IN ('10001', '10002', '10003'))".

• The method is given below from where error occurs.

    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
            {
                //Note:Same Client not using Different Versions of Client
                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);// **At this point error occurs**
    
        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);
    }

• This is the error i am getting.

    * 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)

Answer 1

top limits the whole row rather than a single column, so you don't need to specify it twice in one command.

You want the query to be:

SELECT TOP 10 A._KEY, A._VAL FROM ""Orders"".""ORDER"" A WHERE ISOPTIONTRADE=FALSE AND  ( ACCOUNT IN (  '10001', '10002', '10003' ) ) ; 

I don't know enough Linq to suggest how to fix your code, but may that will give you (or someone else) a clue.