Bug #75046 Date-type coercion breaks constraint

• Submitted: 28 Nov 2014 22:39
• Modified: 29 Dec 2014 6:24
• Reporter: Ariane van der Steldt
• Status: No Feedback
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.6.20
• OS: Any
• CPU Architecture: Any
• Tags: coercion, constraint, date, MariaDB, MySQL

[28 Nov 2014 22:39] Ariane van der Steldt

Description:
http://nahratzah.wordpress.com/2014/11/28/mysql-constraints-and-type-coercion/

How to repeat:
See description.

Suggested fix:
- Fail query on invalid type cast.
- Move constraint validation after type coercion.  (Or if it already is, fix it to recognize all-zero date value as null.)
- Enforce for all type conversions that they only yield null values if the input is null and never yield null values if the input is non-null.

[29 Nov 2014 6:24] MySQL Verification Team

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
For INSERTS you could have used a strict mode.  Please see results of this
and check if that is how you expected it to work.  
Note, strict mode does not apply to SELECT.

---
drop table if exists t1;
create table t1(id int primary key,a date not null)engine=innodb;
set sql_mode='';
insert into t1 values(1,null);
insert into t1 values(2,"--");
set sql_mode='STRICT_ALL_TABLES';
insert into t1 values(5,null);
insert into t1 values(6,"--");
select * from t1;
----

[30 Dec 2014 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".