The transaction itself:

CREATE PROCEDURE `prog_transact`(vprog_name varchar(255), yprog_cy year, vprog_purp varchar (255), vprog_dt varchar (255), ilic_id int, vprog_vers varchar (63), vprog_ff varchar (255), vprog_website varchar (511), vprog_interface varchar(127), iarch_id int, idev_id int, ipl_id int, ios_id int) begin declare iprog_id int; declare exit handler for sqlexception begin rollback; select 'transaction not commited: sqlexception'; show errors; end; start transaction; if ilic_id=null or iarch_id=null or idev_id=null or ipl_id=null or ios_id=null then rollback; select 'transaction not commited: nulldata'; end if; insert into program (name, creation_year, purpose, dist_type, lic_id, version, file_formats, website, interface) values (vprog_name, yprog_cy, vprog_purp, vprog_dt, ilic_id, vprog_vers, vprog_ff, vprog_website, vprog_interface); select max(id) into iprog_id from program; insert into arch_prog (arch_id, prog_id) values (iarch_id, iprog_id); insert into dev_prog (dev_id, prog_id) values (idev_id, iprog_id); insert into pl_prog (pl_id, prog_id) values (ipl_id, iprog_id); insert into os_prog (os_id, prog_id) values (ios_id, iprog_id); commit; end

In theory, if the last variables in the procedure are set as null 's, then the transaction should be rolled back. No matter how wrong!

 call prog_transact('Spore',2008,'Компьютерная игра','коммерческое',11,'1.05.1',null,'spore.com','графический', null, null, null, null);

... and the transaction is successfully confirmed! Why is the condition not checked above?

    1 answer 1

    You cannot compare a value with NULL using the = operator. A comparison with an undefined value is always an undefined value. To find out if a value accepts a NULL value, use the special operator IS NULL and its negative form IN NOT NULL

     SELECT NULL IS NULL, NULL IS NOT NULL, NULL = NULL, NULL <> NULL; +--------------+------------------+-------------+--------------+ | NULL IS NULL | NULL IS NOT NULL | NULL = NULL | NULL <> NULL | +--------------+------------------+-------------+--------------+ | 1 | 0 | NULL | NULL | +--------------+------------------+-------------+--------------+