MySQL Bugs: #31035: select from function, group by result crasher

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

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:
.

Workaround: use DETERMINISTIC clause

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
 }

Marking as a showstopper for 5.0.50 based on my conversation with Kostja

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.

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.

Igor is correct that the bug was originally introduced not by 29338 but by 27354, which 29338 supersedes.

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).

Not a regression; removing Showstopper. Should still be fixed for 5.0.50, and next 5.1.

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 ...

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.

Pushed into 5.0-runtime.

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

Pushed into 5.1.23-beta

Pushed into 5.0.52

A crash could occur when a NON-DETERMINISTIC stored function was used in a query with GROUP BY clause.

Noted in 5.0.52, 5.1.23 changelogs.

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.52

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

Fixed in 5.0.50, not 5.0.52.

Actually fixed in 5.0.50.