Search code examples
sqldatabasecnosdb

While I was using cnosdb, I found a problem with SQL execution failing

I tried to execute some SQL using cnosdb.When I execute the following sql, the execution fails, I don't find the syntax error, the error message is sum(...) ... Not a "field name". Here's the sql I executed：

select
driver,
date_trunc('day',time) as c_date,
time,
load_capacity,
fuel_capacity,
sum(case when elevation<50 then elevation else 0 end) over (partition by driver)
/sum(elevation) over (partition by driver)/2
from readings
where driver in ('Seth','Albert')
and time>='2017-01-01T00:00:00'
and time<='2017-01-02T00:00:00';

This is the error message about the sql： enter image description here

Solution

  • This is a bug of the CnosDB optimizer.

    The elevation field of this table is Double type, and the expression elevation<50 in SQL will be parsed into elevation < Int64(50).

    The optimizer will convert the type of the expression to elevation < Float64(50), but this conversion will cause the name of the expression to change, making the projection unable to find the corresponding expression.

    It has been fixed (https://github.com/cnosdb/cnosdb/pull/1036). The solution is to alias the expression to the original expression after converting the expression.