[IGNITE-17663] Apache ignite is giving error when execute a sql query with Top keyword

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Cannot Reproduce
• Affects Version/s: 2.7.6
• Component/s: cache, sql

Environment

Windows 10 x64, Apache ignite .net,

• Flags: Important
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Adnan
• Created: 09/Sep/22 12:52
• Updated: 20/Sep/22 12:24
• Resolved: 20/Sep/22 12:24

Description

• 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

  Unknown macro: { //Note}

} 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){}}}{}