MySQL Bugs: #46968: 'Incorrect information in file' with partitioned table created with ANSI

Bug #46968	'Incorrect information in file' with partitioned table created with ANSI_QUOTES
Submitted:	28 Aug 2009 5:14	Modified:	28 Aug 2009 6:23
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1.39, 6.0.12, next bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ANSI_QUOTES, Incorrect information in file

Description:
testcase outputs this error:

mysql> show create table `t1`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"1" VALUES LESS THAN (1) ENGINE = MyISAM)' at line 2
mysql>

mysql error log contains this:

090828  7:13:14 [ERROR] mysqld: Incorrect information in file: '.\test\t1.frm'

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` int)engine=myisam;
set session sql_mode='ANSI_QUOTES';
alter table `t1` partition by range(`a`)(partition `1` values less than (1));
set session sql_mode='';
show create table `t1`;

Thank you for the report.

Verified as described.

As the MySQL Reference Manual says:
"Tables employing user-defined partitioning do not preserve the SQL mode in effect at the time that they were created. As discussed in Section 5.1.8, “Server SQL Modes”, the results of many MySQL functions and operators may change according to the server SQL mode. Therefore, a change in the SQL mode at any time after the creation of partitioned tables may lead to major changes in the behavior of such tables, and could easily lead to corruption or loss of data. For these reasons, it is strongly recommended that you never change the server SQL mode after creating partitioned tables."
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

Updated docs as requested by James: http://lists.mysql.com/commits/96856

Related to bug#18336321 (fix exists in rb#5797).