Bug #11868 update with subquery fails
Submitted: 11 Jul 2005 18:33 Modified: 17 Jul 2005 0:33
Reporter: Dave Rogers Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.7/5.0.10 OS:Linux (Fedora Core 4/windows)
Assigned to: Evgeny Potemkin

[11 Jul 2005 18:33] Dave Rogers
Description:
The following query fails with the error: 

ERROR 2013 (HY000): Lost connection to MySQL server during query

update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton and t1.startscandate = t2.startscandate and t2.severity = 4 and t2.falsepos = 1 group by ipaton);

This query with the same data works on MySQL 4.1.9.

How to repeat:
CREATE TABLE `t1` (
  `ipaton` int(4) unsigned NOT NULL default '0',
  `pcount` int(4) default NULL,
  `ncount` int(4) default NULL,
  `startscandate` datetime default NULL,
  KEY `aaa` (`ipaton`,`startscandate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES
(03222327078,1,NULL,'2005-03-31 02:30:25'),
(3231378172,1,NULL,'2005-03-31 06:40:23'),
(3222327085,1,NULL,'2005-05-02 03:38:57'),
(3222449185,6,NULL,'2005-04-29 06:30:43'),
(3236012060,36,NULL,'2005-01-11 09:03:02'),
(3231378050,1,NULL,'2005-04-30 08:46:25');

CREATE TABLE `t2` (
  `ip` varchar(20) default NULL,
  `falsepos` int(2) default NULL,
  `severity` int(2) default NULL,
  `pluginid` int(4) default NULL,
  `detailsid` int(4) default NULL,
  `startscandate` datetime NOT NULL default '0000-00-00 00:00:00',
  `service` varchar(20) default NULL,
  `risk` varchar(20) NOT NULL default '',
  `port` int(3) unsigned default NULL,
  `ipaton` int(4) unsigned default NULL,
  `daemon` char(1) NOT NULL default '',
  `scansourceid` int(1) NOT NULL default '1',
  KEY `i_dateid` (`startscandate`,`detailsid`,`pluginid`),
  KEY `i_ipfs` (`ipaton`,`port`,`falsepos`,`severity`),
  KEY `i_ipr` (`ipaton`,`port`,`risk`),
  KEY `i_issfp` (`ipaton`,`startscandate`,`port`,`severity`,`falsepos`),
  KEY `i_ipfst` (`ipaton`,`port`,`falsepos`,`severity`,`risk`),
  KEY `i_ipsfst` (`ipaton`,`port`,`startscandate`,`falsepos`,`severity`,`risk`),  KEY `aaa` (`ipaton`,`startscandate`,`severity`,`falsepos`,`risk`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `t2` VALUES
('192.16.207.38',1,0,11936,113866,'2005-03-31 02:30:25','','none',0,3222327078,'',1),
('192.154.234.252',1,0,10150,203152,'2005-04-30 08:46:25','137/udp','medium',137,3231378172,'',1),
('192.16.207.45',1,0,11936,113866,'2005-03-31 06:40:23','','none',0,3222327085,'',1),
('192.18.172.33',1,0,11936,125649,'2005-05-02 03:38:57','','none',0,3222449185,'',1),
('192.225.160.28',1,0,99999,1188524,'2005-04-29 06:30:43','','none',0,3236012060,'',1),
('192.154.234.130',1,0,10150,1236531,'2005-01-11 03:03:02','137/udp','medium',137,3231378050,'',1),
('192.16.207.38',1,4,11835,604730,'2005-03-31 02:30:25','135/tcp','high',135,3473985318,'',1),
('192.16.207.45',1,4,11835,604730,'2005-03-31 06:40:23','135/tcp','high',135,3473985325,'',1),
('192.18.172.33',0,4,11791,301,'2005-05-02 03:38:57','161/udp','high',161,3474107425,'',1);
[11 Jul 2005 19:41] Miguel Solorzano
mysql> update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton
    -> and t1.startscandate = t2.startscandate and t2.severity = 4 and t2.falsepos = 1
    -> group by ipaton);
ERROR 2013 (HY000): Lost connection to MySQL server during query

210711 16:10:38  InnoDB: Started; log sequence number 0 43675
210711 16:10:38 [Note] libexec/mysqld: ready for connections.
Version: '5.0.10-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld got signal 11;
[11 Jul 2005 19:46] Miguel Solorzano
Below backtrace on Windows:

>	mysqld-debug.exe!add_cond_and_fix(Item * * e1=0x0000000c, Item * e2=0x00ffab78)  Line 5110 + 0x3	C++
 	mysqld-debug.exe!add_not_null_conds(JOIN * join=0x00ff9cd8)  Line 5185 + 0x10	C++
 	mysqld-debug.exe!make_join_select(JOIN * join=0x00ff9cd8, SQL_SELECT * select=0x030cd460, Item * cond=0x030cc6e0)  Line 5333 + 0x9	C++
 	mysqld-debug.exe!JOIN::optimize()  Line 736 + 0x1d	C++
 	mysqld-debug.exe!subselect_single_select_engine::exec()  Line 1434 + 0xb	C++
 	mysqld-debug.exe!Item_subselect::exec()  Line 198 + 0x13	C++
 	mysqld-debug.exe!Item_singlerow_subselect::val_int()  Line 463 + 0xe	C++
 	mysqld-debug.exe!Item::save_in_field(Field * field=0x030b2338, int no_conversions=0)  Line 3520 + 0x11	C++
 	mysqld-debug.exe!fill_record(THD * thd=0x030ae640, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0)  Line 3808 + 0x11	C++
 	mysqld-debug.exe!fill_record_n_invoke_before_triggers(THD * thd=0x030ae640, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0, Table_triggers_list * triggers=0x00000000, trg_event_type event=TRG_EVENT_UPDATE)  Line 3849 + 0x15	C++
 	mysqld-debug.exe!mysql_update(THD * thd=0x030ae640, st_table_list * table_list=0x030cb730, List<Item> & fields={...}, List<Item> & values={...}, Item * conds=0x00000000, unsigned int order_num=0, st_order * order=0x00000000, unsigned long limit=4294967295, enum_duplicates handle_duplicates=DUP_ERROR, int ignore=0)  Line 399 + 0x1c	C++
 	mysqld-debug.exe!mysql_execute_command(THD * thd=0x030ae640)  Line 3193 + 0x5a	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x030ae640, char * inBuf=0x030cb630, unsigned int length=175)  Line 5382 + 0x9	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x030ae640, char * packet=0x030c3571, unsigned int packet_length=176)  Line 1674 + 0x1d	C++
 	mysqld-debug.exe!do_command(THD * thd=0x030ae640)  Line 1477 + 0x31	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x030ae640)  Line 1126 + 0x9	C++
 	mysqld-debug.exe!pthread_start(void * param=0x030b2df0)  Line 63 + 0x7	C
 	mysqld-debug.exe!_threadstart(void * ptd=0x030afc60)  Line 173 + 0xd	C
 	kernel32.dll!7c80b50b() 	
 	kernel32.dll!7c8399f3()
[11 Jul 2005 22:39] Miguel Solorzano
Backtrace on Linux:

210711 19:33:45 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.10-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1132444592 (LWP 9773)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1132444592 (LWP 9773)]
0x08239308 in add_cond_and_fix (e1=0xc, e2=0x8dfe950) at sql_select.cc:5110
5110      if (*e1)
(gdb) backtrace full
#0  0x08239308 in add_cond_and_fix (e1=0xc, e2=0x8dfe950) at sql_select.cc:5110
No locals.
#1  0x08224a74 in add_not_null_conds (join=0x8e2dd00) at sql_select.cc:5185
        item = (class Item *) 0x8dfd480
        not_null_item = (class Item_field *) 0x8dfd480
        referred_tab = (JOIN_TAB *) 0x0
        notnull = (class Item_func_isnotnull *) 0x8dfe950
        keypart = 1
        tab = (JOIN_TAB *) 0x8dfe390
        i = 0
        _db_func_ = 0x85fbbad "make_join_select"
        _db_file_ = 0x8224d24 "\203Ä \203}\f"
        _db_level_ = 1132438408
        _db_framep_ = (char **) 0x87824f8
#2  0x08224d3c in make_join_select (join=0x8e2dd00, select=0x8dfe6c8, cond=0x8dfd9d0) at sql_select.cc:5333
        used_tables = 610278397228079676
        thd = (class THD *) 0x8df5158
        _db_func_ = 0x0
        _db_file_ = 0x87824f8 "ø$x\b"
        _db_level_ = 148888124
        _db_framep_ = (char **) 0x0
#3  0x08218b71 in JOIN::optimize (this=0x8e2dd00) at sql_select.cc:736
        _db_func_ = 0xffffffff <Address 0xffffffff out of bounds>
        _db_file_ = 0xffffffff <Address 0xffffffff out of bounds>
        _db_level_ = 148884992
        _db_framep_ = (char **) 0x8dfd974
        sel = (SELECT_LEX *) 0x8dfccb8
#4  0x0819e517 in subselect_single_select_engine::exec (this=0x8dfdbf8) at item_subselect.cc:1434
        unit = (SELECT_LEX_UNIT *) 0x8dfce00
        _db_func_ = 0x8dfd110 ""
        _db_file_ = 0x8df5158 "\b©[\bè\235x\bì\235x\b\030©[\bPéß\bxQß\b"
        _db_level_ = 136373744
        _db_framep_ = (char **) 0x437fa618
        save_where = 0x85fa7b9 "field list"
        save_select = (SELECT_LEX *) 0x8df5384
#5  0x0819afee in Item_subselect::exec (this=0x8dfdb70) at item_subselect.cc:198
        res = 148888296
#6  0x0819bb50 in Item_singlerow_subselect::val_int (this=0x8dfdb70) at item_subselect.cc:463
No locals.
---Type <return> to continue, or q <return> to quit---
#7  0x0814a134 in Item::save_in_field (this=0x8dfdb70, field=0x8e29980, no_conversions=false) at item.cc:3520
        nr = 576460765328551887
        error = 1132439736
#8  0x08210975 in fill_record (thd=0x8df5158, fields=@0x8df5408, values=@0x8df55c4, ignore_errors=false) at sql_base.cc:3801
        rfield = (class Field *) 0x8e29980
        table = (TABLE *) 0x8e29038
        f = {<base_list_iterator> = {list = 0x8df5408, el = 0x8dfdc48, prev = 0x0, current = 0x0}, <No data fields>}
        v = {<base_list_iterator> = {list = 0x8df55c4, el = 0x8dfdc50, prev = 0x0, current = 0x0}, <No data fields>}
        value = (class Item *) 0x8dfdb70
        field = (class Item_field *) 0x8dfcc30
        _db_func_ = 0x437fa894 "HÜß\b"
        _db_file_ = 0x1 <Address 0x1 out of bounds>
        _db_level_ = 0
        _db_framep_ = (char **) 0x0
#9  0x08210a20 in fill_record_n_invoke_before_triggers (thd=0x8df5158, fields=@0x8df5408, values=@0x8df55c4, ignore_errors=false, 
    triggers=0x0, event=TRG_EVENT_UPDATE) at sql_base.cc:3840
No locals.
#10 0x08246b12 in mysql_update (thd=0x8df5158, table_list=0x8dfcac8, fields=@0x8df5408, values=@0x8df55c4, conds=0x0, order_num=0, 
    order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false) at sql_update.cc:397
        using_limit = false
        safe_update = false
        used_key_is_modified = false
        transactional_table = false
        res = 136219145
        error = 0
        used_index = 64
        want_privilege = 0
        table_count = 2
        query_id = 8
        timestamp_query_id = 0
        updated = 0
        found = 0
        old_used_keys = {map = 1}
        table = (TABLE *) 0x8e29038
        select = (SQL_SELECT *) 0x0
        info = {table = 0x8e29038, file = 0x8e29748, forms = 0x437faa40, read_record = 0x828d806 <rr_sequential>, thd = 0x8df5158, 
  select = 0x0, cache_records = 0, ref_length = 6, struct_length = 0, reclength = 0, rec_cache_size = 0, error_offset = 0, index = 0, 
  ref_pos = 0x0, record = 0x8e29838 "õ&Ï\020À\001", rec_buf = 0x0, cache = 0x0, cache_pos = 0x0, cache_end = 0x0, read_positions = 0x0, 
  io_cache = 0x0, print_error = true, ignore_not_found_rows = false}
---Type <return> to continue, or q <return> to quit---
        select_lex = (SELECT_LEX *) 0x8df5384
        _db_func_ = 0x5 <Address 0x5 out of bounds>
        _db_file_ = 0x8dfca5f "group by ipaton)"
        _db_level_ = 140995488
        _db_framep_ = (char **) 0x0
#11 0x081e458c in mysql_execute_command (thd=0x8df5158) at sql_parse.cc:3185
        res = false
        result = 0
        lex = (LEX *) 0x8df5198
        select_lex = (SELECT_LEX *) 0x8df5384
        slave_fake_lock = false
        fake_prev_lock = (MYSQL_LOCK *) 0x0
        first_table = (TABLE_LIST *) 0x8dfcac8
        all_tables = (TABLE_LIST *) 0x8dfcac8
        unit = (SELECT_LEX_UNIT *) 0x8df51a8
        _db_func_ = 0x0
        _db_file_ = 0x0
        _db_level_ = 0
        _db_framep_ = (char **) 0x437fb04c
#12 0x081ea096 in mysql_parse (thd=0x8df5158, 
    inBuf=0x8dfc9c0 "update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton\nand t1.startscandate = t2.startscandate and t2.severity = 4 and t2.falsepos = 1\ngroup by ipaton)", length=175) at sql_parse.cc:5381
        lex = (LEX *) 0x8df5198
        _db_func_ = 0x878c000 "\210\021`\b"
        _db_file_ = 0x81e0bda "\203Ä \203=\024Äy\b"
        _db_level_ = 1132442408
        _db_framep_ = (char **) 0x437fbbb0
#13 0x081e0c3a in dispatch_command (command=COM_QUERY, thd=0x8df5158, 
    packet=0x8e18791 "update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton\nand t1.startscandate = t2.startscandate and t2.severity = 4 and t2.falsepos = 1\ngroup by ipaton)", packet_length=176) at sql_parse.cc:1674
        packet_end = 0x8dfca6f ""
        net = (NET *) 0x8df58a0
        error = false
        _db_func_ = 0x6076f8 <Address 0x6076f8 out of bounds>
        _db_file_ = 0x0
        _db_level_ = 136924480
        _db_framep_ = (char **) 0x437fb348
#14 0x081e0521 in do_command (thd=0x8df5158) at sql_parse.cc:1477
        packet = 0x8e18790 "\003update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton\nand t1.startscandate = t2.star---Type <return> to continue, or q <return> to quit---
tscandate and t2.severity = 4 and t2.falsepos = 1\ngroup by ipaton)"
        old_timeout = 30
        packet_length = 176
        net = (NET *) 0x8df58a0
        command = COM_QUERY
        _db_func_ = 0x8df619c "ÿÿÿÿ"
        _db_file_ = 0x81ba1e7 "\203Ä\020ÉÃU\211å\203ì\b\203ì\fÿu\bè\217"
        _db_level_ = 1132442472
        _db_framep_ = (char **) 0x1010
#15 0x081df72f in handle_one_connection (arg=0x8df5158) at sql_parse.cc:1126
        error = 0
        net = (NET *) 0x8df58a0
        thd = (class THD *) 0x8df5158
        launch_time = 0
        set = {__val = {0 <repeats 32 times>}}
#16 0x4017daa7 in start_thread () from /lib/tls/libpthread.so.0
No symbol table info available.
#17 0x402aec2e in clone () from /lib/tls/libc.so.6
No symbol table info available.
(gdb)
[15 Jul 2005 17:43] Igor Babaev
This  crash can be demonstrated on the latest tree for 4.1 as well.
The problem is due to the new code that adds NOT NULL predicates
It contains the following statements:
          JOIN_TAB *referred_tab= not_null_item->field->table->reginfo.join_tab;
          ...
          add_cond_and_fix(&referred_tab->select_cond, notnull);

The join_tab field is equal to 0 for the t1 table in the reported update statement.
That's why dereferencing of referred_tab causes a crash for the update. 

The code adding NOT NULL predicates had another problem fixed by the patch for
bug #11482. Both problems are related, but formally they are caused by different 
bugs.
[15 Jul 2005 17:57] Evgeny Potemkin
Add_not_null_conds() were tried to optimize query by adding NOT NULL constraint to t1.ipaton. For doing this it adds Item_not_null to not_null_item->field->table->reginfo.join_tab->select_cond. But for UPDATE that join_tab is null and try to get acces by it caused the crash.
[15 Jul 2005 20:33] 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/27194
[15 Jul 2005 22:01] Evgeny Potemkin
Fixed in 4.1.14, cset 1.2359.2.1
[17 Jul 2005 0:33] Paul Dubois
Noted in 4.1.14 changelog.