Null, -2147483648 or what am I doing wrong?

Community
1

Hi, I have a question:

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);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.

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.

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.

I find -2147483648 instead of null.

Am I doing something wrong?

2

Hi @BepTheWolf ,

Thanks for the report! I was able to reproduce this, it looks like a potential bug converting from the null LONG to a DOUBLE. Let me look into it!

3

Are you on Windows, by any chance?

4

Hi, no I’m on Mac OS

5

Cool, I have confirmed this as two bugs.

  1. When the INT is copied into the table’s DOUBLE column, the conversion is not null-aware. We use -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.

2. On the first insert, you see the value at all, when it should be 101. Indeed you see the 101 if you try the insert again. So there is an issue with how the UNION is handled.

In the meantime, if you change your code to perform a double conversion i.e.

101::double
null::double

It will work as expected :slight_smile:

6

Great.
Thanks a lot :slightly_smiling_face: