Hi Shravya! It caused by bug in H2 parser which erases information about type of the column expression of subqueries and reports it just like ‘column’, so Ignite can’t properly split the query. For thin client the splitting is not always needed since the requests starts its execution on the server node (i.g. in case when you execute query over a replicated cache). But for thick client the splitting is always needed.
The possible workaround is to add an alias to the column with aggregate inside of the subquery: SELECT * FROM (select region, sum(total_cost) as sum FROM ecom_sales group by region limit 200) AS _X LIMIT 30 OFFSET 0 Hope it helps. -- Regards, Konstantin Orlov Software Engineer, St. Petersburg +7 (921) 445-65-75 https://www.gridgain.com Powered by Apache® Ignite™ > On 3 Nov 2020, at 08:12, Shravya Nethula > <shravya.neth...@aline-consulting.com> wrote: > > Hi, > > For Ignite thick client, the below sql query is throwing CacheException with > the following code: > public class ClientNode { > public static void main(String[] args) { > IgniteConfiguration igniteCfg = new IgniteConfiguration(); > igniteCfg.setClientMode(true); > > Ignite ignite = Ignition.start(igniteCfg); > IgniteCache foo = ignite.getOrCreateCache("foo"); > SqlFieldsQuery sql = new SqlFieldsQuery("SELECT * FROM (select > region, sum(total_cost) FROM ecom_sales group by region limit 200) AS _X > LIMIT 30 OFFSET 0"); > List<List<?>> data = foo.query(sql).getAll(); > System.out.println("data:::"+data); > } } > > Output: > Exception in thread "main" javax.cache.CacheException: Failed to parse query. > Column "_X__Z1.SUM(TOTAL_COST)" not found; > > > On the other hand, for thin client, the result for the same sql query is > coming up as expected. > public class ClientNode { > public static void main(String[] args) { > ClientConfiguration clientConfig = new ClientConfiguration(); > cc.setUserName("username"); > cc.setUserPassword("password"); > > IgniteClient thinClient = Ignition.startClient(clientConfig); > SqlFieldsQuery sql = new SqlFieldsQuery("SELECT * FROM (select > region, sum(total_cost) FROM ecom_sales group by region limit 200) AS _X > LIMIT 30 OFFSET 0"); > List<List<?>> data = thinClient.query(sql).getAll(); > System.out.println("data:::"+data); > } } > Output: > [Europe, 2.4160571432339935E10] > [Asia, 1.3585889703059944E10] > > Such behaviour is observed only in the scenarios with subqueries having > aggregate functions. > Can you please explain, why is the behaviour different for the same query? > What can be changed to get the data properly even in Thick client mode? > > Any help is much appreciated! > Thanks in advance! > > > Regards, > Shravya Nethula, > BigData Developer, > <Outlook-1ozm3fum.png> > Hyderabad.
