MySQL Bugs: #48166: Partitions: error message refers to value after function completion

Bug #48166	Partitions: error message refers to value after function completion
Submitted:	19 Oct 2009 18:28	Modified:	14 Jan 2010 11:03
Reporter:	Peter Gulutzan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
I'm using mysql-trunk-wl3352.
I create a partitioned table with a to_seconds function.
I try to insert a value that isn't allowed for the partition.
I get the error message "Table has no partition for value NULL".
But I'm not trying to insert NULL. NULL is the result of to_seconds().
As an inserter, I'd have no idea why the error message mentions NULL.

How to repeat:
drop table if exists t1;
create table t1 (a time) partition by list(to_seconds(a))
(partition p1 values in (0));
insert into t1 values ('12:00:00');

Thank you for the bug report.

miguel@laras:~/dbs/5.4-trunk$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.4.3-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (a time) partition by list(to_seconds(a))
    -> (partition p1 values in (0));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values ('12:00:00');
ERROR 1527 (HY000): Table has no partition for value NULL
mysql>

This also exists with partitioning in 5.1 (current mysql-5.1-bugteam):
DROP TABLE IF EXISTS t1;
Warnings:
Note	1051	Unknown table 't1'
CREATE TABLE t1 (a DATETIME) PARTITION BY LIST(TO_DAYS(a))
(PARTITION p1 VALUES IN (0));
INSERT INTO t1 VALUES ('2000-01-01 12:00:00');
ERROR HY000: Table has no partition for value 730485
DROP TABLE t1;

as the inserter, the value 730485 is nonsense...

I suggest to leave the info, but change the string to something like:
Table has no partition for the result 'X' from the partitioning expression

Peter, is that acceptable, or should we simply replace the string with (ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT):
Table has no partition for some existing values
?

The message
Table has no partition for the result 'X' from the partitioning expression
looks good.

I only find the current message confusing in the TO_SECONDS case,
but I agree that the fix could apply for similar cases in version 5.1.

This bug is against MySQL 5.1 as well and isn't
special for WL#3352.