Apache IoTDB Nested Query Issue: `NULL` Value Handling in IN Subquery Doesn't Meet Expectations
I'm encountering an issue with NULL value handling when using nested queries in Apache IoTDB 2.0.5 table model. When I use IN subqueries to filter data, records containing NULL values are unexpectedly filtered out, which doesn't meet my business requirements.
I have two tables: table1and table2, with the following data:
table1 data
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| `time`|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status| arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-29T11:00:00.000+08:00| SH1| 3002| 100| E| 180| `null`| 45.1| true| `null`|
|2024-11-29T18:30:00.000+08:00| SH1| 3002| 100| E| 180| 90.0| 35.4| true|2024-11-29T18:30:15.000+08:00|
|2024-11-28T08:00:00.000+08:00| SH1| 3001| 100| C| 90| 85.0| `null`| `null`|2024-11-28T08:00:09.000+08:00|
|2024-11-28T09:00:00.000+08:00| SH1| 3001| 100| C| 90| `null`| 40.9| true| `null`|
|2024-11-28T10:00:00.000+08:00| SH1| 3001| 100| C| 90| 85.0| 35.2| `null`|2024-11-28T10:00:11.000+08:00|
|2024-11-28T11:00:00.000+08:00| SH1| 3001| 100| C| 90| 88.0| 45.1| true|2024-11-28T11:00:12.000+08:00|
|2024-11-26T13:37:00.000+08:00| PK0| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:37:34.000+08:00|
|2024-11-26T13:38:00.000+08:00| PK0| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:38:25.000+08:00|
|2024-11-30T09:30:00.000+08:00| SH1| 3002| 101| F| 360| 90.0| 35.2| true| `null`|
|2024-11-30T14:30:00.000+08:00| SH1| 3002| 101| F| 360| 90.0| 34.8| true|2024-11-30T14:30:17.000+08:00|
|2024-11-29T10:00:00.000+08:00| SH1| 3001| 101| D| 360| 85.0| `null`| `null`|2024-11-29T10:00:13.000+08:00|
|2024-11-27T16:38:00.000+08:00| PK0| 1001| 101| B| 180| `null`| 35.1| true|2024-11-27T16:37:01.000+08:00|
|2024-11-27T16:39:00.000+08:00| PK0| 1001| 101| B| 180| 85.0| 35.3| `null`| `null`|
|2024-11-27T16:40:00.000+08:00| PK0| 1001| 101| B| 180| 85.0| `null`| `null`|2024-11-27T16:37:03.000+08:00|
|2024-11-27T16:41:00.000+08:00| PK0| 1001| 101| B| 180| 85.0| `null`| `null`|2024-11-27T16:37:04.000+08:00|
|2024-11-27T16:42:00.000+08:00| PK0| 1001| 101| B| 180| `null`| 35.2| false| `null`|
|2024-11-27T16:43:00.000+08:00| PK0| 1001| 101| B| 180| `null`| `null`| false| `null`|
|2024-11-27T16:44:00.000+08:00| PK0| 1001| 101| B| 180| `null`| `null`| false|2024-11-27T16:37:08.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
table2 data
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| `time`|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status| arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-29T11:00:00.000+08:00| SH1| 3002| 100| E| 180| `null`| 45.1| true| `null`|
|2024-11-28T08:00:00.000+08:00| SH1| 3001| 100| C| 90| 85.0| 35.2| false|2024-11-28T08:00:09.000+08:00|
|2024-11-26T13:37:00.000+08:00| PK0| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:37:34.000+08:00|
|2024-11-30T00:00:00.000+08:00| SH1| 3002| 101| F| 360| 90.0| 35.2| true| `null`|
|2024-11-29T00:00:00.000+08:00| SH1| 3001| 101| D| 360| 85.0| 35.1| `null`|2024-11-29T10:00:13.000+08:00|
|2024-11-27T00:00:00.000+08:00| PK0| 1001| 101| B| 180| 85.0| 35.1| true|2024-11-27T16:37:01.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
I executed the following SQL query:
select * from table1
where temperature in(select temperature from table2)
and humidity in(select humidity from table2);
The result got 4 records returned:
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| `time`|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status| arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-26T13:37:00.000+08:00| PK0| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:37:34.000+08:00|
|2024-11-26T13:38:00.000+08:00| PK0| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:38:25.000+08:00|
|2024-11-28T10:00:00.000+08:00| SH1| 3001| 100| C| 90| 85.0| 35.2| `null`|2024-11-28T10:00:11.000+08:00|
|2024-11-30T09:30:00.000+08:00| SH1| 3002| 101| F| 360| 90.0| 35.2| true| `null`|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
The expected result should include 5 records:
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| `time`|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status| arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-26T13:37:00.000+08:00| PK0| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:37:34.000+08:00|
|2024-11-26T13:38:00.000+08:00| PK0| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:38:25.000+08:00|
|2024-11-28T10:00:00.000+08:00| SH1| 3001| 100| C| 90| 85.0| 35.2| `null`|2024-11-28T10:00:11.000+08:00|
|2024-11-29T11:00:00.000+08:00| SH1| 3002| 100| E| 180| `null`| 45.1| true| `null`|
|2024-11-30T09:30:00.000+08:00| SH1| 3002| 101| F| 360| 90.0| 35.2| true| `null`|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
The record 2024-11-29T11:00:00.000+08:00should be included because the combination of temperature(null) and humidity(45.1) exists in both table1and table2.
I tried handling NULL cases separately:
SELECT * FROM table1
WHERE ((temperature IS `NULL` AND EXISTS (SELECT 1 FROM table2 WHERE temperature IS `NULL`))
OR temperature IN (SELECT temperature FROM table2 WHERE temperature IS NOT `NULL`))
AND ((humidity IS `NULL` AND EXISTS (SELECT 1 FROM table2 WHERE humidity IS `NULL`))
OR humidity IN (SELECT humidity FROM table2 WHERE humidity IS NOT `NULL`));
However, this query returned unexpected records (2024-11-27T16:38:00.000+08:00and 2024-11-27T16:42:00.000+08:00) because table2doesn't contain data with temperature/humidity combinations of null/35.1and null/35.2:
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| `time`|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status| arrival_time|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-26T13:37:00.000+08:00| PK0| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:37:34.000+08:00|
|2024-11-26T13:38:00.000+08:00| PK0| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:38:25.000+08:00|
|2024-11-27T16:38:00.000+08:00| PK0| 1001| 101| B| 180| `null`| 35.1| true|2024-11-27T16:37:01.000+08:00|
|2024-11-28T10:00:00.000+08:00| SH1| 3001| 100| C| 90| 85.0| 35.2| `null`|2024-11-28T10:00:11.000+08:00|
|2024-11-30T09:30:00.000+08:00| SH1| 3002| 101| F| 360| 90.0| 35.2| true| `null`|
|2024-11-27T16:42:00.000+08:00| PK0| 1001| 101| B| 180| `null`| 35.2| false| `null`|
|2024-11-29T11:00:00.000+08:00| SH1| 3002| 100| E| 180| `null`| 45.1| true| `null`|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
The issue is that my attempted solution treats NULL values independently for each column, but I need to ensure that the combination of temperature and humidity values exists in table2.
Since NULL values are unavoidable during data collection, how should I adjust my SQL query to achieve the expected results?