Bug #54465 assert: field_types == 0 || field_types[field_pos] == MYSQL_TYPE_LONGLONG

Description:
this bug has multiple facets.  debug server asserts.  some servers print message in logs:  100613 12:00:16 [ERROR] trx->active_trans == 0, but trx->conc_state != TRX_NOT_STARTED

5.1.47-debug crash:
Version: '5.1.47-enterprise-gpl-advanced-debug'  socket: ''  port: 3307  MySQL Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: field_types == 0 || field_types[field_pos] == MYSQL_TYPE_LONGLONG, file .\protocol.cc, line 914

mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2049]
mysqld-debug.exe!raise()[winsig.c:590]
mysqld-debug.exe!abort()[abort.c:71]
mysqld-debug.exe!_wassert()[assert.c:212]
mysqld-debug.exe!Protocol_text::store_longlong()[protocol.cc:914]
mysqld-debug.exe!Item::send()[item.cc:5613]
mysqld-debug.exe!select_send::send_data()[sql_class.cc:1679]
mysqld-debug.exe!end_send_group()[sql_select.cc:12344]
mysqld-debug.exe!sub_select()[sql_select.cc:11286]
mysqld-debug.exe!do_select()[sql_select.cc:11079]
mysqld-debug.exe!JOIN::exec()[sql_select.cc:2314]
mysqld-debug.exe!mysql_select()[sql_select.cc:2511]
mysqld-debug.exe!handle_select()[sql_select.cc:269]
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:5067]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2263]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5986]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1233]
mysqld-debug.exe!do_command()[sql_parse.cc:874]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1134]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:293]
mysqld-debug.exe!_threadstart()[thread.c:277]

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` int)engine=innodb;
insert into `t1` values (1),(2);
select max((select 1 from `t1`,`t1` `a`
order by @a limit 1)) as `a`
from `t1` `a`,`t1` order by `t1`.`a`;

just to clarify, i was able to crash release build server with a larger testcase, but debug got same assertion.  so, this is a serious bug.

Verified just as described, also - with current mysql-trunk from bzr:

Version: '5.5.5-m3-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (field_types == 0 || field_types[field_pos] == MYSQL_TYPE_LONGLONG), function store_longlong, file protocol.cc, line 1079.
100613 13:08:17 - mysqld got signal 6 ;

another very similar testcase:

drop table if exists `t1`;
create table `t1`(`a` int)engine=myisam;
insert into `t1` values (0),(0);
select  min((select  1 from `t1` group by @@server_id))
from `t1` `t2`,`t1` order by `t1`.`a`;

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

3491 Alexey Kopytov	2010-08-27
      Bug #54465: assert: field_types == 0 || field_types[field_pos]
                  == MYSQL_TYPE_LONGLONG
      
      A MIN/MAX() function with a subquery as its argument could lead
      to a debug assertion on debug builds or wrong data on release
      ones.
      
      The problem was a combination of the following factors:
      
      - Item_sum_hybrid::fix_fields() might use the argument
      (args[0]) to calculate 'hybrid_field_type' which was later used
      to decide how the data should be sent to the client.
      
      - Item_sum::make_field() might use the argument again to
      calculate the field's type when sending result set metadata to
      the client.
      
      - The argument could be changed in between these two calls via
        Item::set_arg() leading to inconsistent metadata being
        reported.
      
      Here is what was happening for the bug's test case:
      
      1. Item_sum_hybrid::fix_fields() calculates hybrid_field_type
      as MYSQL_TYPE_LONGLONG based on args[0] which is an
      Item::SUBSELECT_ITEM at that time.
      
      2. A temporary table is created to execute the
      query. create_tmp_field_from_item() creates a Field_long object
      according to the subselect's max_length.
      
      3. The subselect item in Item_sum_hybrid is replaced by the
      Item_field object referencing the newly created Field_long.
      
      4. Item_sum::make_field() rightfully returns the
      MYSQL_TYPE_LONG type when calculating the result set metadata.
      
      5. When sending the actual data, Item::send() relies on the
      virtual field_type() function which in our case returns
      previously calculated hybrid_field_type == MYSQL_TYPE_LONGLONG.
      
      It looks like the only solution is to never refer to the
      argument's metadata after the result metadata has been
      calculated in fix_fields(), since the argument itself may be
      different by then. In this sense, Item_sum::make_field() should
      never be used, because it may rely on the argument's metadata
      and is only called after fix_fields(). The "default"
      implementation in Item::make_field() should be used instead as
      it relies only on field_type(), but not on the argument's type.
      
      Fixed by removing Item_sum::make_field() so that the superclass
      implementation Item::make_field() is always used.
     @ mysql-test/r/func_group.result
        Added a test case for bug #54465.
     @ mysql-test/t/func_group.test
        Added a test case for bug #54465.
     @ sql/item_sum.cc
        Removed Item_sum::make_field() so that the superclass
        implementation Item::make_field() is always used.
     @ sql/item_sum.h
        Removed Item_sum::make_field() so that the superclass
        implementation Item::make_field() is always used.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100901130501-4g2k86dub29auj8y) (version source revid:alik@sun.com-20100901130012-9bmmvzcnnw6n5rw6) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100901130614-pgop3m80rmutewxn) (version source revid:alik@sun.com-20100901130033-8k19cjn6n2blm3py) (pib:21)

Pushed into mysql-5.5 5.5.7-m3 (revid:alik@sun.com-20100901125952-4hsrosoa0xreionr) (version source revid:alik@sun.com-20100901125952-4hsrosoa0xreionr) (merge vers: 5.5.7-m3) (pib:21)

Noted in 5.1.51, 5.5.7, 5.6.1 changelogs.

MIN() or MAX() with a subquery argument could raise a debug assertion
for debug builds or return incorrect data for nondebug builds.

Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Already documented in the 5.1.51 changelog. No new changelog entries required. Reverting to Closed state.