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

[25 May 2007 18:13] Peter Gulutzan
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)
[25 May 2007 19:02] Miguel Solorzano
Thank you for the bug report. Verified as described on FC 6 32-bit.
[25 May 2007 21:38] Andrey Hristov
The fix for #28666 is already in mysql-5.1-runtime
[17 Oct 2007 13:17] Mattias Jonsson
Duplicate of bug#28687 .
The partitioning is doing indexed search.