MySQL Bugs: #2479: dependant subquery with limit crash

Bug #2479	dependant subquery with limit crash
Submitted:	22 Jan 2004 5:05	Modified:	28 Jan 2004 15:20
Reporter:	fra rio	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySQL 4.1.1-alpha-log	OS:	Linux (Linux)
Assigned to:	Jani Tolonen	CPU Architecture:	Any

Description:
crash with dependant subquery and limit
(may be related to bug #1992 ?)

- To recreate the crash tab_02 need to have at least two records

SELECT cns.max_anno_dep = (
	SELECT s.anno_dep
	FROM tab_01 AS s 
	WHERE s.id_cns = cns.id 
	ORDER BY s.anno_dep DESC
	LIMIT 1
	) AS PIPPO
FROM  tab_02 AS cns
;

How to repeat:
DROP TABLE IF EXISTS tab_01;
CREATE TABLE tab_01 (
  id int(11) NOT NULL auto_increment,
  ts timestamp NOT NULL,
  id_cns tinyint(3) unsigned NOT NULL default '0',
  id_desc_nota int(11) NOT NULL default '1',
  id_publ_uff int(11) NOT NULL default '0',
  tipo enum('','UNO','DUE') NOT NULL default '',
  f_aggiunte set('TRE','TRETRE','QUATTRO','CINQUE','SEI','SETTE') NOT NULL default '',
  anno_dep smallint(4) unsigned zerofill NOT NULL default '0000',
  data_dep smallint(4) unsigned zerofill NOT NULL default '0000',
  particolare mediumint(8) unsigned NOT NULL default '0',
  generale mediumint(8) unsigned NOT NULL default '0',
  bis tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY idx_cns_gen_anno (anno_dep,id_cns,generale,particolare),
  UNIQUE KEY idx_cns_par_anno (id_cns,anno_dep,tipo,particolare,bis)
) TYPE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1;

INSERT INTO tab_01 (id, ts, id_cns, id_desc_nota, id_publ_uff, tipo, f_aggiunte, anno_dep, data_dep, particolare, generale, bis) VALUES
(NULL, NULL,  16, 29, 622, 'UNO', '', 1987, 1218, 2048, 9681, 0),
(NULL, NULL,  50, 23, 1717, 'UNO', '', 1987, 1126, 1536, 13987, 0),
(NULL, NULL,  16, 123, 123, 'UNO', '', 1987, 1221, 2432, 14594, 0),
(NULL, NULL,  16, 124, 124, 'UNO', '', 1987, 1201, 1792, 13422, 0),
(NULL, NULL,  16, 125, 125, 'UNO', '', 1987, 0723, 1025, 10240, 0),
(NULL, NULL,  16, 126, 126, 'UNO', '', 1987, 1204, 1026, 7089, 0);
  
DROP TABLE IF EXISTS tab_02;
CREATE TABLE tab_02 (
  id tinyint(3) unsigned NOT NULL auto_increment,
  descr varchar(40) NOT NULL default '',
  f_servizi set('UNO','DUE') NOT NULL default '',
  data_uno_min int(8) unsigned NOT NULL default '0',
  data_due_min int(8) unsigned NOT NULL default '0',
  max_anno_dep smallint(6) unsigned NOT NULL default '0',
  data_agg int(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (id)
) 
;

INSERT INTO tab_02 (id, descr, f_servizi, data_uno_min, data_due_min, max_anno_dep, data_agg) VALUES 
(16, 'C_UNO', 'UNO,DUE', 19000000, 30000000, 1987, 0),
(50, 'C_TRE', 'UNO', 19000000, 30000000, 1990, 0);
    

##################################################################################
#2013 - Lost connection to MySQL server during query
##################################################################################
UPDATE tab_02 AS cns
SET cns.max_anno_dep = (
	SELECT s.anno_dep
	FROM tab_01 AS s 
	WHERE s.id_cns = cns.id 
	ORDER BY s.anno_dep DESC
	LIMIT 1
	)
;

##########################
#OK BUT VERY SLOW in real world
##########################
UPDATE tab_02 AS cns
SET cns.max_anno_dep = (
	# SELECT s.anno_dep
	SELECT MAX(s.anno_dep) 
	FROM tab_01 AS s 
	WHERE s.id_cns = cns.id 
	# ORDER BY s.anno_dep DESC
	# LIMIT 1
	)
;

Backtrace follows

(gdb) bt
#0  0x085b8367 in ?? ()
#1  0x0844dca2 in vtable for FT_SELECT ()
#2  0x0000014d in ?? ()
#3  0x40152870 in ?? ()
#4  0x40152874 in ?? ()
#5  0x40152878 in ?? ()
#6  0x4015287c in ?? ()
#7  0x40152928 in ?? ()
#8  0x00000010 in ?? ()
#9  0x081e6a46 in filesort(THD*, st_table*, st_sort_field*, unsigned, SQL_SELECT*, unsigned long, unsigned long*) (thd=0x85ac3d8, table=0x85abb80, sortorder=0x85a79a8, s_length=1, select=0x85a7728, max_rows=140165096, examined_rows=0x85abfe8) at filesort.cc:186
#10 0x081aca67 in create_sort_index (thd=0x85abfe8, join=0x85a7288, order=0x85a6420, filesort_limit=140165096, select_limit=140165096) at sql_select.cc:7200
#11 0x0819f2f7 in JOIN::exec() (this=0x85a65c0) at sql_select.cc:1478
#12 0x08136b32 in subselect_single_select_engine::exec() (this=0x85a64c0) at item_subselect.cc:1044
#13 0x08133783 in Item_subselect::exec() (this=0x85a6450) at item_subselect.cc:142
#14 0x0813419c in Item_singlerow_subselect::val_int() (this=0x85a6450) at item_subselect.cc:366
#15 0x080ea832 in Item::save_in_field(Field*, bool) (this=0x85a6450, field=0x85b29d0, no_conversions=false) at item.cc:1202
#16 0x0819760a in fill_record(List<Item>&, List<Item>&, bool) (fields=@0x85abfe8, values=@0x85abfe8, ignore_errors=false) at sql_base.cc:2319
#17 0x081bb808 in mysql_update(THD*, st_table_list*, List<Item>&, List<Item>&, Item*, unsigned, st_order*, unsigned long, enum_duplicates) (thd=0x85ac3d8, table_list=0x85a6548, fields=@0x85ac530, values=@0x85ac6d0, conds=0x0, order_num=140165096, order=0x0, limit=4294967294, handle_duplicates=DUP_ERROR) at sql_update.cc:308
#18 0x08175cd5 in mysql_execute_command(THD*) (thd=0x85ac3d8) at sql_parse.cc:2496
#19 0x08179a29 in mysql_parse(THD*, char*, unsigned) (thd=0x85ac3d8, inBuf=0x85a5dd0 "UPDATE tab_02 AS cns\nSET cns.max_anno_dep = (\nSELECT s.anno_dep\nFROM tab_01 AS s \nWHERE s.id_cns = cns.id \nORDER BY s.anno_dep DESC\nLIMIT 1\n)", length=140166120) at sql_parse.cc:3895
#20 0x0817290b in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x85ac3d8, packet=0x85b30c9 "UPDATE tab_02 AS cns\nSET cns.max_anno_dep = (\nSELECT s.anno_dep\nFROM tab_01 AS s \nWHERE s.id_cns = cns.id \nORDER BY s.anno_dep DESC\nLIMIT 1\n)", packet_length=142) at sql_parse.cc:1393
#21 0x08172229 in do_command(THD*) (thd=0x85ac3d8) at sql_parse.cc:1223
#22 0x081716e2 in handle_one_connection (arg=0x85abfe8) at sql_parse.cc:989
#23 0x45255484 in start_thread () from /lib/tls/libpthread.so.0
#24 0x450a9147 in clone () from /lib/tls/libc.so.6

The problem was that memory was free'd, but the same area was referenced
later on. The freeing itself was also unneccessary, because join_free() /
JOIN::cleanup took care of it afterwords in any case.

The fix will appear in the next 4.1.2 release.

Patch follows:

*** sql/sql_select.cc.org       2004-01-29 00:36:41.000000000 +0200
--- sql/sql_select.cc   2004-01-29 01:12:43.000000000 +0200
***************
*** 7200,7206 ****
    table->sort.found_records=filesort(thd, table,sortorder, length,
                                       select, filesort_limit, &examined_rows);
    tab->records=table->sort.found_records;             // For SQL_CALC_ROWS
-   delete select;                              // filesort did select
    tab->select=0;
    tab->select_cond=0;
    tab->type=JT_ALL;                           // Read with normal read_record
--- 7200,7205 ----

Regards,
Jani