After installing 12.10 point release existing databases started
to give syntax errors on existing triggers, when using
FOR VAR IN NEW.FIELD1..NEW.FIELD2 DO
loop. It works, when there are spaces around ".." - like:
FOR VAR IN NEW.FIELD1 .. NEW.FIELD2 DO
It also still works without spaces, when used with declared variables or
constants.
Has anyone else run into it? And can anyone test it with Debian testing?
Bug reporting seems to want know it.
Easiest way to duplicate it is (there are also examples of working
ways):
-----------------------------------------------------------------------
CREATE TABLE TESTDATA (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
START_VALUE INT NOT NULL,
END_VALUE INT NOT NULL,
TOTAL INT DEFAULT NULL,
FIX_TOTAL INT DEFAULT NULL
) ENGINE=InnoDB;
COMMIT;
DELIMITER ^
DROP TRIGGER IF EXISTS BI_TESTDATA^
CREATE OR REPLACE TRIGGER BI_TESTDATA
BEFORE INSERT ON TESTDATA FOR EACH ROW
BEGIN
DECLARE VAL INT;
DECLARE LB INT;
DECLARE HB INT;
SET NEW.TOTAL = 0;
SET NEW.FIX_TOTAL = 0;
FOR VAL IN 1..10 DO -- works with constants..
SET NEW.FIX_TOTAL = NEW.FIX_TOTAL + VAL;
END FOR;
SET LB=NEW.START_VALUE;
SET HB=NEW.START_VALUE;
FOR VAL IN LB..HB DO -- works this vay
SET NEW.TOTAL = NEW.TOTAL + VAL;
END FOR;
/*
* https://mariadb.com/kb/en/for/ examples have no space between bounds and ..
* but there is syntax error with NEW.
* */
SET NEW.TOTAL = 0;
FOR VAL IN NEW.START_VALUE..NEW.END_VALUE DO -- used to work last version.
-- FOR VAL IN NEW.START_VALUE .. NEW.END_VALUE DO -- this would work in
newest version.
SET NEW.TOTAL = NEW.TOTAL + VAL;
END FOR;
END^
DELIMITER ;
COMMIT;
-----------------------------------------------------------------------
--
Virgo Pärna
virgo.pa...@mail.ee
