Bug #11821 Select from subselect using aggregate function on an enum segfaults.
Submitted: 8 Jul 2005 17:11 Modified: 12 Jul 2005 19:49
Reporter: Daniel Ostrowski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.12-standard/BK source OS:Linux (Linux)
Assigned to: Sergey Petrunya CPU Architecture:Any

[8 Jul 2005 17:11] Daniel Ostrowski
Description:
When selecting from a subselect alias that uses an aggregate function on an enum, MySQL segfaults. (i.e. select * from (select max(fld) from tbl) as foo; where fld is an enum)

Note that as recently as 4.1.8, this seems to work properly. In 4.1.11 it also seems to segfault. It is not just MAX() any aggregate that normally returns valid results from evaluating enums seems to do it. Note the subquery is valid by itself, but when it is a table alias for a select it segfaults.

-- Stack Trace (with symbols)
0x812bf77 handle_segfault + 423
0x40179559 _end + 935630257
0x80cf6e0 get_full_info__16Item_type_holderP4Item + 32
0x81d72aa prepare__18st_select_lex_unitP3THDP13select_resultUlPCc + 1226
0x81d879d mysql_handle_derived__FP6st_lex + 285
0x81d86d9 mysql_handle_derived__FP6st_lex + 89
0x8159e36 open_and_lock_tables__FP3THDP13st_table_list + 86
0x813d8fe mysql_execute_command__FP3THD + 958
0x81425a3 mysql_parse__FP3THDPcUi + 211
0x813c741 dispatch_command__F19enum_server_commandP3THDPcUi + 1345
0x813c1f3 do_command__FP3THD + 179
0x813b979 handle_one_connection + 601
0x401736de _end + 935606070
0x402f2107 _end + 937173343

How to repeat:
-- SQL That crashes server
create table tbl (fld enum('0','1'));
insert into tbl values ('1');
select * from (select max(fld) from tbl) as foo;
[8 Jul 2005 17:23] Miguel Solorzano
Thank you for the bug report I was able to repeat:

[New Thread 147466 (LWP 4212)]
/share/dbs/4.1/libexec/mysqld: ready for connections.
Version: '4.1.13-debug-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 163851 (LWP 4214)]
[Thread 163851 (LWP 4214) exited]
[New Thread 180236 (LWP 4216)]
mysqld: item.cc:3134: void Item_type_holder::get_full_info(Item*): Assertion `(enum_set_typelib && get_real_type(item) == MYSQL_TYPE_NULL) || (!enum_set_typelib && item->type() == Item::FIELD_ITEM && (get_real_type(item) == MYSQL_TYPE_ENUM || get_real_type(item) == MYSQL_TYPE_SET) && ((Field_enum*)((Item_field *) item)->field)->typelib)' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 180236 (LWP 4216)]
0x40269ef1 in kill () from /lib/libc.so.6
(gdb) backtrace full
#0  0x40269ef1 in kill () from /lib/libc.so.6
No symbol table info available.
#1  0x4018fbb1 in pthread_kill () from /lib/libpthread.so.0
No symbol table info available.
#2  0x4018ff2b in raise () from /lib/libpthread.so.0
No symbol table info available.
#3  0x40269b24 in raise () from /lib/libc.so.6
No symbol table info available.
#4  0x4026b3fd in abort () from /lib/libc.so.6
No symbol table info available.
#5  0x4026300f in __assert_fail () from /lib/libc.so.6
No symbol table info available.
#6  0x081084e5 in Item_type_holder::get_full_info (this=0x8c75e48, item=0x8c74be8) at item.cc:3128
No locals.
#7  0x08107f16 in Item_type_holder (this=0x8c75e48, thd=0x8c6d648, item=0x8c74be8) at item.cc:2883
No locals.
#8  0x0823f3aa in st_select_lex_unit::prepare (this=0x8c74aa0, thd_arg=0x8c6d648, sel_result=0x8c74b4c, additional_options=0, 
    tmp_table_alias=0x8c74d38 "foo") at item.h:114
        it = {<base_list_iterator> = {list = 0x8c749f0, el = 0x8c74c88, prev = 0x0, current = 0x0}, <No data fields>}
        item_tmp = (class Item *) 0x8c74be8
        can_skip_order_by = 6
        join = (JOIN *) 0x8c74be8
        lex_select_save = (SELECT_LEX *) 0x8c6d770
        sl = (SELECT_LEX *) 0x8c74988
        first_select = (SELECT_LEX *) 0x8c74988
        tmp_result = (class select_result *) 0x8c74e88
        is_union = false
        empty_table = (TABLE *) 0x8c75940
        _db_func_ = 0x260 <Address 0x260 out of bounds>
        _db_file_ = 0xbe3ff150 "\230íÇ\bû\003\031@ô_\031@lñ?¾P\002\031@\210NÇ\b\210IÇ\b\214ñ?¾×\213\025\bðIÇ\b\210LÇ\b"
        _db_level_ = 3191861588
        _db_framep_ = (char **) 0xbe3ff158
#9  0x082405fc in mysql_derived (thd=0x8c6d648, lex=0x8c6d684, unit=0x8c74aa0, org_table_list=0x8c74d60) at sql_derived.cc:126
        first_select = (SELECT_LEX *) 0x8c74988
        table = (TABLE *) 0x8c74e88
        res = 0
        derived_result = (class select_union *) 0x8c74e88
        is_union = false
        save_current_select = (SELECT_LEX *) 0x8c6d770
---Type <return> to continue, or q <return> to quit---
        _db_func_ = 0x813c718 "\211ì]ÃU\211åVS\213u\b\203ì\fVèÍÿÿÿ\211Ã\203Ä\020\205Àt\017\203ì\004Vj"
        _db_file_ = 0x8c6d65c "¸HÇ\b"
        _db_level_ = 4
        _db_framep_ = (char **) 0xbe3ff204
#10 0x0824050f in mysql_handle_derived (lex=0x8c6d684) at sql_derived.cc:57
        res = 0
        cursor = (TABLE_LIST *) 0x8c74d60
        sl = (SELECT_LEX *) 0x8c6d770
#11 0x0819a2c1 in open_and_lock_tables (thd=0x8c6d648, tables=0x8c74e20) at sql_base.cc:1718
        _db_func_ = 0x0
        _db_file_ = 0x0
        _db_level_ = 13031044
        _db_framep_ = (char **) 0x8c6d684
        counter = 1
#12 0x08177cc7 in mysql_execute_command (thd=0x8c6d648) at sql_parse.cc:2058
        result = (class select_result *) 0x0
        res = 0
        lex = (LEX *) 0x8c6d684
        slave_fake_lock = false
        fake_prev_lock = (MYSQL_LOCK *) 0x0
        select_lex = (SELECT_LEX *) 0x8c6d770
        tables = (TABLE_LIST *) 0x8c74e20
        unit = (SELECT_LEX_UNIT *) 0x8c6d690
        _db_func_ = 0x8c99d5d "\226]\201Q-q\230P`y¹\200³qiü:Å\034\205ð*\016\221Þ\204½ø M@»\027\003\001"
        _db_file_ = 0x8c99d5d "\226]\201Q-q\230P`y¹\200³qiü:Å\034\205ð*\016\221Þ\204½ø M@»\027\003\001"
        _db_level_ = 48
        _db_framep_ = (char **) 0x8ca3580
#13 0x0817ce93 in mysql_parse (thd=0x8c6d648, inBuf=0x8c748c8 "select * from (select max(fld) from tbl) as foo", length=147248772)
    at sql_parse.cc:4259
        lex = (LEX *) 0x8c6d684
        _db_func_ = 0x8c6d648 "\210\226D\b8B_\b<B_\b\230\226D\bH^Ç\b¸HÇ\b"
        _db_file_ = 0x3 <Address 0x3 out of bounds>
        _db_level_ = 147248712
        _db_framep_ = (char **) 0xbe3ff9cc
#14 0x081769ea in dispatch_command (command=COM_QUERY, thd=0x8c6d648, packet=0x8c7ca29 "select * from (select max(fld) from tbl) as foo", 
    packet_length=48) at sql_parse.cc:1502
        packet_end = 0x8c748f7 ""
        net = (NET *) 0x8c6db9c
        error = false
---Type <return> to continue, or q <return> to quit---
        _db_func_ = 0x8c7ca28 "\003select * from (select max(fld) from tbl) as foo"
        _db_file_ = 0xbe3ff93c "\234ù?¾gÙ\025\b\230\006Ê\b\001"
        _db_level_ = 1074219133
        _db_framep_ = (char **) 0x8c6eb48
#15 0x0817640f in do_command (thd=0x8c6d648) at sql_parse.cc:1315
        packet = 0x8c7ca28 "\003select * from (select max(fld) from tbl) as foo"
        old_timeout = 30
        packet_length = 48
        net = (NET *) 0x8c6db9c
        command = COM_QUERY
        _db_func_ = 0x8156f3c "\203Ä\020\213]ü\211ì]ÃU\211åVS\213]\b\203ì\fSèm"
        _db_file_ = 0x8c6e7c4 "\210\027Ç\b"
        _db_level_ = 8192
        _db_framep_ = (char **) 0x1000
#16 0x081759a2 in handle_one_connection (arg=0x0) at sql_parse.cc:1047
        error = 1075404788
        net = (NET *) 0x8c6db9c
        thd = (class THD *) 0x8c6d648
        launch_time = 0
        set = {__val = {0 <repeats 32 times>}}
#17 0x4018d54e in pthread_start_thread () from /lib/libpthread.so.0
No symbol table info available.
#18 0x4018d5df in pthread_start_thread_event () from /lib/libpthread.so.0
No symbol table info available.
#19 0x402fab8a in clone () from /lib/libc.so.6
No symbol table info available.
(gdb)
[11 Jul 2005 19:54] 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/internals/26913
[12 Jul 2005 13:01] 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/internals/26941
[12 Jul 2005 13:08] 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/internals/26942
[12 Jul 2005 13:51] Sergey Petrunya
Fix pushed into 4.1.13 tree.
[12 Jul 2005 19:49] Paul Dubois
Noted in 4.1.13, 5.0.10 changelogs.
[14 Aug 2005 22:11] Arun Devaravar
It is still a bug in 4.1.13. 

Please use attached data for testing.

DROP TABLE IF EXISTS TmpCampDeliv1;
CREATE TABLE TmpCampDeliv1
SELECT CmCampaignKey, CmSiteKey, RmWhen, Impressions
FROM CmDelivery CD2
WHERE CD2.AdjQty IS NULL;
CREATE INDEX IX1 ON TmpCampDeliv1 ( CmCampaignKey,CmSiteKey,RmWhen); 
#
UPDATE CmDelivery CD4
SET CD4.ToDateQty=(SELECT SUM(D1.Impressions) FROM TmpCampDeliv1 D1 WHERE CD4.CmCampaignKey=D1.CmCampaignKey AND CD4.CmSiteKey=D1.CmSiteKey AND D1.RmWhen <= CD4.RmWhen)
WHERE CD4.ToDateQty IS NULL;
COMMIT;
[14 Aug 2005 22:16] Arun Devaravar
It is not allowing me to attach file. Please let me know how to send attachment.
[14 Aug 2005 22:18] Arun Devaravar
Use this if required. Create dummy data for same CmCampaignKey for multiple days.

CREATE TABLE `CmDelivery` (
  `RmWhen` date default NULL,
  `CmCampaignKey` int(11) NOT NULL default '0',
  `CmSiteKey` int(11) default NULL,
  `Impressions` int(11) default NULL,
  `Clickthrus` int(11) default NULL,
  `Aquisitions` int(11) default '0',
  `EmailSent` int(11) default '0',
  `Days` int(11) default NULL,
  `WhoCreatedCmUserKey` smallint(6) default '1',
  `WhenCreated` datetime default NULL,
  `WhoModifiedCmUserKey` smallint(6) default '1',
  `WhenModified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `CmPlacementKey` int(11) default NULL,
  `ToDateQty` int(11) default NULL,
  `AdjQty` double default NULL,
  `FinalQty` double default NULL,
  `DiscardDaysQtyFlag` char(1) default NULL,
  UNIQUE KEY `CmDeliveryUx1` (`CmCampaignKey`,`CmSiteKey`,`RmWhen`),
  KEY `CmDeliveryIx1` (`CmCampaignKey`),
  KEY `CmDeliveryIx2` (`CmSiteKey`),
  KEY `CmDeliveryIx3` (`RmWhen`),
  KEY `CmDeliveryIx4` (`CmPlacementKey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[15 Aug 2005 14:26] Daniel Ostrowski
Re: Arun Devaravar

Your example table does not contain an enum, so I cannot see how it would be related to this bug.