Prepared statement of INSERT with SELECT fails when types difer

[nuno-faria]

Describe the bug
When preparing a statement of inserts with selects, the prepare fails when the parameters have different types.

To Reproduce

• Create the test table and add some data:

  CREATE TABLE Test (c1 int not null, c2 varchar(255) not null, c3 int not null);
  INSERT INTO Test VALUES (1, 'asd', 1);

• Create a prepared statement of a insert with select:

  PREPARE INSERT INTO Test
  SELECT c1, ?, ?
  FROM Test;
  -- or PREPARE INSERT INTO Test (c1, c2, c3) SELECT ...
  
  > Internal error while compiling statement: TypeException:user.p0_0[16]:'algebra.select' undefined in:     X_15:any := algebra.select(X_14:bat[:int], X_16:str, X_16:str, true:bit, true:bit, false:bit);

Additional notes

• If both parameters have the same type, the prepare works correctly. E.g.:

  CREATE TABLE Test (c1 int not null, c2 varchar(255) not null, c3 varchar(255) null);
  INSERT INTO Test VALUES (1, 'asd', 'asd');
  
  PREPARE INSERT INTO Test
  SELECT c1, ?, ?
  FROM Test;
  
  > execute prepared statement using: EXEC 0(...)
  +---------+--------+-------+--------+-------+--------+
  | type    | digits | scale | schema | table | column |
  +=========+========+=======+========+=======+========+
  | varchar |    255 |     0 | null   | null  | null   |
  | varchar |    255 |     0 | null   | null  | null   |
  +---------+--------+-------+--------+-------+--------+

• The error also does not occur when all the columns are custom parameters, i.e.:

  PREPARE INSERT INTO Test
  SELECT ?, ?, ?
  FROM Test;
  
  > execute prepared statement using: EXEC 0(...)
  +---------+--------+-------+--------+-------+--------+
  | type    | digits | scale | schema | table | column |
  +=========+========+=======+========+=======+========+
  | int     |     32 |     0 | null   | null  | null   |
  | varchar |    255 |     0 | null   | null  | null   |
  | int     |     32 |     0 | null   | null  | null   |
  +---------+--------+-------+--------+-------+--------+

• The error also does not occur when we remove the not null from the table definition. However, the types are not correct:

  CREATE TABLE Test (c1 int, c2 varchar(255), c3 int);
  INSERT INTO Test VALUES (1, 'asd', 1);
  
  PREPARE INSERT INTO Test
  SELECT c1, ?, ?
  FROM Test;
  
  > execute prepared statement using: EXEC 0(...)
  +------+--------+-------+--------+-------+--------+
  | type | digits | scale | schema | table | column |
  +======+========+=======+========+=======+========+
  | int  |     32 |     0 | null   | null  | null   | -- should be varchar
  | int  |     32 |     0 | null   | null  | null   |
  +------+--------+-------+--------+-------+--------+

  And if we try to use the prepared statement:

  -- trying to pass a string to c2
  EXEC 0('asd', 1);
  > conversion of string 'asd' to type int failed.
  
  -- or trying to pass an integer to c2
  EXEC 0(1, 1);
  > Append failed

Software versions

• MonetDB v11.43.0 (Jan22 branch)
• Ubuntu 20.04
• Self-installed and compiled

[PedroTadim]

Found the issue. Tomorrow I will push the fix.

[nuno-faria]

Can confirm that it is now working correctly. Thanks.