Bug #23652 Partitions: intermittent use of ansi_quotes causes errors
Submitted: 25 Oct 2006 22:31 Modified: 17 Oct 2007 13:44
Reporter: Peter Gulutzan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.1.13-beta-debug OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: Assigned Account CPU Architecture:Any

[25 Oct 2006 22:31] Peter Gulutzan 
Description:
I set sql_mode=ansi_quotes.
I create a table and use "quotes" in the partiiton function.
I set sql_mode=''.
I try to insert into the table.
Failure.

How to repeat:
mysql> create table tnx (s1 int) partition by list ("s1") (partition p1 values in (1));
Query OK, 0 rows affected (0.01 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tnx values (1);
ERROR 1473 (HY000): Constant/Random expression in (sub)partitioning function is not allowed
mysql> set sql_mode=ansi_quotes;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tnx values (1);
Query OK, 1 row affected (0.00 sec)
[26 Oct 2006 1:18] MySQL Verification Team 
Thank you for the bug report.

mysql> set sql_mode=ansi_quotes;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tnx (s1 int) partition by list ("s1") (partition p1 values
    -> in (1));
Query OK, 0 rows affected (0.01 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tnx values (1);
ERROR 1473 (HY000): Constant/Random expression in (sub)partitioning function is not allowed
mysql> set sql_mode=ansi_quotes;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tnx values (1);
Query OK, 1 row affected (0.00 sec)

mysql>
[17 Oct 2007 13:44] Mattias Jonsson 
This is the current behavior according to:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

I will probably be fixed in a later version.