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:
None 
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:17] Robert Hunt
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.
[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.