Luigi (Inactive) added a comment - 2023-08-11 13:13 julien.fritsch yes I did get the same error in my test installation using the query/procedure provided by the customer. Thanks!

Sergei Petrunia added a comment - 2023-09-19 08:15 'Illegal parameter data types row and bigint for operation '+' ' This doesn't seem to be related to query optimization in any way. Looks like a datatype inference problem. Also, it says operation '+' but I don't see the query doing any addition. This belongs to runtime team.

Oleksandr Byelkin added a comment - 2023-09-19 19:12 - edited could you provide test suite with no syntax error: ' failed: ER_PARSE_ERROR (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''forIterateBug'() MODIFIES SQL DATA BEGIN DECLARE 'loopDone' TINYINT DEFAULT ...' at line 1

Oleksandr Byelkin added a comment - 2023-09-19 19:13 Also as mentioned before I do not find '+' operation in the example

Oleksandr Byelkin added a comment - 2023-09-19 19:49 - edited Thanks Elena, here is correct test suite with no syntax error: delimiter //; CREATE PROCEDURE forIterateBug() BEGIN DECLARE loopDone TINYINT DEFAULT FALSE; FOR _unused IN (SELECT '') DO innerLoop: LOOP IF loopDone THEN LEAVE innerLoop; END IF; SET loopDone = TRUE; BEGIN ITERATE innerLoop; END; END LOOP; END FOR; END// delimiter ;// call forIterateBug();

Oleksandr Byelkin added a comment - 2023-09-19 20:03 it 10.4 it also produce interesting error: CURRENT_TEST: main.test mysqltest: At line 20: query 'call forIterateBug()' failed: 1241: Operand should contain 1 column(s)

Alexander Barkov added a comment - 2023-09-20 00:36 - edited delimiter // CREATE OR REPLACE PROCEDURE forIterateBug() BEGIN DECLARE loopDone TINYINT DEFAULT FALSE ; FOR _unused IN ( SELECT '' ) DO innerLoop: LOOP IF loopDone THEN LEAVE innerLoop; END IF ; SET loopDone = TRUE ; BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END ; ITERATE innerLoop; END ; END LOOP; END FOR ; END // delimiter ; SHOW PROCEDURE CODE forIterateBug; +-----+------------------------------------------------+ | Pos | Instruction | +-----+------------------------------------------------+ | 0 | set loopDone@0 0 | | 1 | cpush [implicit_cursor]@0 | | 2 | cursor_copy_struct [implicit_cursor] _unused@1 | | 3 | copen [implicit_cursor]@0 | | 4 | cfetch [implicit_cursor]@0 _unused@1 | | 5 | jump_if_not 18(18) `[implicit_cursor]`%FOUND | | 6 | jump_if_not 8(8) loopDone@0 | | 7 | jump 16 | | 8 | set loopDone@0 1 | | 9 | hpush_jump 11 2 CONTINUE | | 10 | hreturn 2 | | 11 | set _unused@1 _unused@1 + 1 | <-- This instruction is wrong | 12 | hpop 1 | | 13 | jump 6 | | 14 | hpop 1 | | 15 | jump 6 | | 16 | cfetch [implicit_cursor]@0 _unused@1 | | 17 | jump 5 | | 18 | cpop 1 | +-----+------------------------------------------------+ There is a wrong instruction in the above output: set _unused@1 _unused@1 + 1 _unused@1 is a ROW type variable declared in the FOR header. It's wrong that it gets incremented in the inner loop.

Alexander Barkov added a comment - 2023-09-20 00:44 The problem seems to be in here: │ 7668 bool LEX::sp_continue_loop(THD *thd, sp_label *lab) │ │ 7669 { │ │ >7670 if (lab->ctx->for_loop().m_index) │ │ 7671 { │ │ 7672 // We're in a FOR loop, increment the index variable before backward jump │ │ 7673 sphead->reset_lex(thd); It checks if we're inside a FOR loop. However, it does not check if this FOR loop in the most inner loop.