MySQL Bugs: #16258: WITH CHECK OTION fails in MS Access

Bug #16258	WITH CHECK OTION fails in MS Access
Submitted:	6 Jan 2006 16:16	Modified:	24 Jul 2007 19:21
Reporter:	gernot adams	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.11-2 and 3.51.12	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any
Tags:	ODBC5-RC

Description:
hi,
i've created a view with CHECK OPTION:

CREATE VIEW viewPatient
AS
SELECT
   PatientNo,
   Sex,
   DateBirth
FROM
   tblPatient
WHERE
   (YEAR(CURDATE())-YEAR(DateBirth)) -
(RIGHT(CURDATE(),5)<RIGHT(DateBirth,5))<150
OR
   (YEAR(CURDATE())-YEAR(DateBirth)) -
(RIGHT(CURDATE(),5)<RIGHT(DateBirth,5))>17
WITH CHECK OPTION
;

When i try to insert a dataset with the MySQL client in this view where the age is >150 or <18 then the insertion fails and the check option works fine.

Now i link this view via ODBC in MS Access (File -> Get external Data -> Link Tables) and do the following statement (in Access-style):

INSERT INTO viewPatient (PatientNo, Sex, DateBirth) VALUES ("345", "m", #1/1/1850#) 

then the CHECK OPTION doesn't work and the dataset will be inserted.

When i insert the same data via a Pass-Through-Query then the CHECK OPTION works fine again.

Do you have an idea on that?

cheers
gernot

How to repeat:
Create a view with a check option. Link this view in MS Access via File -> Get external Data -> Link Tables. Insert data that violates the check.

I was able to reproduce the bug. I was able to insert fail value from MS Access.

mysql> select * from tblPatient;
+-----------+------+------------+
| PatientNo | Sex  | DateBirth  |
+-----------+------+------------+
|       345 | m    | 1850-01-01 |
+-----------+------+------------+
1 row in set (0.00 sec)

but from mysql client:

mysql> INSERT INTO viewPatient (PatientNo, Sex, DateBirth) VALUES ("346", "f",01
011851);
ERROR 1369 (HY000): CHECK OPTION failed 'test.viewpatient'

I would like to notice probably you need to use "AND" instead of "OR" in "CREATE VIEW" and use RIGHT(DATE_FORMAT(CURDATE(),"%m%d"),5) instead of RIGHT(CURDATE(),5)

This is not repeatable with Connector/ODBC 3.51.17 and Microsoft Access 2003 once the view definition is fixed to use 'AND' instead of 'OR'.