To reproduce

Query returns incorrect results when INT column IN list contains mixed positive and negative numbers

Description

I have encountered an issue where a SELECT query returns an incorrect number of rows when the IN list for an INTEGER column contains a mix of positive and negative numbers, even though the data only contains the negative values.

The query execution appears to be disrupted by the presence of the positive numbers in the IN list when combined with other WHERE clause filters.

Steps to Reproduce

1. Table Schema: The table used for the query is similar to this (actual query uses ANOMALYLOGTBL with similar schema):

   CREATE TABLE 'ANOMALYLOGTBL' ( 
   ANOMALY_ID STRING,
   ANOMALY_TRIGGER_TIME TIMESTAMP,
   SYSTEM_ID SYMBOL CAPACITY 16384 CACHE,
   LOG_ID STRING,
   RUN_TIME TIMESTAMP,
   ANOMALY_SOURCE STRING,
   ANOMALY_TYPE SYMBOL CAPACITY 64 CACHE,
   ANOMALY_SCORE DOUBLE,
   ANOMALY_LEVEL INT,
   ANOMALY_ANALYSIS STRING,
   ANOMALY_REMARK STRING,
   ANOMALY_IP SYMBOL CAPACITY 4096 CACHE,
   ANOMALY_HOSTNAME SYMBOL CAPACITY 4096 CACHE,
   ANOMALY_RISKLVL INT,
   ANOMALY_ACTION INT,  -- The critical column is INT
   ANOMALY_JSON STRING
   ) timestamp(ANOMALY_TRIGGER_TIME) PARTITION BY MONTH WAL
   WITH maxUncommittedRows=500000, o3MaxLag=600000000us;

   The column ANOMALY_ACTION in the queried table (ANOMALYLOGTBL) is confirmed to be of type INTEGER.

2. Data State: For the specific time range and filters used, there are 11 rows that satisfy all filtering conditions. Crucially, the ANOMALY_ACTION value in all 11 relevant rows is either -1 or -2. There are no rows with ANOMALY_ACTION equal to 1, 2, or 3.

3. Failing Query (Mixed Positive/Negative in IN list): When querying with the full set of values, only 3 rows are returned, causing 8 rows to be incorrectly filtered out.

   SELECT * FROM ANOMALYLOGTBL 
   WHERE ((RUN_TIME BETWEEN '2025-10-24 00:00:00.0' AND '2025-11-24 23:59:59.999') 
       AND ANOMALY_TYPE IN ('S3', 'O1', 'O2', 'O3', 'M1', 'O4', 'M2', 'O5', 'I1', 'I2', 'I3', 'I4', 'I5', 'C1', 'C2', 'C3', 'C4', 'R1', 'R2', 'R3', 'F1', 'F2', 'F3', 'F4', 'F5', 'S1', 'S2') 
       AND ANOMALY_RISKLVL IN (1, 2, 3) 
       AND ANOMALY_ACTION IN (1, 2, 3, -1, -2) -- FAILING CONDITION
   ) ORDER BY RUN_TIME DESC LIMIT 2000;
   
   -- Result: 3 rows (FAILED)

4. Successful Query 1 (Negative Numbers Only): When the positive values are removed, the query works correctly.

   SELECT * FROM ANOMALYLOGTBL 
   WHERE ((RUN_TIME BETWEEN '2025-10-24 00:00:00.0' AND '2025-11-24 23:59:59.999') 
       AND ANOMALY_TYPE IN ('S3', 'O1', 'O2', 'O3', 'M1', 'O4', 'M2', 'O5', 'I1', 'I2', 'I3', 'I4', 'I5', 'C1', 'C2', 'C3', 'C4', 'R1', 'R2', 'R3', 'F1', 'F2', 'F3', 'F4', 'F5', 'S1', 'S2') 
       AND ANOMALY_RISKLVL IN (1, 2, 3) 
       AND ANOMALY_ACTION IN (-1, -2) -- ONLY NEGATIVE VALUES, WORKS FINE WITHOUT 1,2,3
   ) ORDER BY RUN_TIME DESC LIMIT 2000;
   
   -- Result: 11 rows (Correct)

   I make sure all ANOMALY_ACTION values are -1 or -2 in the table, therefore, the query results should be the same with step3, but one result is 3 and the other is 11.

5. Successful Query 2 (Using OR to bypass IN): Using OR statement to relpace IN statement, results are also correct.

   SELECT * FROM ANOMALYLOGTBL 
   WHERE ((RUN_TIME BETWEEN '2025-10-24 00:00:00.0' AND '2025-11-24 23:59:59.999') 
       AND ANOMALY_TYPE IN ('S3', 'O1', 'O2', 'O3', 'M1', 'O4', 'M2', 'O5', 'I1', 'I2', 'I3', 'I4', 'I5', 'C1', 'C2', 'C3', 'C4', 'R1', 'R2', 'R3', 'F1', 'F2', 'F3', 'F4', 'F5', 'S1', 'S2') 
       AND ANOMALY_RISKLVL IN (1, 2, 3) 
       AND (ANOMALY_ACTION = 1 OR ANOMALY_ACTION = 2 OR ANOMALY_ACTION = 3 OR ANOMALY_ACTION = -1 OR ANOMALY_ACTION = -2) -- USING OR INSTEAD OF IN
   ) ORDER BY RUN_TIME DESC LIMIT 2000;
   
   -- Result: 11 rows (Correct)

Expected Behavior

The Failing Query (Step 3) should return 11 rows.

I am new to the QuestDB, so I am unsure if this is a bug or if there is a specific reason for this behavior. Thanks your patience and help!

--
Elven

QuestDB version:

9.1.1 and 9.2.0

OS, in case of Docker specify Docker and the Host OS:

Windows 11 x64 with bundle JVM 17

File System, in case of Docker specify Host File System:

Windows NTFS

Full Name:

elven0630

Affiliation:

[email protected], WNJsoft Inc.

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• Yes, I have

Additional context

No response