Field conversion sticks to subsequent queries

8 Jul 2015

      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

Dennis Lorson

Niels Nes

Eyal Rozenberg

Dennis Lorson

Martin Kersten

tags

participants (4)