Why do I get ERROR 4078 (HY000): Illegal parameter data types int and row for operation '='?

Question

I am using MariaDB 11.0.2 and my database contains two tables. One with a pretty normal primary key and a column for naming tests.

CREATE TABLE `Tests` (
  `Test_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Test_Name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Test_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

It contains some 34 fields.

Then I have another table, where I enter the results of tests, and has a foreign key for its own Test_ID column that references the previous one.

CREATE TABLE `Test_Results` (
  `Test_ID` int(11) DEFAULT NULL,
  `Numerator` tinyint(1) NOT NULL,
  `Denominator` tinyint(1) DEFAULT NULL,
  `Test_Num` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Test_Num`),
  KEY `Test_ID_Match` (`Test_ID`),
  CONSTRAINT `Test_ID_Match` FOREIGN KEY (`Test_ID`) REFERENCES `Tests` (`Test_ID`) ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=2456 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The denominators are entered automatically by a trigger

CREATE DEFINER=`root`@`localhost` TRIGGER Automatic_Denominators
BEFORE INSERT ON Test_Results
FOR EACH ROW
BEGIN
    IF NEW.Test_ID in (3, 12, 14, 16, 19, 29) THEN
        SET NEW.Denominator = 4;
    ELSEIF NEW.Test_ID in (11, 21) THEN
        SET NEW.Denominator = 5;
    ELSEIF NEW.Test_ID in (25, 36) THEN
        SET NEW.Denominator = 6;
    ELSEIF NEW.Test_ID in (2, 9, 17) THEN
        SET NEW.Denominator = 7;
    ELSEIF NEW.Test_ID in (5, 27, 28) THEN
        SET NEW.Denominator = 8;
    ELSEIF NEW.Test_ID = 1 THEN
        SET NEW.Denominator = 12;
    ELSEIF NEW.Test_ID = (4, 8) THEN
        SET NEW.Denominator = 15;
    ELSEIF NEW.Test_ID in (6, 7, 22, 30) THEN
        SET NEW.Denominator = 20;
    ELSEIF NEW.Test_ID in (0) THEN
        SET NEW.Denominator = 26;
    END IF;
END

Here is the funny thing:

MariaDB [Tutors_Prime]> insert into Test_Results (Test_ID, Numerator) VALUES (0, 26);
MariaDB [Tutors_Prime]> insert into Test_Results (Test_ID, Numerator) VALUES (4, 26);

These two entries with test IDs 0 and 4 return ERROR 4078 (HY000): Illegal parameter data types int and row for operation '='

MariaDB [Tutors_Prime]> insert into Test_Results (Test_ID, Numerator) VALUES (5, 26);

but any other test IDs will be entered correctly. This is strange because 0, 4 and 5 are all present in the 'Test' table, but only those two numbers return the error.

I have tried changing the faulty primary key values with ON UPDATE CASCADE, so my data still makes sense. But if I change the primary key from 0 to 100, the error persists, and now:

MariaDB [Tutors_Prime]> insert into Test_Results (Test_ID, Numerator) VALUES (100, 26);

also spits back the error.

Any ideas why? Honestly, I don't even understand the error code.

Thank you!

Answer 1

Change

ELSEIF NEW.Test_ID = (4, 8) THEN to ELSEIF NEW.Test_ID IN (4, 8) THEN