MySQL Bugs: #46607: Assertion failed: (cond_type == Item::FUNC

Bug #46607	Assertion failed: (cond_type == Item::FUNC_ITEM) results in server crash
Submitted:	7 Aug 2009 16:32	Modified:	14 Sep 2009 19:50
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1, 5.4	OS:	Any
Assigned to:	Alexey Kopytov	CPU Architecture:	Any
Tags:	assertion failed, crash

Description:
I encountered a crash in 5.1-bugteam with the following error output:
Assertion failed: (cond_type == Item::FUNC_ITEM), function check_group_min_max_predicates, file opt_range.cc, line 9633.
090807  5:51:51 - mysqld got signal 6 ;

I'm still analyzing this bug to produce a repeatable test case.

I was running the random query generator against a 5.1-bugteam server using the Transfomer validator.

How to repeat:
These are the last queries that were run prior to the crash.  Will attach a repeatable test case once I have produced it.

 SELECT  MAX( `int_key`) AS field1 , `varchar_key` AS field2 FROM D AS table1 WHERE table1 . `int_key` IS  NULL  GROUP BY field2 

SELECT  MAX( `int_key`) AS field1 , `varchar_key` AS field2 FROM D AS table1 WHERE table1 . `int_key` IS  NULL  GROUP BY field2 LIMIT 1  

SELECT  MAX( `int_key`) AS field1 , `varchar_key` AS field2 FROM D AS table1 WHERE table1 . `int_key` IS  NULL  GROUP BY field2 LIMIT 4294836225

SELECT STRAIGHT_JOIN  MAX( `int_key`) AS field1 , `varchar_key` AS field2 FROM D AS table1 WHERE table1 . `int_key` IS  NULL  GROUP BY field2  

SELECT   MIN( table2 . `varchar_nokey` ) AS field1 , table1 . `datetime_key` AS field2 , table1 . `varchar_key` AS field3 FROM ( CC AS table1 RIGHT  JOIN CC AS table2 ON (join_condition_list ) ) WHERE ( table1 . `pk`  IN ( SELECT subquery_t1 . `int_key` FROM D AS subquery_t1  ))  GROUP BY field2, field3 HAVING (field2 <= 'wx' AND field2 = 'k') ORDER BY field1 DESC , field3  failed: 2006 MySQL server has gone away

additional crash output

Attachment: Bug46607_crash_output.txt (text/plain), 10.09 KiB.

Test case:
This bug does not seem to be affected by optimizer_switch, optimizer_use_mrr, engine_condition_pushdown, or join_cache_level.

I have *not* tested this on 5.0, but it is present in 5.1 and azalea.

SET SESSION optimizer_switch ='firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=on,semijoin=on' ;
!50400 SET SESSION optimizer_use_mrr = 'force' ;
!50400 SET SESSION engine_condition_pushdown = 'ON' ;
!50400 SET SESSION join_cache_level = 1 ;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ D;
--enable_warnings

CREATE TABLE `D` (
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (NULL,NULL),(6,NULL),(9,NULL),(25,NULL),(1,'a'),(2,'a'),(8,'a'),(3,'b'),(4,'b'),(5,'b'),(5,'b'),(0,'c'),(2,'c'),(7,'c'),(8,'c'),(9,'c'),(60,'c'),(147,'c'),(0,'d'),(6,'d'),(7,'d'),(8,'d'),(9,'d'),(255,'d'),(0,'e'),(1,'e'),(2,'e'),(4,'e'),(4,'e'),(8,'e'),(NULL,'f'),(3,'f'),(5,'f'),(NULL,'g'),(4,'g'),(5,'g'),(NULL,'h'),(3,'h'),(6,'h'),(8,'h'),(5,'i'),(1,'j'),(5,'j'),(5,'j'),(1,'k'),(7,'k'),(1,'l'),(151,'l'),(NULL,'m'),(6,'m'),(6,'m'),(7,'m'),(NULL,'n'),(6,'n'),(23,'n'),(0,'o'),(1,'o'),(195,'o'),(0,'p'),(6,'p'),(7,'p'),(3,'q'),(7,'q'),(8,'q'),(NULL,'r'),(3,'r'),(8,'r'),(0,'s'),(0,'s'),(8,'s'),(8,'s'),(2,'t'),(9,'t'),(9,'t'),(77,'t'),(3,'u'),(7,'u'),(9,'u'),(248,'u'),(NULL,'v'),(1,'v'),(2,'v'),(4,'v'),(NULL,'w'),(NULL,'w'),(0,'w'),(1,'w'),(1,'w'),(98,'w'),(NULL,'x'),(1,'x'),(8,'x'),(2,'y'),(3,'y'),(4,'y'),(5,'y'),(0,'z'),(1,'z'),(6,'z'),(8,'z');

 
SELECT  MAX( `int_key`  )  , `varchar_key` field3  
FROM D table1  
WHERE `int_key`  
GROUP  BY field3   ;

DROP TABLE D;
#/* End of test case for query 0 */

# 22:52:20 #4  0x90f033db in __assert_rtn ()
# 22:52:20 #5  0x00231f60 in check_group_min_max_predicates (cond=0x1121860, min_max_arg_item=0x1121358, image_type=Field::itRAW) at opt_range.cc:9633
# 22:52:20 #6  0x002333a8 in get_best_group_min_max (param=0xb041ee9c, tree=0x0) at opt_range.cc:9543
# 22:52:20 #7  0x00234062 in SQL_SELECT::test_quick_select (this=0x1122ef8, thd=0x1103418, keys_to_use={map = 32}, prev_tables=0, limit=18446744073709551615, force_quick_range=false) at opt_range.cc:2352
# 22:52:20 #8  0x0017a32f in get_quick_record_count (thd=0x1103418, select=0x1122ef8, table=0x1118c18, keys=0x1122cc8, limit=18446744073709551615) at sql_select.cc:2445
# 22:52:20 #9  0x0018fae8 in make_join_statistics (join=0x1121a30, tables_arg=0x1121658, conds=0x1121860, keyuse_array=0x1122b34) at sql_select.cc:2855
# 22:52:20 #10 0x0019dedd in JOIN::optimize (this=0x1121a30) at sql_select.cc:954
# 22:52:20 #11 0x001a5cae in mysql_select (thd=0x1103418, rref_pointer_array=0x1104864, tables=0x1121658, wild_num=0, fields=@0x1104800, conds=0x1121860, og_num=1, order=0x0, group=0x1121988, having=0x0, proc_param=0x0, select_options=2147764736, result=0x1121a18, unit=0x11044fc, select_lex=0x110476c) at sql_select.cc:2384
# 22:52:20 #12 0x001a60c2 in handle_select (thd=0x1103418, lex=0x11044a0, result=0x1121a18, setup_tables_done_option=0) at sql_select.cc:268
# 22:52:20 #13 0x0010a4a6 in execute_sqlcom_select (thd=0x1103418, all_tables=0x1121658) at sql_parse.cc:5011
# 22:52:20 #14 0x00110f14 in mysql_execute_command (thd=0x1103418) at sql_parse.cc:2206
# 22:52:20 #15 0x0011afa8 in mysql_parse (thd=0x1103418, inBuf=0x1121228 "SELECT  MAX( `int_key`  )  , `varchar_key` field2  FROM D table1  WHERE `int_key`  GROUP  BY field2", length=99, found_semicolon=0xb0422df0) at sql_parse.cc:5931
# 22:52:20 #16 0x0011bd66 in dispatch_command (command=COM_QUERY, thd=0x1103418, packet=0x1931019 " SELECT  MAX( `int_key`  )  , `varchar_key` field2  FROM D table1  WHERE `int_key`  GROUP  BY field2   ", packet_length=103) at sql_parse.cc:1213
# 22:52:20 #17 0x0011d07a in do_command (thd=0x1103418) at sql_parse.cc:854
# 22:52:20 #18 0x00108122 in handle_one_connection (arg=0x1103418) at sql_connect.cc:1127
# 22:52:20 #19 0x90e53155 in _pthread_start ()
# 22:52:20 #20 0x90e53012 in thread_start ()

Ok to push.
Please consider adding DBUG_PRINT("info", ...) under the introduced if().
Thanks.

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/81947

3062 Alexey Kopytov	2009-08-30
      Bug #46607: Assertion failed: (cond_type == Item::FUNC_ITEM) 
                  results in server crash 
       
      check_group_min_max_predicates() assumed the input condition 
      item to be one of COND_ITEM, SUBSELECT_ITEM, or FUNC_ITEM. 
      Since a condition of the form "field" is also a valid condition 
      equivalent to "field <> 0", using such a condition in a query 
      where the loose index scan was chosen resulted in a debug 
      assertion failure. 
       
      Fixed by handling conditions of the FIELD_ITEM type in 
      check_group_min_max_predicates(). 
     @ mysql-test/r/group_min_max.result
        Added a test case for bug #46607.
     @ mysql-test/t/group_min_max.test
        Added a test case for bug #46607.
     @ sql/opt_range.cc
        Handle conditions of the FUNC_ITEM type in 
        check_group_mix_max_predicates().

Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:staale.smedseng@sun.com-20090830170148-ko1pf5jg9wi7oony) (merge vers: 5.1.39) (pib:11)

Noted in 5.1.39 changelog.

In queries for which the loose index scan access method was chosen,
using a condition of the form col_name rather than the equivalent
col_name <> 0 caused an assertion failure. 

Setting report to NDI pending push into 5.4.x.

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)

Noted in 5.4.4 changelog.

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)

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)

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)

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

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)