-- BUG TEST CASE

-- 1. test schema

DROP SCHEMA IF EXISTS testdata;

CREATE SCHEMA IF NOT EXISTS testdata;

USE testdata;

-- 2. test table with a column that is set by a trigger value

DROP TABLE IF EXISTS test_table;

CREATE TABLE test_table (

	`ID` int(11) not null AUTO_INCREMENT,

    `Name` varchar(150) not null,

    `TriggerSet` varchar(75),

	PRIMARY KEY (`ID`)

) ENGINE = InnoDB;

-- 3. stored function that does something

DELIMITER $$

DROP FUNCTION IF EXISTS test$$

CREATE DEFINER = `root`@`localhost` FUNCTION `test`()

RETURNS VARCHAR(50)

READS SQL DATA

BEGIN

	RETURN 'Result';

END$$

DELIMITER ;

-- 4. the trigger that calls the stored function and sets the result to a column value 

DELIMITER $$

DROP TRIGGER IF EXISTS testdata.test_table_update_before$$

CREATE TRIGGER testdata.test_table_update_before BEFORE UPDATE ON testdata.test_table

FOR EACH ROW

BEGIN

	SET NEW.TriggerSet = (SELECT testdata.test());

END$$

DELIMITER ;

-- 5. test data

INSERT INTO testdata.test_table (Name)

VALUES

('1'), ('2'), ('3'), ('4'), ('5');

-- 6. a query to form another table that has modifications

DROP TABLE IF EXISTS scratch.`modify`;

CREATE TABLE scratch.modify (PRIMARY KEY (`ID`))

SELECT ID, CONCAT(Name,'_') Name FROM testdata.test_table WHERE ID BETWEEN 2 AND 4;

-- 7. this update FAILS in MariaDB 10.2.24 but not 10.2.23

UPDATE testdata.test_table t 

INNER JOIN scratch.modify m USING (ID) 

SET t.Name = m.Name;

-- 8. the resulting table that should have the modifications, but in 10.2.24, the update statement above fails

SELECT * FROM testdata.test_table;

-- 9. this is the same function that is called in the trigger, it succeeds when called on its own

SELECT testdata.test();

-- this functions in 10.2.24 and the triggerset column is populated with the result of the stored -- function

UPDATE testdata.test_table SET Name = '1_1' WHERE ID = 1;