Bug #48293 crash with procedure analyse, view with > 10 columns, having clause...
Submitted: 25 Oct 2009 20:00 Modified: 18 Jun 2010 12:56
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.86, 5.1.40 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: PROCEDURE ANALYSE

[25 Oct 2009 20:00] Shane Bester 
Description:
This may be a followup to bug #46184 whose testcase doesn't crash on 5.1.40, but this testcase does.

5.1.40 stack trace:

mysqld-debug.exe!fill_record()[sql_base.cc:8226]
mysqld-debug.exe!select_union::send_data()[sql_union.cc:61]
mysqld-debug.exe!analyse::end_of_records()[sql_analyse.cc:800]
mysqld-debug.exe!end_send()[sql_select.cc:12038]
mysqld-debug.exe!do_select()[sql_select.cc:10866]
mysqld-debug.exe!JOIN::exec()[sql_select.cc:2205]
mysqld-debug.exe!mysql_select()[sql_select.cc:2396]
mysqld-debug.exe!mysql_derived_filling()[sql_derived.cc:294]
mysqld-debug.exe!mysql_handle_derived()[sql_derived.cc:56]
mysqld-debug.exe!open_and_lock_tables_derived()[sql_base.cc:5001]
mysqld-debug.exe!open_and_lock_tables()[mysql_priv.h:1563]
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:5008]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2238]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5967]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1226]
mysqld-debug.exe!do_command()[sql_parse.cc:865]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:295]
mysqld-debug.exe!_threadstart()[thread.c:277]
kernel32.dll!BaseThreadStart()

How to repeat:
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 ();
create or replace view `v1` as select * from `t1` having `a`>1;
select * from `v1` procedure analyse();
[25 Oct 2009 20:20] MySQL Verification Team 
Thank you for the bug report. Verified as described.

miguel@laras:~/dbs$ 5.1/libexec/mysqld
091025 18:17:41 [Note] Plugin 'FEDERATED' is disabled.
091025 18:17:42 [Note] Plugin 'ndbcluster' is disabled.
091025 18:17:42  InnoDB: Started; log sequence number 0 44233
091025 18:17:42 [Note] Event Scheduler: Loaded 0 events
091025 18:17:42 [Note] 5.1/libexec/mysqld: ready for connections.
Version: '5.1.41-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
091025 18:18:19 - mysqld got signal 11 ;
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=8384512
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 = 338308 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x251a778
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...
stack_bottom = 0x7f8e526750e8 thread_stack 0x40000
5.1/libexec/mysqld(my_print_stacktrace+0x35)[0xb673bb]
5.1/libexec/mysqld(handle_segfault+0x288)[0x6c3858]
/lib/libpthread.so.0[0x7f8e56a2b190]
5.1/libexec/mysqld(_Z11fill_recordP3THDPP5FieldR4ListI4ItemEb+0xeb)[0x731ae3]
5.1/libexec/mysqld(_ZN12select_union9send_dataER4ListI4ItemE+0x7b)[0x8a808f]
5.1/libexec/mysqld(_ZN7analyse14end_of_recordsEv+0xaa8)[0x87ba40]
5.1/libexec/mysqld[0x764108]
5.1/libexec/mysqld[0x7615e2]
5.1/libexec/mysqld(_ZN4JOIN4execEv+0x263e)[0x74a632]
5.1/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x343)[0x74adcc]
5.1/libexec/mysqld(_Z21mysql_derived_fillingP3THDP6st_lexP10TABLE_LIST+0x1db)[0x8ab220]
5.1/libexec/mysqld(_Z20mysql_handle_derivedP6st_lexPFbP3THDS0_P10TABLE_LISTE+0x8a)[0x8aaade]
5.1/libexec/mysqld(_Z28open_and_lock_tables_derivedP3THDP10TABLE_LISTb+0x1d3)[0x72a282]
5.1/libexec/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LIST+0x28)[0x6e649c]
5.1/libexec/mysqld[0x6df05b]
5.1/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x8f4)[0x6d628e]
5.1/libexec/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x29c)[0x6e17f3]
5.1/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xce9)[0x6d3afe]
5.1/libexec/mysqld(_Z10do_commandP3THD+0x27e)[0x6d2b1e]
5.1/libexec/mysqld(handle_one_connection+0x14e)[0x6d0e31]
/lib/libpthread.so.0[0x7f8e56a22a04]
/lib/libc.so.6(clone+0x6d)[0x7f8e55eb17bd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x25773b8 = select * from `v1` procedure analyse()
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.
miguel@laras:~/dbs$
[25 Oct 2009 20:23] Peter Laursen 
no crash if VIEW is defined using WHERE:

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 t1;
CREATE OR REPLACE VIEW `v1` AS SELECT * FROM `t1` WHERE `a`>1;
SELECT * FROM `v1` PROCEDURE analyse(); --valid result and no crash
[28 Oct 2009 15:53] 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/88498

2828 Georgi Kodinov	2009-10-28
      Bug #48293: crash with procedure analyse, view with > 10 columns,
      having clause...
      
      The fix for bug 46184 was not very complete. It was not covering
      views using temporary tables and multiple tables in a FROM clause.
      Fixed by reverting the fix for 46184 and making a more general
      check that is checking at the right execution stage and for all
      of the non-supported cases.
[28 Oct 2009 16:25] 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/88510

2828 Georgi Kodinov	2009-10-28
      Bug #48293: crash with procedure analyse, view with > 10 columns,
      having clause...
      
      The fix for bug 46184 was not very complete. It was not covering
      views using temporary tables and multiple tables in a FROM clause.
      Fixed by reverting the fix for 46184 and making a more general
      check that is checking at the right execution stage and for all
      of the non-supported cases.
[30 Oct 2009 5:40] MySQL Verification Team 
Joro, please check if this patch also fixes similar crash:

drop table if exists `t1`,`t2`;
create table `t1`(`a` int);
insert into `t1` values (1),(2);
create table `t2` select 1
from `t1`, `t1` `t3`
group by `t3`.`a`
procedure analyse();
[30 Oct 2009 9:16] 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/88702

2828 Georgi Kodinov	2009-10-30
      Bug #48293: crash with procedure analyse, view with > 10 columns,
      having clause...
      
      The fix for bug 46184 was not very complete. It was not covering
      views using temporary tables and multiple tables in a FROM clause.
      Fixed by reverting the fix for 46184 and making a more general
      check that is checking at the right execution stage and for all
      of the non-supported cases.
      Now PROCEDURE ANALYZE on non-top level SELECT is also forbidden.
      
      Merged the analyze.test and analyse.test into a single test file.
[30 Oct 2009 9:41] 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/88709

2828 Georgi Kodinov	2009-10-30
      Bug #48293: crash with procedure analyse, view with > 10 columns,
      having clause...
      
      The fix for bug 46184 was not very complete. It was not covering
      views using temporary tables and multiple tables in a FROM clause.
      Fixed by reverting the fix for 46184 and making a more general
      check that is checking at the right execution stage and for all
      of the non-supported cases.
      Now PROCEDURE ANALYZE on non-top level SELECT is also forbidden.
      Updated the analyse.test and subselect.test accordingly.
[30 Oct 2009 9:45] 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/88710

2830 Georgi Kodinov	2009-10-30
      Bug #48293: crash with procedure analyse, view with > 10 columns,
      having clause...
      
      The fix for bug 46184 was not very complete. It was not covering
      views using temporary tables and multiple tables in a FROM clause.
      Fixed by reverting the fix for 46184 and making a more general
      check that is checking at the right execution stage and for all
      of the non-supported cases.
      Now PROCEDURE ANALYZE on non-top level SELECT is also forbidden.
      Updated the analyse.test and subselect.test accordingly.
[4 Nov 2009 9:17] Bugs System 
Pushed into 5.0.88 (revid:joro@sun.com-20091104091355-hpz6dwgkrfmokj3k) (version source revid:joro@sun.com-20091030094044-quadg0bwjy7cwqzw) (merge vers: 5.0.88) (pib:13)
[4 Nov 2009 9:24] Bugs System 
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)
[11 Nov 2009 6:50] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 2009 6:57] Bugs System 
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105092355-jzukkw6wnd7hmgrj) (merge vers: 5.5.0-beta) (pib:13)
[17 Nov 2009 23:34] Paul DuBois 
Noted in 5.0.88, 5.1.41, 5.5.0, 6.0.14 changelogs.

A query containing a view using temporary tables and multiple tables
in the FROM clause and PROCEDURE ANALYSE() caused a server crash.

As a result of this bug fix, PROCEDURE ANALYSE() is legal only in a
top-level SELECT.
[7 Dec 2009 16:46] Paul DuBois 
Noted in 5.1.40sp1 changelog.
[8 Dec 2009 9:30] Bugs System 
Pushed into 5.1.43 (revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (version source revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (merge vers: 5.1.43) (pib:13)
[16 Dec 2009 8:35] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091215065750-5m04ogppd5l0pol5) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:42] Bugs System 
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alik@sun.com-20091211070127-kl8uvlrv9cr11kva) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:48] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 10:26] 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:42] 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 10:58] 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:12] 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)
[17 Feb 2010 16:27] Paul DuBois 
Noted in 5.0.87sp1 changelog.
[20 Feb 2010 17:11] Bugs System 
Pushed into 5.0.91 (revid:build@mysql.com-20100220170835-5kr6ztsg25va7qzz) (version source revid:build@mysql.com-20100220170835-5kr6ztsg25va7qzz) (merge vers: 5.0.91) (pib:16)
[1 Mar 2010 8:42] Bugs System 
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joro@sun.com-20100226131646-kpvzk740hxbtaexn) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:33] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:38] Bugs System 
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alik@sun.com-20100301090215-63o2w2y16go8n53p) (merge vers: 5.5.3-m2) (pib:16)
[2 Mar 2010 14:44] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100301094536-2zc4uqyy3os8san7) (pib:16)
[12 Mar 2010 14:06] Bugs System 
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:22] Bugs System 
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:36] Bugs System 
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[17 Jun 2010 12:06] Bugs System 
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:52] Bugs System 
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:34] Bugs System 
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)