IoT Core Rule SQL - Why doesn't the AWS IoT SQL WHERE clause work with many fields when using the OR operator?

Question

ex:
I have a SQL query:  `select * from 'iot/+/noti' where a = 0 OR b = 0 OR c = 0`
* when i publish { "timestamp": 123, "a": 0} => OK, i see iot log { "timestamp": 123, "a": 0}
* when i publish  { "timestamp": 123, "b": 0} => NG, i don't see iot log
* when i publish  { "timestamp": 123, "c": 0} => NG, i don't see iot log

Please help me ! Where is the error in my SQL statement ?
Note: SQL version: 2016-03-23

Accepted Answer

Hi. The problem is that the missing values will be `Undefined` and not evaluate as Boolean: https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-operators.html#iot-sql-operators-or

Try this:

```
select * from 'iot/+/noti' where (isUndefined(a) = False AND a = 0) OR (isUndefined(b) = False AND b = 0) OR (isUndefined(c) = False AND c = 0)
```

IoT Core Rule SQL - Why doesn't the AWS IoT SQL WHERE clause work with many fields when using the OR operator?

Relevant content