MySQL Bugs: #15142: Partitions: crash if list(convert

Bug #15142	Partitions: crash if list(convert_tz)
Submitted:	22 Nov 2005 16:54	Modified:	3 Apr 2006 12:04
Reporter:	Peter Gulutzan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.2-alpha-debug-log	OS:	Linux (SUSE 10.0)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If I partition by list, using an expression that includes 'convert_tz', crash.

The expression returns a number (0) that is listed for the first partition,
so this doesn't appear to be the same as bug#14365.

Also: I used fixed time zones in my convert_tz example, but I expect that if I
used 'system' then results would not be deterministic. Maybe
support of 'convert_tz' is not possible.

How to repeat:
mysql> select extract(hour from convert_tz('2004-01-01 00:00','+00:00','+00:00'));
+---------------------------------------------------------------------+
| extract(hour from convert_tz('2004-01-01 00:00','+00:00','+00:00')) |
+---------------------------------------------------------------------+
|                                                                   0 |
+---------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> create table tj (s1 datetime) partition by list (extract(hour from convert_tz(s1,'+00:00','+00:00'))) (partition p1 values in (0),partition p2 values in (1));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tj values ('2004-01-01 00:00:00');
ERROR 2013 (HY000): Lost connection to MySQL server during query

Take this into account too:

With this example from the docs

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

server will crash if a value '0' (zero) NULL or <no value at all> for store_id is entered with an INSERT statement

like 

insert into `test`.`employees` ( `id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id` ) values (  '',  'e',  'f',  '1970-01-01',  '9999-12-31',  '',  '0' )

or 

insert into `test`.`employees` ( `id`, `fname`, `lname`, `hired`, `separated`, `job_code`, `store_id` ) values (  '',  'e',  'f',  '1970-01-01',  '9999-12-31',  '',  NULL )

or

insert into `test`.`employees` ( `id`, `fname`, `lname`, `hired`, `separated`, `job_code` ) values (  '',  'e',  'f',  '1970-01-01',  '9999-12-31',  '' )

The comment from Peter Laursen looks like an example of bug#14365.

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

This will be fixed by not allowing convert_tz in partition function and this will be fixed by
bug18198 so this bug is put in Duplicate