[MDEV-32275] getting error 'Illegal parameter data types row and bigint for operation '+' ' when using ITERATE in a FOR..DO Created: 2023-07-11  Updated: 2024-02-05  Resolved: 2023-10-04

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.6.12, 10.6.14
Fix Version/s: 11.1.1, 10.4.32, 10.5.23, 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3, 11.2.2, 11.3.1

Type: Bug Priority: Critical
Reporter: Luigi Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: triage
Environment:

Ubuntu 20.04.6 LTS

Issue Links:
Problem/Incident
is caused by MDEV-10580 sql_mode=ORACLE: FOR loop statement Closed
is caused by MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR ... Closed
is caused by MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR ... Closed
is caused by MDEV-12314 sql_mode=ORACLE: Implicit cursor FOR ... Closed
is caused by MDEV-14415 Add Oracle-style FOR loop to sql_mode... Closed

 Description   

Hi,
customer is getting the following error when he comments the DECLARE CONTINUE, while it works if the DECLARE is uncommented:

'Illegal parameter data types row and bigint for operation '+' '

The following procedure was created by the customer to test the ITERATE clause but it's identical to the one used in prod

delimiter //
 
CREATE DEFINER='root'@'%' PROCEDURE 'forIterateBug'()
 
    MODIFIES SQL DATA
 
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 
//



 Comments   
Comment by Luigi [ 2023-08-11 ]

julien.fritsch yes I did get the same error in my test installation using the query/procedure provided by the customer. Thanks!

Comment by Sergei Petrunia [ 2023-09-19 ] 

'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.

Comment by Oleksandr Byelkin [ 2023-09-19 ]

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

Comment by Oleksandr Byelkin [ 2023-09-19 ]

Also as mentioned before I do not find '+' operation in the example

Comment by Oleksandr Byelkin [ 2023-09-19 ]

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();

Comment by Oleksandr Byelkin [ 2023-09-19 ]

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)

Comment by Alexander Barkov [ 2023-09-20 ] 

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.

Comment by Alexander Barkov [ 2023-09-20 ]

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.

Generated at Thu Feb 08 10:30:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.