| Bug #40761 | LEFT JOIN on inline view crashes server | ||
|---|---|---|---|
| Submitted: | 15 Nov 2008 19:17 | Modified: | 28 Jan 2009 21:38 |
| Reporter: | Robert Hunt | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.1.29/5.0/5.1/6.0 | OS: | Windows (WinXP (Intel-32)/Linux) |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[15 Nov 2008 19:43]
MySQL Verification Team
Thank you for the bug report. Would be nice if you provide a repeatable test case with tables and data which allow to run the offended query, that file could be private if you want. Thanks in advance.
[15 Nov 2008 19:53]
MySQL Verification Team
I was able to repeat even with empty tables with 2 weeks older Windows server, however looks a quite different call stack:
mysqld.exe!_NMSG_WRITE(int rterrnum=0x0000000a) Line 195 C
mysqld.exe!abort() Line 44 + 0x7 C
mysqld.exe!_assert(const char * expr=0x00bed4d4, const char * filename=0x00bed4c8, unsigned int lineno=0x000005b6) Line 306 C
mysqld.exe!decimal_bin_size(int precision=0x00000000, int scale=0x00000000) Line 1462 + 0x28 C
mysqld.exe!my_decimal_get_binary_size(unsigned int precision=0x00000000, unsigned int scale=0x00000000) Line 217 + 0xd C++
mysqld.exe!Field_new_decimal::Field_new_decimal(unsigned int len_arg=0x00000001, bool maybe_null_arg=true, const char * name=0x01f11298, unsigned char dec_arg=0x00, bool unsigned_arg=false) Line 2490 + 0x14 C++
mysqld.exe!Item_sum::create_tmp_field(bool group=false, st_table * table=0x01f170a0, unsigned int convert_blob_length=0x00000000) Line 504 + 0x46 C++
mysqld.exe!Item_sum_hybrid::create_tmp_field(bool group=false, st_table * table=0x01f170a0, unsigned int convert_blob_length=0x00000000) Line 710 + 0x14 C++
mysqld.exe!create_tmp_field(THD * thd=0x01ed71e8, st_table * table=0x01f170a0, Item * item=0x01f11168, Item::Type type=SUM_FUNC_ITEM, Item * * * copy_func=0x038edc90, Field * * from_field=0x01f17a54, Field * * default_field=0x01f179ec, bool group=false, bool modify_item=false, bool table_cant_handle_bit_fields=false, bool make_copy_field=false, unsigned int convert_blob_length=0x00000000) Line 9437 + 0x1c C++
mysqld.exe!create_tmp_table(THD * thd=0x01ed71e8, TMP_TABLE_PARAM * param=0x01f15598, List<Item> & fields={...}, st_order * group=0x00000000, bool distinct=false, bool save_sum_fields=true, unsigned __int64 select_options=0x0000000090045a00, unsigned __int64 rows_limit=0xffffffffffffffff, char * table_alias=0x01f14300) Line 9864 + 0xbd C++
mysqld.exe!select_union::create_result_table(THD * thd_arg=0x01ed71e8, List<Item> * column_types=0x01f0f2a4, bool is_union_distinct=false, unsigned __int64 options=0x0000000090045a00, const char * alias=0x01f14300) Line 124 + 0x2c C++
mysqld.exe!mysql_derived_prepare(THD * thd=0x01ed71e8, st_lex * lex=0x01ed8208, TABLE_LIST * orig_table_list=0x01f14328) Line 171 + 0x27 C++
mysqld.exe!mysql_handle_derived(st_lex * lex=0x01ed8208, bool (THD *, st_lex *, TABLE_LIST *)* processor=0x0040972d) Line 56 + 0x17 C++
mysqld.exe!open_and_lock_tables_derived(THD * thd=0x01ed71e8, TABLE_LIST * tables=0x01f14328, bool derived=true) Line 4974 + 0x19 C++
mysqld.exe!open_and_lock_tables(THD * thd=0x01ed71e8, TABLE_LIST * tables=0x01f14328) Line 1532 + 0xf C++
mysqld.exe!execute_sqlcom_select(THD * thd=0x01ed71e8, TABLE_LIST * all_tables=0x01f14328) Line 4793 + 0xd C++
mysqld.exe!mysql_execute_command(THD * thd=0x01ed71e8) Line 2136 + 0xd C++
mysqld.exe!mysql_parse(THD * thd=0x01ed71e8, const char * inBuf=0x01efef60, unsigned int length=0x000006d8, const char * * found_semicolon=0x038efd00) Line 5727 + 0x9 C++
mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x01ed71e8, char * packet=0x01ef6eb1, unsigned int packet_length=0x000006d8) Line 1152 + 0x1e C++
mysqld.exe!do_command(THD * thd=0x01ed71e8) Line 809 + 0x1b C++
mysqld.exe!handle_one_connection(void * arg=0x01ed71e8) Line 1115 + 0x9 C++
mysqld.exe!pthread_start(void * param=0x01220df8) Line 85 + 0x9 C
mysqld.exe!_threadstart(void * ptd=0x01eb1048) Line 196 + 0xd C
kernel32.dll!7c80b713()
[15 Nov 2008 20:00]
MySQL Verification Team
The released version 5.1.29 with empty tables don't crash:
01' ), ifNull(
-> stc.updated, te.updated ) desc, te.nascency;
Empty set (0.13 sec)
[15 Nov 2008 22:33]
MySQL Verification Team
Thank you for the feedback.
[16 Nov 2008 0:03]
MySQL Verification Team
Repeatable on 5.1.29 released version on Windows however today bzr source server on Ubuntu 64-bit shows an assertion. Repeatable with MyISAM engine too: Version: '5.0.74-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution mysqld: decimal.c:1456: decimal_bin_size: Assertion `scale >= 0 && precision > 0 && scale <= precision' failed. 081115 21:00:03 - mysqld got signal 6 ; 081115 21:23:20 [Note] 5.1/libexec/mysqld: ready for connections. Version: '5.1.31-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution mysqld: decimal.c:1462: decimal_bin_size: Assertion `scale >= 0 && precision > 0 && scale <= precision' failed. 081115 21:24:17 - mysqld got signal 6 ; Version: '6.0.9-alpha-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution mysqld: decimal.c:1445: decimal_bin_size: Assertion `scale >= 0 && precision > 0 && scale <= precision' failed. 081115 21:54:49 - mysqld got signal 6 ;
[12 Dec 2008 14:35]
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/61507 2748 Gleb Shchepa 2008-12-12 Bug #40761: Assert on sum function on IF(..., CAST(longtext AS UNSIGNED), signed_val) (was: LEFT JOIN on inline view crashes server) Select from a LONGTEXT column wrapped with an expression like "IF(..., CAST(longtext_column AS UNSIGNED), smth_signed)" failed an assertion or crashed the server. IFNULL function was affected too. LONGTEXT column item has a maximum length of 32^2-1 bytes, at the same time this is a maximum possible length of any MySQL item. CAST(longtext_column AS UNSIGNED) returns some unsigned numeric result of length 32^2-1, so the result of IF/IFNULL function of this number and some other signed number will have text length of (32^2-1)+1=32^2 (one byte for the minus sign) - there is integer overflow, and the length is equal to zero. That caused assert/crash. CAST AS UNSIGNED function has been modified to limit maximal length of resulting number to 67 (maximal length of DECIMAL and two characters for minus sign and dot).
[6 Jan 2009 13:57]
Bugs System
Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)
[9 Jan 2009 1:20]
Paul DuBois
Noted in 5.0.76 changelog. IF(..., CAST(longtext_val AS UNSIGNED), signed_val) as an argument to an aggregate function could cause an assertion failure. Setting report to NDI pending push into 5.1.x/6.0.x.
[15 Jan 2009 6:36]
Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)
[15 Jan 2009 16:33]
Paul DuBois
Noted in 5.1.31 changelog. Setting report to NDI pending push into 6.0.x.
[19 Jan 2009 11:22]
Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:00]
Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 14:50]
Jon Stephens
Setting status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:06]
Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 18:53]
Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[28 Jan 2009 21:38]
Paul DuBois
Noted in 6.0.10 changelog.
Description: Query: ---------------------------------------------------- SELECT @now:=now() , @cCls:='xyz' ; SELECT te.cID , date_format( te.nascency, '%Y/%m/%d %H:%i' ) as nascency , date_format( ifNull( stc.updated, te.updated ), '%Y/%m/%d %H:%i' ) as updated , date_format( te.expiration, '%Y/%m/%d %H:%i' ) as expiration , te.priorityCol , te.headline , te.headlineLink , stc.content as story , te.storyOpts FROM ( SELECT cID , updated , max( if( attrName = 'status', attrValue, null ) ) as sts , max( if( attrName = 'headline', attrValue, null ) ) as headline , max( if( attrName = 'headlineLink', attrValue, null ) ) as headlineLink , max( if( attrName = 'nascency', timestamp( attrValue ), null ) ) as nascency , max( if( attrName = 'expiration', timestamp( attrValue ), null ) ) as expiration , max( if( attrName = 'priorityCol', cast( attrValue as unsigned ), 0 ) ) as priorityCol , max( if( attrName = 'priorityRow', cast( attrValue as unsigned ), 0 ) ) as priorityRow , max( if( attrName = 'mimeType', attrValue, null ) ) as mimeType , max( if( attrName = 'sourceID', attrValue, null ) ) as srcID , group_concat( if( attrName not in ( 'status', 'headline', 'headlinelink', 'nascency', 'expiration', 'priorityCol', 'priorityRow', 'mimeType', 'sourceID' ), concat( attrName, ': \'', attrValue, '\'' ), null ) separator ', ' ) as storyOpts FROM siteContent as sc WHERE cClass = @cCls GROUP BY cClass, cID HAVING sts = 'active' AND @now BETWEEN ifNull( nascency, @now ) AND ifNull( expiration, @now ) AND mimeType LIKE 'text%' ) as te LEFT JOIN siteTextContent as stc ON stc.cID = ifNull( te.srcID, te.cID ) ORDER BY te.priorityCol, te.priorityRow, ifNull( te.expiration, '9999-01-01' ), ifNull( stc.updated, te.updated ) desc, te.nascency ---------------------------------------------------- Inner query (te) works fine (5 rows returned). When full query is run, mysqld crashes with this info from mysql.err: ---------------------------------------------------- 081115 13:53:09 - mysqld got exception 0xc0000005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=1 max_threads=151 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337711 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x1895a28 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... 00635736 mysqld.exe!my_charset_same()[charset.c:36] 004E0B08 mysqld.exe!Protocol::store_string_aux()[protocol.cc:760] 004E1CFD mysqld.exe!Protocol_text::store()[protocol.cc:925] 004430A2 mysqld.exe!ha_release_temporary_latches()[handler.cc:1673] 0053069C mysqld.exe!select_send::send_data()[sql_class.cc:1559] 00574E3B mysqld.exe!end_send()[sql_select.cc:11910] 0057462C mysqld.exe!evaluate_join_record()[sql_select.cc:11170] 0057A9C7 mysqld.exe!sub_select()[sql_select.cc:11055] 0057462C mysqld.exe!evaluate_join_record()[sql_select.cc:11170] 0057A9C7 mysqld.exe!sub_select()[sql_select.cc:11055] 0058F0E7 mysqld.exe!do_select()[sql_select.cc:10811] 00590239 mysqld.exe!JOIN::exec()[sql_select.cc:2183] 00590893 mysqld.exe!mysql_select()[sql_select.cc:2363] 00590CDC mysqld.exe!handle_select()[sql_select.cc:269] 0055060F mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4828] 00551B70 mysqld.exe!mysql_execute_command()[sql_parse.cc:2136] 00558850 mysqld.exe!mysql_parse()[sql_parse.cc:5731] 005593F8 mysqld.exe!dispatch_command()[sql_parse.cc:1154] 0055A36A mysqld.exe!do_command()[sql_parse.cc:809] 005E020B mysqld.exe!handle_one_connection()[sql_connect.cc:1115] 0040A605 mysqld.exe!() 8A0775C0 Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 018D2B18=SELECT ... (query as noted above) thd->thread_id=1 thd->killed=NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. ------------------------------------------------------------------------ DDL for tables in question: ------------------------------------------------------------------------ CREATE TABLE `sitecontent` ( `cClass` varchar(128) DEFAULT NULL, `cID` varchar(20) NOT NULL DEFAULT '', `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `attrIx` int(11) NOT NULL DEFAULT '0', `attrName` text, `attrValue` longtext, KEY `ixSCcid` (`cID`), KEY `ixSCclsIDattr` (`cClass`(32),`cID`,`attrName`(16)), KEY `ixSCattrValcls` (`attrValue`(64),`cClass`(32),`attrName`(16),`cID`(8)), KEY `ixSCclsAttrVal` (`cClass`(32),`attrName`(16),`attrValue`(64),`cID`(8)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Site content attributes'; -- Host: localhost -- Database: lnal -- Table: 'sitetextcontent' -- CREATE TABLE `sitetextcontent` ( `cID` varchar(20) NOT NULL DEFAULT '', `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `content` longtext, PRIMARY KEY (`cID`), CONSTRAINT `sitetextcontent_ibfk_1` FOREIGN KEY (`cID`) REFERENCES `sitecontent` (`cID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Site text content repository'; --------------------------------------------------------------------------- This query works fine in 4.1(.13) database. How to repeat: This query crashes the server every time.