| Bug #45262 | Bad effects with CREATE TABLE and DECIMAL | ||
|---|---|---|---|
| Submitted: | 2 Jun 2009 11:09 | Modified: | 17 Jul 2009 3:30 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.1,5.4 | OS: | Any |
| Assigned to: | Alexey Kopytov | CPU Architecture: | Any |
[2 Jun 2009 11:14]
Matthias Leich
About the fix: In case there are good technical reasons why the CREATE TABLE must get an error or maybe a warning, the corresponding error message/warning must not confuse the user.
[3 Jul 2009 7:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/77845 2784 Alexey Kopytov 2009-07-03 Bug #45262: Bad effects with CREATE TABLE and DECIMAL Using DECIMAL constants with more than 65 digits in CREATE TABLE ... SELECT led to bogus errors in release builds or assertion failures in debug builds. The problem was in inconsistency in how DECIMAL constants and fields are handled internally. We allow arbitrarily long DECIMAL constants, whereas DECIMAL(M,D) columns are limited to M<=65 and D<=30. my_decimal_precision_to_length() was used in both Item and Field code and truncated precision to DECIMAL_MAX_PRECISION when calculating value length without adjusting precision and decimals. As a result, a DECIMAL constant with more than 65 digits ended up having length less than precision or decimals which led to assertion failures. Fixed by modifying my_decimal_precision_to_length() so that precision is truncated to DECIMAL_MAX_PRECISION only for Field object which is indicated by the new 'truncate' parameter. Another inconsistency fixed by this patch is how DECIMAL constants and expressions are handled for CREATE ... SELECT. create_tmp_field_from_item() (which is used for constants) was changed as a part of the bugfix for bug #24907 to handle long DECIMAL constants gracefully. Item_func::tmp_table_field() (which is used for expressions) on the other hand was still using a simplistic approach when creating a Field_new_decimal from a DECIMAL expression. @ mysql-test/r/type_newdecimal.result Added a test case for bug #45262. @ mysql-test/t/type_newdecimal.test Added a test case for bug #45262. @ sql/item.cc Use the new 'truncate' parameter in my_decimal_precision_to_length(). @ sql/item_cmpfunc.cc Use the new 'truncate' parameter in my_decimal_precision_to_length(). @ sql/item_func.cc 1. Use the new 'truncate' parameter in my_decimal_precision_to_length(). 2. Do not truncate decimal precision to DECIMAL_MAX_PRECISION for additive expressions involving long DECIMAL constants. 3. Fixed an incosistency in how DECIMAL constants and expressions are handled for CREATE ... SELECT. @ sql/item_func.h Use the new 'truncate' parameter in my_decimal_precision_to_length(). @ sql/item_sum.cc Use the new 'truncate' parameter in my_decimal_precision_to_length(). @ sql/my_decimal.h Do not truncate precision to DECIMAL_MAX_PRECISION when calculating length in my_decimal_precision_to_length() if 'truncate' parameter is FALSE. @ sql/sql_select.cc 1. Use the new 'truncate' parameter in my_decimal_precision_to_length(). 2. Use a more correct logic when adjusting value's length.
[8 Jul 2009 13:30]
Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:alexey.kopytov@sun.com-20090703104354-pmbuhg3gv6pessx5) (merge vers: 5.1.37) (pib:11)
[9 Jul 2009 7:36]
Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:alexey.kopytov@sun.com-20090703104354-pmbuhg3gv6pessx5) (merge vers: 5.1.37) (pib:11)
[10 Jul 2009 11:21]
Bugs System
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:alexey.kopytov@sun.com-20090703104813-ls11ni4hyit20rw0) (merge vers: 5.4.4-alpha) (pib:11)
[17 Jul 2009 3:30]
Paul DuBois
Noted in 5.1.37, 5.4.4 changelogs. Use of DECIMAL constants with more than 65 digits in CREATE TABLE ... SELECT statements led to spurious errors or assertion failures.
[12 Aug 2009 22:17]
Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 23:08]
Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[7 Oct 2009 18:37]
Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
Description: CREATE TEMPORARY TABLE t2 AS SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; Effects in release compiled with compile-pentium64-debug-max: -------------------------------- main.ml012 [ fail ] Test ended at 2009-06-02 11:48:00 CURRENT_TEST: main.ml012 mysqltest: At line 1: query 'CREATE TEMPORARY TABLE t2 AS SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col' failed: 2013: Lost connection to MySQL server during query Failed to read from '/work2/5.1/mysql-5.1-bugteam-work/mysql-test/var/log/ml012.log', errno: 22 Thread 1 (process 6182): #0 0x00007fece113dce6 in pthread_kill () from /lib64/libpthread.so.0 #1 0x0000000000afce8f in my_write_core (sig=6) at stacktrace.c:310 #2 0x00000000006b4db0 in handle_segfault (sig=6) at mysqld.cc:2537 #3 <signal handler called> #4 0x00007fece03465c5 in raise () from /lib64/libc.so.6 #5 0x00007fece0347bb3 in abort () from /lib64/libc.so.6 #6 0x00007fece033f1e9 in __assert_fail () from /lib64/libc.so.6 #7 0x0000000000683627 in Field_new_decimal (this=0x1217858, len_arg=67, maybe_null_arg=true, name=0x11f2a08 "my_col", dec_arg=81 'Q', unsigned_arg=false) at field.cc:2488 #8 0x000000000061ab71 in Item_func::tmp_table_field (this=0x11f2940, table=0x413d10a0) at item_func.cc:458 #9 0x000000000075cad0 in create_table_from_items (thd=0x118a448, create_info=0x413d2760, create_table=0x11f2480, alter_info=0x413d3060, items=0x118c3d8, lock=0x413d2118, hooks=0x413d1db0) at sql_insert.cc:3401 #10 0x000000000075d23d in select_create::prepare (this=0x11f2a10, values=@0x118c3d8, u=0x118bea8) at sql_insert.cc:3584 #11 0x0000000000753f9c in JOIN::prepare (this=0x11f2af0, rref_pointer_array=0x118c4a0, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x118c2d0, unit_arg=0x118bea8) at sql_select.cc:638 #12 0x00000000007543c9 in mysql_select (thd=0x118a448, rref_pointer_array=0x118c4a0, tables=0x0, wild_num=0, fields=@0x118c3d8, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0x11f2a10, unit=0x118bea8, select_lex=0x118c2d0) at sql_select.cc:2364 #13 0x0000000000759a52 in handle_select (thd=0x118a448, lex=0x118be08, result=0x11f2a10, setup_tables_done_option=0) at sql_select.cc:268 #14 0x00000000006c7967 in mysql_execute_command (thd=0x118a448) at sql_parse.cc:2675 #15 0x00000000006cf3a8 in mysql_parse (thd=0x118a448, inBuf=0x11f22c8 "CREATE TEMPORARY TABLE t2 AS SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col", length=132, found_semicolon=0x413d3f00) at sql_parse.cc:5933 #16 0x00000000006d01d5 in dispatch_command (command=COM_QUERY, thd=0x118a448, packet=0x11de889 "CREATE TEMPORARY TABLE t2 AS SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col", packet_length=132) at sql_parse.cc:1213 #17 0x00000000006d1599 in do_command (thd=0x118a448) at sql_parse.cc:854 #18 0x00000000006be131 in handle_one_connection (arg=0x118a448) at sql_connect.cc:1115 #19 0x00007fece1139040 in start_thread () from /lib64/libpthread.so.0 #20 0x00007fece03e708d in clone () from /lib64/libc.so.6 #21 0x0000000000000000 in ?? () Effects in release compiled with compile-pentium64-max: -------------------------------- At line 1: query 'CREATE TEMPORARY TABLE t2 AS SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col' failed: 1118: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs This error message is pure non sense. I have these effects with mysql-5.1-bugteam and mysql-azalea May 2009 My environment: Linux OpenSuSE 11.0 64 Bit, Intel Core2Duo It might be that this bug and Bug#45261 Crash, stored procedure + decimal suffer from the same reason. How to repeat: See above