Bug #5992 Views with check option: trigger trouble

• Submitted: 8 Oct 2004 21:10
• Modified: 22 Oct 2004 7:49
• Reporter: Peter Gulutzan
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha-debug
• OS: Linux (SuSE 8.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[8 Oct 2004 21:10] Peter Gulutzan

Description:
If I have both a trigger and a view with check option, 
and I insert something which causes trigger activation 
which in turn would violate the check option, the 
insert fails -- but there is no error message saying 
that the insert failed. I get "1 row affected, 1 
warning". 

How to repeat:
mysql> create table tm (s1 tinyint); 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> create trigger tm_bi before insert on tm for each row set new.s1 = 500; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create view vm as select * from tm where s1 <> 127 with check option; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into vm values (0); 
Query OK, 1 row affected, 1 warning (0.00 sec) 
 
mysql> show warnings; 
+---------+------+------------------------------------------------------+ 
| Level   | Code | Message                                              | 
+---------+------+------------------------------------------------------+ 
| Warning | 1264 | Out of range value adjusted for column 's1' at row 1 | 
+---------+------+------------------------------------------------------+ 
1 row in set (0.00 sec) 
 
mysql> select * from vm; 
Empty set (0.00 sec) 
 
mysql> select @@sql_mode; 
+------------+ 
| @@sql_mode | 
+------------+ 
|            | 
+------------+ 
1 row in set (0.00 sec)

[8 Oct 2004 21:57] MySQL Verification Team

Verified on latest BK source.

[21 Oct 2004 11:59] Oleksandr Byelkin

I can't understand which relation this problem have to view? 
trigger fire after WITH CHECK OPTION check for view, when value go to underlying 
_table_. how VIEW can be responsible for underlying table mechanism? 
 
+ create table t1 (s1 tinyint); 
+ create trigger t1_bi before insert on t1 for each row set new.s1 = 500; 
+ create view v1 as select * from t1 where s1 <> 127 with check option; 
+ insert into v1 values (0); 
+ Warnings: 
+ Warning       1264    Out of range value adjusted for column 's1' at row 1 
+ select * from v1; 
+ s1 
+ select * from t1; 
+ s1 
+ 127 
+ insert into t1 values (0); 
+ Warnings: 
+ Warning       1264    Out of range value adjusted for column 's1' at row 1 
+ select * from t1; 
+ s1 
+ 127 
+ 127 
+ select * from v1; 
+ s1

[21 Oct 2004 13:43] Oleksandr Byelkin

Ok, thank you for comment, I'll fix it

[21 Oct 2004 14:06] Oleksandr Byelkin

ChangeSet 
  1.1668 04/10/21 17:05:45 [e-mail] +3 -0 
  value should be checked after 'before' trigger work (BUG#5992)

[22 Oct 2004 7:49] Oleksandr Byelkin

Thank you for bugreport! Bug is fixed, patch is pushed into our source repository.