MySQL Bugs: #45807: crash accessing partitioned table and sql_mode contains ONLY_FULL_GROUP

Bug #45807	crash accessing partitioned table and sql_mode contains ONLY_FULL_GROUP_BY
Submitted:	28 Jun 2009 13:12	Modified:	29 Jul 2009 10:48
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.30, 5.1.37, 6.0.12	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	ONLY_FULL_GROUP_BY

Description:
mysqld.exe!Item_field::fix_fields()[item.cc:4415]
mysqld.exe!fix_fields_part_func()[sql_partition.cc:968]
mysqld.exe!fix_partition_func()[sql_partition.cc:1624]
mysqld.exe!open_table_from_share()[table.cc:1835]
mysqld.exe!ha_create_table()[handler.cc:3547]
mysqld.exe!rea_create_table()[unireg.cc:418]
mysqld.exe!mysql_create_table_no_lock()[sql_table.cc:3803]
mysqld.exe!mysql_create_table()[sql_table.cc:3908]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2696]
mysqld.exe!mysql_parse()[sql_parse.cc:5933]
mysqld.exe!dispatch_command()[sql_parse.cc:1213]
mysqld.exe!do_command()[sql_parse.cc:854]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:293]
mysqld.exe!_threadstart()[thread.c:277]
kernel32.dll!FlsSetValue()

How to repeat:
drop table if exists t1;
set session sql_mode='ONLY_FULL_GROUP_BY';
create table t1(id int,key(id))engine=myisam partition by hash(id) partitions 2;

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

2988 Georgi Kodinov	2009-06-30
      Bug #45807: crash accessing partitioned table and sql_mode
      contains ONLY_FULL_GROUP_BY
      
      The partitioning code needs to issue a Item::fix_fields()
      on the partitioning expression in order to prepare 
      it for being evaluated.
      It does this by creating a special table and a table list 
      for the scope of the partitioning expression.
      In doing so it was not initializing the cacheable_table
      flag to true and this caused problems with the code 
      in Item_field::fix_fields() that calculates if the 
      ONLY_FULL_GROUP_BY mode constraints are being met.
      Fixed by rectifying the following errors :
      1. Item_field::fix_fields() : the code that check for 
      ONLY_FULL_GROUP_BY relies on having tables with 
      cacheable_table set. This is mostly true, the only 
      two exceptions being the partitioning context table
      and the trigger context table.
      Fixed by taking the current parsing context if no link
      to the TABLE_LIST instance is present in the cached_table.
      
      2. fix_fields_part_func() : 
      
      2a. The code that adds the table being created to the 
      scope for the partitioning expression is mostly a copy 
      of the add_table_to_list and friends with one exception :
      it was not marking the table as cacheable (something that
      normal add_table_to_list is doing). This caused the 
      problem in the check for ONLY_FULL_GROUP_BY in 
      Item_field::fix_fields() to appear.
      Fixed by setting the correct members to make the table
      cacheable.
      The ideal structural fix for this is to use a unified 
      interface for adding a table to a table list 
      (add_table_to_list?) : noted in a TODO comment
      
      2b. The Item::fix_fields() was called with a NULL destination
      pointer. This causes problems in the overloaded ::fix_fields()
      function (namely Item_field::fix_fields()) as it expects a 
      non-zero pointer there. Fixed by passing the source pointer
      similarly to how it's done in JOIN::prepare().
     @ mysql-test/r/partition.result
        Bug #45807: test case
     @ mysql-test/t/partition.test
        Bug #45807: test case
     @ sql/item.cc
        Bug #45807: fix the ONLY_FULL_GROUP_BY check code to 
        handle correctly non-cacheable tables.
     @ sql/sql_partition.cc
        Bug #45807: fix the Item::fix_fields() context
        initializatio for the partitioning expression in 
        CREATE TABLE.

Approved but please take into account the comments in my review.
They are only about comments/explanations.

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

2988 Georgi Kodinov	2009-07-02
      Bug #45807: crash accessing partitioned table and sql_mode
      contains ONLY_FULL_GROUP_BY
      
      The partitioning code needs to issue a Item::fix_fields()
      on the partitioning expression in order to prepare 
      it for being evaluated.
      It does this by creating a special table and a table list 
      for the scope of the partitioning expression.
      But when checking ONLY_FULL_GROUP_BY the 
      Item_field::fix_fields() was relying that there always be
      cached_table set and was trying to use it to get the 
      select_lex of the SELECT the field's table is in.
      But the cached_table was not set by the partitioning code
      that creates the artificial TABLE_LIST used to resolve the
      partitioning expression and this resulted in a crash.
       
      Fixed by rectifying the following errors :
      1. Item_field::fix_fields() : the code that check for 
      ONLY_FULL_GROUP_BY relies on having tables with 
      cacheable_table set. This is mostly true, the only 
      two exceptions being the partitioning context table
      and the trigger context table.
      Fixed by taking the current parsing context if no pointer
      to the TABLE_LIST instance is present in the cached_table.
      
      2. fix_fields_part_func() : 
      
      2a. The code that adds the table being created to the 
      scope for the partitioning expression is mostly a copy 
      of the add_table_to_list and friends with one exception :
      it was not marking the table as cacheable (something that
      normal add_table_to_list is doing). This caused the 
      problem in the check for ONLY_FULL_GROUP_BY in 
      Item_field::fix_fields() to appear.
      Fixed by setting the correct members to make the table
      cacheable.
      The ideal structural fix for this is to use a unified 
      interface for adding a table to a table list 
      (add_table_to_list?) : noted in a TODO comment
      
      2b. The Item::fix_fields() was called with a NULL destination
      pointer. This causes uninitalized memory reads in the 
      overloaded ::fix_fields() function (namely 
      Item_field::fix_fields()) as it expects a non-zero pointer 
      there. Fixed by passing the source pointer similarly to how 
      it's done in JOIN::prepare().
     @ mysql-test/r/partition.result
        Bug #45807: test case
     @ mysql-test/t/partition.test
        Bug #45807: test case
     @ sql/item.cc
        Bug #45807: fix the ONLY_FULL_GROUP_BY check code to 
        handle correctly non-cacheable tables.
     @ sql/sql_partition.cc
        Bug #45807: fix the Item::fix_fields() context
        initializatio for the partitioning expression in 
        CREATE TABLE.

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

2993 Georgi Kodinov	2009-07-02
      Bug #45807: crash accessing partitioned table and sql_mode
      contains ONLY_FULL_GROUP_BY
      
      The partitioning code needs to issue a Item::fix_fields()
      on the partitioning expression in order to prepare 
      it for being evaluated.
      It does this by creating a special table and a table list 
      for the scope of the partitioning expression.
      But when checking ONLY_FULL_GROUP_BY the 
      Item_field::fix_fields() was relying that there always be
      cached_table set and was trying to use it to get the 
      select_lex of the SELECT the field's table is in.
      But the cached_table was not set by the partitioning code
      that creates the artificial TABLE_LIST used to resolve the
      partitioning expression and this resulted in a crash.
       
      Fixed by rectifying the following errors :
      1. Item_field::fix_fields() : the code that check for 
      ONLY_FULL_GROUP_BY relies on having tables with 
      cacheable_table set. This is mostly true, the only 
      two exceptions being the partitioning context table
      and the trigger context table.
      Fixed by taking the current parsing context if no pointer
      to the TABLE_LIST instance is present in the cached_table.
      
      2. fix_fields_part_func() : 
      
      2a. The code that adds the table being created to the 
      scope for the partitioning expression is mostly a copy 
      of the add_table_to_list and friends with one exception :
      it was not marking the table as cacheable (something that
      normal add_table_to_list is doing). This caused the 
      problem in the check for ONLY_FULL_GROUP_BY in 
      Item_field::fix_fields() to appear.
      Fixed by setting the correct members to make the table
      cacheable.
      The ideal structural fix for this is to use a unified 
      interface for adding a table to a table list 
      (add_table_to_list?) : noted in a TODO comment
      
      2b. The Item::fix_fields() was called with a NULL destination
      pointer. This causes uninitalized memory reads in the 
      overloaded ::fix_fields() function (namely 
      Item_field::fix_fields()) as it expects a non-zero pointer 
      there. Fixed by passing the source pointer similarly to how 
      it's done in JOIN::prepare().
     @ mysql-test/r/partition.result
        Bug #45807: test case
     @ mysql-test/t/partition.test
        Bug #45807: test case
     @ sql/item.cc
        Bug #45807: fix the ONLY_FULL_GROUP_BY check code to 
        handle correctly non-cacheable tables.
     @ sql/sql_partition.cc
        Bug #45807: fix the Item::fix_fields() context
        initializatio for the partitioning expression in 
        CREATE TABLE.

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:bernt.johnsen@sun.com-20090703083334-4mmotptrkcpa3skh) (merge vers: 5.1.37) (pib:11)

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:bernt.johnsen@sun.com-20090703083334-4mmotptrkcpa3skh) (merge vers: 5.1.37) (pib:11)

Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:joro@sun.com-20090702155104-ppfey90mo3b7qmvl) (merge vers: 5.4.4-alpha) (pib:11)

Documented bugfix in the 5.1.37 and 5.4.4 changelogs as follows:

        Accessing a table having user-defined partitioning when the
        server SQL mode included ONLY_FULL_GROUP_BY caused the
        MySQL server to crash. For example, the following sequence of
        statements crashed the server:

        DROP TABLE IF EXISTS t1;

        SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY';

        CREATE TABLE t1 (id INT, KEY(id))
            PARTITION BY HASH(id) PARTITIONS 2;

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed into 5.4.2.