Padraig O'Sullivan wrote:
Hi, I'm currently working on bug#337038 (decimal truncation should be an error, not warning) and am looking for some input on an issue I'm having. Basically, I have a fix implemented but it changes the expected behavior in a number of test cases. The basic question I have at the moment is whether an error should be thrown when a temporary table is created where a decimal value is truncated? I'll expand on the question a little bit below but that's basically what I'm looking for input on. For example, if I create a table as follows (based on a test case in func_group.test): drizzle> create table t1 (a int, b int, c int); and then insert some values which look like: drizzle> select * from t1; +------+------+------+ | a | b | c | +------+------+------+ | 1 | 1 | 1 | | 1 | 1 | 2 | | 1 | 1 | 3 | | 1 | 1 | 3 | +------+------+------+ 4 rows in set (0.00 sec) Now, if I execute a query with a group by where I group by a fraction as follows (this is the current behavior in trunk): drizzle> select b/c as v, count(*) from t1 group by v; +--------+----------+ | v | count(*) | +--------+----------+ | 0.3333 | 2 | | 0.5000 | 1 | | 1.0000 | 1 | +--------+----------+ 3 rows in set (0.01 sec) drizzle> we can see that a decimal value has been truncated in a temporary table (1/3 has been truncated to 0.3333).
Truncation is inevitable is this case (an arithmetic operation). It seems to me that the original problem is about loosing precision in a copy operation. I guess that the real problem here is that the arithmetic operation generates a result with one precision, but the temporary table expects another and has to truncate the inserted value.
If it was possible to generate the proper precision directly from the arithmetic, there would not have been any truncation.
Now, letting an integer-divideby-integer operation return a decimal result - that's a completely different story...
Thanks, Roy
Now with the fix I've implemented, when a decimal is truncated, an error occurs when the store_value() method in decimal.cc is executed but in the case of temporary tables when results are recorded in a temporary table, the result value is never checked (it should be checked in the copy_funcs() method in sql_select.cc). Therefore, an error is not issued which results in behavior like so (with the same table definition and data as above): drizzle> select b/c as v, count(*) from t1 group by v; +-----------------+----------+ | v | count(*) | +-----------------+----------+ | 0.5000 | 1 | | 1.0000 | 1 | | 9999999999.9999 | 2 | +-----------------+----------+ 3 rows in set (0.00 sec) drizzle> If an error was thrown, this statement would not execute. Instead, an error would be returned to the user indicating that truncation of a decimal value occurred. My question for the list is whether we should throw an error when creating a temporary table where a decimal value is truncated? -Padraig _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
