number limitations in some queries

[leehart]

server/DQXDbTools.py works on strings (see the ToSafeIdentifier function), which causes string-to-decimal conversion issues in monet, e.g.

sql>SELECT count(*) AS "TotalRecordCount" FROM "pf_samples" WHERE ("year" < '1000000000000000000' * "year" + '0.02');
Decimal (1000000000000000000) doesn't have format (18.0)

The exception:

throw(SQL, STRING(TYPE), "decimal (%s) doesn't have format (%d.%d)", *val, *d, *sc);

seems to originate from https://dev.monetdb.org/hg/MonetDB/file/8f10a8b13e77/sql/backends/monet5/sql_round_impl.h

That particular problem (string-to-decimal limitation) could be avoided by not using strings in numeric expressions, e.g.

sql>SELECT count(*) AS "TotalRecordCount" FROM "pf_samples" WHERE ("year" < 1000000000000000000 * "year" + 0.02);

(gives no errors)

There is also another related problem to avoid (to support big numbers), which might need a separate issue log:

sql>SELECT count(*) AS "TotalRecordCount" FROM "pf_samples" WHERE ("year" < 1000000000000000000 * "year" + 1000000000000000000);
overflow in calculation 1000000000000000000*2014.

[leehart]

Seems related (string casting limit of 18 prec), Monet data types:
https://www.monetdb.org/book/export/html/187

DECIMAL '(' Prec ',' Scale ')' \|DEC '(' Prec ',' Scale ')' \|NUMERIC '(' Prec ',' Scale ')' 

Exact decimal number with precision Prec and scale Scale. Prec must be between 1 and 18 (or 38 when HUGEINT is also supported). Scale must be between 0 and Prec

[edit:]
Actually, that just seems to be a coincidence. Casting errors occur with as few as 9 zeros:

OperationalError: decimal (1000000000) doesn't have format (9.0)