Bug #46184 Crash, SELECT ... FROM derived table procedure analyze
Submitted: 14 Jul 2009 20:21 Modified: 18 Dec 2009 13:13
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0,5.1,5.4 OS:Any
Assigned to: Sergey Glukhov
Tags: crash
Triage: Triaged: D1 (Critical)

[14 Jul 2009 20:21] Matthias Leich
Description:
CREATE TEMPORARY TABLE `t1` (`char_utf8_1` CHAR (1) CHARACTER SET utf8,
`c_latin1_1` CHAR (1)
             CHARACTER SET latin1,
`e_utf8`     ENUM ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o',
                   'p','q','r','s','t','u','v','w','x','y','z')
             CHARACTER SET utf8,
`c_latin1_2` CHAR (1) CHARACTER SET latin1,
`s_utf8_1`   SET  ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o',
                   'p','q','r','s','t','u','v','w','x','y','z')
             CHARACTER SET utf8,
`e_latin1_1` ENUM ('a','b','c','d','e','f','g','h','i','j','k','l','m','n',
                   'o','p','q','r','s','t','u','v','w','x','y','z')
             CHARACTER SET latin1,
`s_utf8_2`   SET  ('a','b','c','d','e','f','g','h','i','j','k','l','m','n',
                   'o','p','q','r','s','t','u','v','w','x','y','z')
             CHARACTER SET utf8,
`c_utf8_2`   CHAR (1) CHARACTER SET utf8,
`s_latin1_1` SET  ('a','b','c','d','e','f','g','h','i','j','k','l','m','n',
                   'o','p','q','r','s','t','u','v','w','x','y','z')
             CHARACTER SET latin1,
`e_latin1_2` ENUM ('a','b','c','d','e','f','g','h','i','j','k','l','m','n',
                   'o','p','q','r','s','t','u','v','w','x','y','z')
             CHARACTER SET latin1,
`s_latin1_2` SET  ('a','b','c','d','e','f','g','h','i','j','k','l','m','n',
                   'o','p','q','r','s','t','u','v','w','x','y','z')
             CHARACTER SET latin1);

INSERT IGNORE INTO t1 VALUES  ('f', NULL, 'p', 'h', 'k', NULL, 'b', 'b', 'u', 'k', 'd');
COMMIT;
# THIS STATEMENT CRASHES THE SERVER
CREATE TEMPORARY TABLE my_table AS
SELECT * FROM (SELECT * FROM `t1`) AS my_tab1 PROCEDURE ANALYSE(10, 2000);

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.ml101                               [ fail ]
        Test ended at 2009-07-14 21:41:50

CURRENT_TEST: main.ml101
mysqltest: At line 30: query 'CREATE TEMPORARY TABLE my_table AS
SELECT * FROM (SELECT * FROM `t1`) AS my_tab1 PROCEDURE ANALYSE(10, 2000)' failed: 2013: Lost connection to MySQL server during query

My environment:
---------------
- Intel Core2Duo (64 Bit)
- Linux OpenSuSE 11.0 (64 Bit)
- MySQL compile from source with compile-pentium64-debug-max
- mysql-azalea (5.4) 2009-07-12
  mysql-5.1-bugteam 2009-07-14
  mysql-5.0-bugteam 2009-07-14

Observations when trying to shrink the test:
--------------------------------------------
- no crash if the derived table "FROM (SELECT ...)"
  is replaced by a simple table "FROM t1"
- many attempts to remove the CHARACTER SETs latin1 and
  utf8 or columns removed also the crash
- TEMPORARY is not required
  I avoid via temporary the cleanup at test end.
- PROCEDURE ANALYSE(10, 2000) is needed for the crash

How to repeat:
See above

Suggested fix:
http://dev.mysql.com/doc/refman/6.0/en/procedure-analyse.html
says about "procedure analyse":
...
It examines the result from a query and returns
an analysis of the results that suggests optimal
data types for each column.

In case of CREATE TABLE ... PROCEDURE the suggestions
about optimal data types are somehow too late because
we already have the table with more or less optimal
data types.

At least till now I do not see a value of a
INSERT INTO ... SELECT ... PROCEDURE ANALYSE(10, 2000)'
CREATE TABLE ... AS SELECT ... PROCEDURE .

I propose to disallow PROCEDURE ANALYSE in any
statement which does not begin with "SELECT".
[14 Jul 2009 20:22] Matthias Leich
Backtrace

Attachment: backtrace.txt (text/plain), 5.86 KiB.

[14 Jul 2009 20:24] Matthias Leich
Test script

Attachment: ml101.test (application/octet-stream, text), 1.63 KiB.

[15 Jul 2009 5:42] Shane Bester
nicer looking testcase:

drop table if exists `t1`;
create table `t1`(`a` int,`b` int,`c` int,`d` int,`e` int,`f` int,`g` int,`h` int,`i` int,`j` int,`k` int);
insert into `t1` values ();
select * from (select * from `t1`)`d` procedure analyse();
[15 Jul 2009 9:59] Matthias Leich

 
[15 Jul 2009 14:14] Philip Stoev
Pasting the backtrace as a comment, so that it is visible when searching:

#2  0x00000000006ecf81 in handle_segfault (sig=11) at mysqld.cc:2718
#3  <signal handler called>
#4  0x000000000074bf87 in fill_record (thd=0x16c9b48, ptr=0x1641920, values=@0x152f718, ignore_errors=true) at sql_base.cc:7475
#5  0x00000000008c9117 in select_union::send_data (this=0x16cd080, values=@0x152f718) at sql_union.cc:60
#6  0x000000000089d470 in analyse::end_of_records (this=0x152f680) at sql_analyse.cc:755
#7  0x000000000076f455 in end_send (join=0x16a32d0, join_tab=0x0, end_of_records=true) at sql_select.cc:17301
#8  0x0000000000788761 in do_select (join=0x16a32d0, fields=0x1606418, table=0x0, procedure=0x152f680) at sql_select.cc:15797
#9  0x00000000007a2fc8 in JOIN::exec (this=0x16a32d0) at sql_select.cc:2900
#10 0x000000000079d99c in mysql_select (thd=0x16c9b48, rref_pointer_array=0x16064f8, tables=0x1606b80, wild_num=0, fields=@0x1606418, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0x16cd080,
    unit=0x16065e0, select_lex=0x1606310) at sql_select.cc:3091
#11 0x00000000008c966f in mysql_derived_filling (thd=0x16c9b48, lex=0x16cb418, orig_table_list=0x16ccb18) at sql_derived.cc:295
#12 0x00000000008c9410 in mysql_handle_derived (lex=0x16cb418, processor=0x8c94a1 <mysql_derived_filling(THD*, LEX*, TABLE_LIST*)>) at sql_derived.cc:56
#13 0x000000000075a95c in open_and_lock_tables_derived (thd=0x16c9b48, tables=0x16ccb18, derived=true, flags=0) at sql_base.cc:4242
#14 0x000000000070a382 in open_and_lock_tables (thd=0x16c9b48, tables=0x16ccb18) at ../../sql/mysql_priv.h:1519
#15 0x00000000006ffe49 in mysql_execute_command (thd=0x16c9b48) at sql_parse.cc:2636
#16 0x000000000070741e in mysql_parse (thd=0x16c9b48, inBuf=0x16054c0 "CREATE TEMPORARY TABLE my_table AS\nSELECT * FROM (SELECT * FROM `t1`) AS my_tab1 PROCEDURE ANALYSE(10, 2000)", length=108, found_semicolon=0x40d83f30) at sql_parse.cc:5942
#17 0x000000000070803c in dispatch_command (command=COM_QUERY, thd=0x16c9b48, packet=0x1601469 "CREATE TEMPORARY TABLE my_table AS\nSELECT * FROM (SELECT * FROM `t1`) AS my_tab1 PROCEDURE ANALYSE(10, 2000)", packet_length=108) at sql_parse.cc:1061
#18 0x000000000070951a in do_command (thd=0x16c9b48) at sql_parse.cc:743
#19 0x00000000006f6893 in handle_one_connection (arg=0x16c9b48) at sql_connect.cc:1158
#20 0x00007f5ac4a60040 in start_thread () from /lib64/libpthread.so.0
#21 0x00007f5ac3a0108d in clone () from /lib64/libc.so.6
[25 Aug 2009 13:04] 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/81504

2797 Sergey Glukhov	2009-08-25
      Bug#46184 Crash, SELECT ... FROM derived table procedure analyze
      The crash happens because select_union object is used as result set
      for queries which have derived tables.
      select_union use temporary table as data storage and if
      fields count exceeds 10(count of values for procedure ANALYSE())
      then we get a crash on fill_record() function.
      The fix is to disallow use of procedure ANALYSE() if derived tables exist.
     @ mysql-test/r/analyse.result
        test result
     @ mysql-test/r/subselect.result
        result fix
     @ mysql-test/t/analyse.test
        test case
     @ mysql-test/t/subselect.test
        test fix
     @ sql/sql_yacc.yy
        The crash happens because select_union object is used as result set
        for queries which have derived tables.
        select_union use temporary table as data storage and if
        fields count exceeds 10(count of values for procedure ANALYSE())
        then we get a crash on fill_record() function.
        The fix is to disallow use of procedure ANALYSE() if derived tables exist.
[27 Aug 2009 11:20] 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/81709

2796 Sergey Glukhov	2009-08-27
      Bug#46184 Crash, SELECT ... FROM derived table procedure analyze
      The crash happens because select_union object is used as result set
      for queries which have derived tables.
      select_union use temporary table as data storage and if
      fields count exceeds 10(count of values for procedure ANALYSE())
      then we get a crash on fill_record() function.
     @ mysql-test/r/analyse.result
        test result
     @ mysql-test/r/subselect.result
        result fix
     @ mysql-test/t/analyse.test
        test case
     @ mysql-test/t/subselect.test
        test fix
     @ sql/sql_yacc.yy
        The crash happens because select_union object is used as result set
        for queries which have derived tables.
        select_union use temporary table as data storage and if
        fields count exceeds 10(count of values for procedure ANALYSE())
        then we get a crash on fill_record() function.
[2 Sep 2009 10:25] Bugs System
Pushed into 5.0.86 (revid:joro@sun.com-20090902102337-n5rw8227wwp5cpx8) (version source revid:sergey.glukhov@sun.com-20090827102219-sgjz0v5t1rfccs14) (merge vers: 5.0.86) (pib:11)
[2 Sep 2009 16:42] Bugs System
Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:sergey.glukhov@sun.com-20090827105925-j56pdghdzb8nlw3p) (merge vers: 5.1.39) (pib:11)
[14 Sep 2009 16:06] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[18 Sep 2009 19:38] Paul Dubois
Noted in 5.0.86, 5.1.39, 5.4.4 changelogs.

A query containing a subquery in the FROM clause and PROCEDURE
ANALYSE() caused a server crash.
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[2 Oct 2009 1:15] Paul Dubois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[12 Oct 2009 14:12] Paul Dubois
Noted in 5.0.84sp1 changelog.
[14 Oct 2009 8:20] Bugs System
Pushed into 5.0.88 (revid:build@mysql.com-20091014081604-yhwy9zh6fq8kcurj) (version source revid:build@mysql.com-20091014081604-yhwy9zh6fq8kcurj) (merge vers: 5.0.88) (pib:13)
[14 Oct 2009 14:39] Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (version source revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (merge vers: 5.1.41) (pib:13)
[14 Oct 2009 16:51] Paul Dubois
Already noted in earlier changelogs.
[22 Oct 2009 6:37] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:09] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091019131937-nchb8tjk88jpfjav) (merge vers: 5.5.0-beta) (pib:13)
[22 Oct 2009 19:16] Paul Dubois
Noted in 5.5.0, 6.0.14 changelogs.
[18 Dec 2009 10:39] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:55] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:09] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:24] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[18 Dec 2009 13:13] MC Brown
Already documented in 5.1.41