I am using Apache IoTDB 2.0.6 (table model) and encountered an issue when using the COALESCE function for data queries.

Sample Data:

  CREATE TABLE sensor_data (device_id tag,real_temp FLOAT field, backup_temp FLOAT field, default_temp FLOAT field);
  
  INSERT INTO sensor_data (`time`, device_id, real_temp, backup_temp, default_temp) VALUES (2024-11-30 00:00:00, 'sensor_01', `NULL`, -5.0, 25.0),(2024-11-30 00:01:00, 'sensor_01', -10.0, `NULL`, -8.0),(2024-11-30 00:02:00, 'sensor_01', 30.0, 28.0, 26.0);
  
  select * from sensor_data
  +-----------------------------+---------+---------+-----------+------------+
  | time|device_id|real_temp|backup_temp|default_temp|
  +-----------------------------+---------+---------+-----------+------------+
  |2024-11-30T00:00:00.000+08:00|sensor_01| `null`| -5.0| 25.0|
  |2024-11-30T00:01:00.000+08:00|sensor_01| -10.0| `null`| -8.0|
  |2024-11-30T00:02:00.000+08:00|sensor_01| 30.0| 28.0| 26.0|
  +-----------------------------+---------+---------+-----------+------------+

Query using COALESCE:

  SELECT COALESCE(real_temp, backup_temp, default_temp) AS final_temp FROM sensor_data;

Actual Result:

  +----------+
  |final_temp|
  +----------+
  | -5.0|
  | -10.0|
  | 30.0|
  +----------+
  Total line number = 3

Expected Logic:

Return the first value that is non-NULL AND greater than 0;

If all parameters are NULL or ≤ 0, return NULL.

For the sample data above, the expected result should be:

Row 1: real_temp is NULL, backup_temp is negative, default_temp is positive → expected 25.0

Row 2: real_temp is negative, backup_temp is NULL, default_temp is negative → expected NULL

Row 3: real_temp is positive, backup_temp is positive, default_temp is positive → expected 30.0

Expected Result:

  +----------+
  |final_temp|
  +----------+
  | 25.0|
  | `NULL`|
  | 30.0|
  +----------+

My Question: How can I write an SQL query in Apache IoTDB to achieve this logic, i.e., making COALESCE return the first non-NULL value that is greater than 0?