Repository navigation
bug: INSERT … VALUES and CSV/TSV COPY INTO parse BIGINT/UINT64 text with a fraction or exponent through f64 (1760000000123456789.4 → 1760000000123456768) and clamp -9223372036854775809 to the BIGINT minimum #20786
Description
Search before asking
- I had searched in the issues and found no similar issues.
Version
main e9b49fd (source build, v1.2.953-nightly-e9b49fde); also v1.2.952-nightly-8862a6285b.
What's Wrong?
When the text for a BIGINT/UINT64 column is not a plain integer, the text decoders fall back to parsing it as an f64.
This covers a fraction (1760000000123456789.4), an exponent (1.760000000123456789e18), and a value just outside the
range. It affects INSERT … VALUES (fast path) and CSV/TSV COPY INTO. The f64 keeps only 53 bits, so integers above
2^53 are silently changed by up to a few hundred. -9223372036854775809 (out of range) rounds to exactly -2^63, passes
the range check, and is stored as the BIGINT minimum instead of being rejected. Every row is reported as loaded.
CAST of the very same text (or literal) parses it as a decimal and is exact (CAST('1.760000000123456789e18' AS BIGINT)
is 1760000000123456789, and TRY_CAST('-9223372036854775809' AS BIGINT) is NULL). Plain integer text is exact too.
Cause: read_int in SeparatedTextDecoder (src/query/formats/src/field_decoder/separated_text.rs:194, CSV/TSV) and
in FastFieldDecoderValues (src/query/formats/src/field_decoder/fast_values.rs:209, INSERT … VALUES) first try the
exact integer parse. If that fails, they parse the text as f64 and use num_traits::cast::cast(val.round()) ("can not
use read_num_text_exact directly, because we need to allow int like '1.0'"). The fallback could parse a decimal instead
(as the CAST path does), or reject values that do not convert exactly.
How to Reproduce?
CREATE OR REPLACE TABLE ib (id INT, b BIGINT, u UINT64);
INSERT INTO ib VALUES (1, 1760000000123456789.4, 1760000000123456789.4),
(2, 1.760000000123456789e18, 1.760000000123456789e18),
(3, 9007199254740993.4, 9007199254740993.4),
(4, -9223372036854775809, 0);
SELECT id, b, u FROM ib ORDER BY id;
-- 1 | 1760000000123456768 | 1760000000123456768
-- 2 | 1760000000123456768 | 1760000000123456768
-- 3 | 9007199254740994 | 9007199254740994
-- 4 | -9223372036854775808 | 0 <-- out of range, accepted as the minimum
SELECT CAST(1760000000123456789.4 AS BIGINT), CAST('1.760000000123456789e18' AS BIGINT),
CAST(9007199254740993.4 AS UINT64), TRY_CAST('-9223372036854775809' AS BIGINT);
-- 1760000000123456789 | 1760000000123456789 | 9007199254740993 | NULLCSV load:
CREATE OR REPLACE STAGE s119;
CREATE OR REPLACE TABLE txt (id INT, s STRING);
INSERT INTO txt VALUES (1, '1760000000123456789.4'), (2, '1.760000000123456789e18'), (3, '9007199254740993.4'),
(4, '-9223372036854775809'), (5, '-9223372036854776000');
COPY INTO @s119/csv/ FROM txt FILE_FORMAT = (TYPE = CSV);
CREATE OR REPLACE TABLE cb (id INT, b BIGINT);
COPY INTO cb FROM @s119/csv/ FILE_FORMAT = (TYPE = CSV); -- 5 rows loaded, 0 errors
SELECT c.id, t.s AS text_in_file, c.b AS stored, TRY_CAST(t.s AS BIGINT) AS cast_of_text
FROM cb c JOIN txt t ON c.id = t.id ORDER BY c.id;
-- 1 | 1760000000123456789.4 | 1760000000123456768 | 1760000000123456789
-- 2 | 1.760000000123456789e18 | 1760000000123456768 | 1760000000123456789
-- 3 | 9007199254740993.4 | 9007199254740994 | 9007199254740993
-- 4 | -9223372036854775809 | -9223372036854775808 | NULL
-- 5 | -9223372036854776000 | -9223372036854775808 | NULLExpected: the same value as CAST(text AS BIGINT) (rounded as CAST rounds), and an error for out-of-range text.
Single node, default settings, HTTP handler.
Are you willing to submit PR?
- Yes I am willing to submit a PR!
Activity