SELECT inside trigger gives wrong result (it ignores the WHERE clause) 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
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 | '&lt;select expression&gt; INTO &lt;destination&gt;;' is deprecated and will be removed in a future release. Please use 'SELECT &lt;select list&gt; INTO &lt;destination&gt; 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';