LET is error with aggregation

[Hung Tran]

Hi,

I am migrating from version 1.7.8 to version 2.1.8. The below query is failed to execute

SELECT $v1 as Id,
       $v2 AS EmailAddress,
       Count(*) AS Total,
       BounceType,
       IFNULL($v0,'') AS __a0,
       IFNULL($v3,'') AS LastTime,
       IFNULL(Contact,'') AS Contact
  FROM ContactAction
   LET $v0 = BounceInfo.Detail,
       $v1 = Contact,
       $v2 = Contact.Email.Address,
       $v3 = Max(Request.At)
 WHERE (Type=3 AND Campaign=#64:25) AND EntityInfo.State=0
 GROUP BY Contact LIMIT 10

the error is

com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error on parsing command at position #419: Aggregate function cannot be used in LET clause together with GROUP BY Command: SELECT $v1 as Id, $v2 AS EmailAddress, Count(*) AS Total, BounceType, IFNULL($v0,'') AS __a0, IFNULL($v3,'') AS LastTime, IFNULL(Contact,'') AS Contact FROM ContactAction LET $v0 = BounceInfo.Detail, $v1 = Contact, $v2 = Contact.Email.Address, $v3 = Max(Request.At) WHERE (Type=3 AND Campaign=#64:25) AND EntityInfo.State=0 GROUP BY Contact LIMIT 10 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^

but when I do flatten all fields

SELECT Contact, 
       Contact.Email.Address, 
       Count(*), 
       BounceType, 
       BounceInfo.Detail, Max(Request.At)
  FROM ContactAction
 WHERE (Type=3 AND Campaign=#64:25) AND EntityInfo.State=0 
 GROUP BY Contact LIMIT 10

It is working perfectly. 

My Best,

Hung Tran

[Luca Son]

Hi Hung Tran,

could you post your code or do you have a small DB test to reproduce the issue ?

Thans

[Hung Tran]

Hi Luca,

It's easy to duplicate it, let's see

Database schema:

  create class TestClass
    create property TestClass.Name string
    create property TestClass.IntF integer

Working SQL

  select Name, max (IntF) from TestClass group by Name

Failed SQL in version 2.1.8 but worked with version 1.7.8

  select Name, $v1 from TestClass LET $v1 = max(IntF) group by Name

The error is also clear, LET statement does not support any aggregate function yet

  com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error on parsing command at position #60: Aggregate function   cannot be used in LET clause together with GROUP BY Command: select Name, $v1 from TestClass LET $v1 = max(IntF) group by Name

The LET statement is a unique highlight feature in OrientDB SQL, that helps me resolve many issues and reduce the query complexity, I hope it could be still strong in v2.1.8+

My Best,

Hung Tran

[Luca Son]

Hi Hung Tran,

I would advise you to use your first 'Working SQL' in this case but if you want to use the LET statement in your queries this could be a solution:

select Name, $v1.max from TestClass

let $v1=(select max(IntF) from TestClass where $parent.current.Name in Name)

group by Name

Which gives the same results.

could it be helpful ?