Hi all,
I have a table with an INT column.
The table is sorted by that column, and monetdb has recognized the sort order.
While performing this query:
--------------
SELECT * FROM table WHERE column = 12345678900;
--------------
monetdb converts to BIGINT/LNG as evidenced by the trace:
--------------
3612684 | X_17=[89617785] := batcalc.lng(X_16=[89617785]);
--------------
This seems logical behavior since the condition value would overflow INT. (Of course the result set is empty by definition, but it’s a valid query)
However, the same happens when I then perform subsequent queries with values that *can* be represented by INT:
--------------
SELECT * FROM table WHERE column = 123;
--------------
It is only when an explicit INT value is entered, that the type conversion is stopped for that query and subsequent ones:
--------------
SELECT * FROM table WHERE column = CAST(123 AS INT);
--------------
In other words, once the type conversion is performed once, it seems to “stick” to subsequent queries.
Type conversion on that column takes ~4s on a 90MM row table here, while the regular query time is usually sub-millisecond.
Entering the “wrong" value once will influence the DB performance heavily until restarted, which is far from a desirable scenario.
Of course I could always clamp my value to INT range at the application level, but this is brittle, and the DB could do the right thing here.
Is there a design consideration that I’m missing, or should I file this as a bug/performance issue?
Thanks,
Dennis
