MySQL Bugs: #52317: Assertion failing in Field_varstring::store () at field.cc:6833

Bug #52317	Assertion failing in Field_varstring::store () at field.cc:6833
Submitted:	23 Mar 2010 22:44	Modified:	13 Aug 2010 2:15
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6, 6.0	OS:	Any
Assigned to:	Øystein Grøvlen	CPU Architecture:	Any
Tags:	assertion, crashing bug

Description:
Assertion failing in Field_varstring::store () at field.cc:6833
This is 6.0-codebase only and does not appear to be affected by optimizer_switch or join_cache_level settings.  

Will attach full backtrace and mtr test case as separate files, could not due to space limitations.

This query:
EXPLAIN  
SELECT `col_datetime_key`  
FROM (  
SELECT * 
FROM C  ) table1  
WHERE ( 'd' , 'z' )  NOT  IN (  
SELECT `col_varchar_key`  , `col_varchar_nokey`  
FROM CC  )   ;

Provides this backtrace:

Thread 1 (Thread 20077):
#0  0x0018c422 in __kernel_vsyscall ()
#1  0x00eb8e93 in __pthread_kill (threadid=3011000176, signo=6)
    at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:64
#2  0x08d1f00f in my_write_core (sig=6) at stacktrace.c:328
#3  0x08422222 in handle_segfault (sig=6) at mysqld.cc:2843
#4  <signal handler called>
#5  0x0018c422 in __kernel_vsyscall ()
#6  0x003074d1 in *__GI_raise (sig=6)
    at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#7  0x0030a932 in *__GI_abort () at abort.c:92
#8  0x00300648 in *__GI___assert_fail (
    assertion=0x8fe3f14 "!table || (!table->write_set || bitmap_is_set(table->write_set, field_index))", file=0x8fe3d6c "field.cc", line=6833, 
    function=0x8fe5820 "virtual int Field_varstring::store(const char*, uint, CHARSET_INFO*)") at assert.c:81
#9  0x083bc380 in Field_varstring::store (this=0xa2b77d0, from=0xa2b666d "d", 
    length=1, cs=0x91daa00) at field.cc:6833
#10 0x08268e46 in Item::save_in_field (this=0xa2b6600, field=0xa2b77d0, 
    no_conversions=true) at item.cc:5395
#11 0x08276efd in Item_cache_str::save_in_field (this=0xa2b6600, 
    field=0xa2b77d0, no_conversions=true) at item.cc:7693
#12 0x0826fb31 in Item_ref::save_in_field (this=0xa281808, to=0xa2b77d0, 
    no_conversions=true) at item.cc:6555
#13 0x085a6daf in store_key_const_item::copy_inner (this=0xa2b77b0)
    at sql_select.h:2007
#14 0x0838266f in store_key::copy (this=0xa2b77b0) at sql_select.h:1902
#15 0x0837b83c in subselect_uniquesubquery_engine::copy_ref_key (
    this=0xa2b82a0) at item_subselect.cc:2442
#16 0x0837bfd4 in subselect_indexsubquery_engine::exec (this=0xa2b82a0)
    at item_subselect.cc:2644
#17 0x0836fa0d in Item_subselect::exec (this=0xa280340)
    at item_subselect.cc:283
#18 0x0836fd99 in Item_in_subselect::exec (this=0xa280340)
    at item_subselect.cc:333
#19 0x0836fa52 in Item_subselect::exec (this=0xa280340)
    at item_subselect.cc:288
#20 0x0836fd99 in Item_in_subselect::exec (this=0xa280340)
    at item_subselect.cc:333
#21 0x08373802 in Item_in_subselect::val_bool (this=0xa280340)
    at item_subselect.cc:929
#22 0x0827ea8c in Item::val_bool_result (this=0xa280340) at item.h:846
#23 0x082e70ac in Item_in_optimizer::val_int (this=0xa281538)
    at item_cmpfunc.cc:1825
#24 0x0824d19d in Item::val_bool (this=0xa281538) at item.cc:184
#25 0x082dee7a in Item_func_not::val_int (this=0xa2803f8)
    at item_cmpfunc.cc:287
#26 0x08561147 in make_join_select (join=0xa2abf48, cond=0xa2803f8)
    at sql_select.cc:8977
#27 0x085401ff in JOIN::optimize (this=0xa2abf48) at sql_select.cc:1757
#28 0x08548fa2 in mysql_select (thd=0xa1e7008, rref_pointer_array=0xa1e8618, 
    tables=0xa11c1a0, wild_num=0, fields=..., conds=0xa2803f8, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2147748612, result=0xa280fe0, unit=0xa1e8054, 
    select_lex=0xa1e8514) at sql_select.cc:3170
#29 0x085a20bb in mysql_explain_union (thd=0xa1e7008, unit=0xa1e8054, 
    result=0xa280fe0) at sql_select.cc:22378
#30 0x08459858 in execute_sqlcom_select (thd=0xa1e7008, all_tables=0xa11c1a0)
    at sql_parse.cc:5010
#31 0x08448b0c in mysql_execute_command (thd=0xa1e7008) at sql_parse.cc:2295
#32 0x0845dd5a in mysql_parse (thd=0xa1e7008, 
    inBuf=0xa11aa88 "EXPLAIN  \nSELECT `col_datetime_key`  \nFROM (  \nSELECT * \nFROM C  ) table1  \nWHERE ( 'd' , 'z' )  NOT  IN (  \nSELECT `col_varchar_key`  , `col_varchar_nokey`  \nFROM CC  )", length=169, found_semicolon=0xb3782910)
    at sql_parse.cc:6060
#33 0x084439ad in dispatch_command (command=COM_QUERY, thd=0xa1e7008, 
    packet=0xa1b5eb1 "", packet_length=172) at sql_parse.cc:1091
#34 0x084421c7 in do_command (thd=0xa1e7008) at sql_parse.cc:775
#35 0x0843e551 in do_handle_one_connection (thd_arg=0xa1e7008)
    at sql_connect.cc:1173
#36 0x0843e238 in handle_one_connection (arg=0xa1e7008) at sql_connect.cc:1113
#37 0x08e7a303 in pfs_spawn_thread (arg=0xa228630) at pfs.cc:1011
#38 0x00eb380e in start_thread (arg=0xb3783770) at pthread_create.c:300
#39 0x003a98de in clone () at ../sysdeps/unix/sysv/linux/i386/clone.S:130

How to repeat:
/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */

#NOTE:  These settings don't affect the crash, only included for completeness
/*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,mrr=on,mrr_cost_based=off,index_condition_pushdown=on' */;
/*!50400 SET SESSION optimizer_join_cache_level = 0 */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `CC` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (8,'2002-02-26 06:14:37','v','v');
INSERT INTO `CC` VALUES (9,'1900-01-01 00:00:00','r','r');
INSERT INTO `CC` VALUES (9,'2006-12-03 09:37:26','a','a');
INSERT INTO `CC` VALUES (186,'2008-05-26 12:27:10','m','m');
INSERT INTO `CC` VALUES (NULL,'2004-12-14 16:37:30','y','y');
INSERT INTO `CC` VALUES (2,'2003-02-11 21:19:41','j','j');
INSERT INTO `CC` VALUES (3,'2009-10-18 02:27:49','d','d');
INSERT INTO `CC` VALUES (0,'2000-09-26 07:45:57','z','z');
INSERT INTO `CC` VALUES (133,NULL,'e','e');
INSERT INTO `CC` VALUES (1,'2005-11-10 12:40:29','h','h');
INSERT INTO `CC` VALUES (8,'2009-04-25 00:00:00','b','b');
INSERT INTO `CC` VALUES (5,'2002-11-27 00:00:00','s','s');
INSERT INTO `CC` VALUES (5,'2004-01-26 20:32:32','e','e');
INSERT INTO `CC` VALUES (8,'2007-10-26 11:41:40','j','j');
INSERT INTO `CC` VALUES (6,'2005-10-07 00:00:00','e','e');
INSERT INTO `CC` VALUES (51,'2000-07-15 05:00:34','f','f');
INSERT INTO `CC` VALUES (4,'2000-04-03 16:33:32','v','v');
INSERT INTO `CC` VALUES (7,NULL,'x','x');
INSERT INTO `CC` VALUES (6,'2001-04-25 01:26:12','m','m');
INSERT INTO `CC` VALUES (4,'2000-12-27 00:00:00','c','c');
CREATE TABLE `C` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,'2004-10-11 18:13:16','w','w');
INSERT INTO `C` VALUES (9,NULL,'m','m');
INSERT INTO `C` VALUES (3,'1900-01-01 00:00:00','m','m');
INSERT INTO `C` VALUES (9,'2009-07-25 00:00:00','k','k');
INSERT INTO `C` VALUES (NULL,NULL,'r','r');
INSERT INTO `C` VALUES (9,'2008-07-27 00:00:00','t','t');
INSERT INTO `C` VALUES (3,'2002-11-13 16:37:31','j','j');
INSERT INTO `C` VALUES (8,'1900-01-01 00:00:00','u','u');
INSERT INTO `C` VALUES (8,'2003-12-10 00:00:00','h','h');
INSERT INTO `C` VALUES (53,'2001-12-21 22:38:22','o','o');
INSERT INTO `C` VALUES (0,'2008-12-13 23:16:44',NULL,NULL);
INSERT INTO `C` VALUES (5,'2005-08-15 12:39:41','k','k');
INSERT INTO `C` VALUES (166,NULL,'e','e');
INSERT INTO `C` VALUES (3,'2006-09-11 12:06:14','n','n');
INSERT INTO `C` VALUES (0,'2007-12-15 12:39:34','t','t');
INSERT INTO `C` VALUES (1,'2005-08-09 00:00:00','c','c');
INSERT INTO `C` VALUES (9,'2001-09-02 22:50:02','m','m');
INSERT INTO `C` VALUES (5,'2005-12-16 22:58:11','y','y');
INSERT INTO `C` VALUES (6,'2007-04-19 00:19:53','f','f');
INSERT INTO `C` VALUES (2,'1900-01-01 00:00:00','d','d');

 EXPLAIN  
SELECT `col_datetime_key`  
FROM (  
SELECT * 
FROM C  ) table1  
WHERE ( 'd' , 'z' )  NOT  IN (  
SELECT `col_varchar_key`  , `col_varchar_nokey`  
FROM CC  )   ;

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

Full mtr test case with both simplified and original queries

Attachment: bug52137_test.txt (text/plain), 8.42 KiB.

Full crash output

Attachment: bug52317_backtrace.txt (text/plain), 14.61 KiB.

Seems to be related to BUG#49906

I am also seeing this crash for other types such as Field_long, etc.

I see this crash in next-mr as well, but *not* in 5.1

bzrfind in mysql-next-mr-bugfixing says:

# Revision jorgen.loland@sun.com-20100311102910-tsbh5nal1qz73fjd: test FAILED
# Regression source: jorgen.loland@sun.com-20100311102910-tsbh5nal1qz73fjd
jorgen.loland@sun.com-20100311102910-tsbh5nal1qz73fjd

which is the fix for
Bug #50257  	Missing info in REF column of the EXPLAIN lines for subselects

Developer, please check if this one is a duplicate of 52717.

Seems to be duplicate of
Bug #52717  	Assert in EXPLAIN SELECT with very particular combination with ANY and index

This bug was tagged SRFEATURE and version 6.0
Then I discovered it applies to other trees as well,
so I asked for a re-triage.

Fails in next-mr, but not in trunk.

Observations:
 - get_store_key() returns a store_key_const_item for key on CC.col_varchar_key.
 - This occurs because keyuse->used_tables is empty.
 - Later, store_key_const_item::copy_inner() is called.  
 - Unlike store_key_item::copy_inner() is does not try to modify the write_set
   of the table.
 - Assert hits because one is trying to copy into a field which is not in the
   write set.

It seems to me that either keyuse->used_tables should have been set, or one should not try to copy into this field.

A minimal test case:

CREATE TABLE t1 (i INTEGER);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (i INTEGER, KEY k(i));
INSERT INTO t2 VALUES (1), (2);

EXPLAIN  
SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2);

DROP TABLE t2;
DROP TABLE t1;

The following query fails the same way in next-mr, but not does not fail in 6.0-codebase:

EXPLAIN SELECT i FROM t1 WHERE (1) IN (SELECT i FROM t2)

Note that the above comment about 6.0-codebase, was with semijoin turned off, so execution should be equivalent.

(Continuing discussion with myself ...)
My previous comment was wrong.  Materialization also needs to be turned off to
get same execution path in 6.0-codebase as in next-mr.  If that is done, IN-query also fail sin 6.0-codebase.

In trunk, where the fix for Bug#50257, is not (yet?) present, a store_key_item is created instead of a store_key_const_item.  Hence, writing to the associated field is allowed.

The following change, fixes the issue, but also changes the explain output in some cases (replacing const with func in ref column) so I am not certain it is correct:

=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc   2010-04-01 19:34:09 +0000
+++ sql/sql_select.cc   2010-04-20 12:57:23 +0000
@@ -5926,7 +5926,7 @@
 get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables,
              KEY_PART_INFO *key_part, uchar *key_buff, uint maybe_null)
 {
-  if (!((~used_tables) & keyuse->used_tables))         // if const item
+  if (!((~used_tables) & keyuse->used_tables) && !thd->lex->describe)  // if const item
   {
     return new store_key_const_item(thd,
                                    key_part->field,

The issue here is related to the fact that subqueries are executed as part of 
EXPLAIN.  Hence, unlike for "top-level queries", store_key_const_item::copy_inner() will actually be called.   This is different
for normal execution of subqueries where a temporary store_key_item object is used instead.  So if we are to continue to use the trick with store_key_const_item for EXPLAIN,  I think we will have to allow writing into
the underlying field for store_key_const_item as we do for store_key_item.

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

3150 oystein.grovlen@sun.com	2010-04-23
      Bug#52317 Assertion failing in Field_varstring::store () at field.cc:6833
      
      In order for EXPLAIN to print const-refs, a Store_key_const_item object
      is created. This is different for normal execution of subqueries where
      a temporary store_key_item object is used instead. The problem is that
      EXPLAIN will execute subqueries.  This leads to a scenario where a
      store_key_const_item object it told to write to its underlying field.
      This results in a failing assert since the write set of the underlying
      table does not reflect this.  
      
      The resolution is to do the same trick as for store_key_item::copy_inner().
      That is, temporarily change the write set to allow writes to all columns.
      This is only necessary in debug version since non-debug version does not
      contain asserts on write_set.
     @ mysql-test/r/subselect4.result
        Test case for Bug#52317
     @ mysql-test/t/subselect4.test
        Test case for Bug#52317
     @ sql/sql_select.h
        Temporarily change write_set in store_key_const_item::copy_inner() to
        allow initialization of underlying field.  This is necessary since 
        subqueries are executed for EXPLAIN.  (For normal execution, 
        store_key_item::copy_inner is used.)

"Voluteering" Tor since he did some early investigation of this bug and
Evgeny since he reviewed the patch that introduced this regression.

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

3204 oystein.grovlen@sun.com	2010-05-27
      Bug#52317 Assertion failing in Field_varstring::store () at field.cc:6833
      
      In order for EXPLAIN to print const-refs, a Store_key_const_item object
      is created. This is different for normal execution of subqueries where
      a temporary store_key_item object is used instead. The problem is that
      EXPLAIN will execute subqueries.  This leads to a scenario where a
      store_key_const_item object it told to write to its underlying field.
      This results in a failing assert since the write set of the underlying
      table does not reflect this.  
      
      The resolution is to do the same trick as for store_key_item::copy_inner().
      That is, temporarily change the write set to allow writes to all columns.
      This is only necessary in debug version since non-debug version does not
      contain asserts on write_set.
     @ mysql-test/r/subselect4.result
        Test case for Bug#52317
     @ mysql-test/t/subselect4.test
        Test case for Bug#52317
     @ sql/sql_select.h
        Temporarily change write_set in store_key_const_item::copy_inner() to
        allow initialization of underlying field.  This is necessary since 
        subqueries are executed for EXPLAIN.  (For normal execution, 
        store_key_item::copy_inner is used.)

Pushed to mysql-next-mr-bugfixing with revision id oystein.grovlen@sun.com-20100527064644-diqyhckwybeb56bd

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:18)

Noted in 5.6.0 changelog.

Subquery execution for EXPLAIN could be done incorrectly and raise an
assertion.