Error code 1415 not allowed to return a result set from a trigger

For 5.5 and later it is possible to use signals:

delimiter @

create trigger checkcollision 
after update on players 
for each row 
begin 
    declare dummy int default 0; 
    select 1 into dummy from walls where x=NEW.x and y=NEW.y; 
    if (dummy = 1) then 
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message'; 
    end if; 
end @

delimiter ;

For 5.1 and earlier version there is no support for signal. You could try to mimic it with an forced exception, like division by zero or by referencing something that does not exist. You don’t get a nice error message though:

delimiter @

create trigger checkcollision  
after update on players  
for each row  begin
    declare dummy int default 0;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    if (dummy = 1) then
        select 1/0 into dummy; 
    end if;
end @

delimiter ;

A slightly more elegant way is to use an exit handler which is supported in 5.1, still no error message though:

create trigger checkcollision  
after update on players  
for each row  
begin
    declare dummy int default 0; 
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    select 1/0 into dummy;
end @

If no wall is found an empty exit handler is invoked, otherwise the trigger continues and an deliberate error is made.

By adding a dummy table like:

create table dummy (msg varchar(100) primary key);

We can force a primary key violation by inserting the same value twice from the trigger:

delimiter @
create trigger checkcollision  
after update on players  
for each row  begin
    declare dummy int default 0; 
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;     
    select 1 into dummy from walls where x=NEW.x and y=NEW.y; 
    insert into dummy (msg) values ('ERROR: Collision')
                                 , ('ERROR: Collision');  
end @
delimiter ;

We will get an error message like (tested in 10.0.20-MariaDB):

ERROR 1062 (23000): Duplicate entry 'ERROR: Collision' for key 'PRIMARY'

You might want to encapsulate this into a stored procedure:

create procedure my_signal (msg varchar(100)) 
begin 
    insert into dummy (msg) 
    values ('ERROR: Collision')
         , ('ERROR: Collision'); 
end @

which can be called from the trigger:

create trigger checkcollision  
after update on players  
for each row  
begin
    declare dummy int default 0; 
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y; 
    call my_signal('ERROR: Collision');  
end @

M

What you are looking for is the inverse of a GROUP BY aggregate query using the GROUP_CONCAT. If you are willing to store the results in a temp table, I got just the thing.
First, here is the code to use you sample data in a table called prod and a temp table called prodcat to hold the results you are looking for.
use test
drop table if exists prod;
drop table if exists prodcat;
create table prod
(
product_id int not null,
categories varchar(255)
) engine=MyISAM;
create table prodcat
(
product_id int not null,
cat int not null
) engine=MyISAM;
insert into prod values
(10,’9,12′),(11,’8′),(12,’11,18,5′);
select * from prod;

Here it is loaded
mysql> use test
Database changed
mysql> drop table if exists prod;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists prodcat;
Query OK, 0 rows affected (0.00 sec)

mysql> create table prod
-> (
-> product_id int not null,
-> categories varchar(255)
-> ) engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql> create table prodcat
-> (
-> product_id int not null,
-> cat int not null
-> ) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into prod values
-> (10,’9,12′),(11,’8′),(12,’11,18,5′);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from prod;
+————+————+
| product_id | categories |
+————+————+
| 10 | 9,12 |
| 11 | 8 |
| 12 | 11,18,5 |
+————+————+
3 rows in set (0.00 sec)

mysql>

OK, you need query to put together each product_id with each category. Here it is:
select concat(‘insert into prodcat select ‘,product_id,’,cat from (select NULL cat union select ‘,
replace(categories,’,’,’ union select ‘),’) A where cat IS NOT NULL;’) ProdCatQueries from prod;

Here it is executed
mysql> select concat(‘insert into prodcat select ‘,product_id,’,cat from (select NULL cat union select ‘,
-> replace(categories,’,’,’ union select ‘),’) A where cat IS NOT NULL;’) ProdCatQueries from prod;
+———————————————————————————————————————————-+
| ProdCatQueries |
+———————————————————————————————————————————-+
| insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL; |
| insert into prodcat select 11,cat from (select NULL cat union select A where cat IS NOT NULL; |
| insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL; |
+———————————————————————————————————————————-+
3 rows in set (0.00 sec)

mysql>

Let me run each line by hand
mysql> insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into prodcat select 11,cat from (select NULL cat union select A where cat IS NOT NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql>

OK, good. The queries work. Did the prodcat table populate properly?
mysql> select * from prodcat;
+————+——+
| product_id | cat |
+————+——+
| 10 | 9 |
| 10 | 12 |
| 11 | 8 |
| 12 | 11 |
| 12 | 18 |
| 12 | 5 |
+————+——+
6 rows in set (0.00 sec)

mysql>

OK Great. It has the data.
To be honest, I think SQL Server can perform all of this in a single pivot query without a handmade temp table.
I could have taken it to another level and concatenated all the queries into a single query, but the SQL would have been insanely long. If your actual query had 1000s of rows, a single MySQL would not have been practical.
Instead of running the 3 INSERT queries by hand, you could echo the 3 INSERT queries to a text file and execute it as a script. Then, you have a table with the products and categories combinations individually written.