MySQL Bugs: #31068: "select into" syntax doesn't allow new.* variables as target

Bug #31068	"select into" syntax doesn't allow new.* variables as target
Submitted:	17 Sep 2007 20:09	Modified:	18 Sep 2007 4:43
Reporter:	Mack Earnhardt	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.32, 5.0.44	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
The following syntax results in ERROR 1327 (42000): Undeclared variable: new

create trigger messages_insert 
before insert on messages for each row 
  select sm.id 
   into new.shared_message_id 
   from shared_messages sm 
  where sm.mail_id = new.mail_id;

Here is my workaround:

create trigger messages_insert 
before insert on messages for each row 
begin
  declare temp_id int;
  select sm.id 
    into temp_id 
    from shared_messages sm 
   where sm.mail_id = new.mail_id;
  set new.shared_message_id = temp_id;
end;

How to repeat:
See example above...

Thank you for a prblem report. I was able to repeat the behaviour described, but I can not call it a bug, as manual clearly says (http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html):

"A column named with NEW can be referred to if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it."

Looks like SET is the only intended way to change NEW.col values. I agree, though, that SELECT INTO should be also supported. So, I think, this is a reasonable feature request.