Innodb – SELECT inside trigger gives wrong result (it ignores the WHERE clause)

innodbmariadbtrigger

When I run this code inside an after UPDATE trigger:

SELECT * FROM `stock_totals` WHERE `product_id` = 2 AND `stock_place_id` = 3 INTO OUTFILE 'query3.csv'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY '\n';

I get this into query3.csv: (field order: product_id, stock_place_id, date, quantity)

"1","3","2020-01-01","42"

Notice how the resulting row does not match the WHERE clause: the stock_place_id is correct, but the product_id is wrong (I got 1, but it should be 2).

When the same query is ran alone, I get an empty table which is the expected result given my data:

+------------+----------------+------------+----------+
| product_id | stock_place_id | date       | quantity |
+------------+----------------+------------+----------+
|          1 |              1 | 2020-01-01 |      -42 |
|          1 |              3 | 2020-01-01 |       42 |
+------------+----------------+------------+----------+

I'm running MariaDB version 10.4.12 and innodb_version 10.4.12 on Arch Linux.

The full query for the stock_totals table is:

CREATE TABLE `stock_totals` (
 `product_id` int(11) NOT NULL,
 `stock_place_id` int(11) NOT NULL,
 `date` date NOT NULL,
 `quantity` int(11) NOT NULL,
 PRIMARY KEY (`product_id`,`stock_place_id`,`date`),
 KEY `fk_stock_totals_stock_place1_idx` (`stock_place_id`),
 CONSTRAINT `fk_stock_totals_product1` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_stock_totals_stock_place1` FOREIGN KEY (`stock_place_id`) REFERENCES `stock_place` (`place_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Best Answer

I've just tested this with MariaDB 10.4.12 with innodb_version 10.4.12 on Fedora Linux, but I don't see the problem you have - I get an empty query3.csv file. The only difference I have (besides the Linux distro) is that I don't have your foreign key constraints since I don't have those tables.

Are you sure you're looking at the correct output file?

Note also that you will get a warning when you run the query or create a trigger with that query:

SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                               |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

So you could try moving the FROM and WHERE clauses to the end of the statement. I get the same result with this, just without the warning.

You can also try enumerating the columns in the SELECT instead of SELECT *, just to see if it changes anything.

This is my trigger:

CREATE TRIGGER trigger1 AFTER UPDATE ON stock_totals 
  FOR EACH ROW 
    SELECT * FROM `stock_totals` 
    WHERE `product_id` = 2 AND `stock_place_id` = 3 
    INTO OUTFILE '/tmp/query10.csv'  
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'         
    LINES TERMINATED BY '\n';
Related Question