SELECT INTO variable does not work with WITH clause
I have a stored procedure, which worked in MariaDB 10.2 but it does not work in MariaDB 10.4. After some investigation, I found out that if I comment out all INTO clauses, the stored procedure is saved. Could you please help me? Thank you!
This is the stored procedure, which worked fine in MariaDB 10.2. It is a smaller piece of the whole procedure but if the INTO clause from the SELECT is removed, the procedure is saved without error.
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_checkpoint_record` () BEGIN DECLARE qr_code_var VARCHAR(16);
WITH active_enabled_cards AS ( SELECT ROW_NUMBER() OVER() row_num, qr_code FROM cards c JOIN users_link_cards ulc ON c.id = ulc.card_id WHERE c.enabled = 1 AND ulc.active = 1 ), random_card_number AS ( SELECT FLOOR(1 + RAND() * MAX(row_num)) row_num_random FROM active_enabled_cards ) SELECT qr_code INTO qr_code_var FROM active_enabled_cards aec JOIN random_card_number rcn ON aec.row_num = rcn.row_num_random; END$$