Bug #57900 create table .. select asserts scale >= 0 && precision > 0 && scale <= precision
Submitted: 1 Nov 2010 16:10 Modified: 25 Apr 2011 13:20
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S1 (Critical)
Version:5.1.55, 5.5.9-debug OS:Any
Assigned to: CPU Architecture:Any

[1 Nov 2010 16:10] Shane Bester
Description:
Version: '5.1.51-enterprise-gpl-advanced-debug'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: scale >= 0 && precision > 0 && scale <= precision, file decimal.c, line 1460

mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2086]
mysqld-debug.exe!raise()[winsig.c:590]
mysqld-debug.exe!abort()[abort.c:71]
mysqld-debug.exe!_wassert()[assert.c:212]
mysqld-debug.exe!decimal_bin_size()[decimal.c:1460]
mysqld-debug.exe!my_decimal_get_binary_size()[my_decimal.h:230]
mysqld-debug.exe!Field_new_decimal::Field_new_decimal()[field.cc:2486]
mysqld-debug.exe!Field_new_decimal::create_from_item()[field.cc:2530]
mysqld-debug.exe!Item_func::tmp_table_field()[item_func.cc:454]
mysqld-debug.exe!create_table_from_items()[sql_insert.cc:3553]
mysqld-debug.exe!select_create::prepare()[sql_insert.cc:3767]
mysqld-debug.exe!JOIN::prepare()[sql_select.cc:692]
mysqld-debug.exe!mysql_select()[sql_select.cc:2504]
mysqld-debug.exe!handle_select()[sql_select.cc:269]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2784]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:6051]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1260]
mysqld-debug.exe!do_command()[sql_parse.cc:888]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1136]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:293]
mysqld-debug.exe!_threadstart()[thread.c:277]

debug binary asserts, release binary creates the table with column datatype set to: decimal(0,0) DEFAULT NULL

How to repeat:
on debug server:

drop table if exists t1;
create table t1 select ceil(((linestringfromwkb(1) div null)));
[1 Nov 2010 16:18] Valeriy Kravchuk
Verified on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.7-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 select ceil(((linestringfromwkb(1) div null)));
ERROR 2013 (HY000): Lost connection to MySQL server during query
bin/mysqld_safe: line 144: 72460 Abort trap              nohup /Users/openxs/dbs/5.5/libexec/mysqld --basedir=/Users/openxs/dbs/5.5 --datadir=/Users/openxs/dbs/5.5/var --plugin-dir=/Users/openxs/dbs/5.5/lib/mysql/plugin --user=mysql --log-error=/Users/openxs/dbs/5.5/var/macbook-pro.err --pid-file=/Users/openxs/dbs/5.5/var/macbook-pro.pid < /dev/null >> /Users/openxs/dbs/5.5/var/macbook-pro.err 2>&1
mysql> 101101 18:17:10 mysqld_safe mysqld restarted

mysql> exit
Bye
macbook-pro:5.5 openxs$ tail -80 var/macbook-pro.err 

101030 13:29:13 mysqld_safe mysqld from pid file /Users/openxs/dbs/5.5/var/macbook-pro.pid ended
101101 17:52:10 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.5/var
101101 17:52:11 [Warning] Setting lower_case_table_names=2 because file system for /Users/openxs/dbs/5.5/var/ is case insensitive
101101 17:52:11 [Warning] One can only use the --user switch if running as root

101101 17:52:11 [Note] Plugin 'FEDERATED' is disabled.
101101 17:52:11 [Note] Plugin 'ndbcluster' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
101101 17:52:11  InnoDB: highest supported file format is Barracuda.
101101 17:52:11 InnoDB 1.1.2 started; log sequence number 788361434
101101 17:52:11 [ERROR] Native table 'performance_schema'.'THREADS' has the wrong structure
101101 17:52:11 [ERROR] Native table 'performance_schema'.'EVENTS_WAITS_SUMMARY_GLOBAL_BY_EVENT_NAME' has the wrong structure
101101 17:52:11 [Note] Event Scheduler: Loaded 0 events
101101 17:52:11 [Note] /Users/openxs/dbs/5.5/libexec/mysqld: ready for connections.
Version: '5.5.7-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (scale >= 0 && precision > 0 && scale <= precision), function decimal_bin_size, file decimal.c, line 1443.
101101 18:17:09 - mysqld got signal 6 ;
...
[1 Nov 2010 16:57] MySQL Verification Team
please also add this testcase to the testsuite:

drop table if exists t2;
create table t2 select if(1,convert('',unsigned),1);
[15 Dec 2010 16:22] MySQL Verification Team
another testcase for 5.1.55-debug and 5.5.9-debug... this interferes with random testing...

create table t2 select floor((select geomfromtext("point(1 1)") div 1 from t1)) d;
[24 Apr 2011 6:23] MySQL Verification Team
5.1.56-debug crashes on this:
select distinct convert((sum(distinct (truncate((.1),(extract(day from (@@global.large_files_support))))))),unsigned);

Version: '5.1.56-community-debug-log'  socket: ''  port: 3306  MySQL Community Server - Debug (GPL)
Assertion failed: scale >= 0 && precision > 0 && scale <= precision, file decimal.c, line 1460

 mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2084]
 mysqld-debug.exe!raise()[winsig.c:590]
 mysqld-debug.exe!abort()[abort.c:71]
 mysqld-debug.exe!_wassert()[assert.c:212]
 mysqld-debug.exe!decimal_bin_size()[decimal.c:1460]
 mysqld-debug.exe!my_decimal_get_binary_size()[my_decimal.h:230]
 mysqld-debug.exe!Field_new_decimal::Field_new_decimal()[field.cc:2469]
 mysqld-debug.exe!make_field()[field.cc:10103]
 mysqld-debug.exe!create_virtual_tmp_table()[sql_select.cc:10707]
 mysqld-debug.exe!Item_sum_distinct::setup()[item_sum.cc:1009]
 mysqld-debug.exe!setup_sum_funcs()[sql_select.cc:15847]
 mysqld-debug.exe!JOIN::exec()[sql_select.cc:2191]
 mysqld-debug.exe!mysql_select()[sql_select.cc:2547]
 mysqld-debug.exe!handle_select()[sql_select.cc:269]
 mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:5158]
 mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2293]
 mysqld-debug.exe!mysql_parse()[sql_parse.cc:6075]
 mysqld-debug.exe!dispatch_command()[sql_parse.cc:1261]
 mysqld-debug.exe!do_command()[sql_parse.cc:889]
 mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1149]
 mysqld-debug.exe!pthread_start()[my_winthread.c:85]
 mysqld-debug.exe!_callthreadstart()[thread.c:293]
 mysqld-debug.exe!_threadstart()[thread.c:277]
 kernel32.dll!FlsSetValue()
[25 Apr 2011 6:45] MySQL Verification Team
^^^ i filed oracle bug #12392636 for the above crash ^^^  it seems a bit different to this bug.
[25 Apr 2011 13:20] Paul Dubois
Noted in 5.1.57, 5.5.12, 5.6.3 changelogs.

An assertion could be raised in
Item_func_int_val::fix_num_length_and_dec() due to overflow for
geometry functions. 

CHANGESET - http://lists.mysql.com/commits/133980