I make a table:
CREATE TABLE ‘MyTab’ (
index_time TIMESTAMP,
ColA DOUBLE NULL,
ColB DOUBLE NULL,
ColC DOUBLE NULL,
ColD DOUBLE NULL
) timestamp(index_time) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(index_time);
I run this query:
INSERT INTO MyTab (index_time, ColA, ColB, ColC, ColD)
SELECT ‘2025-04-09 17:20:00.000’ as “index_time”, 100 as “ColA”, null as ColB, 102 as “ColC”, 103 as “ColD”;
I get this result:
2025-04-09T17:20:00.000000Z, 100, null, 102, 103.
Then I
TRUNCATE TABLE MyTab;
I run this query:
WITH MyTmp AS (
SELECT ‘2025-04-09 17:20:00.000’ as “index_time”, 100 as “ColA”, null as ColB, 102 as “ColC”, 103 as “ColD”
)
INSERT INTO MyTab (“index_time”, “ColA”, “ColB”, “ColC”, “ColD”)
select “index_time”, “ColA”, “ColB”, “ColC”, “ColD” from MyTmp limit 1;
I get this result:
2025-04-09T17:20:00.000000Z, 100, null, 102, 103.
Then
TRUNCATE TABLE MyTab;
I run this query:
WITH MyTmp AS (
SELECT ‘2025-04-09 17:20:00.000’ as “index_time”, 100 as “ColA”, 101 as “ColB”, 102 as “ColC”, 103 as “ColD” FROM MyTab WHERE index_time = ‘2025-04-09 17:20:00.000’
UNION
SELECT ‘2025-04-09 17:20:00.000’ as “index_time”, 100 as “ColA”, null as ColB, 102 as “ColC”, 103 as “ColD”
)
INSERT INTO MyTab (“index_time”, “ColA”, “ColB”, “ColC”, “ColD”)
select “index_time”, “ColA”, “ColB”, “ColC”, “ColD” from MyTmp limit 1;
I get this result:
2025-04-09T17:20:00.000000Z, 100, -2147483648, 102, 103.
And as you can see, I find -2147483648 instead of null.
Am I doing something wrong?
This is a bug with type conversions and lack of null checks. When the INT is copied into the table’s DOUBLE column, the conversion is not null-aware. QuestDB uses -2147483648 to denote a null int, and NaN for a null double. So you are seeing the result of a direct conversion without checking for nulls. I have added an issue into the QuestDB repository to get it fixed.
In the meantime, if you change your code to perform a double conversion i.e.
101::double
null::double
It will work as expected