How to get data for subqueries with aggregate functions in Ignite Thick client mode

classic Classic list List threaded Threaded
2 messages Options
Shravya Nethula Shravya Nethula
Reply | Threaded
Open this post in threaded view
|

How to get data for subqueries with aggregate functions in Ignite Thick client mode

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,


Hyderabad.

Konstantin Orlov Konstantin Orlov
Reply | Threaded
Open this post in threaded view
|

Re: How to get data for subqueries with aggregate functions in Ignite Thick client mode

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 <[hidden email]> 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.