MySQL Bugs: #45863: Assertion failed: (fixed == 0), function fix

Bug #45863	Assertion failed: (fixed == 0), function fix_fields, file item.cc, line 4448
Submitted:	30 Jun 2009 19:20	Modified:	22 Nov 2010 0:48
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Roy Lyseng	CPU Architecture:	Any
Tags:	firstmatch, LooseScan, materialization, optimizer_switch, server crash, subquery

Description:
Assertion failed: (fixed == 0), function fix_fields, file item.cc, line 4448
This is the data returned on a variety of crashing queries:

Examples:
# 13:57:21 Query:  SELECT GRANDPARENT1 .`time_key` G1  FROM CC GRANDPARENT1  WHERE ( `varchar_key`  , GRANDPARENT1 .`varchar_key`  )  IN (  SELECT  DISTINCT PARENT1 .`varchar_nokey`  AS P1  , PARENT1 .`varchar_nokey`  AS P2  FROM CC  AS PARENT1  LEFT  JOIN B  AS PARENT2  USING ( `date_key`  )  WHERE ( GRANDPARENT1 .`varchar_nokey`  > 'h' )  ORDER  BY PARENT1 .`int_nokey`  )  AND ( GRANDPARENT1 .`datetime_key`  <= '2007-11-04' OR  NOT GRANDPARENT1 .`int_key`  IS  NOT  NULL  )  GROUP  BY GRANDPARENT1 .`int_key`  HAVING G1  <> '2004-04-13' LIMIT  3    failed: 2006 MySQL server has gone away

********

# 14:00:47 Query:  SELECT OUTR .`datetime_nokey`  FROM B OUTR2  JOIN C OUTR  ON OUTR2 .`datetime_nokey`  = OUTR .`datetime_key`  WHERE ( OUTR .`varchar_key`  , OUTR .`varchar_key`  )  IN (  SELECT INNR .`varchar_nokey`  AS X  , INNR .`varchar_key`  AS Y  FROM CC  AS INNR  WHERE INNR .`pk`  >= INNR .`int_nokey`  AND  NOT OUTR .`varchar_nokey`  <> 'm' )  AND OUTR .`varchar_key`  = 'j' XOR OUTR .`varchar_key`  >= 'u' ORDER  BY OUTR .`time_nokey`  , OUTR .`pk`    failed: 2006 MySQL server has gone away

*******

# 14:04:13 Query:  SELECT `int_key`  FROM B OUTR  WHERE ( `varchar_key`  , OUTR .`var
char_key`  )  IN (  SELECT  DISTINCT INNR .`varchar_key`  AS X  , INNR .`varchar_noke
y`  AS Y  FROM BB  AS INNR2  LEFT  JOIN CC  AS INNR  ON ( INNR2 .`datetime_key`  <> I
NNR .`datetime_key`  )  WHERE INNR .`datetime_nokey`  >= INNR .`time_key`  XOR OUTR .
`time_key`  > '2004-01-06' ORDER  BY INNR .`varchar_key`  )  AND OUTR .`date_nokey`  
<> '2002-01-17' ORDER  BY OUTR .`time_key`  , OUTR .`pk`    failed: 2006 MySQL server
 has gone away

How to repeat:
Pasting one RQG-generated .test file here.
Will package additional data in a tarball attachment -
rqg output files
rqg-generated .test files

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `CC` VALUES (10,8,'2007-02-14','00:00:00','2006-07-07 07:26:28','2006-07-07 07:26:28','q','q'),(11,8,'2002-10-03','19:02:44','2002-09-23 00:00:00','2002-09-23 00:00:00','m','m'),(12,3,'2006-12-02','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),(13,2,'2007-05-02','23:49:29','2006-06-07 00:00:00','2006-06-07 00:00:00','z','z'),(14,2,'2001-11-18','02:51:11','2000-09-16 12:15:34','2000-09-16 12:15:34','a','a'),(15,6,'2006-09-09','00:00:00','2007-08-05 15:47:52','2007-08-05 15:47:52','',''),(16,8,'0000-00-00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),(17,9,'2003-07-22','00:00:00','2005-12-02 19:34:26','2005-12-02 19:34:26','t','t'),(18,2,'2001-12-22','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','q','q'),(19,6,'0000-00-00','19:51:30','0000-00-00 00:00:00','0000-00-00 00:00:00','b','b'),(20,5,'2006-09-02','00:00:00','2007-12-28 00:00:00','2007-12-28 00:00:00','w','w'),(21,2,'0000-00-00','06:20:13','2004-08-02 11:48:43','2004-08-02 11:48:43','m','m'),(22,4,'0000-00-00','14:35:22','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'),(23,9,'2001-02-28','02:15:41','2004-04-19 12:18:43','2004-04-19 12:18:43','',''),(24,6,'0000-00-00','07:21:20','2009-04-27 00:00:00','2009-04-27 00:00:00','w','w'),(25,5,'2007-05-19','00:00:00','2006-10-20 14:52:15','2006-10-20 14:52:15','x','x'),(26,0,'2005-02-15','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),(27,0,'2000-10-19','04:57:18','2002-03-22 11:48:37','2002-03-22 11:48:37','e','e'),(28,8,'2005-07-07','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),(29,0,'2008-10-18','20:55:32','2001-01-04 03:55:07','2001-01-04 03:55:07','x','x');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `BB` VALUES (10,5,'0000-00-00','00:00:00','2007-08-19 08:08:38','2007-08-19 08:08:38','i','i'),(11,8,'2005-08-18','00:00:00','2000-05-21 03:51:51','2000-05-21 03:51:51','','');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `B` VALUES (1,9,'2003-07-28','15:13:38','0000-00-00 00:00:00','0000-00-00 00:00:00','f','f'),(2,0,'0000-00-00','00:05:48','2004-07-02 14:34:13','2004-07-02 14:34:13','x','x');

/*

OPTIMIZER SETTINGS:

*/

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';
SET SESSION optimizer_use_mrr = 'disable';
SET SESSION engine_condition_pushdown = '0';
SET SESSION join_cache_level = '1';
SET GLOBAL 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';
SET GLOBAL optimizer_use_mrr = 'disable';
SET GLOBAL engine_condition_pushdown = '0';
SET GLOBAL join_cache_level = '1';

/*

ORIGINAL QUERY:

SELECT  OUTR . `int_key` AS X FROM B AS OUTR WHERE ( OUTR . `varchar_key` , OUTR . `varchar_key` ) IN ( SELECT DISTINCT INNR . `varchar_key` AS X , INNR . `varchar_nokey` AS Y FROM BB AS INNR2 LEFT JOIN CC AS INNR ON ( INNR2 . `datetime_key` <> INNR . `datetime_key` ) WHERE INNR . `datetime_nokey` >= INNR . `time_key` XOR OUTR . `time_key` > '2004-01-06' ORDER BY INNR . `varchar_key` ) AND OUTR . `date_nokey` <> '2002-01-17'  ORDER BY OUTR . `time_key` , OUTR . `pk`;

ORIGINAL DIFF:

--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen56715-server0.dump	2009-06-30 14:04:15.000000000 -0400
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen56715-server1.dump	2009-06-30 14:04:15.000000000 -0400
@@ -0,0 +1 @@
+0

SIMPLIFIED QUERY:

 SELECT `int_key`  FROM B OUTR  WHERE ( `varchar_key`  , OUTR .`varchar_key`  )  IN (  SELECT  DISTINCT INNR .`varchar_key`  AS X  , INNR .`varchar_nokey`  AS Y  FROM BB  AS INNR2  LEFT  JOIN CC  AS INNR  ON ( INNR2 .`datetime_key`  <> INNR .`datetime_key`  )  WHERE INNR .`datetime_nokey`  >= INNR .`time_key`  XOR OUTR .`time_key`  > '2004-01-06' ORDER  BY INNR .`varchar_key`  )  AND OUTR .`date_nokey`  <> '2002-01-17' ORDER  BY OUTR .`time_key`  , OUTR .`pk`   ;

SIMPLIFIED DIFF:

*/

Suggested fix:
Correct whatever is causing these queries to crash.

Simplified test case:

--disable_warnings
DROP TABLE IF EXISTS C, BB;
--enable_warnings

CREATE TABLE `C` (
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES ('k','k'),('a','a'),('',''),('u','u'),('e','e'),('v','v'),('i','i'),('t','t'),('u','u'),('f','f'),('u','u'),('m','m'),('j','j'),('f','f'),('v','v'),('j','j'),('g','g'),('e','e'),('h','h'),('z','z');
CREATE TABLE `BB` (
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES ('i','i'),('t','t');

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';
SET SESSION optimizer_use_mrr = 'disable';
SET SESSION engine_condition_pushdown = 'ON';
SET SESSION join_cache_level = 1;

 SELECT `varchar_nokey`  FROM C  WHERE ( `varchar_nokey`  , OUTR  )  IN (  SELECT `varchar_key`  FROM BB  )   ;

DROP TABLE C, BB;

Backtrace:

# 07:56:25 #6  0x000000315a42bec9 in __assert_fail () from /lib64/libc.so.6
# 07:56:25 #7  0x000000000059a8dd in Item_field::fix_fields (this=0x2ea8b80, thd=0x7f80f0c52198, reference=0x2ea8e20) at item.cc:4448
# 07:56:25 #8  0x000000000061d7d1 in Item_row::fix_fields (this=0x2ea8d98, thd=0x7f80f0c52198, ref=0x2fcf848) at item_row.cc:68
# 07:56:25 #9  0x000000000061a305 in Item_in_subselect::select_in_like_transformer (this=0x2fb3e48, join=0x2fdd350, func=0x104f7c0) at item_subselect.cc:1716
# 07:56:25 #10 0x000000000061a590 in Item_in_subselect::select_transformer (this=0x2fb3e48, join=0x2fdd350) at item_subselect.cc:1643
# 07:56:25 #11 0x0000000000719281 in JOIN::prepare (this=0x2fdd350, rref_pointer_array=0x2ea9018, tables_init=0x2ea9820, wild_num=0, conds_init=0x0, og_num=0,
# 07:56:25     order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x2ea8e30, unit_arg=0x2ea9100) at sql_select.cc:711
# 07:56:25 #12 0x00000000006168c4 in subselect_single_select_engine::prepare (this=0x2fb3f88) at item_subselect.cc:2105
# 07:56:25 #13 0x000000000061af0c in Item_subselect::fix_fields (this=0x2fb3e48, thd_param=0x7f80f0c52198, ref=0x2fb4418) at item_subselect.cc:174
# 07:56:25 #14 0x000000000061b314 in Item_in_subselect::fix_fields (this=0x2fb3e48, thd_arg=0x7f80f0c52198, ref=0x2fb4418) at item_subselect.cc:1784
# 07:56:25 #15 0x00000000005dc4a1 in Item_cond::fix_fields (this=0x2fb4310, thd=0x7f80f0c52198, ref=0x2fdd230) at item_cmpfunc.cc:3980
# 07:56:25 #16 0x00000000006cb41d in setup_conds (thd=0x7f80f0c52198, tables=0x2ea8598, leaves=0x2ea8598, conds=0x2fdd230) at sql_base.cc:7244
# 07:56:25 #17 0x00000000007220e9 in setup_without_group (thd=0x7f80f0c52198, ref_pointer_array=0x2fb4ae8, tables=0x2ea8598, leaves=0x2ea8598, fields=@0x7f80f0c54070,
# 07:56:25     all_fields=@0x2fdd148, conds=0x2fdd230, order=0x2fb49c8, group=0x2fb4538, hidden_group_fields=0x2fdd127) at sql_select.cc:457
# 07:56:25 #18 0x00000000007188ca in JOIN::prepare (this=0x2fd7590, rref_pointer_array=0x7f80f0c54150, tables_init=0x2ea8598, wild_num=0, conds_init=0x2fb4310,
# 07:56:25     og_num=2, order_init=0x2fb49c8, group_init=0x2fb4538, having_init=0x2fb4728, proc_param_init=0x0, select_lex_arg=0x7f80f0c53f68, unit_arg=0x7f80f0c53b00)
# 07:56:25     at sql_select.cc:539
# 07:56:25 #19 0x0000000000719e4b in mysql_select (thd=0x7f80f0c52198, rref_pointer_array=0x7f80f0c54150, tables=0x2ea8598, wild_num=0, fields=@0x7f80f0c54070,
# 07:56:25     conds=0x2fb4310, og_num=2, order=0x2fb49c8, group=0x2fb4538, having=0x2fb4728, proc_param=0x0, select_options=2147764736, result=0x2fb4aa8,
# 07:56:25     unit=0x7f80f0c53b00, select_lex=0x7f80f0c53f68) at sql_select.cc:3053
# 07:56:25 #20 0x000000000071f6d5 in handle_select (thd=0x7f80f0c52198, lex=0x7f80f0c53a60, result=0x2fb4aa8, setup_tables_done_option=0) at sql_select.cc:310
# 07:56:25 #21 0x000000000067a933 in execute_sqlcom_select (thd=0x7f80f0c52198, all_tables=0x2ea8598) at sql_parse.cc:4987
# 07:56:25 #22 0x000000000067c181 in mysql_execute_command (thd=0x7f80f0c52198) at sql_parse.cc:2172
# 07:56:25 #23 0x0000000000684966 in mysql_parse (thd=0x7f80f0c52198,
# 07:56:25     inBuf=0x2ea7e30 "SELECT `datetime_nokey` G1  FROM C GRANDPARENT1  WHERE ( `int_nokey`  , GRANDPARENT1  )  IN (  SELECT  DISTINCT PARENT1 .`int_key`  AS P1  , PARENT1 .`int_nokey`  AS P2  FROM CC  AS PARENT1  LEFT  JOI"..., length=406, found_semicolon=0x7f80ee9b6f10) at sql_parse.cc:6002
# 07:56:25 #24 0x000000000068554d in dispatch_command (command=COM_QUERY, thd=0x7f80f0c52198,
# 07:56:25     packet=0x7f80f0c9dbc9 " SELECT `datetime_nokey` G1  FROM C GRANDPARENT1  WHERE ( `int_nokey`  , GRANDPARENT1  )  IN (  SELECT  DISTINCT PARENT1 .`int_key`  AS P1  , PARENT1 .`int_nokey`  AS P2  FROM CC  AS PARENT1  LEFT  JO"..., packet_length=410) at sql_parse.cc:1064
# 07:56:25 #25 0x0000000000686a35 in do_command (thd=0x7f80f0c52198) at sql_parse.cc:746
# 07:56:25 #26 0x0000000000673e34 in handle_one_connection (arg=0x7f80f0c52198) at sql_connect.cc:1158
# 07:56:25 #27 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0
# 07:56:25 #28 0x000000315a4e627d in clone () from /lib64/libc.so.6

This bug is not related to MRR and happens for both settings of the optimizer_use_mrr switch. Note that the simplified query:

 SELECT `varchar_nokey`  FROM C  WHERE ( `varchar_nokey`  , OUTR  )  IN (  SELECT `varchar_key`  FROM BB  )   ;

is actually semantically invalid, since there is no database object OUTR. Therefore, this is a bug in the name resolution process.

NOTE:  If *any* of the optimizer_switch values listed are set to 'on', this crash will occur for the attached test case:
materialization, semijoin ,loosescan ,firstmatch

If all are set to 'off', the crash will not occur.

Bug #46767 has been marked as a duplicate to this one.

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

3699 Roy Lyseng	2009-11-05
      BUG#45863: Assertion failed: (fixed == 0)...
      The problem in JOIN::prepare() is that fix_fields() is called prematurely
      from subquery_types_allow_materialization().
      The call to fix_fields() observed the unknown column name and set error
      status accordingly.
      But JOIN::prepare() failed to notice the error and attempted to call
      fix_fields() again, now with a half-way resolved expression, and we have
      an assert().
      The fix is mostly taken from the committed but unpushed work in WL#4389,
      which is about applying semijoin transformation to EXISTS queries,
      but also consolidates some of the semantic checking on subqueries.
      The call to fix_fields() inside JOIN::prepare() is moved so that it is
      always executed before subquery_types_allow_materialization().
      Hence, the latter no longer needs to call fix_fields() and we can remove
      the thd argument (and also let both return values become output arguments).
      I also removed one of the "psergey-todo" comments, because the subselect
      cardinality check is now always performed in the early stages of
      JOIN::prepare(). But eliminating all the comments would be beyond the scope
      of this bugfix.
      Noticed another problem during development as well: One query was optimized
      differently in regular and prepared mode, and this happened because the
      is_correlated flag was set differently in the two modes. Had to make sure
      that this flag is set consistently, by calling fix_fields() from
      JOIN::prepare() also for the first execution of a prepared statement.
      Otherwise, is_correlated would be set inconsistently by the fix_fields()
      call in convert_subq_to_sj().
      
        mysql-test/r/subselect4.result
          Added test results for bug
      
        mysql-test/t/subselect4.test
          Added tests for bug
      
        sql/sql_select.cc
          Modified JOIN::prepare() and subquery_types_allow_materialization
          according to above description.

NOTE:
The diff supplied in the attached test case no longer occurs when comparing the 6.0-codebase and 5.1-bugteam trees.

While the main thrust of this bug was the crash, there were some questions about the reported difference between servers.  This note is to let everyone know it is no longer repeatable.

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

3738 Roy Lyseng	2009-11-30
      BUG#45863: Assertion failed: (fixed == 0)...
      The problem in JOIN::prepare() is that fix_fields() is called prematurely
      from subquery_types_allow_materialization().
      The call to fix_fields() observed the unknown column name and set error
      status accordingly.
      But JOIN::prepare() failed to notice the error and attempted to call
      fix_fields() again, now with a half-way resolved expression, and we have
      an assert().
      The fix is mostly taken from the committed but unpushed work in WL#4389,
      which is about applying semijoin transformation to EXISTS queries,
      but also consolidates some of the semantic checking on subqueries.
      The call to fix_fields() inside JOIN::prepare() is moved so that it is
      always executed before subquery_types_allow_materialization().
      Hence, the latter no longer needs to call fix_fields() and we can remove
      the thd argument. Changes to subquery object are now applied inside this
      function, making code slightly more object-oriented.
      I also removed one of the "psergey-todo" comments, because the subselect
      cardinality check is now always performed in the early stages of
      JOIN::prepare(). But eliminating all the comments would be beyond the scope
      of this bugfix.
      Noticed another problem during development as well: One query was optimized
      differently in regular and prepared mode, and this happened because the
      is_correlated flag was set differently in the two modes. Had to make sure
      that this flag is set consistently, by calling fix_fields() from
      JOIN::prepare() also for the first execution of a prepared statement.
      Otherwise, is_correlated would be set inconsistently by the fix_fields()
      call in convert_subq_to_sj().
      
        mysql-test/r/subselect4.result
          Added test results for bug.
      
        mysql-test/t/subselect4.test
          Added tests for bug.
      
        sql/sql_select.cc
          Modified JOIN::prepare() and subquery_types_allow_materialization()
          according to above description.

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

3762 Roy Lyseng	2009-12-08
      BUG#45863: Assertion failed: (fixed == 0)...
      The problem in JOIN::prepare() is that fix_fields() is called prematurely
      from subquery_types_allow_materialization().
      The call to fix_fields() observed the unknown column name and set error
      status accordingly.
      But JOIN::prepare() failed to notice the error and attempted to call
      fix_fields() again, now with a half-way resolved expression, and we have
      an assert().
      The fix is mostly taken from the committed but unpushed work in WL#4389,
      which is about applying semijoin transformation to EXISTS queries,
      but also consolidates some of the semantic checking on subqueries.
      The call to fix_fields() inside JOIN::prepare() is moved so that it is
      always executed before subquery_types_allow_materialization().
      Hence, the latter no longer needs to call fix_fields() and we can remove
      the thd argument (and also let both return values become output arguments).
      I also removed one of the "psergey-todo" comments, because the subselect
      cardinality check is now always performed in the early stages of
      JOIN::prepare(). But eliminating all the comments would be beyond the scope
      of this bugfix.
      Noticed another problem during development as well: One query was optimized
      differently in regular and prepared mode, and this happened because the
      is_correlated flag was set differently in the two modes. Had to make sure
      that this flag is set consistently, by calling fix_fields() from
      JOIN::prepare() also for the first execution of a prepared statement.
      Otherwise, is_correlated would be set inconsistently by the fix_fields()
      call in convert_subq_to_sj().
      
      mysql-test/r/subselect4.result
        Added test results for bug.
            
      mysql-test/t/subselect4.test
        Added tests for bug.
            
      sql/sql_select.cc
        Modified JOIN::prepare() and subquery_types_allow_materialization()
        according to above description.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 6.0.14 changelog.

Specifying an unknown column name together with an invalid number of
expressions on the left-hand side of an IN subquery caused a core
dump in Item_field::fix_fields().

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

3135 Roy Lyseng	2010-05-06
      BUG#45863: Assertion failed: (fixed == 0)...
      
      The problem in JOIN::prepare() is that fix_fields() is called prematurely
      from subquery_types_allow_materialization().
      The call to fix_fields() observed the unknown column name and set error
      status accordingly.
      But JOIN::prepare() failed to notice the error and attempted to call
      fix_fields() again, now with a half-way resolved expression, and we have
      an assert().
      The fix is mostly taken from the committed but unpushed work in WL#4389,
      which is about applying semijoin transformation to EXISTS queries,
      but also consolidates some of the semantic checking on subqueries.
      The call to fix_fields() inside JOIN::prepare() is moved so that it is
      always executed before subquery_types_allow_materialization().
      Hence, the latter no longer needs to call fix_fields() and we can remove
      the thd argument (and also let both return values become output arguments).
      I also removed one of the "psergey-todo" comments, because the subselect
      cardinality check is now always performed in the early stages of
      JOIN::prepare(). But eliminating all the comments would be beyond the scope
      of this bugfix.
      Noticed another problem during development as well: One query was optimized
      differently in regular and prepared mode, and this happened because the
      is_correlated flag was set differently in the two modes. Had to make sure
      that this flag is set consistently, by calling fix_fields() from
      JOIN::prepare() also for the first execution of a prepared statement.
      Otherwise, is_correlated would be set inconsistently by the fix_fields()
      call in convert_subq_to_sj().
        
      mysql-test/r/subselect4.result
        Added test results for bug.
              
      mysql-test/t/subselect4.test
        Added tests for bug.
              
      sql/sql_select.cc
        Modified JOIN::prepare() and subquery_types_allow_materialization()
        according to above description.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Noted in 5.6.1 changelog.

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.