MySQL Bugs: #32677: InnoDB engine mistakenly applies column constraint to column in where clause

Bug #32677	InnoDB engine mistakenly applies column constraint to column in where clause
Submitted:	23 Nov 2007 18:37	Modified:	30 Nov 2007 11:33
Reporter:	Simon Grantham	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.38	OS:	Linux (Ubuntu 7.04)
Assigned to:		CPU Architecture:	Any
Tags:	date, innodb

Description:
Innodb engine will erroneously generate a column constraint error on a comparison in a where clause.  It happens only when a DATE() call returns NULL and is compared to a column with a not null constraint.

How to repeat:
Try this:

CREATE TABLE test(someDate DATETIME PRIMARY KEY) ENGINED=InnoDB;
     -- create test table

SELECT * FROM test WHERE someDate = DATE(NULL);
     -- fails with "Error: Column 'someDate' must not be null"

SELECT * FROM test WHERE someDate = IF(DATE(NULL) IS NULL, NULL, NULL);
     -- succeeds (and of course, no rows returned)

Suggested fix:
Not sure, however a work around for those that may have incoming null
procedure parameters that have this kind of select statement is use change

WHERE myContstrainedColumn = DATE(myParameter)

-- to

WHERE myConstrainedColumn = IF(DATE(someParameter) IS NULL, NULL, DATE(someParameter))

-- which will work

Please, try to repeat with a newer version, 5.0.45 at least. I can not repeat the behaviour described on any recent version:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.0.44-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test(someDate DATETIME PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (1.14 sec)

mysql> SELECT * FROM test WHERE someDate = DATE(NULL);
Empty set (0.14 sec)

Okay, I installed 5.0.45 on a Ubuntu machine and the problem did not occur which is great.  However, I would suggest it might be worth testing on a 64 bit machine which I am unable to do as I cannot, at this time, upgrade mysql on our AMD64 servers. (Perhaps its a problem of bits being set in the upper half of what was intended to be a 32 bit value?)

This is probably a bug in MySQL's interpreter.

Thank you for the feedback.

I additionally tested it on 32-bit machine. Bug is repeatable with version 5.0.38, 5.0.41 and not repeatable since version 5.0.42. So I'll close the report as "Can't repeat"