MySQL Bugs: #28682: Partitions: failure with extract function and sql

Bug #28682	Partitions: failure with extract function and sql_mode change
Submitted:	25 May 2007 18:13	Modified:	17 Oct 2007 13:17
Reporter:	Peter Gulutzan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	6.0.1-alpha-debug/5.1BK	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I create a partitioned table with one date column.
The partitioning function depends on EXTRACT().
I insert '0000-00-00'.
I change sql_mode so that '0000-00-00' can't be input.
I try to select where the date column is '0000-00-00'.
I find nothing.

How to repeat:
mysql> drop table if exists t18;
Query OK, 0 rows affected (0.00 sec)

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

mysql> create table t18 (s1 date)
    -> partition by list (extract(year from s1))
    -> (partition p1 values in (0));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t18 values ('0000-00-00');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from t18 where s1 = '0000-00-00';
Empty set, 2 warnings (0.00 sec)

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

mysql> alter table t18 remove partitioning;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> select * from t18 where s1 = '0000-00-00';
+------------+
| s1         |
+------------+
| 0000-00-00 |
+------------+
1 row in set, 2 warnings (0.01 sec)

Thank you for the bug report. Verified as described on FC 6 32-bit.

The fix for #28666 is already in mysql-5.1-runtime

Duplicate of bug#28687 .
The partitioning is doing indexed search.