Bug #20340 Paritioning does not obey to column type
Submitted: 8 Jun 2006 12:58 Modified: 26 Jul 2006 0:44
Reporter: Andrey Hristov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.12-BK, 5.1.11 OS:Any (all)
Assigned to: Mikael Ronström CPU Architecture:Any

[8 Jun 2006 12:58] Andrey Hristov
Description:
NODEGROUP defined as LONGLONG used as STRING

sql_show.cc:

ST_FIELD_INFO partitions_fields_info[]=
{
...
  {"NODEGROUP", 21 , MYSQL_TYPE_LONG, 0, 0, 0},
...
}

static void store_schema_partitions_record(THD *thd, TABLE *table,
                                           partition_element *part_elem,
                                           handler *file, uint part_id)
{
...
    if (part_elem->nodegroup_id != UNDEF_NODEGROUP)
      table->field[23]->store((longlong) part_elem->nodegroup_id, TRUE);
    else
      table->field[23]->store(STRING_WITH_LEN("default"), cs);
...
}

This causes strange errors with the following test case:
  DELIMITER //
  CREATE TABLE t9 (s1 int)//

  CREATE FUNCTION `partition_exists`(p_p_name varchar(64))
  RETURNS int(11)
  return (select count(*) from information_schema.partitions where
partition_name = p_p_name) //

  CREATE PROCEDURE p9 ()
  begin
    declare continue handler for sqlexception insert into t9 values
(partiton_exists('p2'));
    insert into t9 values (partition_exists('p1'));
    end//
  create event ex on schedule every 1 second do call p9()//
  set global event_scheduler = 1//

How to repeat:
  DELIMITER //
  CREATE TABLE t9 (s1 int)//

  CREATE FUNCTION `partition_exists`(p_p_name varchar(64))
  RETURNS int(11)
  return (select count(*) from information_schema.partitions where
partition_name = p_p_name) //

  CREATE PROCEDURE p9 ()
  begin
    declare continue handler for sqlexception insert into t9 values
(partiton_exists('p2'));
    insert into t9 values (partition_exists('p1'));
    end//
  create event ex on schedule every 1 second do call p9()//
  set global event_scheduler = 1//

060608 13:47:05 [Warning] SCHEDULER: [`root`@`localhost`][`db1`.`ex`] Out of range value for column 'NODEGROUP' at row -1515870811]
[11 Jun 2006 8:18] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.1.12-BK on Linux:

openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> CREATE TABLE t9 (s1 int)//
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION `partition_exists`(p_p_name varchar(64))
    ->   RETURNS int(11)
    ->   return (select count(*) from information_schema.partitions where
    -> partition_name = p_p_name) //
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE p9 ()
    ->   begin
    ->     declare continue handler for sqlexception insert into t9 values
    -> (partiton_exists('p2'));
    ->     insert into t9 values (partition_exists('p1'));
    ->     end//
Query OK, 0 rows affected (0.01 sec)

mysql> create event ex on schedule every 1 second do call p9()//
Query OK, 1 row affected (0.01 sec)

mysql> set global event_scheduler = 1//
Query OK, 0 rows affected (0.06 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.1> tail var/suse.err
060611 10:01:10 [Warning] SCHEDULER: [`root`@`localhost`][`test`.`ex`] Out of range value for column 'NODEGROUP' at row -1515870811]
060611 10:01:10 [Warning] SCHEDULER: [`root`@`localhost`][`test`.`ex`] Out of range value for column 'NODEGROUP' at row -1515870811]
060611 10:01:10 [Warning] SCHEDULER: [`root`@`localhost`][`test`.`ex`] Out of range value for column 'NODEGROUP' at row -1515870811]
060611 10:01:10 [Warning] SCHEDULER: [`root`@`localhost`][`test`.`ex`] Out of range value for column 'NODEGROUP' at row -1515870811]
060611 10:01:10 [Warning] SCHEDULER: [`root`@`localhost`][`test`.`ex`] Out of range value for column 'NODEGROUP' at row -1515870811]
060611 10:01:10 [Warning] SCHEDULER: [`root`@`localhost`][`test`.`ex`] Out of range value for column 'NODEGROUP' at row -1515870811]
060611 10:01:10 [Warning] SCHEDULER: [`root`@`localhost`][`test`.`ex`] ]
060611 10:01:10 [Warning] SCHEDULER: [`root`@`localhost`][`test`.`ex`] Out of range value for column 'NODEGROUP' at row -1515870811]
060611 10:01:10 [ERROR] SCHEDULER: [`root`@`localhost`][`test`.`ex`] FUNCTION test.partiton_exists does not exist]
060611 10:01:10 [Note] SCHEDULER: [test.ex of root@localhost] executed. RetCode=1
[22 Jul 2006 21:00] Mikael Ronström
Will appear in 5.1.12
[26 Jul 2006 0:44] Jon Stephens
Documented bugfix in 5.1.12 changelog.