Bug #31035 select from function, group by result crasher
Submitted: 14 Sep 2007 20:47 Modified: 23 Oct 2007 0:26
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:5.0.50, 5.1.23 OS:Any
Assigned to: Alexander Nozdrin CPU Architecture:Any
Tags: bfsm_2007_10_18, regression

[14 Sep 2007 20:47] Shane Bester
Description:
thd=0x8af2648
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...
Cannot determine thread, fp=0x4550e044, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x818b1e5 handle_segfault + 417
0x8169f16 Field::offset() + 6
0x81d3602 create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char*) + 2866
0x81ce869 JOIN::optimize() + 3361
0x81d16f6 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select + 134
0x81ccfdc handle_select(THD*, st_lex*, select_result*, unsigned long) + 260
0x81a03d2 mysql_execute_command(THD*) + 678
0x81a6c3d mysql_parse(THD*, char const*, unsigned int, char const**) + 241
0x819ed46 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1198
0x819e85c do_command(THD*) + 144
0x819dec6 handle_one_connection + 646
0x40045aa7 _end + 933533303
0x40176c2e _end + 934782974
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8b11180 = select f1() as a from t1 group by a
thd->thread_id=2
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

How to repeat:
drop table if exists t1;
create table t1(id int)engine=myisam;
insert into t1 values (),();
drop function if exists f1;
create function f1() returns int return 1;
select f1() as a from t1 group by a;

Suggested fix:
.
[14 Sep 2007 22:16] Konstantin Osipov
Workaround: use DETERMINISTIC clause
[14 Sep 2007 22:18] Konstantin Osipov
Quick fix:
===== item_func.cc 1.355 vs edited =====
--- 1.355/sql/item_func.cc	2007-08-03 20:59:12 +04:00
+++ edited/item_func.cc	2007-09-15 02:14:51 +04:00
@@ -5583,15 +5583,19 @@ Item_func_sp::fix_fields(THD *thd, Item 
     
 #endif /* ! NO_EMBEDDED_ACCESS_CHECKS */
   }
+#if 0
   if (!m_sp->m_chistics->detistic)
    used_tables_cache |= RAND_TABLE_BIT;
+#endif
   DBUG_RETURN(res);
 }
 
 
 void Item_func_sp::update_used_tables()
 {
+#if 0
   Item_func::update_used_tables();
   if (!m_sp->m_chistics->detistic)
    used_tables_cache |= RAND_TABLE_BIT;
+#endif
 }
[14 Sep 2007 23:09] Jeffrey Pugh
Marking as a showstopper for 5.0.50 based on my conversation with Kostja
[14 Sep 2007 23:13] Jeffrey Pugh
As above, introduced by http://bugs.mysql.com/29338; thus introduced in 5.0.48, 5.1.21 back-ported to 5.0.44sp1.

Plan to fix in 5.1.23 and 5.0.50.
[15 Sep 2007 5:07] Igor Babaev
This crash has nothing to do with the patch for bug #29338.
I reverted this patch and still had the same kind of crash in the same spot.
Reverting the patch partly as Konstantin did does not have any sense as in this case the function is considered as deterministic and no field is created for its result in the temporary table. Meanwhile the crash occurs when this field is created (defined).

So this is absolutely a new bug and I remove the regression tag from it.
[15 Sep 2007 7:35] Konstantin Osipov
Igor is correct that the bug was originally introduced not by 29338 but by 27354, which 29338 supersedes.
[26 Sep 2007 19:26] Axel Schwenke
There is duplicate bug #31191

According to the reporter the workaround with DETERMINISTIC does not work for 5.0.45-community-nt (where bug #31191 was spotted first).
[27 Sep 2007 21:25] Jeffrey Pugh
Not a regression; removing Showstopper. Should still be fixed for 5.0.50, and next 5.1.
[28 Sep 2007 9:34] Hartmut Holzgraefe
Also reported as bug #31226, and as it only started to happen with 5.0.42 i'd like to question the "not a regression" claim ... as bug #27354 mentioned above was fixed in 5.0.42 it indeed looks like a likely culprit ...
[4 Oct 2007 13:19] 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/34897

ChangeSet@1.2526, 2007-10-04 17:19:14+04:00, anozdrin@station. +3 -0
  Fix for BUG#31035: select from function, group by result crasher.
  
  This actually, fix for the patch for bug-27354. The problem with
  the patch was that Item_func_sp::used_tables() was updated, but
  Item_func_sp::const_item() was not. So, for Item_func_sp, we had
  the following inconsistency:
    - used_tables() returned RAND_TABLE, which means that the item
      can produce "random" results;
    - but const_item() returned TRUE, which means that the item is
      a constant one.
  
  The fix is to change Item_func_sp::const_item() behaviour: it must
  return TRUE (an item is a constant one) only if a stored function
  is deterministic and each of its arguments (if any) is a constant
  item.
[4 Oct 2007 13:21] Alexander Nozdrin
Pushed into 5.0-runtime.
[4 Oct 2007 14:09] 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/34899

ChangeSet@1.2533, 2007-10-04 16:08:13+02:00, df@pippilotta.erinye.com +3 -0
  apply patch for bug#31035 to 5.0.50 release clone
[19 Oct 2007 18:53] Bugs System
Pushed into 5.1.23-beta
[19 Oct 2007 18:54] Bugs System
Pushed into 5.0.52
[19 Oct 2007 18:58] Konstantin Osipov
A crash could occur when a NON-DETERMINISTIC stored function was used in a query with GROUP BY clause.
[23 Oct 2007 0:26] Paul DuBois
Noted in 5.0.52, 5.1.23 changelogs.
[1 Nov 2007 10:15] Bugs System
Pushed into 6.0.4-alpha
[1 Nov 2007 10:16] Bugs System
Pushed into 5.1.23-rc
[1 Nov 2007 10:17] Bugs System
Pushed into 5.0.52
[7 Nov 2007 21:59] Bugs System
Pushed into 6.0.4-alpha
[7 Nov 2007 22:00] Bugs System
Pushed into 5.1.23-rc
[8 Nov 2007 13:35] Paul DuBois
Fixed in 5.0.50, not 5.0.52.
[11 Dec 2007 16:02] Daniel Fischer
Actually fixed in 5.0.50.